oracle绑定变量窥探,动态SQL的dbms_sql.bind_variable无法触发绑定变量窥探

对表格的2个数据分布不均衡的列都做了直方图的统计，今天跟踪系统，发现居然没有触发绑定变量窥探。

程序是用动态SQL开发的一个共用的功能，其中更就用到了：

L_PROCESS_PHASE := 2;

IF P_COL_VALUE_TAB.COUNT > 0 THEN

FOR I IN 1..P_COL_VALUE_TAB.LAST LOOP

IF P_COL_VALUE_TAB.exists(I) THEN

dbms_sql.bind_variable(t_c, ':'||I, P_COL_VALUE_TAB(I).COL_VALUE);

IF G_DEBUG_MODE THEN

DEBUGLOG('绑定变量--'||I||':'||P_COL_VALUE_TAB(I).COL_VALUE);

END IF;

END IF;

END LOOP;

END IF;

没窥探绑定变量，导致数据库还是走了不合适的执行计划。请问怎么破？

数据库版本是11gr2。正常来说应该会bind peeking才对的。

用应用的Form功能来查询的时候，会触发窥探：

Peeked Binds (identified by position):

--------------------------------------

1 - (CHAR(30), CSID=873): 'DB1'

2 - (NUMBER): 106

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

OPT_PARAM('_b_tree_bitmap_plans' 'false')

OPT_PARAM('_fast_full_scan_enabled' 'false')

OPT_PARAM('_optimizer_join_factorization' 'false')

ALL_ROWS

OUTLINE_LEAF(@"SEL$4")

OUTLINE_LEAF(@"SEL$5")

OUTLINE_LEAF(@"SEL$6")

OUTLINE_LEAF(@"SEL$BDE7108E")

MERGE(@"SEL$3512B053")

OUTLINE_LEAF(@"SEL$45EEE623")

MERGE(@"SEL$0DDF58A2")

OUTLINE_LEAF(@"SEL$5C160134")

MERGE(@"SEL$335DD26A")

OUTLINE(@"SEL$21D54C12")

UNNEST(@"SEL$8")

OUTLINE(@"SEL$3512B053")

OUTLINE(@"SEL$15BD0953")

UNNEST(@"SEL$10")

OUTLINE(@"SEL$0DDF58A2")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$335DD26A")

MERGE(@"SEL$3")

OUTLINE(@"SEL$23A33D4B")

OUTLINE(@"SEL$8")

OUTLINE(@"SEL$778796C4")

OUTLINE(@"SEL$10")

OUTLINE(@"SEL$2")

OUTLINE(@"SEL$3")

OUTLINE(@"SEL$7")

OUTLINE(@"SEL$9")

INDEX_RS_ASC(@"SEL$5C160134" "MP"@"SEL$3" ("MTL_PARAMETERS"."ORGANIZATION_ID"))

INDEX_RS_ASC(@"SEL$5C160134" "XJBM"@"SEL$3" ("XYG_JBW_BATCH_MOVE"."ORGANIZATION_ID"

"XYG_JBW_BATCH_MOVE"."FACTORY_CODE" "XYG_JBW_BATCH_MOVE"."MAKE_DATE" "XYG_JBW_BATCH_MOVE"."OPERATION_CODE"))

INDEX_RS_ASC(@"SEL$5C160134" "BSO"@"SEL$3" ("BOM_STANDARD_OPERATIONS"."ORGANIZATION_ID"))

INDEX_RS_ASC(@"SEL$5C160134" "NEXT_BSO"@"SEL$3" ("BOM_STANDARD_OPERATIONS"."ORGANIZATION_ID"))

INDEX_RS_ASC(@"SEL$5C160134" "PREV_BSO"@"SEL$3" ("BOM_STANDARD_OPERATIONS"."ORGANIZATION_ID"))

INDEX_RS_ASC(@"SEL$5C160134" "XJOL"@"SEL$3" ("XYG_JBO_ORDER_LINES_ALL"."ORDER_LINE_ID"))

FULL(@"SEL$5C160134" "XJMN"@"SEL$3")

FULL(@"SEL$5C160134" "XJIB"@"SEL$3")

INDEX_RS_ASC(@"SEL$5C160134" "XJOH"@"SEL$3" ("XYG_JBO_ORDER_HEADERS_ALL"."ORDER_HEADER_ID"))

FULL(@"SEL$5C160134" "HCA"@"SEL$3")

FULL(@"SEL$5C160134" "HP"@"SEL$3")

INDEX_RS_ASC(@"SEL$5C160134" "XJOS"@"SEL$3" ("XYG_JBW_OPERATION_SCHEDULE"."SCHEDULE_ID"))

FULL(@"SEL$5C160134" "FU"@"SEL$3")

