Karl about the Oracle Database

Hi reader,

Once an Oracle Block is read from the disk it gets a place in the buffer cache. How long the block remains cached depends on following parameters.

• if it’s an index block - they are stronger bound to the DEFAULT cache then table blocks resulting in a longer period of cache life cycle.
• if it’s a table block - it will be cached but earlier freed from cache then an index block
• if it’s a frequently accessed (LRU mechanism) a block frequently access will remain in the buffer cache
• if the table block is originated by a long full table scan - a block read by a scattered read on a table which does not fit in a scan window of the buffer cache - it will be displaced immediately from the next blocks.

To guarantee a longer life cycle for a table/index block another cache type i think was introduced with Oracle 8i. The KEEP pool. It tends to keep (to pin) the blocks even they are not frequently accessed and enables caching of all blocks of a segment read with scattered read and not only a subset of it. Thus it is guaranteed that access to a segment cached in KEEP pool will not have any physical I/O. Usually i learnt to keep frequently small objects for example lookup tables into the KEEP pool.

We faced a problem on our company table querying specific company types a full table scan could not prevented. If many columns are used in some reports/lists to filter the result you cannot put an index on every column of the table. The company table is about 1.7G large with about 6.5M of rows. On our test database server two concurrent queries on the company table resulted in  > 60sec instead of 25sec. And even 25 sec are too long.

Having a 64 bit system we decided to upgrade memory and to pin the Company table into the KEEP pool.

Following steps to load the company table int o the KEEP pool:

• set SGA_TARGET to 2.8G - Database is in automatic SGA sizing mode - a restart of the database will be needed
  ALTER SYSTEM SET sga_target=2800M SCOPE=SPFILE
  ;
  
• and then set DBA_KEEP_POOL_SIZE to 1.8G - This reduces available memory for other SGA (Shared pool + DEFAULT buffer cache) components to 1G 
  ALTER SYSTEM SET db_keep_cache_size=1800M SCOPE=BOTH
  ;
  
• Altered the tables buffer cache
  ALTER TABLE COMPANY STORAGE (BUFFER_POOL KEEP)
  ;
  
• Pin the Company Table with a FULL table scan into the keep pool. ( You must use a FULL Hint to prevent an index full scan)
  SQL> SELECT /*+FULL (COMPANY)*/ COUNT(*) FROM COMPANY;
    COUNT(*)
  ———-
     6764758
  SQL>

once loaded the table into the KEEP buffer cache the result of the query response time was < 5sec even with concurrent sessions;

Monitor Buffer Pool Usage (the physical reads on the KEEP pool were caused by the inital load (pin) of the COMPANY Table):

SQL> SELECT Vps.NAME,
  2         Vps.Consistent_Gets,
  3         Vps.Physical_Reads
  4    FROM V$BUFFER_POOL_STATISTICS Vps;

NAME                 CONSISTENT_GETS PHYSICAL_READS
——————– ————— ————–
KEEP                        35762385         194722
DEFAULT                    306683092        3568628

SQL>

To go this way successfully you need following resources

• fast CPU; for high LIO-Rate - on our Dual Xeon’s we had a rate of 1.4G/sec
• higher number of CPU >= 4
• Server optimized Mainboard/Memory access
• fast Memory

Conclusion

If your site drives a 64bit System. You could benefit from loading very large objects into the KEEP pool. Sure - this is not an all round optimisation for your weak SQL and your weak physical and logical design of your database/schema
;-)

HTH
Karl Reitschuster