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

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@xxxxxxxxx> 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@xxxxxxxxx> wrote:
> From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
> Subject: Re: ** identify time/number for individual table, index accesss
> within a sql
> To: ajoshi977@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx, ajoshi97@xxxxxxxxx
> 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@xxxxxxxxx> 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


Other related posts: