Re: ** identify time/number for individual table, index accesss within a sql

From: John Kanagaraj <john.kanagaraj_at_gmail.com>
Date: Tue, 27 Jan 2009 18:08:56 -0800
Message-ID: <2ead3a60901271808sf15d454lbf20d20510a0cca5_at_mail.gmail.com>

Joshi,
Does the raw trace file have lines beginning with STAT? It is possible that you stopped the trace from the outside before the cursors were closed or you have an incomplete trace file w/out the STAT lines. Row source execution stats are present only in these lines and hold the information you seek. Can you check this and let us know? John

On 1/27/09, A Joshi <ajoshi977_at_yahoo.com> wrote:
> Thanks Riyaj. I am going thru and trying to identify.
>
> Thanks Mark for your input.
>
> --- On Mon, 1/26/09, Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com> wrote:
> From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
> Subject: Re: ** identify time/number for individual table, index accesss
> within a sql
> To: ajoshi977_at_yahoo.com
> Cc: oracle-l_at_freelists.org, ajoshi97_at_yahoo.com
> Date: Monday, January 26, 2009, 9:30 AM
>
> Hello Joshi
>
> If I understand correctly, you are trying to find which step of the
> plan is taking longer time? I blogged about this last year.
>
> http://orainternals.wordpress.com/2008/04/15/how-to-tune-sql-statements-scientifically/
>
>
> HTH
> --
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com
> Specialists in Performance, Recovery and EBS11i
> Blog: http://orainternals.wordpress.com
>
>
> On Thu, Jan 22, 2009 at 10:02 AM, A Joshi <ajoshi977_at_yahoo.com> wrote:
>
>
> Hi,
>
>
>
> For : OLTP database
>
>
> Oracle version : 10G
>
>
> OS : SunSolaris
>
>
>
> For a procedure taking a long time : I have taken a 10046 trace.
> From that I can do tkprof and get the times for individual
> sql. Now : How do I get time taken for and number of table, index
> accesses within a sql query. For example : i have sql joining upto 6
> tables and some are full table scan and some are index access. I want
> to know which one is taking time. Please help and thanks. I am
> able to estimate full table time by running it from sqlplus. I think
> full table with some checks takes longer. For index access : I can
> estimate for single index scan : however : i do not know how index
> scans are taking place. From the explain plan I was trying to go in the
> order : but there also doing the order is not easy. Can someone help me
> figure this out. Thanks
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

-- 
Sent from my mobile device

John Kanagaraj <><
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 27 2009 - 20:08:56 CST