Oracle Concepts - Disk IO and the Shared Pool

Oracle Concepts - Disk IO and the Shared Pool Oracle Tips by Burleson Consulting

Disk IO and theShared Pool

The shared SQLarea contains the Pcode versions of all of the current SQL commands thathaven?t been aged out of the shared pool. There are numerous statisticsavailable via the v$sqlarea DPT. The text of SQL statements in the shared poolcan be retrieved (at least the first tens of bytes) from the v$sqltext DPT.Lets look at a report that displays the SQL statements in the SQL area with thegreatest amount of disk reads (these will probably be the ones you will want toreview and tune). Look at the report in Source 26.

REM Name: sqldrd.sql
REM Function: return the sql statements from the shared area with
REM Function: highest disk reads
REM History: Presented in paper 35 at IOUG-A 1997, converted for
REM use 6/24/97 MRA
REM
DEFINE access_level = 1000 (NUMBER)
COLUMN parsing_user_id FORMAT 9999999 HEADING ?User Id?
COLUMN executions       FORMAT9999        HEADING ?Exec?
COLUMN sorts           FORMAT 99999       HEADING ?Sorts?
COLUMN command_type     FORMAT99999       HEADING ?CmdT?
COLUMN disk_reads       FORMAT 999,999,999HEADING ?Block Reads?
COLUMN sql_text   FORMAT a40 HEADING ?Statement? WORD_WRAPPED
SET LINES 130 VERIFY OFF FEEDBACK OFF
START title132 ?SQL Statements With High Reads?
SPOOL rep_out/&db/sqldrd.lis
SELECT 
      parsing_user_id, executions,
      sorts,command_type,
      disk_reads,sql_text
FROM 
      v$sqlarea
WHERE 
      disk_reads > &&access_level
ORDER BY 
      disk_reads;
SPOOL OFF
SET LINES 80 VERIFY ON FEEDBACK ON

Source 26: Scriptto Monitor SQL Area Disk Reads By Script

Date:06/24/97                                                Page:   1
Time: 11:35 PM           SQLStatements With High Reads        SYSTEM

                              ORTEST1 database User


Id   Exec Sorts CmdT Block Reads Statement 
---- ---- ----- ---- ----------- ---------------------------------------
   0  403     0   3          11 select f.file#,f.block#, f.ts#,
                                f.length from fet$ f, ts$ t where
                                t.ts#=f.ts# and t.dflextpct!=0

   0   11     0   3          11 selectorder#,columns,types from   
                                access$ where d_obj#=:1

   0   12     0   3          12 select /*+index(idl_ub1$ i_idl_ub11)
                                +*/ piece#,length,piece from idl_ub1$
                                where obj#=:1 and part=:2 and 
                                version=:3 order by piece#

   5  34     0   3          13 SELECTNAME,VALUE   FROM V$SYSSTAT
                                         WHERE NAME = 'db block gets'

   0   12     0   3          14 select /*+index(idl_ub2$ i_idl_ub21)
                                +*/ piece#,length,piece from idl_ub2$
                                where obj#=:1 and part=:2 and
                                version=:3 order by piece#

   0  17     0   3          27 select file#,block#, ts# from seg$
                                where type# = 3

   0   1     1   3          79 select distinctd.p_obj#,d.p_timestamp
                                from sys.dependency$ d, obj$ o where
                                d.p_obj#>=:1 and d.d_obj#=o.obj# and
                                o.status!=5

   5   34     0  47          90 DECLARE jobBINARY_INTEGER := :job;
                                next_date DATE := :mydate;  broken
                                BOOLEAN := FALSE; BEGIN hitratio;
                                :mydate := next_date; IF broken THEN :b
                                := 1; ELSE :b := 0; END IF; END;

Listing 26: Example Output From SQL Disk Read Script

The example reportin Listing 26 was generated forcing a read count of 10. Usually disk reads willbe in the range specified by the define statement. By tuning those statementswhich show large amounts of disk reads the overall performance of theapplication is increased.

Monitoring Libraryand Data Dictionary Caches

I've spent most ofthis lesson looking at the shared SQL area of the shared pool. Let's wrap upwith a high level look at the library and data dictionary caches. The librarycache area is monitored via the V$LIBRARYCACHE view and contains the SQL area,PL/SQL area, table, index and cluster cache areas. The data dictionary cachescontain cache area for all data dictionary related definitions.

