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

---