Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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)?

Re: Re[2]: How to compare two different SQL executions plans (question is more deeper then seams from first look)?

From: <J.Velikanovs_at_alise.lv>
Date: Wed, 30 Jun 2004 11:05:56 +0300
Message-ID: <OFCF33CF9E.4AFB357C-ONC2256EC3.00273B6C-C2256EC3.002D29EA@alise.lv>


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



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
-----------------------------------------------------------------




-- 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US