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
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 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.

5 Comments »

  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.

    Wolfgang

    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 BST Dec 30,2009 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,909 other followers