示例:
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects where rownum < 2000;
Table created.
SQL> create index idx_t_objid on t(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed columns size 254');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly;
SQL> variable objid number;
SQL> exec :objid := 2;
PL/SQL procedure successfully completed.
SQL> select * from t where object_id < :objid;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3638283050
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 8500 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 8500 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJID | 18 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1026 bytes sent via SQL*Net to client
339 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> exec :objid := 2000;
PL/SQL procedure successfully completed.
SQL> select * from t where object_id < :objid;
(由于使用了绑定变量,SQL沿用了已有的执行计划,没有根据where条件里变量值的不同而重新生成更优的执行计划)
1943 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3638283050
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 8500 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 8500 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJID | 18 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
287 consistent gets
0 physical reads
0 redo size
117777 bytes sent via SQL*Net to client
1769 bytes received via SQL*Net from client
131 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1943 rows processed
SQL> select * from t where object_id < 2000;
(在不使用绑定变量的情况下再次执行,SQL生成了最优执行计划)
1943 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1937 | 160K| 10 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1937 | 160K| 10 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<2000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
156 consistent gets
0 physical reads
0 redo size
117777 bytes sent via SQL*Net to client
1769 bytes received via SQL*Net from client
131 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1943 rows processed
SQL>
由于OLAP运行的都是报表类程序, SQL数量少且每个SQL执行的次数也较少, 所以即使每次都硬解析也不会造成太多额外开销, 而where条件不同的值对SQL选择执行计划影响很大, 所以使用绑定变量反而会导致继续沿用不恰当的执行计划.
OLTP由于多是小而重复的事务, 减少硬解析可以很大程度地降低系统资源争用, 故而更适合使用绑定变量.
--EOF--
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects where rownum < 2000;
Table created.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable, sql_text from v$sql where sql_text like 'select count(*) from t';
no rows selected
SQL> select count(*) from t;
COUNT(*)
----------
1999
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable, sql_text from v$sql where sql_text like 'select count(*) from t';
(硬解析)
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - ----------------------------------------------------------------------------------------------------
cyzznbykb509s 0 1 1 1 1 N Y select count(*) from t
SQL> select count(*) from t;
COUNT(*)
----------
1999
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable, sql_text from v$sql where sql_text like 'select count(*) from t';
(软解析)
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - ----------------------------------------------------------------------------------------------------
cyzznbykb509s 0 1 2 2 2 N Y select count(*) from t
SQL>
更软解析(分析一次,执行10次)
SQL> begin for i in 1..10 loop execute immediate 'select count(*) from t'; end loop; end;
2 /
PL/SQL procedure successfully completed.
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable, sql_text from v$sql where sql_text like 'select count(*) from t';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - ----------------------------------------------------------------------------------------------------
cyzznbykb509s 0 1 2 2 2 N Y select count(*) from t
cyzznbykb509s 1 1 1 10 0 N Y select count(*) from t
SQL>
--EOF--
示例: 一次分析,10次执行
SQL> create table t1 as select * from dba_objects where rownum < 100;
Table created.
SQL> begin
2 for i in 1..10 loop
3 execute immediate 'select * from t1 where object_id = :id' using i;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select * from t1 where object_id = :id';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - ------------------------------------------------
gj5gsnt6mq06m 0 1 1 10 0 N Y select * from t1 where object_id = :id
SQL>
--EOF--
4.演示一个父游标产生3个子游标的示例，并分别说明每个子游标产生的原因。
示例1, 由于优化器模式(optimizer_mode)不同而产生子游标
SQL> create table t2 as select * from dba_objects where rownum <100;
Table created.
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select count(*) from t2;';
no rows selected
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> select count(*) from t2;
COUNT(*)
----------
99
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select count(*) from t2';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - ---------------------------------------
94dwfa8yd87kw 0 1 1 1 1 N Y select count(*) from t2
SQL> alter session set optimizer_mode=first_rows;
Session altered.
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string FIRST_ROWS
SQL> select count(*) from t2;
COUNT(*)
----------
99
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select count(*) from t2';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - -------------------------------------
94dwfa8yd87kw 0 1 1 1 1 N Y select count(*) from t2
94dwfa8yd87kw 1 1 1 1 1 N Y select count(*) from t2
SQL> select sql_id,child_number,optimizer_mode_mismatch from v$sql_shared_cursor where sql_id='94dwfa8yd87kw';
SQL_ID CHILD_NUMBER O
------------- ------------ -
94dwfa8yd87kw 0 N
94dwfa8yd87kw 1 Y
SQL>
示例2, 由于对象权限不同(auth_check_mismatch)而产生子游标
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> create table scott.t2 as select * from apps.t2;
Table created.
SQL> show user;
USER is "APPS"
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select count(*) from t2';
no rows selected
SQL> select count(*) from t2;
COUNT(*)
----------
99
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select count(*) from t2';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - -------------------------
94dwfa8yd87kw 0 1 1 1 1 N Y select count(*) from t2
SQL> conn scott/tiger@rclsh
Connected.
SQL> show user;
USER is "SCOTT"
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select count(*) from t2';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - -----------------------------
94dwfa8yd87kw 0 1 1 1 1 N Y select count(*) from t2
SQL> select count(*) from t2;
COUNT(*)
----------
99
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select count(*) from t2';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - ---------------------------
94dwfa8yd87kw 0 1 1 1 1 N Y select count(*) from t2
94dwfa8yd87kw 1 1 1 1 1 N Y select count(*) from t2
SQL> select sql_id,child_number,optimizer_mode_mismatch,auth_check_mismatch from v$sql_shared_cursor where sql_id='94dwfa8yd87kw';
SQL_ID CHILD_NUMBER O A
------------- ------------ - -
94dwfa8yd87kw 0 N N
94dwfa8yd87kw 1 N Y
SQL>
示例3, 由于变量长度不同(incomplete_cursor)而产生子游标
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select count(*) from t2 where object_name=:name';
no rows selected
SQL> var name varchar2(20);
SQL> exec :name := 'AAA';
PL/SQL procedure successfully completed.
SQL> select count(*) from t2 where object_name=:name;
COUNT(*)
----------
0
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select count(*) from t2 where object_name=:name';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - -----------------------------------------------------------------------------
dqyxszpk5b9ba 0 1 1 1 1 N Y select count(*) from t2 where object_name=:name
SQL> var name varchar2(200);
SQL> exec :name := 'AAA';
PL/SQL procedure successfully completed.
SQL> select count(*) from t2 where object_name=:name;
COUNT(*)
----------
0
SQL> select sql_id,child_number,loads, parse_calls, executions, fetches, is_bind_aware,is_shareable,
2 sql_text from v$sql where sql_text like 'select count(*) from t2 where object_name=:name';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - -----------------------------------------------------------------------------
dqyxszpk5b9ba 0 1 2 1 1 N N select count(*) from t2 where object_name=:name
dqyxszpk5b9ba 1 1 0 1 1 N Y select count(*) from t2 where object_name=:name
SQL> select sql_id,child_number,optimizer_mode_mismatch,auth_check_mismatch,incomplete_cursor from v$sql_shared_cursor where sql_id='dqyxszpk5b9ba';
SQL_ID CHILD_NUMBER O A I
------------- ------------ - - -
dqyxszpk5b9ba 0 N N N
dqyxszpk5b9ba 1 N N Y
SQL>
--EOF--
5.演示ACS（adaptiver cursor sharing)的效果。
由于autotrace在使用绑定变量的时会出现执行计划显示不正确的情况,以下例子使用了10046 trace进行跟踪.
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects where rownum < 2000;
Table created.
SQL> update t set object_id = 1 where rownum =1;
1 row updated.
SQL> update t set object_id = 99 where object_id !=1;
1998 rows updated.
SQL> commit;
Commit complete.
SQL> select object_id,count(*) from t group by object_id;
OBJECT_ID COUNT(*)
---------- ----------
1 1
99 1998
SQL> create index idx_t_objid on t(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true,method_opt=>'for all indexed columns size 254');
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> var objid number;
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> select sql_id,child_number,loads,parse_calls,executions,fetches,is_bind_sensitive,is_bind_aware,
2 is_shareable,sql_text from v$sql where sql_text like 'select * from t where object_id = :objid';
no rows selected
SQL> exec :objid := 1;
PL/SQL procedure successfully completed.
SQL> select * from t where object_id = :objid;
OWNER OBJECT_NAME SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS ICOL$
1 2 TABLE 02-APR-10 02-APR-10 2010-04-02:13:18:38 VALID N N N 1
SQL> exec :objid := 99;
PL/SQL procedure successfully completed.
SQL> select * from t where object_id = :objid;
... (此处省略输出结果)
1998 rows selected.
SQL> select sql_id,child_number,loads,parse_calls,executions,fetches, is_bind_sensitive,is_bind_aware,
2 is_shareable,sql_text from v$sql where sql_text like 'select * from t where object_id = :objid';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - - ------------------------------------------
5qn4zd1az2xd8 0 1 2 2 137 Y N Y select * from t where object_id = :objid
（ is_bind_sensitive= Y说明已经察觉到变量值改变了， is_bind_aware= N说明尚未就变量改变生成新的执行计划）
SQL> select * from t where object_id = :objid;
... (此处省略输出结果)
1998 rows selected.
SQL> select sql_id,child_number,loads,parse_calls,executions,fetches,is_bind_sensitive,is_bind_aware,
2 is_shareable,sql_text from v$sql where sql_text like 'select * from t where object_id = :objid';
SQL_ID CHILD_NUMBER LOADS PARSE_CALLS EXECUTIONS FETCHES I I I SQL_TEXT
------------- ------------ ---------- ----------- ---------- ---------- - - - -------------------------------------------
5qn4zd1az2xd8 0 1 3 2 137 Y N Y select * from t where object_id = :objid
5qn4zd1az2xd8 1 1 0 1 135 Y Y Y select * from t where object_id = :objid
SQL> select sql_id,child_number,bind_equiv_failure from v$sql_shared_cursor where sql_id='5qn4zd1az2xd8';
SQL_ID CHILD_NUMBER B
------------- ------------ -
5qn4zd1az2xd8 0 N
5qn4zd1az2xd8 1 Y
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------------
c:\app\diag\rdbms\d11r2i\d11r2i\trace\d11r2i_ora_5376.trc
SQL>
c:\app\diag\rdbms\d11r2i\d11r2i\trace>tkprof d11r2i_ora_5376.trc d11r2i_ora_5376.trc.prf sys=no aggregate=no
TKPROF: Release 11.2.0.1.0 - Development on Wed Dec 4 19:20:26 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
c:\app\diag\rdbms\d11r2i\d11r2i\trace>notepad d11r2i_ora_5376.trc.prf
c:\app\diag\rdbms\d11r2i\d11r2i\trace>
trace文件部分内容：
...
********************************************************************************
SQL ID: dcx717n8x3071
Plan Hash: 0
BEGIN :objid := 1; END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 10.42 10.42
********************************************************************************
SQL ID: 5qn4zd1az2xd8
Plan Hash: 3638283050
select *
from
t where object_id = :objid
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=0 us cost=2 size=85 card=1)
1 INDEX RANGE SCAN IDX_T_OBJID (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74570)
（先生成一个适合走索引的执行计划）
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 26.42 26.42
********************************************************************************
SQL ID: 5j801dbwcy9mh
Plan Hash: 0
BEGIN :objid := 99; END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 17.84 17.84
********************************************************************************
SQL ID: 5qn4zd1az2xd8
Plan Hash: 3638283050
select *
from
t where object_id = :objid
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 135 0.00 0.01 0 295 0 1998
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 137 0.00 0.01 0 295 0 1998
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1998 TABLE ACCESS BY INDEX ROWID T (cr=295 pr=0 pw=0 time=10118 us cost=2 size=85 card=1)
1998 INDEX RANGE SCAN IDX_T_OBJID (cr=139 pr=0 pw=0 time=9111 us cost=1 size=0 card=1)(object id 74570)
（绑定变量值改变后，第一次执行时仍沿用已有的执行计划）
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 135 0.00 0.00
SQL*Net message from client 135 31.09 38.35
********************************************************************************
select sql_id,child_number,loads,parse_calls,executions,fetches,is_bind_sensitive,is_bind_aware,
is_shareable,sql_text from v$sql where sql_text like 'select * from t where object_id = :objid'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 FIXED TABLE FULL X$KGLCURSOR_CHILD (cr=0 pr=0 pw=0 time=0 us cost=0 size=601 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 35.39 35.40
********************************************************************************
SQL ID: 5qn4zd1az2xd8
Plan Hash: 1601196873
select *
from
t where object_id = :objid
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 135 0.01 0.01 0 159 0 1998
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 137 0.01 0.01 0 159 0 1998
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1998 TABLE ACCESS FULL T (cr=159 pr=0 pw=0 time=8653 us cost=10 size=169830 card=1998)
（绑定变量值改变后，第二次执行时采用ACS，得到正确执行计划）
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 135 0.00 0.00
SQL*Net message from client 135 27.11 36.53
********************************************************************************
...
参考:
Wrong explain plan in Oracle autotrace
http://kokeiblog.blogspot.com/2010/09/wrong-explain-plan-in-oracle-autotrace_27.html
Adaptive Cursor Sharing in Oracle Database 11g Release 1
http://www.oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1.php
--EOF--
199
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
