SQL Costs

Here’s a quick and dirty bit of SQL I use from time to time to check whats going on with a “popular” piece of SQL, i.e. a piece of code that is run very frequently. All it does is compare a few of the statistics from v$sql with their previous values from the last time the script was run. 

As you can see, the script is the 10g version, using the sql_id to access a single statement in the library cache with maximum efficiency – change this to the hash_value for earlier versions of Oracle.

All I do with this script is identify a single ‘interesting’ statement – possibly by checking a recent statspack report for the the most frequently executed statements – and slot in the appropriate sql_id and child_number. Of course, some statements will have multiple child cursors, so a precursor to setting up this script is to pick an interesting child.

As you can see, I use the SQL*Plus new_value feature to capture the most recent values reported for the statistics I am interested in – but use the previous captured values to generate the ‘deltas’. (And to get things going, I define the inputs as zero for the first run of the script).

So all you do is pick your statement, adjust the script, then run it every few seconds for a couple of minutes.

Occasionally it’s nice to have something like this to sample v$sql in near-real time rather than relying on the longer-term averages you get from statspack as an indicator of the work done and results acquired by a single statement. You’ll have to adjust the SQL_ID and CHILD_NUMBER as required, of course; and you could always query v$sqlstats in 10g, or v$sqlstats_plan_hash in 11g.

set linesize 144
set verify off

column	executions	new_value m_exe_ct
column	rows_processed	new_value m_row_ct
column	buffer_gets	new_value m_buf_ct
column	disk_reads	new_value m_dsk_ct

define m_exe_ct = 0
define m_row_ct = 0
define m_buf_ct = 0
define m_dsk_ct = 0

select
	sql_id,
	child_number,
	executions,
	executions - &m_exe_ct 			exe_delta,
	buffer_gets,
	buffer_gets - &m_buf_ct 		buf_delta,
	disk_reads,
	disk_reads - &m_dsk_ct 			dsk_delta,
	rows_processed,
	rows_processed - &m_row_ct 		row_delta,
	round(
		(rows_processed - &m_row_ct) /
		nullif(executions - &m_exe_ct,0)
	)					rows_per_exe,
	round(
		(buffer_gets - &m_buf_ct) /
		nullif(executions - &m_exe_ct,0)
	)					bufs_per_exe,
	round(
		(disk_reads - &m_dsk_ct) /
		nullif(executions - &m_exe_ct,0)
	)					dsks_per_exe
from
	v$sql
where
	sql_id = '6vba5y39vcq1f'
and	child_number = 0
;

Obviously, you can follow the pattern of the code to pick your own metrics – I tend to find “work done per row” and “work done per execution” quite helpful; you may want to review work done in terms of CPU time, sorts, and any other basic metrics available in the view – unfortunately you have to be a little selective otherwise the output gets a bit too wide for comfort.

I’ve attached a sample of the output from running the above query a few times – but I’ve manually adjusted the output, eliminating columns, to allow it to fit on the page; I’ve also deleted the row returned on the first run where the deltas and the base figures are the same:

SQL> /

EXECUTIONS EXE_DELTA  BUFFER_GETS  BUF_DELTA ROWS_PROCESSED ROW_DELTA  ROWS_PER_EXE BUFS_PER_EXE
---------- ---------- ----------- ---------- -------------- ---------- ------------ ------------
   1663712         19   892482440       7355      114727466        469           25          387

SQL> /

EXECUTIONS EXE_DELTA  BUFFER_GETS  BUF_DELTA ROWS_PROCESSED  ROW_DELTA ROWS_PER_EXE BUFS_PER_EXE
---------- ---------- ----------- ---------- -------------- ---------- ------------ ------------
   1663728         16   892488545       6105      114727910        444           28          382

SQL> /

EXECUTIONS EXE_DELTA  BUFFER_GETS  BUF_DELTA ROWS_PROCESSED  ROW_DELTA ROWS_PER_EXE BUFS_PER_EXE
---------- ---------- ----------- ---------- -------------- ---------- ------------ ------------
   1663744         16   892496194       7649      114728240        330           21          478

In this case, the sampling showed that the costs and result sets were fairly stable – the interesting cases tend to be the ones which show a very wide variation in costs or results.