理解EXECUTE_TO_PARSE(二)
目的:
為了深入理解Execute to Parse的統計意義，需要首先理解如下統計項的確切含義：
'execute count'
'parse count (failures)'
'parse count (hard)'
'parse count (total)'
本文透過跟蹤SQL執行過程、會話統計變化，分析各個統計項與SQL執行過程的關係。
1,初始化
--session 1
select sid from v$mystat where rownum<2;
254
alter session set sql_trace=true;
--session 2
SELECT sn.NAME, st.VALUE
FROM v$sesstat st, v$statname sn
WHERE st.sid = 254
AND st.statistic# = sn.statistic#
AND sn.NAME IN ('execute count', 'parse count (failures)',
'parse count (hard)', 'parse count (total)')
ORDER BY sn.statistic#;
parse count (total) 96
parse count (hard) 11
parse count (failures) 0
execute count 99
2,第1次查詢不存在的檢視
--session 1
select * from v$mystata;
--session 2
SELECT sn.NAME, st.VALUE
FROM v$sesstat st, v$statname sn
WHERE st.sid = 254
AND st.statistic# = sn.statistic#
AND sn.NAME IN ('execute count', 'parse count (failures)',
'parse count (hard)', 'parse count (total)')
ORDER BY sn.statistic#;
parse count (total) 98 -->+2
parse count (hard) 12 -->+1
parse count (failures) 1 -->+1
execute count 99 -->+0
==>parse count (failures)與parse count (hard)分別增加1,parse count (total)增加2***
跟蹤檔案中對應:
++++++++++++++++++++++++++++++
PARSE ERROR #2:len=24 dep=0 uid=55 ct=3 lid=62 tim=1317333505186781 err=942
select * from v$mystata
++++++++++++++++++++++++++++++
3,第2次查詢不存在的檢視
--session 1
select * from v$mystata;
--session 2
SELECT sn.NAME, st.VALUE
FROM v$sesstat st, v$statname sn
WHERE st.sid = 254
AND st.statistic# = sn.statistic#
AND sn.NAME IN ('execute count', 'parse count (failures)',
'parse count (hard)', 'parse count (total)')
ORDER BY sn.statistic#;
parse count (total) 100 -->+2
parse count (hard) 13 -->+1
parse count (failures) 2 -->+1
execute count 99 -->+0
==>parse count (failures)與parse count (hard)分別增加1,parse count (total)增加2***
跟蹤檔案中對應:
++++++++++++++++++++++++++++++
PARSE ERROR #3:len=24 dep=0 uid=55 ct=3 lid=62 tim=1317333667678182 err=942
select * from v$mystata
++++++++++++++++++++++++++++++
4,第1次查詢存在的檢視
--session 1
select * from dual;
--session 2
SELECT sn.NAME, st.VALUE
FROM v$sesstat st, v$statname sn
WHERE st.sid = 254
AND st.statistic# = sn.statistic#
AND sn.NAME IN ('execute count', 'parse count (failures)',
'parse count (hard)', 'parse count (total)')
ORDER BY sn.statistic#;
parse count (total) 101 -->+1
parse count (hard) 14 -->+1
parse count (failures) 2 -->+0
execute count 100 -->+1
跟蹤檔案中對應:
++++++++++++++++++++++++++++++
PARSING IN CURSOR #1 len=18 dep=0 uid=55 ct=3 lid=62 tim=1317333944044475 hv=942515969 ad='393d9e4c'
select * from dual
END OF STMT
PARSE #1:c=0,e=3536,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1317333944044465
EXEC #1:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1317333944044750
FETCH #1:c=0,e=170,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1317333944044993
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1317333944045660
STAT #1 id=1 cnt=1 pid=0 pos=1 bj=258 p='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=159 us)'
++++++++++++++++++++++++++++++
==>PARSE #1:..mis=1表示硬解析
5,第2次查詢存在的檢視
--session 1
select * from dual;
--session 2
SELECT sn.NAME, st.VALUE
FROM v$sesstat st, v$statname sn
WHERE st.sid = 254
AND st.statistic# = sn.statistic#
AND sn.NAME IN ('execute count', 'parse count (failures)',
'parse count (hard)', 'parse count (total)')
ORDER BY sn.statistic#;
parse count (total) 102 -->+1
parse count (hard) 14 -->+0
parse count (failures) 2 -->+0
execute count 101 -->+1
跟蹤檔案中對應:
++++++++++++++++++++++++++++++
PARSING IN CURSOR #4 len=18 dep=0 uid=55 ct=3 lid=62 tim=1317334264212954 hv=942515969 ad='393d9e4c'
select * from dual
END OF STMT
PARSE #4:c=0,e=113,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1317334264212949
EXEC #4:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1317334264213141
FETCH #4:c=0,e=90,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1317334264213301
FETCH #4:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1317334264213931
STAT #4 id=1 cnt=1 pid=0 pos=1 bj=258 p='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=81 us)'
++++++++++++++++++++++++++++++
==>PARSE #4:..mis=0表示軟解析
6,第3次查詢存在的檢視
--session 1
select * from dual;
--session 2
SELECT sn.NAME, st.VALUE
FROM v$sesstat st, v$statname sn
WHERE st.sid = 254
AND st.statistic# = sn.statistic#
AND sn.NAME IN ('execute count', 'parse count (failures)',
'parse count (hard)', 'parse count (total)')
ORDER BY sn.statistic#;
parse count (total) 103 -->+1
parse count (hard) 14 -->+0
parse count (failures) 2 -->+0
execute count 102 -->+1
跟蹤檔案中對應:
++++++++++++++++++++++++++++++
PARSING IN CURSOR #2 len=18 dep=0 uid=55 ct=3 lid=62 tim=1317334531445598 hv=942515969 ad='393d9e4c'
select * from dual
END OF STMT
PARSE #2:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1317334531445592
EXEC #2:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1317334531445783
FETCH #2:c=0,e=103,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1317334531445955
FETCH #2:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1317334531446627
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=258 p='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=95 us)'
++++++++++++++++++++++++++++++
==>PARSE #2:..mis=0表示軟解析
7,第4-30次查詢存在的檢視,結果與第3次相同,parse count (total)與execute count分別加1,說明該語句沒有在會話PGA中儲存,每次都需要解析.
8,什麼時候發生軟-軟解析?即parse count (total)不變而execute count加1?
以下測試
初始狀態:
--session 2
SELECT sn.NAME, st.VALUE
FROM v$sesstat st, v$statname sn
WHERE st.sid = 254
AND st.statistic# = sn.statistic#
AND sn.NAME IN ('execute count', 'parse count (failures)',
'parse count (hard)', 'parse count (total)')
ORDER BY sn.statistic#;
parse count (total) 135
parse count (hard) 18
parse count (failures) 3
execute count 132
--session 1
declare
ll int;
l_count int;
begin
for i in 1..100 loop
select count(*) into l_count from t a where rn=i;
end loop;
end;
/
--session 2
SELECT sn.NAME, st.VALUE
FROM v$sesstat st, v$statname sn
WHERE st.sid = 254
AND st.statistic# = sn.statistic#
AND sn.NAME IN ('execute count', 'parse count (failures)',
'parse count (hard)', 'parse count (total)')
ORDER BY sn.statistic#;
parse count (total) 141 -->+6
parse count (hard) 28 -->+10
parse count (failures) 3 -->+0
execute count 237 -->+105
跟蹤檔案中對應:
6次parse count (total)包括:
遞迴呼叫4次
匿名過程1次
查詢語句1次
10次parse count (hard)包括:
遞迴呼叫4次
匿名過程1次
查詢語句1次
還有4次硬解析在哪裡? ***
105次execute count包括:
遞迴呼叫4次
匿名過程1次
查詢語句100次
++++++++++++++++++++++++++++++
=====================
PARSING IN CURSOR #1 len=1664 dep=1 uid=0 ct=3 lid=0 tim=1317336787273311 hv=610427736 ad='3edd25b8'
select '"'||name||'" '||decode(type#,1,decode(charsetform,2,'NVARCHAR2('||decode(bitand(property,8388608),8388608,decode(spare3,0,1,spare3)||')','BYTECOUNT=>'||decode(length,0,1,length)||')'),'VARCHAR2('||decode(bitand(property,8388608),8388608,decode(spare3,0,1,spare3)||' char)',decode(length,0,1,length)||' byte)')),2,decode(scale,null,decode(precision#,null,'NUMBER','FLOAT('||precision#||')'),'NUMBER('||decode(precision#,null,'38', precision#)||','||scale||')'),8,'LONG',9,'VARCHAR('||(decode(length,0,1,length))||')',12,'DATE',23,'RAW('||length||')',24,'LONG RAW',69,'ROWID',96,decode(charsetform,2,'NCHAR('||decode(bitand(property,8388608),8388608,decode(spare3,0,1,spare3)||')','BYTECOUNT=>'||decode(length,0,1,length)||')'),'CHAR('||decode(bitand(property,8388608),8388608,decode(spare3,0,1,spare3)||' char)',decode(length,0,1,length)||' byte)')),97,'VARCHAR('||(decode(length,0,1,length))|| ')',105,'MLSLABEL',106,'MLSLABEL',112,decode(charsetform,2,'NCLOB','CLOB'),113,'BLOB',114,'BFILE',115,decode(charsetform,2,'NCFILE','CFILE'),178,'TIME('||scale||')',179,'TIME('||scale||') WITH TIME ZONE',180,'TIMESTAMP('||scale||')',181,'TIMESTAMP('||scale||') WITH TIME ZONE',231,'TIMESTAMP('||scale||') WITH LOCAL TIME ZONE',182,'INTERVAL YEAR('||NVL(precision#, 0)||') TO MONTH',183,'INTERVAL DAY('||NVL(precision#, 0)||') TO SECOND('||scale||')',208,'UROWID('||spare1||')',100,'BINARY_FLOAT',101,'BINARY_DOUBLE','UNDEFINED')||decode(null$,0,'',' NOT NULL')||decode(bitand(property, 32), 0, ',', ' HIDDEN,'),name, type#, intcol#, null$, bitand(property, 32) from col$ where obj#=:1 and (bitand(property,32) = 0 or bitand(property, 4194304) <> 0) order by col#
END OF STMT
PARSE #1:c=0,e=4219,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1317336787273303
EXEC #1:c=8000,e=7125,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1317336787280788
FETCH #1:c=0,e=210,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1317336787281132
FETCH #1:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1317336787281197
STAT #1 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT ORDER BY (cr=3 pr=0 pw=0 time=218 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 bj=21 p='TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=166 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 bj=3 p='INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=47 us)'
=====================
PARSING IN CURSOR #2 len=124 dep=0 uid=62 ct=47 lid=62 tim=1317336787286593 hv=3125882740 ad='36b53fac'
declare
l_count int;
begin
for i in 1..100 loop
select count(*) into l_count from t where rn=i;
end loop;
end;
END OF STMT
PARSE #2:c=20001,e=18649,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,tim=1317336787286584
=====================
PARSING IN CURSOR #4 len=36 dep=1 uid=62 ct=3 lid=62 tim=1317336787289845 hv=2059482344 ad='3ec49298'
SELECT COUNT(*) FROM T WHERE RN=:B1
END OF STMT
PARSE #4:c=0,e=2771,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1317336787289838
=====================
PARSING IN CURSOR #1 len=69 dep=2 uid=0 ct=3 lid=0 tim=1317336787293485 hv=1471956217 ad='3eedc130'
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
END OF STMT
PARSE #1:c=0,e=3025,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1317336787293477
EXEC #1:c=8001,e=3725,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1317336787297443
FETCH #1:c=0,e=59,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1317336787297615
STAT #1 id=1 cnt=0 pid=0 pos=1 bj=31 p='TABLE ACCESS BY INDEX ROWID CDEF$ (cr=2 pr=0 pw=0 time=69 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 bj=52 p='INDEX RANGE SCAN I_CDEF3 (cr=2 pr=0 pw=0 time=59 us)'
=====================
PARSING IN CURSOR #3 len=146 dep=2 uid=0 ct=3 lid=0 tim=1317336787300845 hv=2107929772 ad='3eeddbcc'
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=3028,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1317336787300838
EXEC #3:c=12000,e=3929,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1317336787305035
FETCH #3:c=0,e=53,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1317336787305202
STAT #3 id=1 cnt=0 pid=0 pos=1 bj=31 p='TABLE ACCESS BY INDEX ROWID CDEF$ (cr=2 pr=0 pw=0 time=63 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 bj=51 p='INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=51 us)'
=====================
PARSING IN CURSOR #1 len=375 dep=2 uid=62 ct=3 lid=62 tim=1317336787309140 hv=2501710195 ad='3ed4db50'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."RN"=:B1 THEN 1 ELSE 0 END AS C2 FROM "T" "T") SAMPLESUB
END OF STMT
PARSE #1:c=0,e=3326,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1317336787309134
EXEC #1:c=0,e=3790,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1317336787313147
FETCH #1:c=0,e=178,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=1,tim=1317336787313409
STAT #1 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=3 pr=0 pw=0 time=187 us)'
STAT #1 id=2 cnt=100 pid=1 pos=1 bj=98308 p='TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=307 us)'
--
EXEC #4:c=28002,e=26567,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,tim=1317336787316574
FETCH #4:c=0,e=94,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1317336787316746
EXEC #4:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1317336787316895
FETCH #4:c=0,e=42,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1317336787316974
EXEC #4:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1317336787317066
FETCH #4:c=0,e=45,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1317336787317147
..刪除96組EXEC,FETCH
EXEC #4:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1317336787332809
FETCH #4:c=0,e=38,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1317336787332882
--
EXEC #2:c=40003,e=46012,p=0,cr=308,cu=0,mis=0,r=1,dep=0,og=1,tim=1317336787332947
++++++++++++++++++++++++++++++
結論:
*在繫結變數情況下,一次解析,多次執行;在該語句執行期間,EXECUTE TO PARSE = (1-parse/execute)*100% =94%
*還是無法理解硬解析次數的缺失
9，為了驗證硬解析次數無法解釋問題，再次測試繫結變數情況：
為了避免軟解析，刪除表t，並重建。
drop table t;
create table t
as
select rownum rn from dual connect by level <=100;
初始狀態:
--session 2
SELECT sn.NAME, st.VALUE
FROM v$sesstat st, v$statname sn
WHERE st.sid = 254
AND st.statistic# = sn.statistic#
AND sn.NAME IN ('execute count', 'parse count (failures)',
'parse count (hard)', 'parse count (total)')
ORDER BY sn.statistic#;
parse count (total) 155
parse count (hard) 47
parse count (failures) 4
execute count 362
--session 1
declare
l_count int;
begin
for i in 1..100 loop
select count(*) into l_count from t where rn=i;
end loop;
end;
/
--session 2
SELECT sn.NAME, st.VALUE
FROM v$sesstat st, v$statname sn
WHERE st.sid = 254
AND st.statistic# = sn.statistic#
AND sn.NAME IN ('execute count', 'parse count (failures)',
'parse count (hard)', 'parse count (total)')
ORDER BY sn.statistic#;
parse count (total) 165 -->+10
parse count (hard) 61 -->+14
parse count (failures) 4 -->+0
execute count 473 -->+111
跟蹤檔案中對應:
10次parse count (total)包括:
遞迴呼叫8次
匿名過程1次
查詢語句1次
其中：軟解析2次，硬解析8次
14次parse count (hard)包括:
遞迴呼叫6次
匿名過程1次
查詢語句1次
還有6次硬解析在哪裡? ****
111次execute count包括:
遞迴呼叫10次
匿名過程1次
查詢語句100次
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
=====================
PARSING IN CURSOR #4 len=1664 dep=1 uid=0 ct=3 lid=0 tim=1317338193345899 hv=610427736 ad='3edd25b8'
select '"'||name||'" '||decode(type#,1,decode(charsetform,2,'NVARCHAR2('||decode(bitand(property,8388608),8388608,decode(spare3,0,1,spare3)||')','BYTECOUNT=>'||decode(length,0,1,length)||')'),'VARCHAR2('||decode(bitand(property,8388608),8388608,decode(spare3,0,1,spare3)||' char)',decode(length,0,1,length)||' byte)')),2,decode(scale,null,decode(precision#,null,'NUMBER','FLOAT('||precision#||')'),'NUMBER('||decode(precision#,null,'38', precision#)||','||scale||')'),8,'LONG',9,'VARCHAR('||(decode(length,0,1,length))||')',12,'DATE',23,'RAW('||length||')',24,'LONG RAW',69,'ROWID',96,decode(charsetform,2,'NCHAR('||decode(bitand(property,8388608),8388608,decode(spare3,0,1,spare3)||')','BYTECOUNT=>'||decode(length,0,1,length)||')'),'CHAR('||decode(bitand(property,8388608),8388608,decode(spare3,0,1,spare3)||' char)',decode(length,0,1,length)||' byte)')),97,'VARCHAR('||(decode(length,0,1,length))|| ')',105,'MLSLABEL',106,'MLSLABEL',112,decode(charsetform,2,'NCLOB','CLOB'),113,'BLOB',114,'BFILE',115,decode(charsetform,2,'NCFILE','CFILE'),178,'TIME('||scale||')',179,'TIME('||scale||') WITH TIME ZONE',180,'TIMESTAMP('||scale||')',181,'TIMESTAMP('||scale||') WITH TIME ZONE',231,'TIMESTAMP('||scale||') WITH LOCAL TIME ZONE',182,'INTERVAL YEAR('||NVL(precision#, 0)||') TO MONTH',183,'INTERVAL DAY('||NVL(precision#, 0)||') TO SECOND('||scale||')',208,'UROWID('||spare1||')',100,'BINARY_FLOAT',101,'BINARY_DOUBLE','UNDEFINED')||decode(null$,0,'',' NOT NULL')||decode(bitand(property, 32), 0, ',', ' HIDDEN,'),name, type#, intcol#, null$, bitand(property, 32) from col$ where obj#=:1 and (bitand(property,32) = 0 or bitand(property, 4194304) <> 0) order by col#
END OF STMT
PARSE #4:c=0,e=88,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1317338193345892
EXEC #4:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1317338193346245
FETCH #4:c=0,e=116,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1317338193346407
FETCH #4:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1317338193346467
STAT #4 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT ORDER BY (cr=3 pr=0 pw=0 time=126 us)'
STAT #4 id=2 cnt=1 pid=1 pos=1 bj=21 p='TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=76 us)'
STAT #4 id=3 cnt=1 pid=2 pos=1 bj=3 p='INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=32 us)'
=====================
PARSING IN CURSOR #1 len=85 dep=1 uid=0 ct=2 lid=0 tim=1317338193350730 hv=4112287963 ad='36bd254c'
insert into idl_sb4$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
END OF STMT
PARSE #1:c=0,e=3165,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1317338193350722
EXEC #1:c=8001,e=4809,p=0,cr=1,cu=5,mis=1,r=1,dep=1,og=4,tim=1317338193355780
EXEC #1:c=0,e=137,p=0,cr=0,cu=3,mis=0,r=1,dep=1,og=4,tim=1317338193356054
=====================
PARSING IN CURSOR #1 len=85 dep=1 uid=0 ct=2 lid=0 tim=1317338193359166 hv=1328987218 ad='36bd2ba0'
insert into idl_ub1$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
END OF STMT
PARSE #1:c=0,e=3013,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1317338193359158
EXEC #1:c=12001,e=3628,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=4,tim=1317338193363025
=====================
PARSING IN CURSOR #1 len=86 dep=1 uid=0 ct=2 lid=0 tim=1317338193366428 hv=842085046 ad='391b75b8'
insert into idl_char$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
END OF STMT
PARSE #1:c=0,e=3221,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1317338193366422
EXEC #1:c=0,e=3598,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=4,tim=1317338193370250
=====================
PARSING IN CURSOR #1 len=85 dep=1 uid=0 ct=2 lid=0 tim=1317338193373395 hv=2485836049 ad='39228204'
insert into idl_ub2$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
END OF STMT
PARSE #1:c=8000,e=2997,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1317338193373388
EXEC #1:c=0,e=3394,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=4,tim=1317338193377012
EXEC #1:c=0,e=170,p=0,cr=0,cu=3,mis=0,r=1,dep=1,og=4,tim=1317338193377323
=====================
PARSING IN CURSOR #4 len=331 dep=1 uid=0 ct=6 lid=0 tim=1317338193380713 hv=2997034431 ad='393190e8'
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
END OF STMT
PARSE #4:c=0,e=3270,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1317338193380708
EXEC #4:c=12001,e=5464,p=0,cr=4,cu=1,mis=1,r=1,dep=1,og=4,tim=1317338193386433
STAT #4 id=1 cnt=0 pid=0 pos=1 bj=0 p='UPDATE OBJ$ (cr=4 pr=0 pw=0 time=470 us)'
STAT #4 id=2 cnt=1 pid=1 pos=1 bj=37 p='INDEX RANGE SCAN I_OBJ2 (cr=4 pr=0 pw=0 time=135 us)'
=====================
PARSING IN CURSOR #3 len=124 dep=0 uid=62 ct=47 lid=62 tim=1317338193391189 hv=3125882740 ad='36b53fac'
declare
l_count int;
begin
for i in 1..100 loop
select count(*) into l_count from t where rn=i;
end loop;
end;
END OF STMT
PARSE #3:c=40003,e=46427,p=0,cr=11,cu=22,mis=1,r=0,dep=0,og=1,tim=1317338193391181
=====================
PARSING IN CURSOR #1 len=36 dep=1 uid=62 ct=3 lid=62 tim=1317338193394328 hv=2059482344 ad='3ec49298'
SELECT COUNT(*) FROM T WHERE RN=:B1
END OF STMT
PARSE #1:c=8001,e=2744,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1317338193394320
=====================
PARSING IN CURSOR #4 len=210 dep=2 uid=0 ct=3 lid=0 tim=1317338193395174 hv=864012087 ad='3ee48d98'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #4:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=1317338193395170
EXEC #4:c=0,e=81,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=1317338193395560
FETCH #4:c=0,e=89,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=3,tim=1317338193395756
STAT #4 id=1 cnt=0 pid=0 pos=1 bj=255 p='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=105 us)'
STAT #4 id=2 cnt=0 pid=1 pos=1 bj=257 p='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=88 us)'
=====================
PARSING IN CURSOR #2 len=375 dep=2 uid=62 ct=3 lid=62 tim=1317338193399413 hv=2501710195 ad='3ed4db50'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."RN"=:B1 THEN 1 ELSE 0 END AS C2 FROM "T" "T") SAMPLESUB
END OF STMT
PARSE #2:c=0,e=3280,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1317338193399406
EXEC #2:c=12000,e=4256,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1317338193403874
FETCH #2:c=0,e=505,p=1,cr=3,cu=0,mis=0,r=1,dep=2,og=1,tim=1317338193404457
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=3 pr=1 pw=0 time=839 us)'
STAT #2 id=2 cnt=100 pid=1 pos=1 bj=98309 p='TABLE ACCESS FULL T (cr=3 pr=1 pw=0 time=540 us)'
--
EXEC #1:c=12000,e=12967,p=1,cr=6,cu=0,mis=1,r=0,dep=1,og=1,tim=1317338193407521
FETCH #1:c=0,e=83,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1317338193407687
EXEC #1:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1317338193407832
FETCH #1:c=0,e=143,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1317338193408011
EXEC #1:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1317338193408110
FETCH #1:c=0,e=47,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1317338193408193
..刪除96組EXEC,FETCH
EXEC #1:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1317338193424015
FETCH #1:c=0,e=37,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1317338193424088
--
EXEC #3:c=40002,e=32673,p=1,cr=306,cu=0,mis=0,r=1,dep=0,og=1,tim=1317338193424153
+++++++++++++++++++++++++++++++++++++++++++++++++
結論:
*在繫結變數情況下,一次解析,多次執行;在該語句執行期間,EXECUTE TO PARSE = (1-parse/execute)*100% =91%
*在遞迴呼叫中,也是繫結變數執行的,如跟蹤檔案中hv=4112287963的語句,解析一次,執行了兩次.
*還是無法理解硬解析次數的缺失
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-696962/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 理解EXECUTE_TO_PARSE(一)
- Reactor模式理解（二）React模式
- 深入理解 GCD（二）GC
- 理解最小二乘解
- 理解.NET中的CLR原理(二)
- 非同步（二）：Generator深入理解非同步
- 我所理解的雲原生（二）
- 【譯】理解Rust中的Futures（二）Rust
- 理解 TCP（二）：報文結構TCP
- [深入理解Android卷二 全文-第二章]深入理解Java Binder和MessageQueueAndroidJava
- 深入理解Java中的鎖（二）Java
- dubbo個人理解於應用章（二）
- 深入理解hashmap（二）理論篇HashMap
- 二維陣列的指標的理解陣列指標
- 深入理解Plasma（二）Plasma 細節ASM
- 理解JVM（二）：垃圾收集演算法JVM演算法
- 機器學習：深入理解LSTM網路 (二)機器學習
- Spring(二)：IOC和DI的理解Spring
- 理解.NET中的CLR原理(二) (轉)
- LuaView SDK第二版設計外掛化理解(二)View
- Java集合詳解（二）：ArrayList原理解析Java
- 深入理解Java虛擬機器（二）Java虛擬機
- MySQL（二）：快速理解MySQL資料庫索引MySql資料庫索引
- 二分查詢的簡單理解
- Java8執行緒池理解（二）Java執行緒
- 大飛帶你深入理解Tomcat（二）Tomcat
- OCI介面學習筆記--基本理解（二）筆記
- 二，Java中常量與變數的理解Java變數
- [深入理解Android卷二 全文-第五章]深入理解PowerManagerServiceAndroid
- .NET Core 3.0之深入原始碼理解HttpClientFactory(二)原始碼HTTPclient
- SpringIOC二—— 容器 和 Bean的深入理解SpringBean
- 理解 Android Binder 機制（二）：C++層AndroidC++
- 關於MongoDB的簡單理解（二）--Java篇MongoDBJava
- 深入理解Emoji(二) —— 位元組序和BOM
- Egg入門學習(二)---理解service作用
- 深入理解二叉樹（超詳細）二叉樹
- 深入理解和應用display屬性（二）
- 大白話講解Promise（二）理解Promise規範Promise