 |
|
Using the V$DB_CACHE_ADVICE View to Tune the Caches
Oracle Tips by Burleson Consulting
|
The V$DB_CACHE_ADVICE view is populated when
the DB_CACHE_ADVICE initialization parameter is set to ON. The view
shows the estimated miss rates for 20 potential buffer cache sizes,
ranging from 10 percent to 200 percent of the current size. Each of
the 20 projected cache sizes has its own row in this view, with the
predicted physical I/O activity that would take place for that cache
size. The DB_CACHE_ADVICE parameter is dynamic, so the advisory can be
enabled and disabled dynamically to allow you to collect advisory data
for a specific workload. (If this sounds familiar to the old hands out
there, it’s probably because you remember the X$KCBRBH and X$KCBCBH
tables in days of yore.)
There are two minor overheads associated with
this advisory process:
CPU load. When the advisory is on,
there is a small increase in CPU usage, because additional bookkeeping
is required.
Memory. The advisory requires memory to
be allocated from the shared pool (on the order of 100 bytes per
projected buffer). This memory is preallocated on instance startup if
DB_CACHE_ADVICE is set to READY in anticipation of collecting advisory
statistics, or if the parameter is set to ON. If the parameter is set
to OFF (the default setting), on instance startup, then the memory is
dynamically allocated from the shared pool at the time the parameter
value is modified to a value other than OFF.
The parameter DB_CACHE_ADVICE should be set to
ON, and a representative workload should then be run on the instance.
Allow the workload to stabilize before querying the V$DB_CACHE_ADVICE
view.
The SQL report in Source 13.13 returns the
predicted I/O requirement for the default buffer pool for various
cache sizes (based on a script taken from the Oracle9i Performance
Guide and Reference, Release 1 (9.0.1), Part # 87503-02, Oracle
Corporation, June 2001).
SOURCE 13.13 DB cache advice report.
Rem
db_cache_ad.sql
Rem from Oracle9i tuning
Rem Mike Ault Initial creation
Rem
col size_est format 999,999,999,999 heading 'Cache Size (m)'
col buf_est format 999,999,999 heading 'Buffers'
col estd_rf format 999.90 heading
'Estd Phys|Read Factor'
column estd_pr format 999,999,999 heading 'Estd Phys| Reads'
SET LINES 80 PAGES 55
@title80 'DB Cache Advisor Report'
SPOOL rep_out/&db/db_cache_ad
SELECT
size_for_estimate size_est,
buffers_for_estimate buf_est,
estd_physical_read_factor est_rf,
estd_physical_reads est_pr
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
AND advice_status = 'ON';
SPOOL OFF
SET PAGES 22
TTITLE OFF
In an effort to generate some sort of
meaningful report, I attempted to load my server enough, but was only
able to get results up to the second level. Listing 13.14 shows sample
output from the DB cache advisor report.
LISTING 13.14 Example DB cache advice output.
Date:
11/08/01 Page: 1
Time: 06:09 PM DB Cache Advisor Report SYS
aultdb1 database
Estd Phys Estd Phys
Cache Size (m) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
6 802 2.42 996
13 1,604 1.03 423
19 2,406 1.00 411
25 3,208 1.00 411
31 4,010 1.00 411
38 4,812 1.00 411
44 5,614 1.00 411
50 6,416 1.00 411
56 7,218 1.00 411
63 8,020 1.00 411
69 8,822 1.00 411
75 9,624 1.00 411
81 10,426 1.00 411
88 11,228 1.00 411
94 12,030 1.00 411
100 12,832 1.00 411
107 13,634 1.00 411
113 14,436 1.00 411
119 15,238 1.00 411
125 16,040 1.00 411
To interpret the results in Listing 13.14, look at the Est Phys
Read Factor column:, if this value is greater then 1, then the buffer
at that size will perform worse than the current buffer size; if the
number is less than 1, then the buffer at that size will perform
better than the current buffer size; and if the value is 1, then the
performance will not change if you set the buffer size to that size.
Using V$BH and X$BH to Monitor Buffer Use
In versions prior to Oracle9i, you may have to
run the catparr.sql script, located in ORACLE_HOME/rdbms/admin, to
create the v$bh view. The v$bh view, and its parent x$bh, are very
important for monitoring buffer usage. Rather then depending on hit
ratio, which is prone to miscalculation, problems with nonselective
indexes, and other woes, the v$bh and x$bh tables can be used to tell
you exactly what is happening with your buffer areas. Look at the
report in Source 13.14.
SOURCE 13.14 Block usage script.
rem
block_usage.sql
rem
rem Mike AUlt
rem
@title80 'Block Usage Inside SGA Block Buffers'
spool rep_out\&db\block_usage
SELECT decode(c.name,null,'UNUSED',c.name) ts_name,
a.file# file_number,
COUNT(a.block#) Blocks,
COUNT (DISTINCT a.file# || a.block#) Distinct_blocks
FROM V$BH a, file$ b, ts$ c
WHERE a.file#=b.file#(+)
AND b.ts#=c.ts#(+)
GROUP BY a.file#,decode(c.name,null,'UNUSED',c.name)
/
spool off
The script in Source 13.14 used the v$bh SYS
view to show which tablespaces have blocks inside the SGA and how many
blocks are free. An example block usage report is shown in Listing
13.15.
LISTING 13.15 Example block usage report.
Date:
11/09/01 Page: 1
Time: 06:05 PM Block Usage Inside SGA Block Buffers SYS
aultdb1 databas
TS_NAME
FILE_NUMBER BLOCKS DISTINCT_BLOCKS
------------------------------ ----------- ------ ---------------
UNUSED 0 7177 3791
SYSTEM 1 3149 3138
RBS 2 707 707
TOOLS 5 441 225
PERFSTAT 8 333 333
TEST_2K 9 2 2
As you can see in my SGA, I have nearly 50
percent of my blocks free. If I am running under a normal load, in
Oracle9i, this would indicate I may have overallocated my
DB_CACHE_SIZE or, in earlier versions, that I have allocated too many
DB_BLOCK_BUFFERS. This type of information is not available from a
mere hit ratio. Another quick look at the v$bh view is obtained by the
report in Source 13.15. The output from the report in this source is
shown in Listing 13.16 for the same SGA as in Listing 13.15.
SOURCE 13.15 V$BH status script.
rem
vbh_status.sql
rem
rem Mike Ault
rem
@title80 'Status of DB Block Buffers'
spool rep_out\&db\vbh_status
select status,count(*) number_buffers from v$bh group by status;
spool off
ttitle off
LISTING 13.16 V$BH status listing.
Date:
11/09/01 Page: 1
Time: 06:10 PM Status of DB Block Buffers SYS
aultdb1 databas
STATU
NUMBER_BUFFERS
----- --------------
cr 227
free 7177
xcur 4405
If we add the cr and xcur values to obtain the
dirty blocks, and compare this to the free value listing, we can see
that this SGA has well over 50 percent of its blocks free. Generally
speaking, if, at two hours, after a normal load is placed on a
database, more than 20 percent of its database base buffer blocks are
free, you probably have overallocated the number of DB_BLOCK_BUFFERS
in pre-Oracle9i instances or overstated the DB_CACHE_SIZE in Oracle9i.
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. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
