SQL> exec sys.dbms_system.set_ev(22,153,10046,12,'');
SQL> exec :v_flag:=410;
SQL> @test
SUM(VALUE)
----------
610250
SQL> exec sys.dbms_system.set_ev(22,153,10046,12,'');
SQL> exec :v_flag:=410;
SQL> @test
SUM(VALUE)
----------
610250
PARSING IN CURSOR #1 len=59 dep=0 uid=55 oct=3 lid=55 tim=7703132042 hv=954944791 ad='27cf5884'
select /*+ testsql */ sum(value) from t1 where flag=:v_flag
END OF STMT
PARSE #1:c=0,e=309,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7703132037
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=0819ca94 bln=22 avl=03 flg=05
value=410
EXEC #1:c=15625,e=8864,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7703164320
WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=7703165526
FETCH #1:c=0,e=237,p=0,cr=27,cu=0,mis=0,r=1,dep=0,og=1,tim=7703166929
WAIT #1: nam='SQL*Net message from client' ela= 187 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=7703168520
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=7703169851
WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=7703171170
WAIT #1: nam='SQL*Net message from client' ela= 421 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=7703172845
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=27 pr=0 pw=0 time=260 us)'
STAT #1 id=2 cnt=25 pid=1 pos=1 obj=51606 op='TABLE ACCESS BY INDEX ROWID T1 (cr=27 pr=0 pw=0 time=439 us)'
STAT #1 id=3 cnt=25 pid=2 pos=1 obj=51607 op='INDEX RANGE SCAN T1_IDX (cr=2 pr=0 pw=0 time=36 us)'
PARSING IN CURSOR #1 len=59 dep=0 uid=55 oct=3 lid=55 tim=7703132042 hv=954944791 ad='27cf5884'
select /*+ testsql */ sum(value) from t1 where flag=:v_flag
END OF STMT
PARSE #1:c=0,e=309,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7703132037
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=0819ca94 bln=22 avl=03 flg=05
value=410
EXEC #1:c=15625,e=8864,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7703164320
WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=7703165526
FETCH #1:c=0,e=237,p=0,cr=27,cu=0,mis=0,r=1,dep=0,og=1,tim=7703166929
WAIT #1: nam='SQL*Net message from client' ela= 187 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=7703168520
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=7703169851
WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=7703171170
WAIT #1: nam='SQL*Net message from client' ela= 421 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=7703172845
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=27 pr=0 pw=0 time=260 us)'
STAT #1 id=2 cnt=25 pid=1 pos=1 obj=51606 op='TABLE ACCESS BY INDEX ROWID T1 (cr=27 pr=0 pw=0 time=439 us)'
STAT #1 id=3 cnt=25 pid=2 pos=1 obj=51607 op='INDEX RANGE SCAN T1_IDX (cr=2 pr=0 pw=0 time=36 us)'
可以看,mis=1,表明发生了硬解析,op=’INDEX RANGE SCAN T1_IDX 表明使用使用了索引,而op=’SORT AGGREGATE (cr=27 表明逻辑读只有27,逻辑读幅下降,的确是使用了索引。
SQL> select sql_id,hash_value,child_number,executions from v$sql where sql_text like '%testsql%' and sql_text not like '%v$sql%';
SQL_ID HASH_VALUE CHILD_NUMBER EXECUTIONS
------------- ---------- ------------ ----------
8hdvb5nwfqm8r 954944791 0 7
8hdvb5nwfqm8r 954944791 1 1
SQL> select * from table(dbms_xplan.display_cursor('8hdvb5nwfqm8r',1));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8hdvb5nwfqm8r, child number 1
-------------------------------------
select /*+ testsql */ sum(value) from t1 where flag=:v_flag
Plan hash value: 1563165360
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 33 | 561 | 30 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 33 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:V_FLAG)
SQL> select sql_id,hash_value,child_number,executions from v$sql where sql_text like '%testsql%' and sql_text not like '%v$sql%';
SQL_ID HASH_VALUE CHILD_NUMBER EXECUTIONS
------------- ---------- ------------ ----------
8hdvb5nwfqm8r 954944791 0 7
8hdvb5nwfqm8r 954944791 1 1
SQL> select * from table(dbms_xplan.display_cursor('8hdvb5nwfqm8r',1));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8hdvb5nwfqm8r, child number 1
-------------------------------------
select /*+ testsql */ sum(value) from t1 where flag=:v_flag
Plan hash value: 1563165360
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 33 | 561 | 30 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 33 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:V_FLAG)
由此,可以看出,开启sql trace之后,会话执行的SQL将不会重用未开启sql trace时生成的执行计划,而发生硬解析。由于硬解析时绑定变量窥视,将会使硬解析产生的子游标,其执行计划可能发生了变化。
从结果可以看到,STATS_ROW_MISMATCH导致了游标不能共享,Oracle文档这个列的解释是:The existing statistics do not match the existing child cursor。
查看所有1条评论>>