分区表对绑定变量的影响计划

最近碰见一例，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 ,s_id ,

G_ID ,_ID

,CT_ID ,GR_ID

,IN_INC_IND ,STR_ID

,CAL_CURR_CDE ,T_OID

,RATEGY_ID ,TPRTY

_ROW_OID ,y ,c.a

mt ,ning_location f

rom staging_posh a ,ft_t_posn_t b

,(select pos_id ,amt

from staging_cavh where owning_

location = :2 ) c where s_

id = s_id and s_id = s_id and a

.owning_location = :3

使用了hash提示，和并行； 该sql如果不用绑定变量，在sqlplus中快速返回。

去掉并行提示，比较bind和no bind的执行计划 （如果sql本身执行计划不好，并行也无助于是）

SQL explain plan for

Google无人驾驶汽车通过合理调配车辆来解决堵车问题 2 select /*+ USE_HASH (a b c) */

to_date(\'\',\'ddmmyyyy\') AS cob_date,s_id , _ID,_ID

3 ,CT_ID,GR_ID ,IN_INC_IND ,STR_ID,CAL_CURR_CDE ,T_OID,RATEGY_ID ,TPRTY_ROW_OID ,y ,t ,ning_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 s_id = s_id

6 and s_id = s_id and ning_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,s_id , _ID,_ID

3 ,CT_ID,GR_ID ,IN_INC_IND ,STR_ID,CAL_CURR_CDE ,T_OID,RATEGY_ID ,TPRTY_ROW_OID ,y ,t ,ning_location

4 from staging_posh a ,ft_t_posn_t b ,(select pos_id,amt from staging_cavh where owning_location = :2 ) c where s_id = s_id

5 and s_id = s_id and ning_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要仔细考虑。

查看本文来源