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

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Thu, 22 Jan 2009 12:14:52 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A90360719C_at_usahm208.amer.corp.eds.com>



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 Thu Jan 22 2009 - 11:14:52 CST

Original text of this message