BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter