查看真实的执行计划绑定变量对执行计划的影响--“绑定变量窥探”

最新推荐文章于 2022-05-20 11:00:00 发布

weixin_33763244

最新推荐文章于 2022-05-20 11:00:00 发布

阅读量266

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/cure-t-x-y/p/4252035.html

本文深入探讨了Oracle数据库中执行计划的生成与分析方法，包括如何使用DBMS_XPLAN包的不同功能来查看执行计划，理解执行计划中的操作符及统计信息，并通过实例展示了绑定变量对执行计划的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--#####################################################
   
--####     AWR执行计划                             ####
   
--#####################################################
   
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('8qfs8857jc8fw',NULL,NULL,'ADVANCED')); 
   
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('8qfs8857jc8fw'))
   
SELECT TF.* FROM DBA_HIST_SQLTEXT HT, TABLE(DBMS_XPLAN.DISPLAY_AWR(HT.SQL_ID, NULL, NULL, 'all')) TF WHERE HT.SQL_TEXT LIKE '%mac%';

--#####################################################
   
--####     dbms_xplan.display_cursor方式查看       ####
   
--#####################################################
   
SQL> conn cqwr
   /cqwr
   
SELECT T_LTE_CARD_INFO.PK, 
   
        T_LTE_CARD_INFO.IMSI, 
   
        T_LTE_CARD_INFO.ICCID, 
   
        T_LTE_CARD_INFO.PIN1, 
   
        T_LTE_CARD_INFO.PIN2, 
   
        T_LTE_CARD_INFO.PUK1, 
   
        T_LTE_CARD_INFO.PUK2, 
   
        T_LTE_CARD_INFO.KI, 
   
        T_LTE_CARD_INFO.EKI, 
   
        T_LTE_CARD_INFO.STATUS 
   
FROM T_LTE_CARD_INFO SAMPLE (
   5), 
   
        T_HLR_MSISDN 
   
WHERE T_HLR_MSISDN.HLR_PK 
   ='HLR12'
AND T_LTE_CARD_INFO.SUB_IMSI 
   ='46002'
AND T_LTE_CARD_INFO.MSISDN 
   = T_HLR_MSISDN.PK 
   
AND ROWNUM 
   <=5

select A.SQL_ID,A.HASH_VALUE,A.CHILD_NUMBER,A.SQL_TEXT 
   from v$
   sql a 
   where A.SQL_TEXT 
   like'SELECT T_LTE_CARD_INFO.PK,%'
-- 0jq118yf3w76u    
   26213163140

- - 传入HASH_VALUE
select * from table (dbms_xplan.display_cursor( 2621316314 , 0 , 'ADVANCED' ));

- - 传入SQL_ID
- - - 为了获取缓存库中的执行计划，可以直接查询动态性能视图v$sql_plan和v$sql_plan_statistics_all等，但更方便的方法是：
select t. * from v$ sql s, table (dbms_xplan.display_cursor(s.sql_id,s.child_number, 'allstats last' )) t where s.sql_id = '0jq118yf3w76u' ;

- - - 当前查看的方式如下
SQL > select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SQL_ID  b4vp4vx8q5jny, child number 0
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SELECT T_LTE_CARD_INFO.PK,        T_LTE_CARD_INFO.IMSI,        T_LTE_CARD_INFO.ICCID,
   T_LTE_CARD_INFO.PIN1,        T_LTE_CARD_INFO.PIN2,        T_LTE_CARD_INFO.PUK1,
T_LTE_CARD_INFO.PUK2,        T_LTE_CARD_INFO.KI,        T_LTE_CARD_INFO.EKI,
T_LTE_CARD_INFO.STATUS    FROM T_LTE_CARD_INFO SAMPLE ( 5 ),        T_HLR_MSISDN   WHERE
T_HLR_MSISDN.HLR_PK = 'HLR12'      AND T_LTE_CARD_INFO.SUB_IMSI = '46002'      AND
T_LTE_CARD_INFO.MSISDN = T_HLR_MSISDN.PK     AND ROWNUM < = 5

