分区表对绑定变量的影响

发表人:dbaoracle 来源：dbaoracle.itpub.net

最近碰见一例，CBO对使用绑定变量的涉及到分区表的SQL,没有产生高效的执行计划。原因是，使用绑定变量的时候，CBO选择的是分区表的全局统计信息；而有些情况，表的全局statistics与单个分区的statistics不一致或者CBO不够聪明，产生的执行计划没有效率。

一个现象就是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要仔细考虑。