Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

Tuning the Oracle 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 unlikely to soon be needed by Oracle again.

We want to use the RECYCLE pool for segregating large tables involved in frequent full-table scans. To locate these large-table full-table scans, we can return to the plan9i.sql full-table scan report:

See code depot for full scripts

                     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 has experienced 52,036 full-table scans.

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.

After candidates for the RECYCLE pool have been identified, we can run a script that reads the plan table generated from plan9i.sql. 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.

See code depot for full scripts

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

-- Generate RECYCLE pool  syntax for appropriate

-- tables & indexes

-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.

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

 

 

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

-- First, get the table list

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

select

   'alter table '||p.owner||'.'||p.name||' storage (buffer_pool . . .

   from

      v$sql_plan

   where

. . .

where

. . .

UNION

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

-- Next, get the index names

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

select

. . .

from

   dba_indexes

where

. . .

select

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

from

   dba_tables   t,

   dba_segments s,

   v$sqlarea    a,

   (select distinct

. . .

)

;

 

spool off;

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.

We can use the x$bh view as another approach for finding RECYCLE candidates, just as we did for the KEEP pool. This topic is addressed in the next section.

 

The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.


Complete Oracle Script Collection Available

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 680 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.

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

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.