Oracle9 i Performance Tuning

1. Oracle9iPerformance Tuning Chapter 2 Tuning the Buffer Cache

2. Chapter Objectives • Understand how the buffer cache works • Learn how to configure the buffer cache • Learn how to configure a database with one or multiple database block sizes • Dynamically allocate SGA memory • Configure a buffer cache with multiple buffer pools Chapter 2: Tuning the Buffer Cache

3. Chapter Objectives (continued) • Understand how automatic table caching works • Learn how to use the new Buffer Cache Size Advice • Diagnose buffer cache configuration • Look inside the buffer cache Chapter 2: Tuning the Buffer Cache

4. Oracle Architecture Chapter 2: Tuning the Buffer Cache

5. Buffer Cache Overview • The SGA is the memory structure in which Oracle caches data that is retrieved for: • Access • Updates • Submitted SQL statements • Executed PL/SQL blocks • Data dictionary definitions • Other cache mechanisms • The major purpose of the SGA is to enhance data retrieval by placing the most frequently used data in memory rather than retrieving it from disk Chapter 2: Tuning the Buffer Cache

6. Buffer Cache Overview (continued) • The SGA consists of the following memory structures: • Buffer cache • Redo log buffers • Shared pool memory • Large pool • Java pool Chapter 2: Tuning the Buffer Cache

7. Buffer Cache Overview (continued) • The buffer cache stores the most frequently accessed Oracle data blocks to reduce disk I/O • If data is cached, the Oracle server sends back the data requested without returning to the data files (this is known as a cache hit) • If data is not cached, the Oracle server fetches the data from data files in the database based on the execution plan (this is known as a cache miss) Chapter 2: Tuning the Buffer Cache

8. Buffer Cache Overview (continued) Chapter 2: Tuning the Buffer Cache

9. Buffer Cache Overview (continued) • The types of buffers are: • Default • Keep • Recycle Chapter 2: Tuning the Buffer Cache

10. Configuring the Buffer Cache • Initial configuration can be based on existing databases and types of applications • The DB_BLOCK_BUFFERS parameter is deprecated in Oracle9i but is still supported for backward compatibility • DB_BLOCK_BUFFERS cannot be allocated dynamically • Use DB_CACHE_SIZE to size the buffer • DB_CACHE_SIZE can be changed without shutting down the database • The Buffer Cache Size Advice feature is enabled only if the DB_CACHE_SIZE parameter is used • The SGA_MAX_SIZE parameter sets the maximum size the SGA can grow to Chapter 2: Tuning the Buffer Cache

11. Database with Multiple Block Sizes • Buffers support tablespaces with different block sizes than the one set with the DB_BLOCK_SIZE parameter • Parameters: • DB_2K_CACHE_SIZE • DB_4K_CACHE_SIZE • DB_8K_CACHE_SIZE • DB_16K_CACHE_SIZE • DB_32K_CACHE_SIZE SQL> CREATE TABLESPACE TEST_DATA_16K 2 LOGGING 3 DATAFILE 4 'C:\ORACLE\ORADATA\SAM\TEST_DATA_16K.ORA' 5 SIZE 100M 6 BLOCKSIZE 16384 7 EXTENT MANAGEMENT LOCAL 8 / SQL> SELECT TABLESPACE_NAME, BLOCK_SIZE 2 FROM DBA_TABLESPACES; Chapter 2: Tuning the Buffer Cache

12. Dynamic SGA Allocation • Use SGA_MAX_SIZE if you need to increase the SGA allocated memory • Use V$SGA_DYNAMIC_COMPONENTS to view the size of the major memory structures in the SGA • When you are adjusting the size of any memory structure in the SGA, it is decremented or incremented in granules • In Oracle9i, a granule is a memory unit Chapter 2: Tuning the Buffer Cache

13. Dynamic SGA Allocation (continued) Chapter 2: Tuning the Buffer Cache

