Home » RDBMS Server » Performance Tuning » SQL-Performance Tuning
SQL-Performance Tuning [message #65122] Mon, 10 May 2004 22:11 Go to next message
Monu
Messages: 5
Registered: April 2004
Junior Member
I am new to tkprof and i need help on it.
i have some stored procedures and packages and i need to collect info. on them using tkprof.
i was able to do it as follows
sql>alter session set timed_statistics=true;
sql>alter session set sql_trace=true;
sql>set autotrace on;
then i ran these procedures and got the trace files,and then used the tkprof utility to get the statistics in an output file...!
Now what i want to know is that ,how do i read the tkprof output(ie interpretation of it)
there are some value in cpu,elapsed...etc

so does a low value of cpu ,elapsed is better for these procdures

So basically i want to know that the values given under cpu time,elapsed time can be used to infer that a procedure is running fine.

can you also tell me that whether hit ratio for a query be between 95-100%.

I have collected the following statistics using TKPROF,So can i assume that the second data collected is better since i have added optimizer hints in my procedures

                                  Before adding optimizer hints

call     count  cpu   elapsed  disk   query  current  rows
--------------------------------------------------------------
Parse      1    0.00   0.00       0       0         0          0
Execute   1    0.15   0.23       6       8        2          0
Fetch      0    0.00   0.00       0       0        0          0
------------- -------- ---------- ---------- -----------------
total      2     0.15   0.23       6        8         2           0

                                 After adding optimizer hints

call     count   cpu   elapsed     disk    query     current      rows
------- ------  -------- ---------- ---------- ---------- ------
Parse     1      0.00     0.00          0          0          0           0
Execute  1      0.09     0.07          0          0          2           0
Fetch     0      0.00     0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ------
total        2      0.09    0.07         0          0          2           0

Which data are better?

Thankx

Gaurav
Re: SQL-Performance Tuning [message #65134 is a reply to message #65122] Fri, 14 May 2004 10:20 Go to previous message
Satish Shrikhande
Messages: 167
Registered: October 2001
Senior Member
After adding optimizer hints
CPU Time is less
although no physical disk I/Os
Previous Topic: IO Advice
Next Topic: Acceptable total Waits ?
Goto Forum:
  


Current Time: Wed Sep 23 12:19:37 CDT 2020