 |
|
Disk I/O and the Shared
Pool
Oracle Tips by Burleson
|
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. Numerous statistics are 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 and
v$sqlarea DPTs. Let’s look at a report that displays the SQL
statements in the SQL area with the greatest number of disk reads
(these will probably be the ones you will want to review and tune).
Look at the report in Source 13.10. Output from this report is shown
in Listing 13.11.
SOURCE 13.10 SQL versus disk reads report.
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
LISTING 13.11 Example SQLDRD.SQL output.
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 1111 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 1104 select order#,columns,types from
access$ where d_obj#=:1
0 12
0 3 912 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;
By tuning those statements that show large
numbers of disk reads, the overall performance of the application is
increased.
Monitoring Library and Data Dictionary
Caches
I've spent most of this section discussing 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, which 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.
Source 13.11 creates a report on the library
caches. The items of particular interest in the report generated by
Source 13.11 (shown in Listing 13.12) are the various ratios.
SOURCE 13.11 Library caches report.
rem Title:
libcache.sql
rem FUNCTION: Generate a library cache report
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
In Listing 13.12 we see that all Get Hit% (gethitratio
in the view), except for indexes, are greater than 80 to 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 percent (except for indexes); this is also desirable.
The other goals of tuning this area are to reduce reloads to 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 6: In a system where there is no flushing,
increase the shared-pool size to reduce reloads and invalidations and
to increase hit ratios.
LISTING 13.12 Example library caches report.
Date:
11/08/01 Page: 1
Time: 04:57 PM Library Caches Report DBAUTIL
aultdb1 database
Library
Object Gets Get Hit% Pins Pin Hit% Reloads Invalid
--------------- ------- -------- ------------ -------- --------
-------
SQL AREA 4,523 94.10 19,395 97.91 13
0
TABLE/PROCEDURE 5,835 80.19 3,423 50.16 0
0
BODY 22 63.64 21 57.14 0
0
TRIGGER 1 .00 1 .00 0
0
INDEX 29 .00 29 .00 0
0
CLUSTER 174 96.55 234 97.01 0
0
OBJECT 0 100.00 0 100.00 0
0
PIPE 0 100.00 0 100.00 0
0
JAVA SOURCE 0 100.00 0 100.00 0
0
JAVA RESOURCE 2 50.00 2 50.00 0
0
JAVA DATA 1 .00 6 66.67 0
0
Formerly, the data dictionary caches were
individually tunable through several initialization parameters; now
they are internally controlled. The report in Source 13.12 can be used
to monitor the overall hit ratio for the data dictionary caches. The
output from Source 13.12 is shown in Listing 13.13.
SOURCE 13.12 Monitoring Data Dictionary hit
ratio.
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))*100 RATIO
FROM v$rowcache;
spool off
pause Press enter to continue
ttitle off
LISTING 13.13 Sample Data Dictionary hit
ratio report.
Date:
11/21/98 Page: 1
Time: 02:59 PM DD Cache Hit Ratio SYSTEM
ORTEST1 database
RATIO
---------
1.273172
The ratio reported from the script in Source
13.12 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 7: In any shared pool in the data dictionary
cache ratio is greater than 1.0, increase the size of the shared pool.
Using these guidelines and the scripts and
techniques covered in this section, you should be well on the way
toward achieving a well-tuned and well-performing shared pool.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
