Re: How to find the 10 worst performing SQL statements?

Check v$sqlarea intermittently for

    extreme numbers of executions
    extreme numbers of buffer_gets
    extreme numbers of disk_reads

Once you are into 'expensive' statements, there is no one 'magic number' that says one statement is definitely worse than
another - but the above gives you an idea of which statements may need to be
addressed urgently.

See scripts on my website listed below.
Follow

    index of topics -> Monitoring and Tuning -> Tuning

--
Jonathan Lewis

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

For latest news of public appearances
See http://www.jlcomp.demon.co.uk

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.




Andy wrote in message <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