Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Utilities Tips

Analyzing tkprof Results

Step 6: Load tkprof Results into Tables

Loading tkprof data into the database is optional, but it can be worthwhile for those DBAs who want historical data or the ability to access data via SQL queries to generate reports. The command used earlier specified insert=tkprof.sql which generated the following SQL in tkprof.sql:

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
);
INSERT INTO tkprof_table values
(
SYSDATE, 1, 0, 59, 0, 0, 0, 0, 0, 0, 0
, 1, 0, 192, 0, 0, 0, 1, 0
, 0, 0, 0, 0, 0, 0, 0, 4294966155
, 'alter session set sql_trace=true
');
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 0, 1, 0, 1232, 0, 0, 0, 1
, 1, 0, 745, 0, 0, 0, 0, 0
, 1, 0, 115, 0, 3, 0, 1, 17866289
, 'select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare
2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or r
emoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is
null)and(subname=:6 or subname is null and :6 is null)
');
INSERT INTO tkprof_table VALUES
(
SYSDATE, 3, 1, 0, 1, 0, 1400, 0, 0, 0, 1
, 1, 0, 658, 0, 0, 0, 0, 0
, 1, 0, 131, 0, 3, 0, 1, 5463
, 'select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0), audit
$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt, avgspc,chncnt,a
vgrln,analyzetime, samplesize,cols,property,nvl(degree,1),nvl(instances,1),avgsp
c_flb,flbcnt,kernelcols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4,spar
e6 from tab$ where obj#=:1
');
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 1, 0, 2, 0, 1110, 0, 0, 0, 1
, 2, 15625, 757, 0, 0, 0, 0, 0
, 2, 0, 221, 0, 6, 0, 2, 8966
, 'select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts, NVL(
lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 4, 1, 0, 1, 0, 1802, 0, 0, 0, 1
, 1, 0, 1089, 0, 0, 0, 0, 0
, 2, 0, 489, 0, 5, 0, 1, 23441
, '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.in
stances,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,spare2,spare6,
decode(i.pctthres$,null,null, mod(trunc(i.pctthres$/256),256)) from ind$ i, (se
lect enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#, mi
n(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 gr
oup by enabled) c where i.obj#=c.enabled(+) and i.bo#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 5, 1, 0, 1, 0, 910, 0, 0, 0, 1
, 1, 0, 573, 0, 0, 0, 0, 0
, 2, 0, 147, 0, 3, 0, 1, 5409
, 'select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 6, 1, 0, 1, 15625, 1426, 0, 0, 0, 1
, 1, 0, 775, 0, 0, 0, 0, 0
, 6, 0, 1744, 0, 3, 0, 5, 10773
, '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,18
3,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$, rowid,col#,pro
perty, 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, 8, 1, 0, 1, 0, 831, 0, 0, 0, 1
, 1, 0, 597, 0, 0, 0, 0, 0
, 1, 0, 59, 0, 1, 0, 0, 5736
, 'select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 9, 1, 0, 1, 0, 973, 0, 0, 0, 1
, 1, 0, 650, 0, 0, 0, 0, 0
, 1, 0, 43, 0, 2, 0, 0, 5050
, '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, 1, 0, 59, 1, 31250, 58068, 1, 1, 0, 1
, 1, 0, 85, 0, 0, 0, 0, 0
, 2, 0, 37301, 3, 4, 0, 1, 39511
, ' select * from employee where emp_id = 87933
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 2, 1, 0, 2, 0, 1122, 0, 0, 0, 1
, 2, 0, 672, 0, 0, 0, 0, 0
, 2, 0, 178, 0, 6, 0, 2, 12416444
, 'select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname, o.datao
bj#,o.flags from obj$ o where o.obj#=:1
');
INSERT INTO tkprof _table VALUES
(
SYSDATE, 1, 0, 59, 1, 0, 353, 0, 0, 0, 1
, 1, 0, 148, 0, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 1160
, 'alter session set sql_trace=false
');


This file contains the DDL to create the table as well as the data to load. If the table already exists, the error will be ignored when it tries to create the table again.


 

To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.


 

  ”call


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.