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

From: A Joshi <ajoshi977_at_yahoo.com>
Date: Thu, 22 Jan 2009 08:02:14 -0800 (PST)
Message-ID: <237210.68378.qm_at_web57506.mail.re1.yahoo.com>


  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

Received on Thu Jan 22 2009 - 10:02:14 CST

Original text of this message