Use V$SQLSTATS not V$SQL to find expensive SQL

Posted by

It’s a holiday here today in Perth, so a very brief blog post because I’ve been busy doing other things today Smile


When I’m contacted by customers or friends and asked to help out with tuning their applications, probably the two most common issues are one or two poorly performing SQL statements, or the server being overly stressed with CPU load with no single SQL being the obvious cause.

In either case, a common plan of attack is probe V$SQL to gauge what statements are potential trouble makers. Due to the myriad of statistical metrics stored in V$SQL it is a very flexible view to discover more about potential problems just by adjusting the predicates. For example:

High I/O?

order by DISK_READS desc

High CPU?

order by BUFFER_GETS desc

Poor parsing applications?


Memory hogs?

order by SHARABLE_MEM desc

each of which can be tweaked to hone into more specific requirements. But there is a risk to doing this. In particular, if you are diagnosing a system that is under duress, than hammering away at V$SQL may do more damage than good. Don’t forget that a system under duress is probably having its shared memory structures contended for just as heavily. And by aggressively querying V$SQL, you may be adding to that workload and/or contention.

You might be thinking this leaves in a Catch-22 situation – how can we diagnose a struggling system if diagnosing it will make it struggle more? Fortunately, a simple remedy is at hand.

Instead of querying V$SQL, try querying V$SQLSTATS instead. From the documentation:

V$SQLSTATS displays basic performance statistics for SQL cursors and contains one row per SQL statement (that is, one row per unique value of SQL_ID). The column definitions for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool). Note that V$SQLSTATS contains a subset of columns that appear in V$SQL and V$SQLAREA.

Obviously this isn’t a leave pass to smash away at this performance view, but by avoiding V$SQL you are steering clear of a critical shared memory structure which any session wishing to parse/execute SQL will be competing for. V$SQLSTATS has been present since 10g and should be a drop-in replacement for your existing queries that use V$SQL.


  1. Hello Connor, I am troubleshooting an issue where my listener keeps going UP & DOWN, probably due to the server being overly stressed with CPU load, & bad Code, so I am trying to pull all sql_fulltext during the time the issue happened.
    Will this query helps me to get the info?

    select sql_id,parsing_schema_name,service,module,FIRST_LOAD_TIME,last_load_time,sql_fulltext
    from v$sql
    where FIRST_LOAD_TIME >=’2020-09-02/10:00:01′
    and FIRST_LOAD_TIME <='2020-09-02/15:00:01'
    order by first_load_time asc;



Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.