Automatic Workload Repository (AWR) in Oracle Database 10g

In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

AWR Features

The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:

• Automatic Database Diagnostic Monitor
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using:

BEGIN

  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.

    interval  => 30);          -- Minutes. Current value retained if NULL.

END;

/

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view.

Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

Extra snapshots can be taken and existing snapshots can be removed using:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN

  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

    low_snap_id  => 22, 

    high_snap_id => 32);

END;

/

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing like:

BEGIN

  DBMS_WORKLOAD_REPOSITORY.create_baseline (

    start_snap_id => 210, 

    end_snap_id   => 220,

    baseline_name => 'batch baseline');

END;

/

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted:

BEGIN

  DBMS_WORKLOAD_REPOSITORY.drop_baseline (

    baseline_name => 'batch baseline',

    cascade       => FALSE); -- Deletes associated snapshots if TRUE.

END;

/

Baseline information can be queried from the DBA_HIST_BASELINE view.

Workload Repository Views

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
• V$METRIC - Displays metric information.
• V$METRICNAME - Displays the metrics associated with each metric group.
• V$METRIC_HISTORY - Displays historical metrics.
• V$METRICGROUP - Displays all metrics groups.
• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
• DBA_HIST_BASELINE - Displays baseline information.
• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
• DBA_HIST_SNAPSHOT - Displays snapshot information.
• DBA_HIST_SQL_PLAN - Displays SQL execution plans.
• DBA_HIST_WR_CONTROL - Displays AWR settings.

Workload Repository Reports

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

Enterprise Manager

The automated workload repository administration tasks have been included in Enterprise Manager.

The "Automatic Workload Repository" page is accessed from the main page by clicking on the "Administration" link,

then the "Workload Repository" link under the "Workload" section.

The page allows you to modify AWR settings or manage snapshots without using the PL/SQL APIs.

Source: Link

Oracle Active Session History (ASH)

Oracle Database 10g now collects the Active Session History (ASH) statistics (mostly the wait statistics for different events) for all active sessions every second, and stores them in a circular buffer in the SGA.
The ASH feature uses about 2MB of SGA memory per CPU.
Current Active Session Data
V$ACTIVE_SESSION_HISTORY enables you to access the ASH statistics. A database session is considered active if it was on the CPU or was waiting for an event that didn’t belong to the Idle wait class (indicated by SESSION_STATE column).
DBA_HIST_ACTIVE_SESSION_HISTORY View
This view in fact is a collection of snapshots from the V$ACTIVE_SESSION_HISTORY view. It is populated either by MMON during its regular snapshot capturing or by MMNL when the memory buffer is full.
Generate ASH Reports
In Oracle Release 2, you can generate ASH Report.
Some of the information it shows are top wait events, top SQL, top SQL command types, and top sessions, among others.
On Database Control:
Performance -> Run ASH Report button
On SQL*Plus:
Run the following script
$ORACLE_HOME/rdbms/admin/ashrpt.sql

Source:Link

Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g

Overview

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL.

The ADDM analysis includes:

• CPU load
• Memory usage
• I/O usage
• Resource intensive SQL
• Resource intensive PL/SQL and Java
• RAC issues
• Application issues
• Database configuration issues
• Concurrency issues
• Object contention

The findings (problems) are listed in order of potential impact on database performance, along with recommendations to resolve the issue and the symptoms which lead to it's discovery.

An example from my test instance is:

FINDING 1: 59% impact (944 seconds)

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

The buffer cache was undersized causing significant additional read I/O.

   RECOMMENDATION 1: DB Configuration, 59% benefit (944 seconds)

      ACTION: Increase SGA target size by increasing the value of parameter

         "sga_target" by 28 M.

   SYMPTOMS THAT LED TO THE FINDING:

      Wait class "User I/O" was consuming significant database time. (83%

      impact [1336 seconds])

The recommendations may include:

• Hardware changes
• Database configuration changes
• Schema changes
• Application changes
• Using other advisors

The analysis of I/O performance is affected by the DBIO_EXPECTED parameter which should be set to the average time (in microseconds) it takes to read a single database block from disk. Typical values range from 5000 to 20000 microsoconds.

The parameter can be set using:

EXECUTE DBMS_ADVISOR.set_default_task_parameter('ADDM', 'DBIO_EXPECTED', 8000);

Enterprise Manager

The obvious place to start viewing ADDM reports is Enterprise Manager. The "Performance Analysis" section on the "Home" page is a list of the top five findings from the last ADDM analysis task.

Specific reports can be produced by clicking on the "Advisor Central" link, then the "ADDM" link. The resulting page allows you to select a start and end snapshot, create an ADDM task and display the resulting report by clicking on a few links.

addmrpt.sql Script

The addmrpt.sql script can be used to create an ADDM report from SQL*Plus. The script is called as follows:

-- UNIX

@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql

-- Windows

@d:\oracle\product\10.1.0\db_1\rdbms\admin\addmrpt.sql

It then lists all available snapshots and prompts you to enter the start and end snapshot along with the report name.

DBMS_ADVISOR

The DBMS_ADVISOR package can be used to create and execute any advisor tasks, including ADDM tasks. The following example shows how it is used to create, execute and display a typical ADDM report:

BEGIN

  -- Create an ADDM task.

  DBMS_ADVISOR.create_task (

    advisor_name      => 'ADDM',

    task_name         => '970_1032_AWR_SNAPSHOT',

    task_desc         => 'Advisor for snapshots 970 to 1032.');

  -- Set the start and end snapshots.

  DBMS_ADVISOR.set_task_parameter (

    task_name => '970_1032_AWR_SNAPSHOT',

    parameter => 'START_SNAPSHOT',

    value     => 970);

  DBMS_ADVISOR.set_task_parameter (

    task_name => '970_1032_AWR_SNAPSHOT',

    parameter => 'END_SNAPSHOT',

    value     => 1032);

  -- Execute the task.

  DBMS_ADVISOR.execute_task(task_name => '970_1032_AWR_SNAPSHOT');

END;

/

-- Display the report.

SET LONG 100000

SET PAGESIZE 50000

SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report

FROM   dual;

SET PAGESIZE 24

The value for the SET LONG command should be adjusted to allow the whole report to be displayed.

The relevant AWR snapshots can be identified using the DBA_HIST_SNAPSHOT view.

Related Views

The following views can be used to display the ADDM output without using Enterprise Manager or the GET_TASK_REPORT function:

• DBA_ADVISOR_TASKS - Basic information about existing tasks.
• DBA_ADVISOR_LOG - Status information about existing tasks.
• DBA_ADVISOR_FINDINGS - Findings identified for an existing task.
• DBA_ADVISOR_RECOMMENDATIONS - Recommendations for the problems identified by an existing task.

Source: Link

cache hit ratio

Buffer cache hit ratio:

http://www.dba-oracle.com/t_buffer_cache_hit_ratio_value.htm

"The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE"

Oracle has the v$db_cache_advice utility and has incorporated a buffer cache advisory into the standard AWR report, ostensibly to provide recommendations about the projected reduction in expensive disk I/O with the addition  of more data buffers.

Hence, on the margin, the data buffer cache advisory is inaccurate for database with an undersized db_cache_size (and db_keep_cache_size, etc.). 

http://www.dba-oracle.com/m_buffer_cache_hit_ratio.htm

http://www.dba-oracle.com/oracle_tips_data_cache_advice.htm

The following query can be used to perform the cache advice function, once the db_cache_advice has been enabled and the database has run long enough to give representative results.

-- ***********************************************************
-- Display cache advice
-- ***********************************************************

 

 

column c1   heading 'Cache Size (meg)'      format 999,999,999,999 
 

select

   size_for_estimate          c1,

   buffers_for_estimate       c2,

   estd_physical_read_factor  c3,

   estd_physical_reads        c4

from

   v$db_cache_advice

where

   name = 'DEFAULT'

and

   block_size  = (SELECT value FROM V$PARAMETER

                   WHERE name = 'db_block_size')

and

   advice_status = 'ON';

The output from the script is shown below.  Note that the values range from 10 percent of the current size to double the current size of the db_cache_size.

                                Estd Phys    Estd Phys
 Cache Size (meg)     Buffers Read Factor        Reads
