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

  • From: 조동욱 <ukja.dion@xxxxxxxxx>
  • To: ajoshi977@xxxxxxxxx
  • Date: Sun, 25 Jan 2009 13:28:19 +0900

Isn't the tkprof report enough to meet your requirement?

Row source operation and corresponding statistics(cr, rows, pr, pw, time,
...) look like exactly what your want.


================================
Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
================================


2009/1/25 A Joshi <ajoshi977@xxxxxxxxx>

> Mark,
>    Thanks. Yes, I see explain plan gives an idea for the number of
> table/index access. However, I want to know the actual time taken for index
> access and the actual number of times index was accessed. I know this
> information can be gotten from 10046. Meaning : total time for query, time
> for table and index accesses within this query. I do not know how to get it.
> Can someone help. Thanks
>
> --- On *Thu, 1/22/09, Powell, Mark D <mark.powell@xxxxxxx>* wrote:
>
> From: Powell, Mark D <mark.powell@xxxxxxx>
> Subject: RE: ** identify time/number for individual table, index accesss
> within a sql
> To: ajoshi977@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
> Date: Thursday, January 22, 2009, 12:14 PM
>
>  If you have a trace why not run tkprof on it and look at the summary
> information as a starting point.  Also one of the tkprof options is to show
> the explain plan which would allow you to see the index/non-index accesses.
>
> -- Mark D Powell --
> Phone (313) 592-5148
>
>
>  ------------------------------
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *A Joshi
> *Sent:* Thursday, January 22, 2009 11:02 AM
> *To:* oracle-l@xxxxxxxxxxxxx; ajoshi97@xxxxxxxxx
> *Subject:* ** identify time/number for individual table, index accesss
> within a sql
>
>   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
>
>
>
>

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2009