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 keep pool caching

Oracle developed the KEEP pool in order to fully cache blocks from frequently accessed tables and indexes in a separate buffer. When determining the size of the KEEP pool, the number of bytes comprising all tables that will reside in the KEEP area must be totaled. This will insure that the KEEP buffer is large enough to fully cache all the tables that have been assigned to it.

Oracle9i requires that a table only reside in a tablespace of the same block size as the buffer assigned to the table. For example, if the DEFAULT buffer is set at 32K, the alter command below would not work if the customer table resides in a 16K tablespace. Recall that the DEFAULT, KEEP, and RECYCLE designations only apply to the default block size; KEEP and RECYCLE buffers cannot be assigned different sizes than the default db_block_size.

alter table CUSTOMER storage (buffer_pool KEEP);

The whole point of the KEEP pool is to always have a data buffer hit ratio of 100 percent. The block size of the KEEP pool is not important because all blocks, once loaded, will remain in RAM memory. A KEEP pool might be defined as a 32K block size because a large RECYCLE buffer was needed to improve the performance of full-table scans.

CAUTION: Selecting tables for the KEEP pool is an iterative process. These reports include only SQL that happens to be in the library cache at the time of the report.

We emphasize that since our goal for the data buffer hit ratio of the KEEP pool is 100 percent, each time a table is added to KEEP, the number of blocks in that table must also be added to the KEEP pool parameter in the Oracle file.

Besides the full-table scan report, we can generate a report that shows every index access within SQL currently residing in the library cache.

The script output below shows how Oracle uses indexes to access Oracle tables (Listing 3.4).

See code depot for full scripts

                  Index range scans and counts



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

SYS       JOB$          I_JOB_NEXT                     3        4,755  

SYS       OBJ$          I_OBJ2                       778          945  

SYS       ACCESS$       I_ACCESS1                    478          787  

SYS       DEPEN         I_DEPENDENCY1                878          787  

SYS       IDL_SB4$      I_IDL_SB41                 1,103          437  

SYS       IDL_UB1$      I_IDL_UB11                26,653          411  

SYS       IDL_CHAR$     I_IDL_CHAR1                  653          410  

SYS       IDL_UB2$      I_IDL_UB21                 2,503          410  

SYS       ARGUMENT$     I_ARGUMENT2                  828          109  

SYS       OBJAUTH$      I_OBJAUTH1                   131           96  



               Index unique scans and counts


OWNER     TABLE_NAME           INDEX_NAME              NBR_SCANS               

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

SYS       OBJ$                 I_OBJ1                        946               

SYS       C_OBJ#               I_OBJ#                        337               

SYS       JAVASNM$             I_JAVASNM1                    127               

SYS       C_COBJ#              I_COBJ#                       114               

SYS       C_FILE#_BLOCK#       I_FILE#_BLOCK#                114               

SYS       C_USER#              I_USER#                        97               

SYS       PROCEDUREJAVA$       I_PROCEDUREJAVA$               93               

SYS       SMON_SCN_TO_TIME     SMON_SCN_TO_TIME_IDX           78               

SYS       C_TS#                I_TS#                          49               

SYS       FILE$                I_FILE2                        37               

SYS       USER$                I_USER1                        37               

SYS       IND$                 I_IND1                         35               

SYS       TRIGGER$             I_TRIGGER2                     25                

SYS       PROCEDURE$           I_PROCEDURE1                   19               

SYS       SYN$                 I_SYN1                         13               

SYS       VIEW$                I_VIEW1                        10               

Listing 3.4 – Index access methods and counts for current SQL

Now that we can identify and locate small-table, full-table scans, let’s see how we can quickly move small tables and indexes into the KEEP pool. We will use a data dictionary query to automatically generate KEEP and RECYCLE syntax.


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.