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