oracle 11g適應性子游標改善PEEKING

10g 使用bind變數時候PEEKING 會造成執行計劃錯誤,11G進行了改進,但或多或少會造成hard parse,兩方面的代價要具體衡量,當然正確的執行計劃是更重要的.

先看下 10G 情況

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

peeking 受到這幾個隱藏引數影響
SQL> SQL> set linesize 132
column name format a30
column value format a25
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj,x. KSPPDESC
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%peek%'
order by
translate(x.ksppinm, ' _', ' ')
/

SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17
NAME                           VALUE                     ISDEFAULT ISMOD      ISADJ
------------------------------ ------------------------- --------- ---------- -----
KSPPDESC
------------------------------------------------------------------------------------------------------------------------------------
_optim_peek_user_binds         TRUE                      TRUE      FALSE      FALSE
enable peeking of user binds

_px_bind_peek_sharing TRUE TRUE FALSE FALSE
enables sharing of px cursors that were built using bind peeking

_xpl_peeked_binds_log_size 8192 TRUE FALSE FALSE
maximum bytes for logging peeked bind values for V$SQL_PLAN (0 = OFF)

SQL> create table t1 (a int,b int);

Table created.

SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats('SYS','T1');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

COUNT(*)
----------
10000

SQL> create index t1_ind on t1(a);

Index created.

SQL> set autotrace off
SQL> create index t1_ind on t1(a);

Index created.
SQL> set autotrace trace explain
SQL> select * from t1 where a>1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 9999 | 69993 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1   | 9999 | 69993 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("A">1)

SQL> select * from t1 where a>9000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2059591622

--------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        | 1000 | 7000 |     6   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T1     | 1000 | 7000 |     6   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | T1_IND | 1000 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("A">9000)

可以看到上面採用字面值的情況，下面使用BIND 變數

QL> variable a number
SQL> execute :a:=1

PL/SQL procedure successfully completed.

SQL> select * from t1 where a>:a;

Execution Plan
----------------------------------------------------------
Plan hash value: 2059591622

--------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   500 | 3500 |     3   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T1     |   500 | 3500 |     3   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | T1_IND |    90 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("A">TO_NUMBER(:A))

問題來了~~~按照PEEKING 的原則至少oracle應該確認 BIND 值為1 走FTS ，但顯示為INDEX RANGE SCAN

現在使用V$sql_plan來檢視執行計劃

SQL> alter system flush shared_pool;

System altered.

SQL> variable a number
SQL> execute :a:=1

PL/SQL procedure successfully completed.
select * from t1 where a>:a

SQL> col sql_text format a40
SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
3711359813 select * from t1 where a>:a
2165387111 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>

SQL> select operation,options,object_name,cardinality from v$sql_plan where hash_value='3711359813';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS FULL
T1 9999

可以看到 set autotrace 不準確 ~~，具體應該依賴於v$sql_plan，下面繼續實驗
execute :a:=9000
select * from t1 where a>:a
;

SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
3711359813 select * from t1 where a>:a
2165387111 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>%'

SQL> select operation,
options,object_name,cardinality from v$sql_plan where hash_value='3711359813';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS FULL
T1 9999

可以看到oracle peeking了一次，然後按第一次FTS 方式共享CURSOR ,共享了FTS 的 EXECUTE PLAN

看下11G 情況
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Productio
NLSRTL Version 11.1.0.6.0 - Production

SQL> create table t1 (a int, b int);

Table created.

SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> create index t1_ind on t1(a);

Index created.

SQL> execute dbms_stats.gather_table_stats('SYS','T1');

SQL> variable a number
SQL> execute :a:=1

PL/SQL procedure successfully completed.

select * from t1 where a>:a;

SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
2165387111 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>%'

2263578425 select * from t1 where a>:a

SQL> select operation,
2 options,object_name,cardinality from v$sql_plan where hash_value='2263578425';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS FULL
T1 9999

SQL> execute :a:=9000

PL/SQL procedure successfully completed.

SQL> select * from t1 where a>:a;

SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
2165387111 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>%'

2263578425 select * from t1 where a>:a

SQL> select operation,
2 options,object_name,cardinality from v$sql_plan where hash_value='2263578425';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS FULL
T1 9999

