JL Computer Consultancy

Reporting the SQL Area

Pre-August 1999


SQL - Too often or too expensive

As everyone knows, an Oracle database is only useful if it is doing some SQL. However there are four things that can go wrong with SQL - it can be too expensive on logical I/O (buffer hits) for what it is doing; it can be too expensive on physical I/O for what it is doing, it can be happening far too often, or it can just be an unreasonable request.

The following script (which needs arbitray constants changed to suit the scale of your database activity) produces a report which tries to highlight the top few statements which might be worth looking at.

Essentially anything that shows up here with big numbers is a probable threat. However, to emphasis the nature of the threat, the report shows not only the absolute numbers but also the cost per execution for buffer gets, disk reads, and rows returned.

There is an oddity of the executions and parse calls columns. Sometimes the execution appears to include the parse count, and sometimes it is clear that it does not. For the purposes of the ratios in this report I have assumed that parse calls are not included in executions.

When calling this script a variable called m_timestamp has to be set - this is used as the secondary file name of the output file. I usually set a loop to call the script every 20 or 30 minutes, passing in the date and time for m_timestamp so that I get a historical sequence of files to check. Note however that this script should NOT be run frequently as it hits the v$sqlarea very hard (see my note on V$ access).

'There is a brief sample of the output at the bottom of the page

Back to Main Index of Topics.


rem
rem     Script:        sqlarea.sql
rem     Author:        J.P.Lewis
rem     Dated:         Many moons ago
rem     Purpose:       Dump stats and text of recent expensive SQL
rem
rem     Notes:
rem     m_timestamp has been defined when this script is called
rem
rem     You can adjust the constants in the WHERE clause to suit your
rem     definition of what is expensive on your system.
rem
rem     The script tries to highlight code on the basis of absolute cost,
rem     and on cost per execution.  
rem
rem     Spot the little trick for avoiding divide by zero errors.  This
rem     can crop up very easily, especially if someone does an:
rem            alter system flush shared pool;
rem     The SQL can stay in the pool but with the set back to zero.
rem
set pagesize 999
set trimspool on
set feedback off
set verify off
clear columns
clear breaks
column  sql_text format a78 word_wrapped
column  memory         noprint new_value m_memory
column  sorts          noprint new_value m_sorts
column  executions     noprint new_value m_executions
column  first_load_time noprint new_value m_first_load_time
column  invalidations  noprint new_value m_invalidations
column  parse_calls    noprint new_value m_parse_calls
column  disk_reads     noprint new_value m_disk_reads
column  buffer_gets    noprint new_value m_buffer_gets
column  rows_processed noprint new_value m_rows_processed
column  row_ratio      noprint new_value m_row_ratio
column  disk_ratio     noprint new_value m_disk_ratio
column  buffer_ratio   noprint new_value m_buffer_ratio
break on row skip page
set heading off
ttitle  -
        "First load time: " m_first_load_time -
        skip 1 -
        "Buffer gets:     " m_buffer_gets " ratio " m_buffer_ratio -
        skip 1 -
        "Disk reads:      " m_disk_reads  " ratio " m_disk_ratio -
        skip 1 -
        "Rows delivered   " m_rows_processed " ratio " m_row_ratio -
        skip 1 -
        "Executions       " m_executions -
        skip 1 -
        "Parses           " m_parse_calls -
        skip 1 -
        "Memory           " m_memory -
        skip 1 -
        "Sorts            " m_sorts -
        skip 1 -
        "Invalidations    " m_invalidations -
        skip 2
spool sqlarea.&m_timestamp
set termout off
select 
        sql_text,
        sharable_mem + persistent_mem + runtime_mem memory,
        sorts,
        executions,
        first_load_time,
        invalidations,
        parse_calls,
        disk_reads,
        buffer_gets,
        rows_processed,
        round(rows_processed/greatest(executions,1))  row_ratio,
        round(disk_reads/greatest(executions,1))      disk_ratio,
        round(buffer_gets/greatest(executions,1))     buffer_ratio
from v$sqlarea
where
        executions > 100
or      disk_reads > 1000
or      buffer_gets > 1000
or      rows_processed > 1000
order by
        executions * 250 + disk_reads * 25 + buffer_gets desc
;
spool off
ttitle off
clear breaks
set heading on

Back to Main Index of Topics


Sample Output

Query 1 is a query slave that is clearly not a very good candidate for parallel execution given the number of rows it has to pass to its parent slave. On the other hand, it has got those rows relatively cheaply - so it may be the least-worst option in a data warehouse .

Query 2 a sample of the internal SQL generated by the Discoverer toolkit - showing a very high physical and logical cost per execution to return only a handful of rows each time.

Query 3 is a small warning about what can happen if you don't have your space management sorted out up front before you build your production system - this is SMON cutting in every 5 minutes with a scan of the ts$ cluster to see if there are any tablespaces that need to be coalesced. (Things improve with 8i)

First load time: 1999-01-28/16:53:20
Buffer gets:         195998 ratio      21778
Disk reads:            2307 ratio        256
Rows delivered       183193 ratio      20355
Executions                9
Parses                    1
Memory                53178
Sorts                     0
Invalidations             1
SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) */ A2."SOURCED" C0,A1.C0 C1,A1.C1
C2,A1.C2 C3,A1.C3 C4,A1.C4 C5,A2."PRICE" C6,A2."ON_HAND" C7,A2."WEEK"
C8,A2."PART_NO" C9,A2."YEAR" C10 FROM (SELECT /*+ ROWID(A3) */
A3."CATEGORY_CODE" C0,A3."CATEGORY_NAME" C1,A3."PART_NO" C2,A3."SKU_DESC"
C3,A3."SIZE_DESC" C4 FROM "REF_OWNER"."PRODUCTS" A3 WHERE ROWID BETWEEN :B1 AND
:B2 AND A3."CATEGORY_CODE"=61) A1,APP_OWNER."DISTRIBUTIONS" A2 WHERE
A2."WEEK">=35 AND A2."WEEK"<=40 AND (A2."YEAR"=1998) AND A1.C2=A2."PART_NO"
First load time: 1999-01-28/17:05:41
Buffer gets:          26845 ratio       5369
Disk reads:            8637 ratio       1727
Rows delivered           26 ratio          5
Executions                5
Parses                    2
Memory                61184
Sorts                     2
Invalidations             0
select document_id,decode(owner,user,NULL,owner),document_name,comments from
EULADMIN3.dis_all_docs where batch = :1 order by
decode(owner,user,NULL,owner||'.')||document_name
First load time: 1999-01-28/06:16:00
Buffer gets:         164332 ratio       1088
Disk reads:           77227 ratio        511
Rows delivered         1963 ratio         13
Executions              151
Parses                  151
Memory                10526
Sorts                     0
Invalidations             0
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts#
and t.dflextpct!=0

Back to Main Index of Topics