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

From: (wrong string) 조동욱 <ukja.dion_at_gmail.com>
Date: Sun, 25 Jan 2009 13:28:19 +0900
Message-ID: <43c2e3d60901242028l62190404q9715136d64f11632_at_mail.gmail.com>

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_at_yahoo.com>

> 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_at_eds.com>* wrote:
>
> From: Powell, Mark D <mark.powell_at_eds.com>
> Subject: RE: ** identify time/number for individual table, index accesss
> within a sql
> To: ajoshi977_at_yahoo.com, oracle-l_at_freelists.org
> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *A Joshi
> *Sent:* Thursday, January 22, 2009 11:02 AM
> *To:* oracle-l_at_freelists.org; ajoshi97_at_yahoo.com
> *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
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jan 24 2009 - 22:28:19 CST