一个现象就是sql在存储过程中运行十分慢,而直接在sqlplus里面运行却很快返回。sql在存储过程中使用了绑定变量,在sqlplus里面运行的时候却没有。
batch执行一简单存储过程,使用并行进程,报错 ERROR at line 1:
ORA-20000: ORA-12801: error signaled in parallel query server P004
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP
ORA-06512: at line 6
经过10046 trace得到sql
select /*+ USE_HASH (a b c) PARALLEL(a,4) PARALLEL(b,4) PARALLEL(c,4)
*/ :1 AS cob_date ,a.pos_id ,b.OR
G_ID ,b.BK_ID
,b.ACCT_ID ,b.LDGR_ID
,b.PRIN_INC_IND ,b.INSTR_ID
,b.LOCAL_CURR_CDE ,b.MKT_OID
,b.STRATEGY_ID ,b.CNTPRTY
_ROW_OID ,a.qty ,c.a
mt ,a.owning_location f
rom staging_posh a ,ft_t_posn_t b
,(select pos_id ,amt
from staging_cavh where owning_
location = :2 ) c where a.pos_
id = b.pos_id and a.pos_id = c.pos_id and a
.owning_location = :3
使用了hash提示,和并行; 该sql如果不用绑定变量,在sqlplus中快速返回。
去掉并行提示,比较bind和no bind的执行计划 (如果sql本身执行计划不好,并行也无助于是)
SQL> explain plan for
2 select /*+ USE_HASH (a b c) */
to_date(''23082005'',''ddmmyyyy'') AS cob_date,a.pos_id , b.ORG_ID,b.BK_ID
3 ,b.ACCT_ID,b.LDGR_ID ,b.PRIN_INC_IND ,b.INSTR_ID,b.LOCAL_CURR_CDE ,
b.MKT_OID,b.STRATEGY_ID ,b.CNTPRTY_ROW_OID ,a.qty ,c.amt ,a.owning_location
4 5 from staging_posh a ,ft_t_posn_t b ,(select pos_id,amt from staging_cavh where owning_location = ''EUR'' ) c where a.pos_id = b.pos_id
6 and a.pos_id = c.pos_id and a.owning_location = ''EUR'' ; < /FONT >
Explained.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 689M| 68G| 101 | | |
| HASH JOIN | | 689M| 68G| 101 | | |
| INDEX FAST FULL SCAN |STAGING_C | 26K| 513K| 14 | 2 | 2 |
| HASH JOIN | | 26K| 2M| 69 | | |
| INDEX FAST FULL SCAN |STAGING_P | 26K| 487K| 15 | 2 | 2 |
| TABLE ACCESS FULL |FT_T_POSN | 32K| 2M| 36 | | |
--------------------------------------------------------------------------------
9 rows selected.
SQL> explain plan for select /*+ USE_HASH (a b c) */
2 :1 AS cob_date,a.pos_id , b.ORG_ID,b.BK_ID
3 ,b.ACCT_ID,b.LDGR_ID ,b.PRIN_INC_IND ,b.INSTR_ID,b.LOCAL_CURR_CDE ,
b.MKT_OID,b.STRATEGY_ID ,b.CNTPRTY_ROW_OID ,a.qty ,c.amt ,a.owning_location
4 from staging_posh a ,ft_t_posn_t b ,(select pos_id,amt from staging_cavh where owning_location = :2 ) c where a.pos_id = b.pos_id
5 and a.pos_id = c.pos_id and a.owning_location = :3; < /FONT >
Explained.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 84 | 45 | | |
| HASH JOIN | | 1 | 84 | 45 | | |
| PARTITION RANGE SINGLE | | | | | KEY | KEY |
| INDEX RANGE SCAN |STAGING_P | 1 | 8 | 4 | KEY | KEY |
| MERGE JOIN CARTESIAN | | 32K| 2M| 40 | | |
| PARTITION RANGE SINGLE | | | | | KEY | KEY |
| INDEX RANGE SCAN |STAGING_C | 1 | 8 | 4 | KEY | KEY |
| SORT JOIN | | 32K| 2M| 36 | | |
| TABLE ACCESS FULL |FT_T_POSN | 32K| 2M| 36 | | |
如上,使用绑定变量的情况下,CBO无法在分析的时候知道使用哪个partition作prunc. 因此执行计划中有很多KEY. 有些情况下,CBO根据全局统计信息无法判断最好的执行计划。
因此在使用分区的时候,bind var要仔细考虑。