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:05:56 +0300
Content-Type: text/plain; charset="windows-1257"
Content-Transfer-Encoding: quoted-printable
>> sure we can't, if it would be so simple, than optimizer wouldn't need=20
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,=20
physical reads" etc. columns available, but no CPU=5FTIME even in 10.1.0.2 =
SQL*Plus
statspack
- There are SQL-s TOPs by BUFFER GETS, PHYSICAL READS, EXECUTIONS=20
PARSE CALLS, but there isn't by CPU=5FTIME
.
Why I am speaking about those tools?=20
Because=20
- 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=20
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 =93CPU time=94 indicator value (and low=
=20
=93parse time cpu=94) then first place to look is TOP SQL-s. But there no T=
OP=20
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:=20
- I need to write some scripting (or use some free available) to provide=20
for developers information about CPU time, which is not available in=20
default tools.=20
- Statspack need additional TOP list of SQL-s sorted by CPU time. BTW I=20
done it and modified statspack report for 9.2 version attached. I hope=20
Oracle will add list sorted by CPU time in next version of statspack=20
report.
Best regards,
Jurijs
Edgar Chupit <edgar.chupit@xxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
30.06.2004 09:21
Please respond to oracle-l
=20
To: "J.Velikanovs@xxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
cc:=20
Subject: Re[2]: How to compare two different SQL executions =
plans (question is more deeper then seams from first look)?
Hello J,
JVal> But for CPU utilization we can=92t use LIOs statistics.
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. I think
that you are trying to simplify things.
But for your questions, to get top sql's by cpu you need to sort by
cpu=5Ftime, but you have to remember, that you want to get top sql over
"problematic" period of time, who cares if "huge" query is running
off-hours? So you need to check queries that had been run when
critical business process was running.
For a second question, you may I think lio minimization rule still
apply, but developers should consider to compare estimated cost of
query and latches.
--=20
Best regards,
Edgar
----------------------------------------------------------------
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
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/octet-stream
-- File: spreport_cpu.sql
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/octet-stream
-- File: sprepins_cpu.sql
----------------------------------------------------------------
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:
