Oracle Scratchpad

June 13, 2008

Recent SQL

Filed under: Troubleshooting — Jonathan Lewis @ 5:01 pm BST Jun 13,2008

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	Script:		recent_sql.sql
rem	Author:		Jonathan Lewis
rem	Dated:		Oct 2001
rem	Last update:	Oct 2005
rem	Last tested
rem	Quick and dirty to get the most recently
rem	arrived bits of SQL from V$sql.
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	You may want to adjust the number of
rem	minutes into the past.
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	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	** there are some columns in the query
rem	that do not exist in 8i

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

	to_char(sysdate,'ddhh24mi') 		timestamp,
		sysdate - &m_Minutes / (24 * 60),
	)					baseline

spool recent_sql_&m_timestamp

	hash_value,	-- 9i and earlier
--	sql_id,		-- 10g and later
	child_latch,	-- 9i (9.2 ?) and later
	fetches,	-- 9i (9.2?) and later
	cpu_time,	-- 9i and later
	elapsed_time,	-- 9i and later
	first_load_time >= '&m_baseline'
order by

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.


  1. Hi Jonathan,
    what are the disadvantages of library cache latches (in thins case)?
    As far as I know latches are used to prevent the block/object to get removed from the cache. So the only thing I could imagine is, that another session has to wait if the library cache is not large enough to hold something currently needed.


    Comment by Wolfgang — June 13, 2008 @ 6:12 pm BST Jun 13,2008 | Reply

  2. Wolfgang,

    It’s simply a case of avoiding stress. If you have a big library cache then this query could easily end up doing tens of thousands of latch gets. It might make the query a little slow but more importantly, if the system is a busy one, it might introduce a brief performance hit that affects lots of other queries.

    So the advice is a little caution – don’t run it every 10 seconds for 10 minutes – but once every few minutes is likely to be fine in most cases.

    Comment by Jonathan Lewis — June 13, 2008 @ 7:29 pm BST Jun 13,2008 | Reply

  3. […] Lewis suggests browsing through V$SQL and provides a nice […]

    Pingback by Few Links of the Week and a Biased Recommendation « I’m just a simple DBA on a complex production system — June 14, 2008 @ 12:04 am BST Jun 14,2008 | Reply

  4. […] Filed under: Uncategorized — Jonathan Lewis @ 5:51 pm UTC Aug 28,2009 I was using my “recent sql” check on client site some time ago, and found that one of their programmers had clearly been […]

    Pingback by Quiz Night « Oracle Scratchpad — August 28, 2009 @ 5:53 pm BST Aug 28,2009 | Reply

  5. […] Leaks: an article that includes three queries to check memory usage for a session. Recent SQL: an article that describes a check for SQL that has recently appeared in the SGA heap Segment Scans: […]

    Pingback by Simple scripts « Oracle Scratchpad — December 30, 2009 @ 5:21 pm GMT Dec 30,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google photo

You are commenting using your Google 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.

Powered by