| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re[2]: How to compare two different SQL executions plans (question is more deeper then seams from first look)?
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
SQL*Plus
statspack
use
- any developer have SQL*Plus and can easily use set autotrace feature
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_at_rs.lv>
Sent by: oracle-l-bounce_at_freelists.org
30.06.2004 09:21
Please respond to oracle-l
=20
To: "J.Velikanovs_at_alise.lv" <oracle-l_at_freelists.org>
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Jun 30 2004 - 03:10:18 CDT
 |
 |