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