sqltrace实战之：3.sql

63 阅读 0 评论 42 点赞

我是靠谱客的博主有魅力紫菜，最近开发中收集的这篇文章主要介绍sqltrace实战之：3.sql，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

REM Edit and/or remove the following CREATE TABLE
REM statement as your needs dictate.
CREATE TABLE tkprof_table
(
date_of_insert DATE
,cursor_num NUMBER
,depth NUMBER
,user_id NUMBER
,parse_cnt NUMBER
,parse_cpu NUMBER
,parse_elap NUMBER
,parse_disk NUMBER
,parse_query NUMBER
,parse_current NUMBER
,parse_miss NUMBER
,exe_count NUMBER
,exe_cpu NUMBER
,exe_elap NUMBER
,exe_disk NUMBER
,exe_query NUMBER
,exe_current NUMBER
,exe_miss NUMBER
,exe_rows NUMBER
,fetch_count NUMBER
,fetch_cpu NUMBER
,fetch_elap NUMBER
,fetch_disk NUMBER
,fetch_query NUMBER
,fetch_current NUMBER
,fetch_rows NUMBER
,ticks NUMBER
,sql_statement LONG
);
set sqlterminator off
INSERT INTO tkprof_table VALUES
(
SYSDATE, 3, 0, 61, 3, 0, 10817, 0, 0, 0, 1
, 3, 0, 113, 0, 0, 0, 0, 0
, 3, 0, 85, 0, 0, 0, 3, 31310358
, 'select ''x'' from dual
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 1, 0, 61, 6, 0, 171, 0, 0, 0, 0
, 6, 0, 487, 0, 0, 0, 0, 6
, 0, 0, 0, 0, 0, 0, 0, 94492
, 'begin :id := sys.dbms_transaction.local_transaction_id; end;
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 0, 61, 1, 0, 810, 0, 0, 0, 1
, 1, 0, 25, 0, 0, 0, 0, 0
, 1, 0, 29, 0, 0, 0, 1, 2343
, 'select sysdate from dual
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 1, 0, 61, 1, 0, 714, 0, 0, 0, 1
, 1, 0, 4458, 0, 0, 0, 1, 1
, 0, 0, 0, 0, 0, 0, 0, 3649
, 'begin
if :enable = 0 then
sys.dbms_output.disable;
else
sys.dbms_output.enable(:size);
end if;
end;
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 3, 2, 0, 3, 0, 1167, 0, 0, 0, 1
, 7, 0, 7466, 0, 0, 0, 1, 0
, 13, 15625, 5115, 0, 50, 0, 6, 13356
, 'select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 3, 0, 3, 0, 445, 0, 0, 0, 1
, 60, 0, 2184, 0, 0, 0, 1, 0
, 60, 0, 1749, 0, 175, 0, 54, 25290
, '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
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 3, 0, 3, 0, 433, 0, 0, 0, 1
, 20, 0, 1133, 0, 0, 0, 1, 0
, 20, 0, 20084, 2, 60, 0, 117, 24612
, 'select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 2, 0, 3, 0, 675, 0, 0, 0, 1
, 6, 0, 1532, 0, 0, 0, 1, 0
, 25, 0, 350, 0, 49, 0, 19, 2559
, 'select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 5, 2, 0, 3, 0, 557, 0, 0, 0, 1
, 7, 0, 2110, 0, 0, 0, 1, 0
, 40, 0, 732, 0, 21, 0, 33, 4416
, 'select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ where obj#=:1 order by intcol#
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 0, 2, 0, 2041, 0, 0, 0, 1
, 2, 0, 2207, 0, 0, 0, 1, 0
, 6, 15625, 144, 0, 16, 0, 4, 1275
, 'select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 0, 2, 0, 1333, 0, 0, 0, 1
, 2, 0, 2281, 0, 0, 0, 1, 0
, 4, 0, 10313, 1, 10, 0, 2, 11420
, 'select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 0, 2, 0, 1639, 0, 0, 0, 1
, 2, 15625, 2502, 0, 0, 0, 1, 0
, 4, 0, 421, 1, 10, 0, 2, 1786
, 'select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 0, 2, 0, 1349, 0, 0, 0, 1
, 2, 0, 2268, 0, 0, 0, 1, 0
, 4, 0, 8756, 1, 14, 0, 4, 9815
, 'select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 5, 1, 0, 3, 15625, 769, 0, 0, 0, 1
, 4, 0, 1617, 0, 0, 0, 1, 0
, 4, 0, 2557, 1, 16, 0, 4, 6227
, 'select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 3, 1, 0, 1, 0, 406, 0, 0, 0, 1
, 17, 0, 1265, 0, 0, 0, 1, 0
, 18, 0, 466, 0, 36, 0, 1, 3559
, 'select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0)
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 1, 0, 2, 0, 874, 0, 0, 0, 1
, 2, 15625, 2590, 0, 0, 0, 1, 0
, 2, 0, 160, 0, 8, 0, 2, 2480
, 'select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 1, 0, 2, 0, 578, 0, 0, 0, 1
, 2, 0, 1322, 0, 0, 0, 1, 0
, 2, 0, 77, 0, 6, 0, 2, 1447
, 'select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 1, 1, 0, 1, 0, 325, 0, 0, 0, 1
, 1, 0, 659, 0, 0, 0, 1, 0
, 1, 0, 38, 0, 3, 0, 1, 1196
, 'select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 0, 1, 0, 347, 0, 0, 0, 1
, 1, 0, 819, 0, 0, 0, 1, 0
, 1, 0, 39, 0, 2, 0, 0, 1488
, 'select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee#
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 1, 0, 1, 0, 324, 0, 0, 0, 1
, 1, 0, 726, 0, 0, 0, 1, 0
, 1, 0, 32, 0, 2, 0, 0, 1256
, 'select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 1, 1, 0, 1, 0, 314, 0, 0, 0, 1
, 1, 0, 572, 0, 0, 0, 1, 0
, 1, 0, 24, 0, 2, 0, 0, 1099
, 'select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 5, 1, 0, 1, 0, 351, 0, 0, 0, 1
, 1, 0, 1971, 0, 0, 0, 1, 0
, 1, 0, 25, 0, 2, 0, 0, 920
, '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
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 3, 0, 61, 1, 0, 3232, 0, 0, 0, 1
, 1, 0, 39, 0, 0, 0, 0, 0
, 1, 0, 1629, 0, 20, 0, 1, 2740
, 'select count(*) from t1
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 0, 61, 1, 0, 964, 0, 0, 0, 1
, 1, 0, 7663, 0, 0, 0, 1, 1
, 0, 0, 0, 0, 0, 0, 0, 10297
, 'declare
Lines sys.dbms_output.chararr;
begin
:NumLines := 10;
sys.dbms_output.get_lines(lines => Lines, numlines => :NumLines);
if :NumLines > 0 then :Line0 := Lines(1); end if;
if :NumLines > 1 then :Line1 := Lines(2); end if;
if :NumLines > 2 then :Line2 := Lines(3); end if;
if :NumLines > 3 then :Line3 := Lines(4); end if;
if :NumLines > 4 then :Line4 := Lines(5); end if;
if :NumLines > 5 then :Line5 := Lines(6); end if;
if :NumLines > 6 then :Line6 := Lines(7); end if;
if :NumLines > 7 then :Line7 := Lines(8); end if;
if :NumLines > 8 then :Line8 := Lines(9); end if;
if :NumLines > 9 then :Line9 := Lines(10); end if;
end;
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 5, 1, 0, 1, 0, 1502, 0, 0, 0, 1
, 1, 0, 1219, 0, 0, 0, 1, 0
, 1, 0, 30, 0, 3, 0, 1, 689
, 'select audit$,properties from type_misc$ where obj#=:1
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 0, 1, 0, 550, 0, 0, 0, 1
, 1, 0, 1944, 0, 0, 0, 1, 0
, 2, 0, 126, 0, 7, 0, 1, 1678
, 'select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 0, 1, 0, 1392, 0, 0, 0, 1
, 1, 0, 1236, 0, 0, 0, 1, 0
, 1, 0, 25, 0, 2, 0, 0, 656
, 'select order#,columns,types from access$ where d_obj#=:1
')
/
set sqlterminator on