Re: Frequent Queries

From: John K. Hinsdale <hin_at_alma.com>
Date: 22 Jan 2007 03:17:24 -0800
Message-ID: <1169464644.119848.249510@38g2000cwa.googlegroups.com>

> Steve Howard wrote:
> > > Is there a way to somehow record what the most frequent running queries
> > > are in your database? We want to know what our most frequently used
> > > queries are............
> >
> > Depending on your version of Oracle and your license, look into
> > DBA_HIST_SQLSTAT (if on 10.x), or whatever the corresponding table name
> > is in STASPACK if you are on less than 10.x.

Mark D Powell wrote:
> If you do not have the Performance Pack ... you can create a
> process where you sample the v$sql views to find the most executed SQL
> statements on a regular basis and keep a record of the SQL and execution
> counts.

Steve, if it helps, this is the query I use to find the most frequently run SQL statements:

    SELECT SA.executions,

           FLOOR(SA.elapsed_time / SA.executions) as avgtime,
           SA.elapsed_time as total_time,
           AU.username, SA.sql_text

    FROM sys.gv_$sqlarea SA, sys.all_users AU     WHERE SA.parsing_user_id = AU.user_id       AND SA.executions > 0
    ORDER BY executions DESC

I presume you're doing some sort of crude tuneup. That's fine, but a couple caveats:

• The most frequently run statements are not necessarily the greatest source of aggregate overall load on the the system. Pay attention to the average (per execution) and total (since server startup) time stats in the above query.
• That said, even if (a) a very frequently run SQL statement is not the #1 _overall_ cost component and (b) the SQL statement is so simple (or already tuned) that you cannot optimize it further, you can still take steps to optimize the calling application NOT to run it repeatedly -- say by caching information. This is a very worthwhile exercise, as many hundreds/thousands of executions incur the overhead of an Oracle "round trip" and that latency, seen by your application(s) will not be included in the Oracle server-side stats above.
• Finally: $SQLAREA is *not* a comprehensive record of all the executed SQL! It can be incomplete. $SQLAREA's primary purpose is as a cache for the compiled queries, and not as a performance auditing tool. But don't let hat stop you. ;)

Because the numbers in $SQLAREA are cumulative, increasing from server startup, I've found it can be enough just to let it build up over a week or so of representative load, and that single sample enough to get a broad clue as to the SQL "profile" of ones apps. If things are more unpredictable and diverse, you can sample at intervals as Mark suggested.

Hope this helps,

        John Hinsdale Received on Mon Jan 22 2007 - 05:17:24 CST