ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 理解EXECUTE_TO_PARSE(二)

理解EXECUTE_TO_PARSE(二)

原创 Linux操作系统 作者:redhouser 时间:2011-06-02 13:29:28 0 删除 编辑

目的:
    为了深入理解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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    826349