The script inSource 27 creates a report on the library caches. The items of particularinterest in the report generated by the script in Source 27 (shown in Lisitng27) are the various ratios. This report should look familiar, it is similar tothe library caches report in the utlestat.sql reports.

REM
REM Title: libcache.sql
REM
REM FUNCTION: Generate a library cache report 
REM
COLUMNnamespace                        HEADING "Library Object"
COLUMNgets            FORMAT 9,999,999 HEADING "Gets"
COLUMN gethitratio      FORMAT999.99    HEADING "Get Hit%"
COLUMNpins            FORMAT 9,999,999 HEADING "Pins"
COLUMN pinhitratio      FORMAT999.99    HEADING "Pin Hit%"
COLUMN reloads          FORMAT99,999    HEADING "Reloads"
COLUMN invalidations    FORMAT 99,999   HEADING  "Invalid"
COLUMNdb              FORMAT a10
SET PAGES 58 LINES 80 
START title80 "Library Caches Report"
DEFINE output = rep_out\&db\lib_cache
SPOOL &output
SELECT 
      namespace,
      gets,
      gethitratio*100 gethitratio,
      pins,
      pinhitratio*100 pinhitratio,
      reloads,
      invalidations 
FROM 
      v$librarycache
/
SPOOL OFF
PAUSE Press enter to continue
SET PAGES 22 LINES 80
TTITLE OFF
UNDEF output

Listing 27: Example Script To Monitor The Library Caches

Look at theexample output from the script in Source 27 in Listing 27. In Listing 27 we seethat all Get Hit% (gethitratio in the view) except for indexes are greater than80-90 percent. This is the desired state, the value for indexes is low becauseof the few accesses of that type of object. Notice that the Pin Hit% is alsogreater than 90% (except for indexes) this is also to be desired. The othergoals of tuning this area are to reduce reloads too as small a value aspossible (this is done by proper sizing and pinning) and to reduceinvalidations. Invalidations happen when for one reason or another an objectbecomes unusable. However, if you must use flushing of the shared pool reloadsand invalidations may occur as objects are swapped in and out of the sharedpool. Proper pinning can reduce the number of objects reloaded and invalidated.

Guideline 7: In asystem where there is no flushing increase the shared pool size in 20%increments to reduce reloads and invalidations and increase hit ratios.

Date: 11/21/98                                             Page:   1
Time: 02:51PM            Library CachesReport            SYSTEM
                             ORTEST1 database

LibraryObject        Gets GetHit%       Pins Pin Hit% Reloads Invalid
--------------- ---------- -------- ---------- -------- ------- -------
SQL AREA           46,044    99.17     99,139   99.36      24      16
TABLE/PROCEDURE      1,824   84.59      6,935   93.21       3      0
BODY                  166    93.98       171    91.23      0       0
INDEX                  27     .00        27      .00      0       0
CLUSTER               373    98.12       373    97.59      0       0

Listing 27:Example Of The Output From Library Caches Report

The datadictionary caches used to be individually tunable through severalinitialization parameters, now they are internally controlled. The script inSource 28 should be used to monitor the overall hit ratio for the datadictionary caches.

REM
REM title:    ddcache.sql
REM FUNCTION: report on the v$rowcache table
REM HISTORY:  created sept 1995 MRA
REM
START title80 "DD Cache Hit Ratio"
SPOOL rep_out\&db\ddcache
SELECT (SUM(getmisses)/SUM(gets)) ratio
FROM v$rowcache
/
SPOOL OFF
PAUSE Press enter to continue
TTITLE OFF

Source 28: Scriptto Monitor the Data Dictionary Caches

The output fromthe script in Source 28 is shown in Listing 28.

Date:11/21/98                                             Page:   1
Time: 02:59PM              DD Cache HitRatio            SYSTEM
                             ORTEST1database

    RATIO
---------
.01273172

Listing 28:Example Output From Data Dictionary Script

The ratio reportedfrom the script in Source 28 should always be less than 1. The ratiocorresponds to the number of times out of 100 that the database engine soughtsomething from the cache and missed. A dictionary cache miss is more expensivethan a data block buffer miss so if your ratio gets near 1 increase the size ofthe shared pool since the internal algorithm isn't allocating enough memory tothe data dictionary caches.

Guideline 8: Inany shared pool, if the overall data dictionary cache miss ratio exceeds 1percent, increase the size of the shared pool.