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

 

 


 

 

 

 

 
 

Oracle KEEP Pool

Oracle Tips by Burleson Consulting

Tuning the RECYCLE Pool

Oracle8 introduced the RECYCLE pool as a reusable data buffer for transient data blocks.  Transient data blocks are blocks that are read as parts of large-table full-table scans and are not likely to be needed again soon. The goal is to use the RECYCLE pool for segregating large tables involved in frequent full-table scans. 

To locate these large-table full-table scans, the plan9i.sql (see code depot) script can be used once again:

 

                     full table scans and counts

                                        

OWNER      NAME                     NUM_ROWS C K   BLOCKS  NBR_FTS   

---------- ------------------------ ------------ - - -------- -----APPLSYS    FND_CONC_RELEASE_DISJS         39 N K        2   98,864   

APPLSYS    FND_CONC_RELEASE_PERIODS       39 N K        2   98,864   

APPLSYS    FND_CONC_RELEASE_STATES         1 N K        2   98,864   

SYS        DUAL                              N K        2   63,466   

APPLSYS    FND_CONC_PP_ACTIONS         7,021 N      1,262   52,036   

APPLSYS    FND_CONC_REL_CONJ_MEMBER        0 N K       22   50,174   

 

One table in the listing is a clear candidate for inclusion in the RECYCLE pool  The fnd_conc_pp_actions table contains 1,262 blocks and experienced 52,036 full-table scans.

 

After candidates for the RECYCLE pool have been identified, a script that reads the plan table generated from plan9i.sql can be run.  This query will search for large tables of over 10,000 blocks that are subject to full-table scans and are not already in the RECYCLE pool.

 

CAUTION: The prudent DBA should verify that the large-table full-table scan is legitimate before blindly assigning a table to the RECYCLE pool.

 

Many queries are structured to perform full-table scans on tables, even though far less than 40 percent of the table rows will be referenced.  A better designed query will only perform large-table full-table scans in systems such as data warehouses that require frequent SUM or AVG queries that touch most or all of the table rows.

·       9i_recycle_syntax.sql

 

set pages 999;

set heading off;

set feedback off;

 

ttitle off;

 

spool keep_syntax.sql

 

-- ***********************************************************

-- First, get the table list

-- ***********************************************************

select

   'alter table '||p.owner||'.'||p.name||' storage (buffer_pool recycle);'

from

   dba_tables t,

   dba_segments s,

   v$sqlarea    a,

   (select distinct

     address,

     object_owner owner,

     object_name name

   from

      v$sql_plan

   where

      operation = 'TABLE ACCESS'

      and

      options = 'FULL') p

where

   a.address = p.address

   and

   t.owner = s.owner

   and

   t.table_name = s.segment_name

   and

   t.table_name = p.name

   and

   t.owner = p.owner

   and

   t.owner not in ('SYS','SYSTEM')

   and

   t.buffer_pool <> 'RECYCLE'

having

   s.blocks > 10000

group by

   p.owner, p.name, t.num_rows, s.blocks

UNION

-- ***********************************************************

-- Next, get the index names

-- ***********************************************************

select

   'alter index '||owner||'.'||index_name||' storage (buffer_pool recycle);'

from

   dba_indexes

where

   owner||'.'||table_name in

(

select

   p.owner||'.'||p.name

from

   dba_tables   t,

   dba_segments s,

   v$sqlarea    a,

   (select distinct

     address,

     object_owner owner,

     object_name name

   from

      v$sql_plan

   where

      operation = 'TABLE ACCESS'

      and

      options = 'FULL') p

where

   a.address = p.address

   and

   t.owner = s.owner

   and

   t.table_name = s.segment_name

   and

   t.table_name = p.name

   and

   t.owner = p.owner

   and

   t.owner not in ('SYS','SYSTEM')

   and

   t.buffer_pool <> 'RECYCLE'
SEE CODE DEPOT FOR FULL SCRIPTS

)

;

 

spool off;

 SEE CODE DEPOT FOR FULL SCRIPTS

The output from this script is shown below:

SQL> @9i_recycle_syntax

 

alter table APPLSYS.FND_CONC_PP_ACTIONS storage (buffer_pool recycle);

 

As a general rule, the DBA should check the SQL source to verify that a full-table query is retrieving over 40 percent of the table rows before adding any table to the RECYCLE pool.

 

The x$bh view can be used as another approach for finding RECYCLE   candidates, similar to what was done for the KEEP pool.  This topic is addressed in the next section.

Advanced RECYCLE Pool Tuning

The query below uses x$bh.tch to identify objects in the buffer cache that are larger than five percent of the total cache and have single touch buffer counts.

 

A significant amount of cache space is filled with these blocks that have only been used once. They are good candidates for inclusion in the RECYCLE buffer pool.  Upon careful examination, DBAs will find that the hot_recycle_blocks.sql script below will identify the percentage of an object’s block in the buffer.
 

·       hot_recycle_blocks.sql

 

 

set lines 80;

set pages 999;

 

column avg_touches format 999

column myname heading 'Name' format a30

column mytype heading 'Type' format a10

column buffers format 999,999

 

SELECT 

   object_type  mytype,

   object_name    myname,

   blocks,

   COUNT(1) buffers,

   100*(COUNT(1)/totsize) pct_cache

FROM

   sys.x$bh    a,

   dba_objects b,

   dba_segments s,

   (select value totsize from v$parameter

         where name = 'db_cache_size')

WHERE

   a.obj = b.object_id

and

   tch=1  -- This line only works in 8.1.6 and above

and

   b.object_name = s.segment_name

and

   b.owner not in ('SYS','SYSTEM')

GROUP BY

   object_type,

   object_name,

   blocks,

   totsize

SEE CODE DEPOT FOR FULL SCRIPTS

;


DBAs must remember that Oracle releases prior to 8.1.6 do not support the reference to the touch (tch) column.  The report can still be useful with releases prior to 8.1.6, but there is no way of knowing how many times the objects have been touched since their entry into the data pool.

 

A sample report from this script is shown below.  These tables and indexes occupy over five percent of the data buffer space and have only been touched once.  This behavior is characteristic of large-table, full-table scans.

 

Type      Name                           BLOCKS  BUFFERS PCT_CACHE

--------- -------------------------- ---------- -------- ---------

INDEX     WIP_REQUIREMENT_OPERATIONS_U1    1042      334      5.57

TABLE     MTL_DEMAND_INTERFACE              847      818     13.63

TABLE     MTL_SYSTEM_ITEMS                 4227      493      8.22

 

The DBA must take into consideration both the number of blocks in the table and how often the table appears in the query output when determining whether or not to add objects to the RECYCLE pool.
 

Selecting candidates for the RECYCLE pool is an iterative process, just as it is for the KEEP pool.  Data buffers are constantly changing, and the DBA may choose to run this script every minute over a period of several hours to get as complete a picture as possible of block activity within the data buffer.
 

This information covered how to monitor and tune the data buffer pools, now it is time to return to a more general consideration of large block sizes and their behavior inside the Oracle data buffers.

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


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