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

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: <ajoshi977@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 25 Jan 2009 15:01:08 -0500

The row access information is in the raw trace.  I do not work with raw
traces very much and cannot remember the index entry access as being
recorded.  Rather I think the index entry access time is embedded in the
table row access though I am sure people who have reason to have to use
the raw trace data in more detail can update/correct this comment.
 

-- Mark D Powell -- 
Phone (313) 592-5148 

 


________________________________

        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of A Joshi
        Sent: Saturday, January 24, 2009 10:17 AM
        To: oracle-l@xxxxxxxxxxxxx; Powell, Mark D
        Subject: RE: ** identify time/number for individual table, index
accesss within a sql
        
        
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