If you’re doing a little casual trouble-shooting, it’s always worth an occasional browse through v$sql just on the off-chance that you’ll find some sort of resource hog that could do with a little fixing.
There are many ways to slice and dice your way through v$sql, but here’s one that I often use when asked to do a quick health-check – especially if a Statspack / AWR report sugggests that the amount of “hard parsing” is unreasonable. The aim is simple – list any SQL that appeared in the last N minutes:
rem rem Script: recent_sql.sql rem Author: Jonathan Lewis rem Dated: Oct 2001 rem Last update: Oct 2005 rem rem Last tested rem 184.108.40.206 rem 10.2.0.3 rem 10.1.0.4 rem 220.127.116.11 rem 18.104.22.168** rem rem Quick and dirty to get the most recently rem arrived bits of SQL from V$sql. rem rem This may be worth running if you see from rem Statspack (AWR) that you are reporting rem more hard parses than you think reasonable rem rem You may want to adjust the number of rem minutes into the past. rem rem The particular target of this query is to rem find 'literal string' SQL, so it sorts by rem sql_text as this tends to pull "identical" rem SQL statements together. rem rem The scan on v$sql will hit the library cache rem latches quite hard, so don't run this query rem casually - especially if you have a large rem shared_pool setting. rem rem ** there are some columns in the query rem that do not exist in 8i rem define m_minutes = 1 set timing off set linesize 160 set pagesize 100 set termout off column timestamp new_value m_timestamp column baseline new_value m_baseline select to_char(sysdate,'ddhh24mi') timestamp, to_char( sysdate - &m_Minutes / (24 * 60), 'yyyy-mm-dd/hh24:mi:ss' ) baseline from dual; spool recent_sql_&m_timestamp select first_load_time, hash_value, -- 9i and earlier -- sql_id, -- 10g and later child_latch, -- 9i (9.2 ?) and later executions, parse_calls, sorts, fetches, -- 9i (9.2?) and later disk_reads, buffer_gets, rows_processed, cpu_time, -- 9i and later elapsed_time, -- 9i and later sql_text from v$sql where first_load_time >= '&m_baseline' order by sql_text ; prompt Interval: &m_minutes minute(s) prompt Output filename: recent_sql_&m_timestamp..lst spool off set termout on ed recent_sql_&m_timestamp..lst
The code picks up sysdate, substracts one minute (which can be adjusted in the define at the top of the script), and formats the result so that it can be compared with the first_load_time column from v$sql.
As a convenience to the user, the script prints the snapshot interval, the output file name, and then starts an edit on its output.
There are plenty of other columns you might choose, and the ordering can be set to suit your purposes – an obvious alternative would be cpu_time or elapsed time. As the comments say, I like to list the data in order of sql_text as this makes it easy to pick up the SQL which is identical apart from the literal values. (Can I claim a worst-case record here of 16,000 statements in 5 minutes ?).
Just one warning: when you access v$sql, the only efficient entry points are by hash_value and sql_id. Any other access into v$sql (such as the ‘tablescan’ here, will hammer the library cache latches – possibly one or two gets for every object in the library cache – so don’t get too carried away running this script on a system with a large library cache.