1. You can use a formula to configure the size of the buffer cache. (True/False)___FALSE___

2. The buffer cache does not support tablespaces with multiple data block sizes. (True/False)__FALSE____

3. Dynamic allocation is not allowed on the default cache size of the same DB_nK_CACHE_SIZE (where n is 2, 4, 8, 16, or 32). (True/False)___FALSE___

4. The purpose of the Keep buffer pool is to keep all data objects in this buffer as long as they are used. (True/False)__FALSE___

5. You need to shutdown the database when you decide to change the SGA_MAX_SIZE parameter. (True/False)___TRUE____

6. Explain briefly the role of the buffer cache in Oracle9i architecture and why it is important to configure the cache size properly?

The role of the buffer cache is to cache data blocks accessed or updated in memory for fast retrieval. If the buffer cache is improperly configured will degrade the performance of the database because data will not be retrieved from memory it will be read from datafiles causing I/O.

7. Describe the internal structure of the buffer cache.
  • Default buffer pool: used when data is not assigned to any specific pool (Keep or Recycle)
  • Keep buffer pool: used to for data blocks that should be retained as long as possible. This pool is subject to the LRU process for flushing data blocks when space is needed. (This buffer pool is optional)
  • Recycle buffer pool: used for data blocks that have the lowest priority for being kept in memory than Default and Keep. (This buffer pool is optional)

8. What are the two dynamic performance views you would use to compute the buffer cache hit ratio and individual buffer pool?

V$SYSSTAT and V$BUFFER_POOL_STATISTICS

9. What is the buffer cache hit ratio threshold. Explain what would you do if it is 70%.

The buffer cache hit ratio threshold is 90%. A consistent ratio of 70% indicates that the buffer cache needs to be increased.

10. What is the purpose of the Cache Advice?

The purpose of the Cache Advice is to provide an estimate of physical reads if you were to increase or decrease the cache buffer size.

11. Define the purpose of V$BH performance dynamic view.

The purpose of the buffer cache in order to:
  • See what objects are cached in memory
  • Determine how many buffers an object is taking in memory
  • Get buffer counts for the different usage and types of the cache
  • Determine how many blocks are modified (dirty) and how many are free or read
  • Inspect blocks that have been rewritten and re-read in a Oracle Real Application Cluster configuration

12. What is implied when you set the Cache Advice to a ready state?

When the Cache Advice is set to READY it implies that you are anticipating to turn the Cache Advice. Ready state will reserve memory in shared pool but it will not collect advisory data.

13. Define the purpose of DB_nK_CACHE_SZIE (where n is 2, 4, 8, 16, or 32).

The purpose of the DB_nK_CACHE_SZIE parameters is to support tablespaces with different database block size.

14. How can you determine the impact of reducing the buffer cache?

You can use the Oracle9i feature called Cache Advice, this feature provide an estimated PHYSICAL_READS if you were to reduce or increase the buffer cache size

15. Write three statements to determine the size of the SGA.

(1) SELECT SUM(BYTES)/(1024*1024)
        FROM V$SGASTAT;

(2) SELECT SUM(VALUE)/(1024*1024)
        FROM V$SGA;

(3) SHOW SGA

(4) SELECT SUM(VALUE)/(1024*1024) +
          CEIL(CEIL(SUM(VALUE)/(1024*1024)) –
          SUM(VALUE)/(1024*1024)) * 1
          “Param Value + 1 granule”
   FROM V$PARAMETER
  WHERE NAME IN('db_cache_size', 'db_keep_cache_size',
                'db_recycle_cache_size', 'db_2k_cache_size',
                'db_4k_cache_size', 'db_8k_cache_size',
                'db_16k_cahce_size', 'db_32k_cache_size',
                'shared_pool_size', ‘log_buffer',
                'large_pool_size', 'java_pool_size');

16. Explain the impact of setting the SGA_MAX_SIZE parameter.

There is no impact of SGA_MAX_SIZE on database performance. However, the value of this parameter dictates how large the SGA will ever be. So if you determine that you need to increase the size of any memory structure to a value higher than the SGA_MAX_SIZE you will not be able to. In this case you must shutdown the database and increase the SGA_MAX_SIZE.

17. Write a query to compute how much buffer cache is allocated.

SELECT SUM(VALUE)/(1024*1024)
  FROM V$PARAMETER
WHERE NAME IN('db_cache_size','db_keep_cache_size',
               'db_recycle_cache_size', 'db_2k_cache_size',
               'db_4k_cache_size', 'db_8k_cache_size',
               'db_16k_cahce_size', 'db_32_cache_size');

18. How do you cache a table in Recycle buffer pool?
You must ALTER TABLE … STORAGE( BUFFER_POOL …)

Method 1:

ALTER TABLE … CACHE

Method 2:
SELECT /*+ CACHE(CATEGORIES) */ *
    FROM CATEGORIES;

19. Why would you need to look inside the buffer cache?

We look inside the buffer cache in order to:
  • See what objects are cached in memory
  • Determine how many buffers an object is taking in memory
  • Get buffer counts for the different usage and types of the cache
  • Determine how many blocks are modified (dirty) and how many are free or read
  • Inspect blocks that have been rewritten and re-read in a Oracle Real Application Cluster configuration
Find out more about which datafiles or tablespaces being accessed most often and how many buffers they use.

20. How do you configure the buffer cache to have multiple buffer pools?

You need to set the following parameters in the initialization file:
  • DB_CACHE_SIZE
  • DB_KEEP_CACHE_SIZE      
  • DB_RECYCLE_CACHE_SIZE