Oracle Tips and Tricks — David Fitzjarrell

May 19, 2008

That’s A Keeper

Filed under: General — dfitzjarrell @ 13:25

The conventional ‘wisdom’ on the Internet regarding logical I/O is that such memory reads are ‘free’, which discounts (drastically) the cost of processing such reads [the CPU cost can be quite high on an active system, making such reads far from ‘free’]. Of course that’s not to say that logical I/O isn’t far faster than physical I/O; it is by a rather large factor. And that factor can be a boon to developers and end users alike, allowing results to return much faster than would be possible with a mixture of physical and logical I/O calls.

So, how does one ‘cache in’ (pun intended here) on this? By configuring the db_keep_cache_size parameter in Oracle 9i and later releases and by altering oft-queried tables and/or indexes to use the KEEP buffer pool. Properly sizing this parameter setting can dramatically improve query throughput, although it shouldn’t be used indiscriminantly.

What, exactly, does the KEEP buffer pool do? When using automatic SGA management (by setting sga_target, sga_max_size or both in the init.ora or spfile) this pool is dynamically allocated; it only needs to have its cache configured and tables altered to use this pool rather than the DEFAULT buffer pool (the … ummm … ‘default’ if you don’t specify which pool to use). Once used the blocks in the KEEP pool don’t get aged out by time; of course if the cache isn’t large enough to contain all of the tables configured to use it then blocks will be replaced and physical I/O will be necessary to replace them as the configured tables are queried — Table A and Table B are both configured to use the KEEP pool, the keep cache is sized to hold either all of Table A or all of Table B, but not both, so queries against Table A will cause the cache to load with those blocks, and unfortunately if Table B is queried thereafter those blocks will replace those from Table A and basically result in no better performance than using the DEFAULT pool, which is not the intended result.

The KEEP cache should be sized a bit larger than the sum of all of the tables configured to use it; the sizing is in blocks, so a simple query of the USER_TABLES view reporting the sum of the BLOCKS column for all tables using the KEEP pool should provide an excellent starting point:

SQL> select sum(blocks)
  2  from user_tables
  3  where buffer_pool  = 'KEEP'
  4  /

SUM(BLOCKS)
-----------
        639

Knowing the granule size is also important, as that will be the value by which the cache is incremented or decremented; dividing the granule size by the db_block_size produces the number of blocks each adjustment will consume:

SQL> select granule_size/value
  2  from v$sga_dynamic_components, v$parameter
  3  where name = 'db_block_size'
  4  and component like 'KEEP%'
  5  /

GRANULE_SIZE/VALUE
------------------
               512

 

Setting the db_keep_cache_size to anything less than 512 blocks will automatically allocate 512 blocks since the memory allocations are made in granule-sized increments when using automatic SGA management. Each adjustment up or down will add or subtract multiples of 512 blocks (at least in this case) to the cache. On my system the minimum value to which I can set db_keep_cache_size is 32 MB; any smaller value I might use will automatically configure the cache at 32 MB. The minimum size is dependent upon how many CPUs are installed and the granule size for the database in question. The formula for sizing this pool is

number of CPUs * granule size

Looking at the block total for all tables configured to use the KEEP pool we see the figure is larger than one granule, so we should set this parameter to at least 1024 (or a value larger than 512 to allow Oracle to ‘bump it up’ automatically to the next granule). Of course Oracle will most likely ‘bump this up’ to the minimum value obtained by the above calculation, but you won’t know that until you query V$BUFFER_POOL to obtain the current size. In any case you should have sufficient space in the KEEP pool using the default calculation. Oh, and any adjustment above the minimum will be added in granule-sized allocations, so attempting to increase this cache to 33 MB will result in a cache 36 MB in size.

How do you know the blocks remain in the cache? Using autotrace tells the tale:

Session 1:

SQL> alter table mytab storage(buffer_pool keep);

Table altered

SQL> select * from mytab;

[... lots of data here ...]

Statistics
----------------------------------------------------------
        239  recursive calls
          0  db block gets
       2964  consistent gets
        513  physical reads
          0  redo size
    2696838  bytes sent via SQL*Net to client
      20252  bytes received via SQL*Net from client
       2860  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      42880  rows processed

SQL> select * from test;

[... lots of data here ...]

Statistics
----------------------------------------------------------
        705  recursive calls
          3  db block gets
        831  consistent gets
        146  physical reads
        632  redo size
     668321  bytes sent via SQL*Net to client
       5216  bytes received via SQL*Net from client
        712  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
      10663  rows processed

Session 2:

SQL> select * from mytab;

[... lots of data here ...]

