Oracle9i Performance Tuning

1
Oracle9i Performance 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

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

4
Oracle Architecture
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

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

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)

8
Buffer Cache Overview (continued)
9
Buffer Cache Overview (continued)

• The types of buffers are
• Default
• Keep
• Recycle

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

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

SQLgt 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 /
SQLgt SELECT TABLESPACE_NAME, BLOCK_SIZE 2
FROM DBA_TABLESPACES
12
Dynamic SGA Allocation

• Use SGA_MAX_SIZE if you need to increase the SGA
  allocated memory
• Use VSGA_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

13
Dynamic SGA Allocation (continued)
14
Dynamic SGA Allocation (continued)
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

SQLgt SELECT SUM(VALUE)/(10241024) 2 FROM
VPARAMETER 3 WHERE NAME IN('db_cache_size','d
b_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 /
16
Configuring Multiple Buffer Pools (continued)

• Cache a table in a specific buffer

SQLgt ALTER TABLE DEPARTMENTS 2 STORAGE (
BUFFER_POOL KEEP) 3 /
Method 1
SQLgt ALTER TABLE DEPARTMENTS CACHE SQLgt SELECT
TABLE_NAME, CACHE, BUFFER_POOL 2 FROM
USER_TABLES 3 ORDER BY TABLE_NAME 4 /
Method 2
SQLgt SELECT / CACHE(CATEGORIES) /
FROM CATEGORIES /
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

18
Buffer Cache Advice
19
Buffer Cache Advice (continued)
20
Buffer Cache Advice (continued)
SQLgt SELECT DECODE(SIZE_FACTOR,
1, 'gt', null) " ",
SIZE_FOR_ESTIMATE CSIZE ,
TRUNC(SIZE_FACTOR100)'' PERCENT,
BUFFERS_FOR_ESTIMATE BUFFERS_EST,
ESTD_PHYSICAL_READ_FACTOR E_PHY_READ_FACTOR,
ESTD_PHYSICAL_READS E_PHY_READS FROM
VDB_CACHE_ADVICE WHERE NAME 'DEFAULT'
AND ADVICE_STATUS 'ON' AND
BLOCK_SIZE (SELECT VALUE
FROM VPARAMETER
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 gt 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 .
. .
21
Buffer Cache Advice (continued)
22
Buffer Cache Diagnosis

• Use VSYSSTAT and VSESSTAT
• 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
VSYSSTAT PHY, VSYSSTAT CUR, VSYSSTAT CON 3
WHERE PHY.NAME 'physical reads' 4 AND
CUR.NAME 'db block gets' 5 AND CON.NAME
'consistent gets'
23
Buffer Cache Diagnosis (continued)

• Buffer cache hit ratio threshold

24
Buffer Cache Diagnosis (continued)

• Hit ratio per session

SQLgt SELECT PHY.SID, 2 S.USERNAME, 3
1 - (PHY.VALUE)/(CUR.VALUE CON.VALUE)
BUFFER_HITRATIO 4 FROM VSESSTAT PHY,
VSESSTAT CUR, VSESSTAT CON, 5
VSTATNAME S1, VSTATNAME S2, VSTATNAME S3, 6
VSESSION 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 ltgt 0 14 AND CON.VALUE ltgt 0 15
AND PHY.SID CUR.SID 16 AND PHY.SID
CON.SID 17 AND PHY.SID S.SID 18 /
25
Buffer Cache Diagnosis (continued)

• Buffer pool statistics are shown using the
  performance dynamic view VBUFFER_POOL_STATISTICS
• Block size buffer hit ratio

SQLgt COLUMN RATIO HEADING "Buffer Cache Hitratio"
FORMAT A30 SQLgt COLUMN NAME HEADING 'Buffer Pool'
FORMAT A15 SQLgt COLUMN BLOCK_SIZE HEADING 'Block
SQLgt SELECT Size' SELECT NAME, 2
BLOCK_SIZE, 3 ROUND( (1 -
(PHYSICAL_READS/ 4 (DB_BLOCK_GETS
CONSISTENT_GETS)))100) '' ratio 5 FROM
VBUFFER_POOL_STATISTICS 6 /
SQLgt SELECT BLOCK_SIZE, 2 ROUND( (1 -
AVG((PHYSICAL_READS/ 3
(DB_BLOCK_GETS CONSISTENT_GETS))))100) ''
ratio 4 FROM VBUFFER_POOL_STATISTICS 5
GROUP BY BLOCK_SIZE 6 /
26
Inside the Buffer Cache

• Use the VBH 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

27
Inside the Buffer Cache (continued)
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