14. Dynamic SGA Allocation (continued) Chapter 2: Tuning the Buffer Cache

15. Configuring Multiple Buffer Pools • DB_KEEP_CACHE_SIZE configures memory allocation for the KEEP pool in the buffer cache • DB_RECYCLE_CACHE_SIZE configures memory allocation for the RECYCLE pool in the buffer cache • Total size of memory for buffers is the sum of the following parameter values: • 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_CACHE_SIZE • DB_32K_CACHE_SIZE SQL> SELECT SUM(VALUE)/(1024*1024) 2 FROM V$PARAMETER 3 WHERE NAME IN('db_cache_size','db_keep_cache_size', 4 'db_recycle_cache_size', 'db_2k_cache_size', 5 'db_4k_cache_size', 'db_8k_cache_size', 6 'db_16k_cahce_size', 'db_32_cache_size') 7 / Chapter 2: Tuning the Buffer Cache

16. Configuring Multiple Buffer Pools (continued) • Cache a table in a specific buffer: SQL> ALTER TABLE DEPARTMENTS 2 STORAGE ( BUFFER_POOL KEEP) 3 / Method 1 SQL> ALTER TABLE DEPARTMENTS CACHE; SQL> SELECT TABLE_NAME, CACHE, BUFFER_POOL 2 FROM USER_TABLES 3 ORDER BY TABLE_NAME 4 / Method 2 SQL> SELECT /*+ CACHE(CATEGORIES) */ * FROM CATEGORIES / Chapter 2: Tuning the Buffer Cache

17. Automatic Table Caching • Oracle9i Release 2 has introduced the automatic caching of small tables based on the size of the table • Small table: a table is considered small if it is less than 20 blocks or 2% of the total cached blocks • Medium table: a table is considered medium if it is more than 20 blocks and less than 10% of the total cached blocks • Automatic table caching is based on these criteria, caching statistics, and table scan frequency • These criteria do not apply to any table that has the CACHE option enabled Chapter 2: Tuning the Buffer Cache

18. Buffer Cache Advice Chapter 2: Tuning the Buffer Cache

19. Buffer Cache Advice (continued) Chapter 2: Tuning the Buffer Cache

20. Buffer Cache Advice (continued) SQL> SELECT DECODE(SIZE_FACTOR, 1, '==>', null) " ", SIZE_FOR_ESTIMATE CSIZE , TRUNC(SIZE_FACTOR*100)||'%' PERCENT, BUFFERS_FOR_ESTIMATE BUFFERS_EST, ESTD_PHYSICAL_READ_FACTOR E_PHY_READ_FACTOR, ESTD_PHYSICAL_READS E_PHY_READS FROM V$DB_CACHE_ADVICE WHERE NAME = 'DEFAULT' AND ADVICE_STATUS = 'ON' AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size'); CACHE SIZE PERCENT BUFFERS_EST E_PHY_READ_FACTOR E_PHY_READS --- ---------- ------- -------------- ----------------- --------------- 4 33% 500 2.1007 6084420 8 66% 1000 1.3393 3879163 ==> 12 100% 1500 1 2896319 16 133% 2000 0.8335 2414111 20 166% 2500 0.7709 2232899 24 200% 3000 0.7646 2214471 28 233% 3500 0.755 2186828 32 266% 4000 0.7444 2156114 . . . Chapter 2: Tuning the Buffer Cache

21. Buffer Cache Advice (continued) Chapter 2: Tuning the Buffer Cache