---------------- ------------ ----------- ------------              30        3,802       18.70  192,317,943 <== 10% size
              60        7,604       12.83  131,949,536
              91       11,406        7.38   75,865,861
             121       15,208        4.97   51,111,658
             152       19,010        3.64   37,460,786
             182       22,812        2.50   25,668,196
             212       26,614        1.74   17,850,847
             243       30,416        1.33   13,720,149
             273       34,218        1.13   11,583,180             304       38,020        1.00   10,282,475 <== Current Size
             334       41,822         .93    9,515,878
             364       45,624         .87    8,909,026
             395       49,426         .83    8,495,039
             424       53,228         .79    8,116,496
             456       57,030         .76    7,824,764
             486       60,832         .74    7,563,180
             517       64,634         .71    7,311,729
             547       68,436         .69    7,104,280
             577       72,238         .67    6,895,122             608       76,040         .66    6,739,731 <== 2x size

From the above listing we see that increasing the db_cache_size from 304 meg to 334 meg would result in approximately 700,000 less physical reads.  This can be plotted as a 1/x function and the exact optimal point computed as the second derivative of the function:

The Buffer Cache Hit Ratio Oracle metric monitors the rate at which Oracle finds the data blocks it needs in memory over the lifetime of an instance.

"many DBAs do their best to get a 99% or better hit ratio, but quickly discover that the performance of their database isn't improving as the hit ratio gets better," therefore, sometimes these statistics can be misleading. However, you can try, "the Oracle Wait Interface (OWI)" for better tuning.

we can use this query
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS;
to learn more about the buffer pool hit ratios.

you have
the default pool
the keep pool
and the recycle pool
When people refer to the buffer cache they usually refer to the default pool

KEEP pool - typically used for objects you want to keep permanently cached. After a warm-up period you hope that every access to an object in this pool is met from the buffer, so the KEEP hit ratio should be 100% if you're using it as you expect.

RECYCLE pool - typically used for objects that are such a nuisance that you can't hope to get any reasonable caching effect for them, but they still knock something out of memory when you read the blocks. You expect the RECYCLE hit ratio to 0% - if you're using it "properly".

DEFAULT pool - whatever you think your hit ratio should be for this pool, the figure is going to be clouded if you sum in the buffer gets and physical blocks read for the other buffer pools.

http://www.dba-oracle.com/m_library_cache_hit_ratio.htm

Oracle Library Cache Hit Ratio

The Library Cache Hit Ratio Oracle metric monitors the percentage of entries in the library cache that were parsed more than once (reloads) over the lifetime of the instance. 
Since you never know in-advance how many SQL statements need to be cached, the Oracle DBA must set shared_pool_size large enough to prevent excessive re-parsing of SQL.

the library cache hit ratio and error code ORA-0403. 

 It states that adjusting the shared pool size may help avoid this error.

To do this, we evaluate the library cache hit ratio metric as such; "The hit ratio helps to measure the usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being reused. The following SQL statement help you to calculate the library cache hit ratio:

SELECT
   SUM(PINS) "EXECUTIONS",
   SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM
   V$LIBRARYCACHE;

If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size. "

Don't even bother trying to tune the Buffer Hit Ratio!
There are better ways to tune now. The Oracle Wait Interface (OWI) provides exact details.

http://www.orafaq.com/wiki/Improving_Buffer_Cache_Hit_Ratio

Hit/Miss Ratios

Buffer Hit Ratio

BUFFER HIT RATIO NOTES:

·  Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.

·  DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).

·  Physical Reads - The cumulative number of blocks read from disk.

·  Logical reads are the sum of consistent gets and db block gets.

·  The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

·  Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