Statistics
----------------------------------------------------------
        239  recursive calls
          0  db block gets
       3477  consistent gets
          0  physical reads
          0  redo size
    2696838  bytes sent via SQL*Net to client
      20252  bytes received via SQL*Net from client
       2860  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      42880  rows processed

Notice that Session 2 had nothing but consistent gets for the query of mytab, even though Session 1 had also queried the table TEST, indicating that the KEEP pool is doing its job of keeping data blocks cached. Had Session 1 queried mytab again, after querying TEST, the same statistics would be generated (nothing but consistent gets for the query). Which makes this a convenient way to ensure often queried tables remain in cache for faster access.

Of course all of this is predicated on the system in question having sufficient memory to run the database, the requisite operating system processes and still have enough left to configure the KEEP cache without having to use swap space. Having to swap parts of the KEEP cache to and from disk defeats the purpose of configuring the pool in the first place, as you’ll likely end up with far worse performance than if you hadn’t used it (the KEEP pool/cache) at all.

Which brings up the question: ‘How large is the KEEP pool currently?’ We answer that with a trip through V$BUFFER_POOL:

SQL> select name, current_size
  2  from v$buffer_pool;

NAME                 CURRENT_SIZE
-------------------- ------------
KEEP                           32
DEFAULT                       488

SQL>

CURRENT_SIZE ie reported in megabytes so in this case the KEEP pool consumes 32 megabytes of memory. (As mentioned earlier on this system 32 MB is the minimum size this cache will attain.) Of course it may not be set currently and, in that situation, no entry will be reported:

SQL> select name, current_size
  2  from v$buffer_pool;

NAME                 CURRENT_SIZE
-------------------- ------------
DEFAULT                       520

SQL> 

Statistics for the pools are viewable in, you guessed it, the V$BUFFER_POOL_STATISTICS view:

SQL> desc v$buffer_pool_statistics
 Name                                   Null?    Type
--------------------------------------- -------- ------------------
 ID                                              NUMBER
 NAME                                            VARCHAR2(20)
 BLOCK_SIZE                                      NUMBER
 SET_MSIZE                                       NUMBER
 CNUM_REPL                                       NUMBER
 CNUM_WRITE                                      NUMBER
 CNUM_SET                                        NUMBER
 BUF_GOT                                         NUMBER
 SUM_WRITE                                       NUMBER
 SUM_SCAN                                        NUMBER
 FREE_BUFFER_WAIT                                NUMBER
 WRITE_COMPLETE_WAIT                             NUMBER
 BUFFER_BUSY_WAIT                                NUMBER
 FREE_BUFFER_INSPECTED                           NUMBER
 DIRTY_BUFFERS_INSPECTED                         NUMBER
 DB_BLOCK_CHANGE                                 NUMBER
 DB_BLOCK_GETS                                   NUMBER
 CONSISTENT_GETS                                 NUMBER
 PHYSICAL_READS                                  NUMBER
 PHYSICAL_WRITES                                 NUMBER

SQL>

You can monitor the overall pool usage, although you cannot monitor (through this view) whether or not a particular table in that pool is being accessed. But, as shown earlier, using autotrace can reveal that information.

Which tables are good candidates for the KEEP pool/cache? That’s up to the DBA to decide, really. Monitoring table access (through auditing) or through regular use of Statspack reports (examining the Top SQL by elapsed time) or, if you’re licensed, AWR reports, can provide a list of regularly accessed tables and the benefits can be weighed against the resource cost (RAM required) to determine which tables could be using the KEEP pool/cache. Heavily updated tables (such as call detail record tables for telecommunications companies) should probably not use the KEEP pool as the blocks in cache will need to be refreshed resulting in physical I/O (which you wanted to reduce or avoid). I say ‘probably not’ because it may be that just such a table could benefit from using the KEEP pool/cache; each situation is different and should be evaluated as such. In all cases I stress that you should:

Test

Test

Test again

and determine from those tests how much benefit, if any, this configuration may provide.

Yes, you can update values in tables configured to use the KEEP pool; there is nothing preventing that from occurring. Remember, though, that wholesale updates/inserts to tables using the KEEP pool will cause those tables to be refreshed at the next query so you’ll probably lose performance, if even slightly, because of this.

Configuring and using the KEEP cache/pool can be beneficial in some circumstances; using it may just help you improve application performance by keeping often used tables in memory (as much as is possible) thus reducing the physical I/O calls. Of course your mileage may vary, so as I said before:

Test

Test

Test again

to verify this is a viable option, as what may be good for one installation may be terribly, horribly wrong for another.

Who knows? You may just find a ‘keeper’.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: