Re: Re[2]: How to compare two different SQL executions plans (question ismore deeper then seams from first look)?
- From: J.Velikanovs@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 30 Jun 2004 11:20:36 +0300
------------ Please excuse me for repeated sending. I just wont to get
this message in normal form (without ?Content-Type: text/plain;
charset="windows-1257"?) Looks like our mail system issue.
>> sure we can't, if it would be so simple, than optimizer wouldn't need
to compute cost of each query, but simply estimate lio count.
Good point, thank you.
.
But take a look on available tools from oracle.
set autotrace on stat
- There are "recursive calls, db block gets, consistent gets,
physical reads" etc. columns available, but no CPU_TIME even in 10.1.0.2
SQL*Plus
statspack
- There are SQL-s TOPs by BUFFER GETS, PHYSICAL READS, EXECUTIONS
PARSE CALLS, but there isn't by CPU_TIME
.
Why I am speaking about those tools?
Because
- they are available by default in any Oracle installation and are frre to
use
- any developer have SQL*Plus and can easily use set autotrace feature
- my recommendation for tuning for Developers were: just switch on
autotrace and take a look on LIO
- spreport (statspack) widely used by customers and it is very easy to ask
to make reports for problematic period
- analyzing spreport, if I sow high ?CPU time? indicator value (and low
?parse time cpu?) then first place to look is TOP SQL-s. But there no TOP
by CPU time available jet (even in 10.1.0.2 spreport)
- even www.oraperf.com (Anjo Kolk) in recommendations use SQL-s TOP sorted
by BUFFERS
So, at the moment I realized that:
- I need to write some scripting (or use some free available) to provide
for developers information about CPU time, which is not available in
default tools.
- Statspack need additional TOP list of SQL-s sorted by CPU time. BTW I
done it and modified statspack report for 9.2 version attached (or
ftp://ftp.alise.lv/pub/oracle/sp/). I hope Oracle will add list sorted by
CPU time in next version of statspack report.
Best regards,
Jurijs
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
