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

  • From: A Joshi <ajoshi977@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx, "Powell, Mark D" <mark.powell@xxxxxxx>
  • Date: Sat, 24 Jan 2009 07:16:56 -0800 (PST)

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