||Oracle Tips by Burleson
Identify new tables for Oracle cache
The script in keep_syn.sql can be run
every hour via dbms_job, and automate the monitoring of KEEP
pool candidates. Every time it finds a candidate, the DBA will execute
the syntax and adjust the total KEEP pool size to accommodate the new
object. Here is the output from this script.
code depot for full scripts
drop table t1;
create table t1 as
. . .
o.data_object_id = bh.objd
o.owner not in ('SYS','SYSTEM')
bh.status != 'free'
. . .
. . .
. . .
. . .
Here is a sample of the output from this
alter TABLE IS.GL_JE_BATCHES
storage (buffer_pool keep);
alter INDEX IS.GL_JE_BATCHES_U2 storage (buffer_pool keep);
alter TABLE IS.GL_JE_HEADERS storage (buffer_pool keep);
Once you have identified the segments for
assignment to the KEEP pool, you will need to adjust the
db_keep_cache_size parameter to ensure that it has enough blocks
to fully cache all of the segments that are assigned to the pool.
Of course, there are many exceptions to this
automated approach. For example, these scripts do not handle table
partitions and other object types. Hence, these scripts should be used
as a framework for your KEEP pool caching strategy, and should not be
Now, let’s look at scripts to automate the
identification of objects for the RECYCLE pool. As we shall see in the
next section, identifying candidates for the RECYCLE pool is very
similar to the KEEP pool process.
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
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
Packed with more than 680 ready-to-use Oracle scripts, this is the
definitive collection for every senior Oracle DBA.
would take man-years to develop these scripts from scratch,
making this download the best value in the Oracle industry.