Re: Help: my tkprof does not give rows info

Joe Bayer wrote:

> I am using ora8173 on solaris
>
> First I analyze the targeted tables and then
> alter session set sql_trace=true;
>
> then I run the query on sqlplus.
>
> Then I run
>
> tkprof archtest_ora_8597.trc output2.txt explain=perfstat/perfstat
>
> Everything seems working fine except the output2.txt, all the rows are
> 0, what I did was wrong?
>
> select /*+ rule */
> to_char(snap_time,'yyyy-mm-dd HH24:mi'),
> newmem.value-oldmem.value sorts_memory,
> newdsk.value-olddsk.value sorts_disk,
> round(((newdsk.value-olddsk.value)/(newmem.value-oldmem.value))
> *100)/100 ratio
> From perfstat.stats$snapshot sn ,
> perfstat.stats$sysstat oldmem,
> perfstat.stats$sysstat newmem,
> perfstat.stats$sysstat newdsk,
> perfstat.stats$sysstat olddsk
> Where snap_time > sysdate - 1
> and newdsk.snap_id = sn.snap_id
> and olddsk.snap_id = (select max(snap_id) from stats$snapshot where
> snap_id < s
> n.snap_id)
> and oldmem.snap_id = (select max(snap_id) from stats$snapshot where
> snap_id <
> sn.snap_id)
> and newmem.snap_id = sn.snap_id
> and oldmem.name = 'sorts (memory)'
> and newmem.name = 'sorts (memory)'
> and olddsk.name = 'sorts (disk)'
> and newdsk.name = 'sorts (disk)'
> and newmem.Value -oldmem.Value > 0
> order by to_char(snap_time,'yyyy-mm-dd HH24:mi')
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0 0
> Execute 1 0.00 0.00 0 0 0 0
> Fetch 20 1.79 1.85 51 6970 4 275
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 22 1.79 1.85 51 6970 4 275
>
> Misses in library cache during parse: 0
> Optimizer goal: RULE
> Parsing user id: 42 ()
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: HINT: RULE
> 0 SORT (ORDER BY)
> 0 NESTED LOOPS
> 0 NESTED LOOPS
> 0 NESTED LOOPS
> 0 NESTED LOOPS
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'STATS$SYSSTAT'
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'STATS$SNAPSHOT'
> 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'STATS$SNAPSHOT_PK' (UNIQUE)
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'STATS$SYSSTAT'
> 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'STATS$SYSSTAT_PK' (UNIQUE)
> 0 SORT (AGGREGATE)
> 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'STATS$SNAPSHOT_PK' (UNIQUE)
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'STATS$SYSSTAT'
> 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'STATS$SYSSTAT_PK' (UNIQUE)
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'STATS$SYSSTAT'
> 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'STATS$SYSSTAT_PK'
> (UNIQUE)
> 0 SORT (AGGREGATE)
> 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'STATS$SNAPSHOT_PK' (UNIQUE)
>
> *********************************************************************
> *********
>
> I tried first_rows,all_rows, the result is the same, no rows info.
>
> Could somebody help me out?
>
> thanks
>
>

Hi,

The statistics say the select fetched 257 rows and had 51 disk io's. Try tkprof without explain=perf..., then you get the actual explain plan from your trace file at execute time. With explain=, you use the explain plan system, and get row results from the time you run the tkprof, not from the trace file and not from execution time, but row counts (and others, use of a newly created index,... things that didnt exist at trace time) ie. explain plan results of "current" tkprof time. The drawback is that some objects are not properly written by name but by object id, i.e
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'STATS$SYSSTAT_PK' can turn into something like INDEX GOAL: ANALYZED (RANGE SCAN) OF (object 65)

/Svend
Oracle DBA OCP3 Received on Fri May 03 2002 - 14:42:45 CDT