Recent SQL

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		11.1.0.6
rem		10.2.0.3
rem		10.1.0.4
rem		 9.2.0.6
rem		 8.1.7.4**
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
	child_latch,	-- 9i (9.2 ?)
	executions,
	parse_calls,
	sorts,
	fetches,	-- 9i (9.2 ?)
	disk_reads,
	buffer_gets,
	rows_processed,
	cpu_time,	-- 9i
	elapsed_time,	-- 9i
	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.