Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to find the 10 worst performing SQL statements?
Another Good way is to run a statspack snapshot.
then in a couple of hours run another.
Then Run a statspack report and you will see a nice list of your heavy
hitter
SQL
Stephen C. Ashmore
Brainbench MVP for Oracle Administration
http://www.brainbench.com
"Ford" <get4ked_at_yahoo.com> wrote in message
news:b2b9197f.0108211654.3bb2e2f8_at_posting.google.com...
> Does'nt seem much point in turning on sql-trace till you've identified
> the statements - look in v$sqlarea for those with the most
> buffer_gets/execute (cpu in effect), physical_reads/execute and those
> that have been executed a lot - pretty much what statspack does - then
> trace those statements. 9i has the cpu time as well in v$sqlarea.
>
> enzoweb_at_hotmail.com (Andy) wrote in message
news:<8d4033cd.0108211451.43a38283_at_posting.google.com>...
> > I have been asked to find out the 10 worst performing SQL statements
> > on a V816 database on Solaris.
> >
> > The only way I can think of doing it is to turn SQL_TRACE on
> > (TIMED_STATS are already on), and run tkprofs against the resulting
> > trace files at the end of each day. Then grep the results to pick out
> > the stat I want and filter through them.
> >
> > Without installing Stats pack, is this the best way?
> >
> > Also, what should I look for - elapsed time, CPU time, or I/O?
> >
> > TIA,
> > Andy
Received on Tue Aug 21 2001 - 15:29:15 CDT