Plan hash value : 1908090178

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  | Operation                       | Name             | Rows   | Bytes | Cost (%CPU)| Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 | SELECT STATEMENT                |                 |       |       |      8 ( 100 )|          |
| *    1 |   COUNT STOPKEY                 |                 |       |       |            |          |
|    2 |    TABLE ACCESS BY INDEX ROWID  | T_LTE_CARD_INFO |      6 |   1308 |      6    ( 0 )| 00:00: 01 |
|    3 |    NESTED LOOPS                |                 |      6 |   1542 |      8    ( 0 )| 00:00: 01 |
|    4 |      TABLE ACCESS BY INDEX ROWID| T_HLR_MSISDN    |     38 |   1482 |      2    ( 0 )| 00:00: 01 |
| *    5 |       INDEX RANGE SCAN          | IND_HLR_PK      |       |       |      1    ( 0 )| 00:00: 01 |
| *    6 |      INDEX RANGE SCAN           | IND_MS_SU       |      6 |       |      5    ( 0 )| 00:00: 01 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Predicate Information (identified by operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    1 - filter(ROWNUM < = 5 )
    5 - access ( "T_HLR_MSISDN" . "HLR_PK" = 'HLR12' )
    6 - access ( "T_LTE_CARD_INFO" . "MSISDN" = "T_HLR_MSISDN" . "PK" AND
               "T_LTE_CARD_INFO" . "SUB_IMSI" = '46002' )
       filter(ORA_HASH(ROWID, 0 , 2038870484 , 'SYS_SAMPLE' , 0 ) < 214748365 )
32 rows selected.

- - - - 查看执行计划中的获取a - rows 信息 E ROWS 预估的行数 - - - - -
- - - - 需要 alter session set statistics_level = all 或 / * + gather_plan_statistics * /

SQL > select * from table (dbms_xplan.display_cursor( null , null , 'ALLSTATS LAST' ));

SQL > select * from table (dbms_xplan.display_cursor( null , null , 'ADVANCED' ));

SQL > select * from table (dbms_xplan.display_cursor( null , null , 'ADVANCED ALLSTATS LAST PEEKED_BINDS' ));

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SQL_ID  b4vp4vx8q5jny, child number 1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SELECT T_LTE_CARD_INFO.PK,        T_LTE_CARD_INFO.IMSI,        T_LTE_CARD_INFO.ICCID,
T_LTE_CARD_INFO.PIN1,        T_LTE_CARD_INFO.PIN2,        T_LTE_CARD_INFO.PUK1,
T_LTE_CARD_INFO.PUK2,        T_LTE_CARD_INFO.KI,        T_LTE_CARD_INFO.EKI,
T_LTE_CARD_INFO.STATUS    FROM T_LTE_CARD_INFO SAMPLE ( 5 ),        T_HLR_MSISDN   WHERE
T_HLR_MSISDN.HLR_PK = 'HLR12'      AND T_LTE_CARD_INFO.SUB_IMSI = '46002'      AND
T_LTE_CARD_INFO.MSISDN = T_HLR_MSISDN.PK     AND ROWNUM < = 5

Plan hash value : 1908090178

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  | Operation                       | Name             | Starts | E - Rows | A - Rows |   A - Time    | Buffers |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| *    1 |   COUNT STOPKEY                 |                 |       1 |        |       5 |00:00:00. 01 |       19 |
|    2 |    TABLE ACCESS BY INDEX ROWID  | T_LTE_CARD_INFO |       1 |       6 |       5 |00:00:00. 01 |       19 |
|    3 |    NESTED LOOPS                |                 |       1 |       6 |       8 |00:00:00. 01 |       15 |
|    4 |      TABLE ACCESS BY INDEX ROWID| T_HLR_MSISDN    |       1 |      38 |       3 |00:00:00. 01 |        3 |
| *    5 |       INDEX RANGE SCAN          | IND_HLR_PK      |       1 |        |       3 |00:00:00. 01 |        2 |
| *    6 |      INDEX RANGE SCAN           | IND_MS_SU       |       3 |       6 |       5 |00:00:00. 01 |       12 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified by operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    1 - filter(ROWNUM < = 5 )
    5 - access ( "T_HLR_MSISDN" . "HLR_PK" = 'HLR12' )
    6 - access ( "T_LTE_CARD_INFO" . "MSISDN" = "T_HLR_MSISDN" . "PK" AND
               "T_LTE_CARD_INFO" . "SUB_IMSI" = '46002' )
       filter(ORA_HASH(ROWID, 0 , 1153052846 , 'SYS_SAMPLE' , 0 ) < 214748365 )

31 rows selected.

- - #####################################################
- - ####     绑定变量对执行计划的影响 “绑定变量窥探” ####
- - #####################################################
SQL > CREATE TABLE t1 AS  SELECT * FROM DBA_OBJECTS ;

SQL > create index idx_t1 on t1 (object_id);

SQL> select count(*) from t1;

COUNT(*)

----------

74789

SQL> select count(distinct(object_id)) from t1;

COUNT(DISTINCT(OBJECT_ID))

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

74789

- - - 收集统计信息
BEGIN
   sys.DBMS_STATS.gather_table_stats (
      ownname            => 'CQWR',
      tabname            => 'T1',
      estimate_percent   => NULL,
      method_opt         => 'for all indexed columns size auto',
      cascade            => TRUE);
END;

- - 先来看不执行绑定变量时候的执行计划
SQL > set wrap off
SQL > select count(*) from  t1 where object_id between 999 and 1000;

   COUNT ( * )
- - - - - - - - - -
          2

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID arym7r4js4gy2, child number 0

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

select count(*) from t1 where object_id between 999 and 1000

Plan hash value: 1970818898

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

PLAN_TABLE_OUTPUT

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

|* 2 | INDEX RANGE SCAN| IDX_T1 | 3 | 15 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID">=999 AND "OBJECT_ID"<=1000)

19 rows selected.

SQL>

SQL > select count(*) from  t1 where object_id between 0 and 50000;

   COUNT ( * )
- - - - - - - - - -
      47490

SQL > select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SQL_ID   3qwssaf8f9kgt , child number 0
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
select count ( * ) from   t1 where object_id between 0 and 50000

Plan hash value : 2101382132

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  | Operation              | Name    | Rows   | Bytes | Cost (%CPU)| Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 | SELECT STATEMENT       |        |       |       |     27 ( 100 )|          |
|    1 |  SORT AGGREGATE        |        |      1 |      5 |            |          |

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| *    2 |    INDEX FAST FULL SCAN| IDX_T1 | 46783 |    228K |     27    ( 4 )| 00:00: 01 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Predicate Information (identified by operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    2 - filter(( "OBJECT_ID" < = 50000 AND "OBJECT_ID" > = 0 ))
19 rows selected.

和我们预期的一致  INDEX RANGE SCAN  和  INDEX FAST FULL SCAN

我们使用绑定变量的方式来执行 SQL

SQL > var x number
SQL > var y number
SQL > exec :x :=99
PL / SQL procedure successfully completed.

SQL > exec :y :=100
PL / SQL procedure successfully completed.

SQL > select count(*) from  t1 where object_id between :x and :y ;

   COUNT ( * )
- - - - - - - - - -
          2

SQL>

alter session set statistics_level=all ;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

PLAN_TABLE_OUTPUT

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

SQL_ID 7pz1dg089j86a, child number 1

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

select count(*) from t1 where object_id between :x and :y

Plan hash value: 2351893609

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

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

| 0 | SELECT STATEMENT | | 1 | | | 2 (100)|

| 1 | SORT AGGREGATE | | 1 | 1 | 5 | |

PLAN_TABLE_OUTPUT

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

|* 2 | FILTER | | 1 | | | |

|* 3 | INDEX RANGE SCAN| IDX_T1 | 1 | 3 | 15 | 2 (0)| 00:

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

3 - SEL$1 / T1@SEL$1

Outline Data

PLAN_TABLE_OUTPUT

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

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

END_OUTLINE_DATA

PLAN_TABLE_OUTPUT

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

Peeked Binds (identified by position):

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

1 - (NUMBER): 99

2 - (NUMBER): 100

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

2 - filter(:X<=:Y)

3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

Column Projection Information (identified by operation id):

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

1 - (#keys=0) COUNT(*)[22]

52 rows selected.

--重新绑定变量为0 --- 50000

SQL > exec :x :=0
PL / SQL procedure successfully completed.
SQL > exec :y :=50000
PL / SQL procedure successfully completed.

SQL > select count(*) from  t1 where object_id between :x and :y ;

   COUNT ( * )
- - - - - - - - - -
      47490

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

PLAN_TABLE_OUTPUT

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

SQL_ID 7pz1dg089j86a, child number 1

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

select count(*) from t1 where object_id between :x and :y

Plan hash value: 2351893609

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

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

| 0 | SELECT STATEMENT | | 1 | | | 2 (100)|

| 1 | SORT AGGREGATE | | 1 | 1 | 5 | |

PLAN_TABLE_OUTPUT

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

|* 2 | FILTER | | 1 | | | |

|* 3 | INDEX RANGE SCAN| IDX_T1 | 1 | 3 | 15 | 2 (0)| 00:

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

3 - SEL$1 / T1@SEL$1

Outline Data

PLAN_TABLE_OUTPUT

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

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

END_OUTLINE_DATA

PLAN_TABLE_OUTPUT

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

Peeked Binds (identified by position):

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

1 - (NUMBER): 99

2 - (NUMBER): 100

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

2 - filter(:X<=:Y)

3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

Column Projection Information (identified by operation id):

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

1 - (#keys=0) COUNT(*)[22]

52 rows selected.

----可以发现两次执行计划都是走的 INDEX RANGE SCAN

因为  Peeked Binds的值还是 99 到100
也就是oracle在处理有绑定变量的时候只有在硬解析的时候才去窥探一下该SQL的绑定变量。
我们可以通过一些无关紧要的DDL语句来实现硬解析。

SQL > ALTER SYSTEM FLUSH SHARED_POOL ;

SQL > select count ( * ) from   t1 where object_id between :x and :y ;

   COUNT ( * )
- - - - - - - - - -
      47490

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

PLAN_TABLE_OUTPUT

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

SQL_ID 7pz1dg089j86a, child number 0

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

select count(*) from t1 where object_id between :x and :y

Plan hash value: 1410530761

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

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

| 0 | SELECT STATEMENT | | 1 | | | 48 (100)|

| 1 | SORT AGGREGATE | | 1 | 1 | 5 | |

PLAN_TABLE_OUTPUT

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

|* 2 | FILTER | | 1 | | | |

|* 3 | INDEX FAST FULL SCAN| IDX_T1 | 1 | 48267 | 235K| 48 (3)|

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

3 - SEL$1 / T1@SEL$1

Outline Data

PLAN_TABLE_OUTPUT

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

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

END_OUTLINE_DATA

PLAN_TABLE_OUTPUT

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

Peeked Binds (identified by position):

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

1 - (NUMBER): 0

2 - (NUMBER): 50000

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

2 - filter(:X<=:Y)

3 - filter(("OBJECT_ID"<=:Y AND "OBJECT_ID">=:X))

Column Projection Information (identified by operation id):

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

1 - (#keys=0) COUNT(*)[22]

52 rows selected.

SQL>

在更换一个值结果又会走INDEX FAST FULL SCAN 以下就略
SQL > exec :x : = 0
PL / SQL procedure successfully completed.
SQL > exec :y : = 5

可以设置这个参数来跳过但是这个事内部参数还是不稳定会有 BUG: 4567767 Peeked Binds 还是会发生
alter system set "_optim_peek_user_binds" = true scope = spfile ;

来自为知笔记(Wiz)

转载于:https://www.cnblogs.com/cure-t-x-y/p/4252035.html