INDEX_RS_ASC(@"SEL$5C160134" "WDJ"@"SEL$3" ("WIP_DISCRETE_JOBS"."WIP_ENTITY_ID"

"WIP_DISCRETE_JOBS"."ORGANIZATION_ID"))

NO_ACCESS(@"SEL$5C160134" "PPFM"@"SEL$3")

NO_ACCESS(@"SEL$5C160134" "PPFH"@"SEL$3")

LEADING(@"SEL$5C160134" "MP"@"SEL$3" "XJBM"@"SEL$3" "BSO"@"SEL$3" "NEXT_BSO"@"SEL$3" "PREV_BSO"@"SEL$3"

"XJOL"@"SEL$3" "XJMN"@"SEL$3" "XJIB"@"SEL$3" "XJOH"@"SEL$3" "HCA"@"SEL$3" "HP"@"SEL$3" "XJOS"@"SEL$3" "FU"@"SEL$3"

"WDJ"@"SEL$3" "PPFM"@"SEL$3" "PPFH"@"SEL$3")

USE_NL(@"SEL$5C160134" "XJBM"@"SEL$3")

USE_HASH(@"SEL$5C160134" "BSO"@"SEL$3")

USE_HASH(@"SEL$5C160134" "NEXT_BSO"@"SEL$3")

USE_HASH(@"SEL$5C160134" "PREV_BSO"@"SEL$3")

USE_NL(@"SEL$5C160134" "XJOL"@"SEL$3")

USE_HASH(@"SEL$5C160134" "XJMN"@"SEL$3")

USE_HASH(@"SEL$5C160134" "XJIB"@"SEL$3")

USE_NL(@"SEL$5C160134" "XJOH"@"SEL$3")

USE_HASH(@"SEL$5C160134" "HCA"@"SEL$3")

USE_HASH(@"SEL$5C160134" "HP"@"SEL$3")

USE_NL(@"SEL$5C160134" "XJOS"@"SEL$3")

USE_HASH(@"SEL$5C160134" "FU"@"SEL$3")

USE_NL(@"SEL$5C160134" "WDJ"@"SEL$3")

USE_HASH(@"SEL$5C160134" "PPFM"@"SEL$3")

USE_HASH(@"SEL$5C160134" "PPFH"@"SEL$3")

SWAP_JOIN_INPUTS(@"SEL$5C160134" "BSO"@"SEL$3")

SWAP_JOIN_INPUTS(@"SEL$5C160134" "NEXT_BSO"@"SEL$3")

SWAP_JOIN_INPUTS(@"SEL$5C160134" "PREV_BSO"@"SEL$3")

SWAP_JOIN_INPUTS(@"SEL$5C160134" "FU"@"SEL$3")

SWAP_JOIN_INPUTS(@"SEL$5C160134" "PPFM"@"SEL$3")

SWAP_JOIN_INPUTS(@"SEL$5C160134" "PPFH"@"SEL$3")

FULL(@"SEL$BDE7108E" "PPF"@"SEL$7")

FULL(@"SEL$BDE7108E" "PER1"@"SEL$8")

LEADING(@"SEL$BDE7108E" "PPF"@"SEL$7" "PER1"@"SEL$8")

USE_HASH(@"SEL$BDE7108E" "PER1"@"SEL$8")

SWAP_JOIN_INPUTS(@"SEL$BDE7108E" "PER1"@"SEL$8")

USE_HASH_AGGREGATION(@"SEL$BDE7108E")

FULL(@"SEL$45EEE623" "PPF"@"SEL$9")

FULL(@"SEL$45EEE623" "PER1"@"SEL$10")

LEADING(@"SEL$45EEE623" "PPF"@"SEL$9" "PER1"@"SEL$10")

USE_HASH(@"SEL$45EEE623" "PER1"@"SEL$10")

SWAP_JOIN_INPUTS(@"SEL$45EEE623" "PER1"@"SEL$10")

USE_HASH_AGGREGATION(@"SEL$45EEE623")

INDEX_RS_ASC(@"SEL$6" "**B"@"SEL$6" ("XYG_JBW_PACK_BOX"."PACK_BOX_ID"))

INDEX_RS_ASC(@"SEL$5" "XJMPL"@"SEL$5" ("XYG_JBW_MANU_PROCESS_LAYER"."NEED_ID"

"XYG_JBW_MANU_PROCESS_LAYER"."LEVEL_TAG"))

INDEX_RS_ASC(@"SEL$4" "XJMPL"@"SEL$4" ("XYG_JBW_MANU_PROCESS_LAYER"."NEED_ID"

"XYG_JBW_MANU_PROCESS_LAYER"."LEVEL_TAG"))

END_OUTLINE_DATA

*/