|
 |
|
Oracle Concepts - Disk
IO and the Shared Pool
Oracle Tips by Burleson Consulting |
Disk IO and the Shared Pool
The shared SQL area contains the Pcode
versions of all of the current SQL commands that haven’t been aged out
of the shared pool. There are numerous statistics available via the
v$sqlarea DPT. The text of SQL statements in the shared pool can 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 the greatest amount of disk reads (these will probably be the
ones you will want to review 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 FORMAT 9999
HEADING ‘Exec’
COLUMN sorts
FORMAT 99999 HEADING ‘Sorts’
COLUMN command_type FORMAT 99999
HEADING ‘CmdT’
COLUMN disk_reads FORMAT
999,999,999 HEADING ‘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: Script to Monitor SQL Area Disk
Reads By Script
Date:
06/24/97
Page: 1
Time: 11:35 PM
SQL Statements 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 select order#,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 SELECT NAME,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 distinct d.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 job BINARY_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 report in Listing 26 was generated
forcing a read count of 10. Usually disk reads will be in the range
specified by the define statement. By tuning those statements which
show large amounts of disk reads the overall performance of the
application is increased.
Monitoring Library and Data Dictionary
Caches
I've spent most of this lesson looking at the
shared SQL area of the shared pool. Let's wrap up with a high level
look at the library and data dictionary caches. The library cache 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
caches contain cache area for all data dictionary related definitions.
The script in Source 27 creates a report on
the library caches. The items of particular interest in the report
generated by the script in Source 27 (shown in Lisitng 27) are the
various ratios. This report should look familiar, it is similar to the
library caches report in the utlestat.sql reports.
REM
REM Title: libcache.sql
REM
REM FUNCTION: Generate a library cache report
REM
COLUMN namespace
HEADING "Library Object"
COLUMN gets
FORMAT 9,999,999 HEADING "Gets"
COLUMN gethitratio FORMAT 999.99
HEADING "Get Hit%"
COLUMN pins
FORMAT 9,999,999 HEADING "Pins"
COLUMN pinhitratio FORMAT 999.99
HEADING "Pin Hit%"
COLUMN reloads
FORMAT 99,999 HEADING "Reloads"
COLUMN invalidations FORMAT 99,999
HEADING "Invalid"
COLUMN db
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 the example output from the script in
Source 27 in Listing 27. In Listing 27 we see that all Get Hit% (gethitratio
in the view) except for indexes are greater than 80-90 percent. This
is the desired state, the value for indexes is low because of the few
accesses of that type of object. Notice that the Pin Hit% is also
greater than 90% (except for indexes) this is also to be desired. The
other goals of tuning this area are to reduce reloads too as small a
value as possible (this is done by proper sizing and pinning) and to
reduce invalidations. Invalidations happen when for one reason or
another an object becomes unusable. However, if you must use flushing
of the shared pool reloads and invalidations may occur as objects are
swapped in and out of the shared pool. Proper pinning can reduce the
number of objects reloaded and invalidated.
Guideline 7: In a system 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:51 PM
Library Caches Report
SYSTEM
ORTEST1 database
Library
Object Gets Get Hit%
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 data dictionary caches used to be
individually tunable through several initialization parameters, now
they are internally controlled. The script in Source 28 should be used
to monitor the overall hit ratio for the data dictionary 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: Script to Monitor the Data
Dictionary Caches
The output from the script in Source 28 is
shown in Listing 28.
Date:
11/21/98
Page: 1
Time: 02:59 PM
DD Cache Hit Ratio
SYSTEM
ORTEST1 database
RATIO
---------
.01273172
Listing 28: Example Output From Data
Dictionary Script
The ratio reported from the script in Source
28 should always be less than 1. The ratio corresponds to the number
of times out of 100 that the database engine sought something from the
cache and missed. A dictionary cache miss is more expensive than a
data block buffer miss so if your ratio gets near 1 increase the size
of the shared pool since the internal algorithm isn't allocating
enough memory to the data dictionary caches.
Guideline 8: In any shared pool, if the
overall data dictionary cache miss ratio exceeds 1 percent, increase
the size of the shared pool.
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It’s
only $19.95 when you buy it directly from the publisher
here.
|