SQL> execute :a:=9999

PL/SQL procedure successfully completed.

SQL> select * from t1 where a>:a;

A B
---------- ----------
10000 10001

SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
2165387111 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>%'

2263578425 select * from t1 where a>:a

SQL> select operation,
2 options,object_name,cardinality from v$sql_plan where hash_value='2263578425';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID
T1 1

INDEX RANGE SCAN
T1_IND 1

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS FULL
T1 9999

select * from t1 where a>9000; (9000的時候也應該走INDEX )

SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>9000%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
705708754 select * from t1 where a>9000
1110618947 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>9000%'

SQL> select operation,
2 options,object_name,cardinality from v$sql_plan where hash_value='705708754';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID
T1 1000

INDEX RANGE SCAN
T1_IND 1000

SQL> select sql_id,child_number,plan_hash_value from v$sql where hash_value='2263578425'
2 /

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
5mu9n4f3fqxtt            0      3617692013
5mu9n4f3fqxtt            1      2059591622

產生了子游標，且產生了不同的執行計劃(plan_hash_value判斷多了一個INDEX SCAN的plan)

SQL> select child_number,is_bind_sensitive,is_bind_aware,is_shareable from v$sql where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER I I I
------------ - - -
0 Y N Y
1 Y Y Y

SQL> select child_number,peeked,executions,rows_processed,buffer_gets from v$sql_cs_statistics where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ - ---------- -------------- -----------
0 Y 1 9999 722
1 Y 1 1 3

SQL> select child_number,predicate,low,high from v$sql_cs_selectivity where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER PREDICATE LOW HIGH
------------ ---------------------------------------- ---------- ----------
1 >A 0.000090 0.000110

execute :a:=1000
select * from t1 where a>:a

這個計劃走的是FTS 又產生一個version_count，

SQL> select child_number,peeked,executions,rows_processed,buffer_gets from v$sql_cs_statistics where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ - ---------- -------------- -----------
           0 Y          1           9999         722
           1 Y          1              1           3
           2 Y          1           9000         622

SQL> select child_number,is_bind_sensitive,is_bind_aware,is_shareable from v$sql where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER I I I
------------ - - -
           0 Y N N
           1 Y Y Y
           2 Y Y Y

SQL> select child_number,predicate,low,high from v$sql_cs_selectivity where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER PREDICATE                                LOW        HIGH
------------ ---------------------------------------- ---------- ----------
           1 >A                                       0.000090   0.000110
           2 >A                                       0.810081   0.990099

SQL> select sql_id,child_number,plan_hash_value from v$sql where hash_value='2263578425'
2 ;

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
5mu9n4f3fqxtt            0      3617692013
5mu9n4f3fqxtt            1      2059591622
5mu9n4f3fqxtt            2      3617692013

SQL> execute :a:=2000

PL/SQL procedure successfully completed.

select * from t1 where a>:a

SQL> select sql_id,child_number,plan_hash_value from v$sql where hash_value='2263578425';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
5mu9n4f3fqxtt            0      3617692013
5mu9n4f3fqxtt            1      2059591622
5mu9n4f3fqxtt            2      3617692013
5mu9n4f3fqxtt            3      3617692013

SQL> select child_number,predicate,low,high from v$sql_cs_selectivity where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER PREDICATE                                LOW        HIGH
------------ ---------------------------------------- ---------- ----------
           1 >A                                       0.000090   0.000110
           2 >A                                       0.810081   0.990099
           3 >A                                       0.720072   0.990099

SQL> select child_number,peeked,executions,rows_processed,buffer_gets from v$sql_cs_statistics where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ - ---------- -------------- -----------
           0 Y          1           9999         722
           1 Y          1              1           3
           2 Y          1           9000         622
           3 Y          1           8000         556

SQL> select child_number,is_bind_sensitive,is_bind_aware,is_shareable from v$sql where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER I I I
------------ - - -
           0 Y N N
           1 Y Y Y
           2 Y Y N
           3 Y Y Y

SQL> execute :a:=1500

PL/SQL procedure successfully completed.

select * from t1 where a>:a;
8500 rows selected.