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

最新推荐文章于 2022-12-01 17:18:07 发布
最新推荐文章于 2022-12-01 17:18:07 发布
阅读量220 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/cure-t-x-y/p/4252035.html
版权
- -#####################################################
- -####     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     2621316314     0
 
- - 传入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
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   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
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-T
--------------------------------------------------------------------------------
|   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
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-T
--------------------------------------------------------------------------------
|   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
 
--------------------------------------------------------------------------------
| Id  | Operation              | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------
|   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 ;






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

weixin_33763244
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
错误的转换绑定变量类型导致执行计划错误
wujizhou的专栏
03-28 570
昨天开发来问我个问题，一个业务sql执行缓慢，下面 SQL> SELECT A.ISSUE_OID,       (SELECT B.ISSUE_END_TIME FROM ISSUE_T B WHERE A.ISSUE_OID = B.IWOID) AS ISSUE_END_TIME,       (SELECT C.ENCASH_END_TIME FROM ISSUE_T C WHERE
Oracle重新绑定执行计划,绑定变量对执行计划的影响
weixin_32821579的博客
04-03 313
sql运行在11g数据库上，做的是绑定变量对执行计划的影响测试，测试过程如下：SQL> exec :x :=999PL/SQL procedure successfully completed.SQL> exec :y :=1000PL/SQL procedure successfully completed.SQL> select count(*) from t1 where ...
oracle 制造执行计划并绑定,使用绑定变量中执行计划的问题
weixin_34534926的博客
04-06 376
在测试使用绑定变量中发现执行计划的问题,比较迷惑在使用绑定变量的过程中,由于具体的查询数值被绑定变量代替,oracle 的优化器在分析绑定变量的SQL语句的时候,使用peeking (窥视)来形成执行计划,也就是使用第1次运行SQL语句的常数来形成固定的执行计划.比如在对一个分布不平衡的列进行查询的时候.如果这个列建立了histogram ,那么CBO 优化器会根据histogram 和第一次运行...
绑定变量窥探
阿飞
07-06 1087
Bind Peeking是Oracle 9i中引入的新特性，一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值 ，以便生成最佳的执行计划。而在oracle 9i之前的版本中，Oracle 只根据统计信息来做出执行计划。 一、绑定变量窥探 使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次执行计划来执行。
KingbaseES 绑定变量窥探机制
lyu1026的博客
04-26 1306
概述： 对于数据严重倾斜的，极端如以下例子，不同的传入值，可能执行计划不同，制定执行计划时，就要求知道变量的值。对于绑定变量的情况，我们知道Oracle 有_optim_peek_user_binds 参数，控制是否启用变量窥探。KingbaseES 也有类似参数，控制是否启用变量窥探。 窥探机制 KingbaseES 采用以下判断机制，决定是否固定执行计划： 前5次执行时，每次都会根据实际传入的实际绑定变量新生成执行计划进行执行，即每次都是硬解析，同时会记录这5次的执行计划； 当第.
不均衡分区和绑定变量窥视导致的查询计划错误
02-27
周一收到生成支持人员的报告，系统上一个作业启动后很长时间没有完成，其执行时间远远大于上周的正常执行时间。接到报告后，首先检查了系统，不存在锁队列的问题。然后查询V$SESSION_LONGOPS，立即发现下面的语句...
绑定执行计划.txt
08-20
对于oracle 执行计划不准，可采用绑定执行计划对其固定
WPF绑定静态变量的示例代码
09-19
通过上述步骤，你已经成功地在WPF应用中实现了控件对静态变量的绑定。在实际项目中，你可能需要根据需求调整静态变量的类型和更新机制，例如，如果静态变量是复杂类型，你可能需要自定义转换器或者使用`...
WPF绑定静态变量的示例代码（二）加上IValueConverter
09-19
在本文中，我们将深入探讨如何在Windows Presentation Foundation (WPF) 中绑定静态变量，并通过使用IValueConverter接口来实现动态更新控件的显示。WPF是.NET Framework的一部分，它为构建丰富的桌面应用程序提供了...
金仓数据库KingbaseES 绑定变量窥探机制
arthemis_14的博客
12-01 490
对于数据严重倾斜的，极端如以下例子，不同的传入值，可能执行计划不同，制定执行计划时，就要求知道变量的值。对于绑定变量的情况，我们知道Oracle 有optim_peek_user_binds 参数，控制是否启用变量窥探。KingbaseES 也有类似参数，控制是否启用变量窥探。
Oracle绑定变量窥探
weixin_33736832的博客
03-07 303
随着具体输入值的不同，SQL的where条件的可选择率(Selectivity)和结果集的行数(Cardinality)可能会随之发生变化，而Selectivity和Cardinality的值会直接影响CBO对于相关执行步骤成本值的估算，进而影响CBO对SQL执行计划的选择。这就意味着随着具体输入值的不同，目标SQL执行计划可能会发生变化。对于不使用绑定变量的SQL而言，具体...
SQL执行异常系列之——绑定变量窥探导致的执行异常
weixin_30415801的博客
09-26 220
应用反映批量进展缓慢，通过查询得知此sql在生成执行计划时，带入的变量为数据分布较小的值，但在数据分布较大值的情况下，再走之前的执行计划就会变得很慢。以下为收集的部分数据，仅作归类记录： 原始代码： SELECT DEAL_ID FROM TQE_PS_DEALS WHERE GENSYS = :B3 AND DEALDATE_CP <= :B2 AND CPTY_BAN...
绑定变量窥探测试
文档搬运工
04-16 208
RDBMS 12.2.0.1 -- 绑定变量的执行计划 ，和正常的语句是一样的 create table t5 as select * from dba_objects; var v_name varchar2(30) exec :V_name:='T5' select * from t5 where object_name=:V_name; select sql_id,address,...
一条sql 优化，执行计划有变动，绑定执行计划 sqlprofile
xxjj2003的专栏
01-10 1090
某日早上业务人员反馈某系统节点1 性能很差，而节点2 比较正常 登录系统节点1 检查当前等待事件，发现节点1等待比较多的都是directory path read 都是同一条sql 引起的，而在节点2 这条sql执行正常 检查sql_id ，都是等direct path read  查看其执行计划 有变化，好的执行计划 有使用索引，差的 使用full tablescan 
绑定变量窥探和直方图
zhaoyangjian724的专栏
01-18 1758
绑定变量窥视cbo的一个附属功能,关闭他不会影响是用cbo还是rbo,就算关闭了绑定变量窥视,cbo还是会利用别的统计信息(num_distinct,density等)来评估cost和cardinality,只 是无法使用直方图信息而已: SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM
oracle 11g 新特性之动态绑定变量窥视(二)
cpbr82909536的博客
02-27 197
2. 11g之后的动态绑定变量窥视 而从11g开始，这个尴尬的问题开始得到了改善。因此从11g开始，引入了所谓的自适应游标共享（Adaptive Cursor Sharing）。该特性是一个非常复杂...
如何查看oracle sql执行计划
热门推荐
paul的专栏
09-10 1万+
 方法一第一步：登入sql/plus 执行命令（无先后顺序）set   time   on; (说明：打开时间显示）     set   autotrace   on; (说明：打开自动分析统计，并显示SQL语句的运行结果）     set   autotrace   traceonly; (说明：打开自动分析统计，不显示SQL语句的运行结果）二者选一个执行第二步： 输入你要查看的
oracle绑定变量执行计划,执行计划于绑定变量 - Oracle数据库管理 - ITPUB论坛－中国专业的IT技术社区...
weixin_42106765的博客
04-14 123
Access Path: TableScanCost:25151.123493Resp: 25151.123493Degree: 0Cost_io: 25087.000000Cost_cpu: 2414414014Resp_io: 25087.000000Resp_cpu: 2414414014****** Costing Index IDX_TT_OIDSPD: Return...
查看有绑定变量的SQL的绑定变量值
lyp13696402570的博客
01-03 1908
设置一个绑定变量，执行SQL，查看执行计划 SQL&gt; var a varchar2(10); SQL&gt; exec :a:='TAB$'; PL/SQL procedure successfully completed. SQL&gt; select owner from dba_objects where object_name =:a; ...
el-table-column绑定变量
最新发布
12-28
el-table-column 绑定变量的方法如下所示： ```html <el-table> <el-table-column> <template slot="header" slot-scope="scope"> <el-input v-model="search" placeholder="输入关键字搜索"></el-input> ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值