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

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


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Oracle Advanced RECYCLE Pool Tuning

The query below uses x$bh.tch to identify objects in the buffer cache that are larger than 5 percent of the total cache with 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.

See code depot for full scripts

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

-- Display hot blocks in recycle pool


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

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


. . . FROM
   sys.x$bh    a,
   dba_objects b,
   dba_segments s,
   (select . . . )
. . .

. . . ;

We stress again 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 these releases, 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. We see that these tables and indexes occupy over 5 percent of the data buffer space, and they 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

To repeat, 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 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.

Now that we understand how to monitor and tune the data buffer pools, letís return to a more general consideration of large blocks and their behavior in Oracle.


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.




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.