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

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 22 Aug 2001 13:37:31 +0100
Message-ID: <3b83a78f$0$8506$ed9e5944@reading.news.pipex.net>

The home grown tool which I use for this purpose contains the java code shown at the bottom. Essentially what it does is query v$sqlarea (and dba_users) for the text of the statement and then populate a list view control with the results. Usually I then sort by disk_reads/execution which is a measure of the cost in physical io. Of course one might also choose to sort by memory or buffer gets (CPU). I hope this is of use. (the variable order is a numeric indicator of the column position in the select clause).

---

public void refresh ()
 {
  Statement statement;
  ResultSet resultSet;

  listView1.setItems (new ListItem[0]);

  try
  {
   statement = connection.createStatement();

   String query = "SELECT sql_text, sharable_mem + persistent_mem +

runtime_mem memory,";
   query = query + "sorts, executions, first_load_time, invalidations,";
   query = query + "parse_calls, disk_reads, buffer_gets, rows_processed,";
   query = query + "round(rows_processed/greatest(executions,1))
rows_per_execution,";
   query = query + "round(disk_reads/greatest(executions,1))
disk_per_execution,";
   query = query + "round(buffer_gets/greatest(executions,1))

buffer_per_execution,";

   query = query + "username ";
   query = query + " FROM v$sqlarea,dba_users";
   query = query + " WHERE parsing_user_id = user_id";
   query = query + " ORDER BY " + order + " DESC";

   resultSet = statement.executeQuery (query);

   while (resultSet.next ())
   {
    String sqltext = resultSet.getString (1);     String[] items = new String[13];

    items[0] = resultSet.getString (2);
    items[1] = resultSet.getString (3);
    items[2] = resultSet.getString (4);
    items[3] = resultSet.getString (5);
    items[4] = resultSet.getString (6);
    items[5] = resultSet.getString (7);
    items[6] = resultSet.getString (8);
    items[7] = resultSet.getString (9);
    items[8] = resultSet.getString (10);
    items[9] = resultSet.getString (11);
    items[10] = resultSet.getString (12);
    items[11] = resultSet.getString (13);
    items[12] = resultSet.getString("username");

    ListItem item = new ListItem (sqltext,items);

    listView1.addItem (item);
   }
   statement.close ();

---

HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message
news:3b8386a8.2937834_at_news...


> On 21 Aug 2001 15:51:33 -0700, enzoweb_at_hotmail.com (Andy) wrote:

>

> >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.

> >

>

> I'd think V$SQLAREA would be the first place to look?  Jonathan will

> certainly provide some input here.

>

>

> Cheers

> Nuno Souto

> nsouto_at_optushome.com.au.nospam

Received on Wed Aug 22 2001 - 07:37:31 CDT