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

  • From: A Joshi <ajoshi977@xxxxxxxxx>
  • To: 조동욱 <ukja.dion@xxxxxxxxx>
  • Date: Sun, 25 Jan 2009 09:19:01 -0800 (PST)

Dion,

   Thanks. I am using tkprof with parameters explain=uid/pwd sort=exeela

 sys=no and I do get overall numbers for a sql as below : 

  Event waited
on                            
Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file scattered
read                    
205461       
0.66        210.47

  db file sequential
read                   
101291       
0.14         13.49

  undo segment
extension                    
109844       
0.00          0.12

  buffer busy
waits                              
7       
0.00          0.00

  log file
sync                                  
6       
0.19          0.28

  latch
free                                   
580       
0.01          0.01

********************************************************************************



However, I would like to know each one for each table/index access
within the sql. My sql  has 5 tables accessed. Some full table
scan, some index scan. From the total number of index scan : how do i
know which one of the four took how much time. Do I need to give
different option for tkprof?  Can you or someone give detail.
Thanks for help. Thanks







--- On Sat, 1/24/09, 조동욱 <ukja.dion@xxxxxxxxx> wrote:
From: 조동욱 <ukja.dion@xxxxxxxxx>
Subject: Re: ** identify time/number for individual table, index accesss  
within a sql
To: ajoshi977@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx, "Powell, Mark D" <mark.powell@xxxxxxx>
Date: Saturday, January 24, 2009, 11:28 PM

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2009