22. Buffer Cache Diagnosis • Use V$SYSSTAT and V$SESSTAT • Buffer cache hit ratio is a percentage of physical reads over logical reads • Hit ratio = 1 - (physical reads/(block gets + consistent gets))/100 • Physical reads is the total number of data blocks accessed from disk • Block gets is the total number of buffers that are obtained for update (means the data block was read for update) • Consistent gets is the total number of buffers that are obtained in consistent read (means that the data block that was accessed used the System Change Number (SCN) to determine that the data block being read did not change since the query was submitted) SELECT ROUND( (1 - (PHY.VALUE/(CUR.VALUE + CON.VALUE)))*100, 1)||'%' ratio 2 FROM V$SYSSTAT PHY, V$SYSSTAT CUR, V$SYSSTAT CON 3 WHERE PHY.NAME = 'physical reads' 4 AND CUR.NAME = 'db block gets' 5 AND CON.NAME = 'consistent gets'; Chapter 2: Tuning the Buffer Cache

23. Buffer Cache Diagnosis (continued) • Buffer cache hit ratio threshold Chapter 2: Tuning the Buffer Cache

24. Buffer Cache Diagnosis (continued) • Hit ratio per session SQL> SELECT PHY.SID, 2 S.USERNAME, 3 1 - (PHY.VALUE)/(CUR.VALUE + CON.VALUE) BUFFER_HITRATIO 4 FROM V$SESSTAT PHY, V$SESSTAT CUR, V$SESSTAT CON, 5 V$STATNAME S1, V$STATNAME S2, V$STATNAME S3, 6 V$SESSION S 7 WHERE S1.NAME = 'physical reads' 8 AND S2.NAME = 'db block gets' 9 AND S3.NAME = 'consistent gets' 10 AND PHY.STATISTIC# = S1.STATISTIC# 11 AND CUR.STATISTIC# = S2.STATISTIC# 12 AND CON.STATISTIC# = S3.STATISTIC# 13 AND CUR.VALUE <> 0 14 AND CON.VALUE <> 0 15 AND PHY.SID = CUR.SID 16 AND PHY.SID = CON.SID 17 AND PHY.SID = S.SID 18 / Chapter 2: Tuning the Buffer Cache

25. Buffer Cache Diagnosis (continued) • Buffer pool statistics are shown using the performance dynamic view V$BUFFER_POOL_STATISTICS • Block size buffer hit ratio SQL> COLUMN RATIO HEADING "Buffer Cache Hitratio" FORMAT A30 SQL> COLUMN NAME HEADING 'Buffer Pool' FORMAT A15 SQL> COLUMN BLOCK_SIZE HEADING 'Block SQL> SELECT Size' SELECT NAME, 2 BLOCK_SIZE, 3 ROUND( (1 - (PHYSICAL_READS/ 4 (DB_BLOCK_GETS + CONSISTENT_GETS)))*100) || '%' ratio 5 FROM V$BUFFER_POOL_STATISTICS 6 / SQL> SELECT BLOCK_SIZE, 2 ROUND( (1 - AVG((PHYSICAL_READS/ 3 (DB_BLOCK_GETS + CONSISTENT_GETS))))*100) || '%' ratio 4 FROM V$BUFFER_POOL_STATISTICS 5 GROUP BY BLOCK_SIZE 6 / Chapter 2: Tuning the Buffer Cache

26. Inside the Buffer Cache • Use the V$BH view to: • Query the view for a list of data objects residing in the buffers and the number of data blocks in use • See how many blocks are modified (dirty) and how many are free or read • Inspect blocks that have been rewritten and re-read in an Oracle Real Application Cluster configuration • Find out more about which data files or tablespaces being accessed most often and how many buffers they use Chapter 2: Tuning the Buffer Cache

27. Inside the Buffer Cache (continued) Chapter 2: Tuning the Buffer Cache

28. Summary • The buffer cache is a memory structure of the SGA • The buffer cache facilitates faster access to data, because when data is retrieved from a disk, the cost to performance is high • The Least Recent Used algorithm ages out the least retrieved and changed blocks of data from the buffer • The buffer cache consists of three major internal structures: the Default buffer pool, Keep buffer pool, and Recycle buffer pool • The DB_CACHE_SIZE parameter configures the size of the buffer cache Chapter 2: Tuning the Buffer Cache