·  select       sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",

        sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",

        sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",

        round((sum(decode(name, 'consistent gets',value, 0)) + 

               sum(decode(name, 'db block gets',value, 0)) - 

               sum(decode(name, 'physical reads',value, 0))) / 

              (sum(decode(name, 'consistent gets',value, 0)) + 

               sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"

from   v$sysstat

Data Dict Hit Ratio

DATA DICTIONARY HIT RATIO NOTES:

·  Gets - Total number of requests for information on the data object.

·  Cache Misses - Number of data requests resulting in cache misses

·  Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select  sum(GETS),

        sum(GETMISSES),

        round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)

from    v$rowcache

SQL Cache Hit Ratio

SQL CACHE HIT RATIO NOTES:

·  Pins - The number of times a pin was requested for objects of this namespace.

·  Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

·  Hit Ratio should be > 85%

select  sum(PINS) Pins,

        sum(RELOADS) Reloads,

        round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio

from    v$librarycache

Library Cache Miss Ratio

LIBRARY CACHE MISS RATIO NOTES:

·  Executions - The number of times a pin was requested for objects of this namespace.

·  Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

·  Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select  sum(PINS) Executions,

        sum(RELOADS) cache_misses,

        sum(RELOADS) / sum(PINS) miss_ratio

from    v$librarycache

ASMM (Automatic Performance Tuning)

http://oradbpedia.com/wiki/Automatic_Performance_Tuning

Oracle 10g adds the capability to monitor and automatically tune the buffer cache size, alleviating the DBA from this responsibility. This ability is part of Oracle's self-tuning database. Automatic Shared Memory Management (ASMM) will automatically configure the shared memory areas within certain parameters. ASMM will dynamically configure the size of the default buffer cache, the shared pool, the Java pool, and the large pool.

To set up ASMM, the statistics_level parameter must be set to TYPICAL or ALL. The sga_target parameter needs to be set to a non-zero value. It is the sga_target parameter that provides the biggest guideline to ASMM. The total size of all four shared areas cannot exceed this value. ASMM will dynamically adjust the size of these four shared areas to meet the overall database workload, the total never exceeding the sga_target size. Additionally, if you set the db_cache_size, shared_pool_size, java_pool_size, and large_pool_size parameters, these parameters serve as minimum sizes for their respective shared areas. The mimimum sizes of the shared areas is defined by the DBA and the maximum total size of all areas is defined by the DBA. ASMM determines where to size the shared areas within those boundaries.

The sga_target parameter is a dynamic parameter. Setting this parameter to zero turns off ASMM. The maximum value for sga_target is equal to the setting for the sga_max_size parameter. Note that the non-default buffer caches, the log buffer, the Streams pool, the fixed SGA, and other internal allocations are not affected by ASMM. The v$sga_current_resize_ops view shows current resize operations. The v$sga_resize_ops view shows the last 400 completed resize operations.

SGA_TARGET

http://www.orafaq.com/wiki/Sga_target

SGA_TARGET provides the following:

• Single parameter for total SGA size
• Automatically sizes SGA components
• Memory is transferred to where most needed
• Uses workload information
• Uses internal advisory predictions
• STATISTICS_LEVEL must be set to TYPICAL

By using one parameter we don't need to use all other SGA parameters like.

• DB_CACHE_SIZE (DEFAULT buffer pool)
• SHARED_POOL_SIZE (Shared Pool)
• LARGE_POOL_SIZE (Large Pool)
• JAVA_POOL_SIZE (Java Pool)

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10755/initparams190.htm

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

·         Log buffer

·         Other buffer caches, such as KEEP, RECYCLE, and other block sizes

·         Streams pool

·         Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

Different pools within the cache

http://www.adp-gmbh.ch/ora/concepts/cache.html

The cache consists actually of three buffer pools for different purposes.

Keep pool

The keep pool's purpose is to take small objects that should always be cached, for example Look Up Tables.

See db_keep_cache_size.

Recycle pool

The recycle pool is for larger objects.

See db_recycle_cache_size.

Default pool

The default pool is for everything else.

See also x$kcbwbpd

http://www.adp-gmbh.ch/ora/admin/init_params/sga.html#db_keep_cache_size

DB_KEEP_CACHE_SIZE

According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_RECYCLE_CACHE_SIZE

According to metalink note 223299.1, this is one of the top parameters affecting performance.

Manual SGA parameters

• db_keep_cache_size
• db_recycle_cache_size
• db_NNk_cache_size
  NN being one of 2, 4, 8, 16, 32
• log_buffer
• streams_pool_size

http://www.remote-dba.net/oracle_10g_tuning/t_oracle_keep_pool.htm

Oracle KEEP Pool

A DBA can easily write a script that automatically identifies candidates for the KEEP pool and generates the syntax to move the tables into the pool. 

buf_keep_pool.sql

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

-- Copyright © 2005 by Rampant TechPress

-- This script is free for non-commercial purposes

-- with no warranties.  Use at your own risk.

--

-- To license this script for a commercial purpose,

-- contact info@rampant.cc

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

set pages 999

set lines 92

spool keep_syn.lst

drop table t1;

create table t1 as

select

   o.owner          owner,

   o.object_name    object_name,

   o.subobject_name subobject_name,

   o.object_type    object_type,

   count(distinct file# || block#)         num_blocks

from

   dba_objects  o,

   v$bh         bh

where

   o.data_object_id  = bh.objd

and

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

and

   bh.status != 'free'

group by

   o.owner,

   o.object_name,

   o.subobject_name,

   o.object_type

order by

   count(distinct file# || block#) desc

;

select

   'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'

from

   t1,

   dba_segments s

where

   s.segment_name = t1.object_name

and

   s.owner = t1.owner

and

   s.segment_type = t1.object_type

and

   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')

and

   buffer_pool <> 'KEEP'

and

   object_type in ('TABLE','INDEX')

group by

   s.segment_type,

   t1.owner,

   s.segment_name

having

   (sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80

;

spool off;

The following is sample of the output from this script.

alter TABLE BOM.BOM_DELETE_SUB_ENTITIES storage (buffer_pool keep);

alter TABLE BOM.BOM_OPERATIONAL_ROUTINGS storage (buffer_pool keep);

alter INDEX BOM.CST_ITEM_COSTS_U1 storage (buffer_pool keep);

alter TABLE APPLSYS.FND_CONCURRENT_PROGRAMS storage (buffer_pool keep);

alter TABLE APPLSYS.FND_CONCURRENT_REQUESTS storage (buffer_pool keep);

alter TABLE GL.GL_JE_BATCHES storage (buffer_pool keep);

alter INDEX GL.GL_JE_BATCHES_U2 storage (buffer_pool keep);

alter TABLE GL.GL_JE_HEADERS storage (buffer_pool keep);

alter TABLE INV.MTL_DEMAND_INTERFACE storage (buffer_pool keep);

alter INDEX INV.MTL_DEMAND_INTERFACE_N10 storage (buffer_pool keep);

alter TABLE INV.MTL_ITEM_CATEGORIES storage (buffer_pool keep);

alter TABLE INV.MTL_ONHAND_QUANTITIES storage (buffer_pool keep);

alter TABLE INV.MTL_SUPPLY_DEMAND_TEMP storage (buffer_pool keep);

alter TABLE PO.PO_REQUISITION_LINES_ALL storage (buffer_pool keep);

alter TABLE AR.RA_CUSTOMER_TRX_ALL storage (buffer_pool keep);

alter TABLE AR.RA_CUSTOMER_TRX_LINES_ALL storage (buffer_pool keep);

alter INDEX WIP.WIP_REQUIREMENT_OPERATIONS_N3 storage (buffer_pool keep);

Using the Oracle KEEP pool

According to Oracle documentation, “A good candidate for a segment to put into the KEEP pool is a segment that is smaller than 10% of the size of the DEFAULT buffer pool and has incurred at least 1% of the total I/Os in the system”. More concisely, a small table that is in high demand is a good candidate for KEEP caching.

http://www.dba-oracle.com/oracle_tips_cache_small_fts.htm

Tuning the Keep and Recycle Pools

Note that ASSM does not automatically tune the keep and recycle buffer pools. You will still need to manually determine the size of the keep and recycle buffer pools with the db_keep_cache_size and db_recycle_cache_size parameters, respectively. And it should be noted that the if you have set the keep and recycle buffer pools, the memory for these pools is deducted from the overall sga_target that ASSM uses.

The best place to start tuning the buffer pools is with the v$db_cache_advice view (available in Oracle 9i and 10g). Before using this view, you must set the db_cache_advice initialization parameter to ON. If this parameter is already set to ON, and you set it to ON again, it will reset the information in the v$db_cache_advice view.

http://www.oradbpedia.com/wiki/Tuning_the_Keep_and_Recycle_Pools

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. 

http://www.remote-dba.net/oracle_10g_tuning/t_oracle_recycle_pool.htm