Tuning des Database Buffer Caches

Database Buffer Cache

Wichtige Parameter sind ON THE FLY änderbar.

alter system set DB_CACHE_SIZE = 64M;
alter system set DB_KEEP_CACHE_SIZE = 100K;
alter system set SHARED_POOL_SIZE = 48M;

Weitere Parameter erfordern Neustart.

alter system set SGA_MAX_SIZE=150M scope=spfile;

Informatione über Größe stehen in V$BUFFER_POOL.

Buffer Cache:
<128MB: 4MB Granular
>128MB: 16MB Granular

Verwaltung durch zwei Listen

• Write-List
• LRU-List

Latches

Latches sind Sperren auf niederer Ebene.

• Werden für den Zugriff auf die Pools vergeben (bis zu insgesamt DB_BLOCK_LRU_LATCHES).
• Ein Latch schützt mindestens 50 Blöcke.
• Sollten ab 9i nicht mehr per Hand verändert werden.

Aufbau der SGA

System Global Area

• Buffer Cache
  • Keep
  • Recycled
  • Default
• Shared Pool
  • Library Cache
• Java Pool
• Large Pool
• Redo-Log-Buffer

Die Summe kann durch folgende Formel ermittelt werden:

DB_CACHE_SIZE 
+ DB_KEEP_CACHE_SIZE
+ DB_RECYCLE_CACHE_SIZE
+ DB_nk_CACHE_SIZE
+ SHARED_POOL_SIZE
+ LARGE_POOL_SIZE
+ JAVA_POOL_SIZE
+ LOG_BUFFERS
+ 1MB

select 1, name, to_number(value/1024/1024) value 
  from v$parameter 
    where upper(name) 
      like 'DB%CACHE_SIZE' or upper(name) in
        ('SHARED_POOL_SIZE','LARGE_POOL_SIZE','JAVA_POOL_SIZE','LOG_BUFFER') 
      union 
        select 1, '+ 1MB', 1 from dual order by 2;

Beispiel

NAME 			   VALUE
----------------------------
+ 1MB                     1
db_cache_size            32
db_keep_cache_size        0
db_recycle_cache_size     0
db_16k_cache_size         0
db_2k_cache_size          0
db_32k_cache_size         0
db_4k_cache_size          0
db_8k_cache_size          0
java_pool_size           32
large_pool_size           0
log_buffer                ,5
shared_pool_size         88

Tuning des Database Buffer Caches

Messen von Cache Hit Ratio je Instanz

Physical Reads, DB Block Gets und Consistent Gets stehen in V$SYSSTAT seit Instanzstart.
Cache Miss Ratio = physical reads/(db block gets + consistent gets)
Cache Hit Ratio = 1-physical reads/(db block gets + consistent gets)
Formel:

select 1-value/
      (
        select sum(value) 
          from v$sysstat 
          where lower(name) in ('consistent gets','db block gets')
       ) 
  from v$sysstat 
  where lower(name) in ('physical reads')
;

Physical Reads sind von Disk gelesene Datenblöcke.
DB Block Gets sind im Cache gefundene Datenblöcke.
Consistent Gets sind im Cache gefundene Datenblöcke für die Lesekonsistenz.

Empfehlung:
Sollte über 0,9 liegen (hängt aber von den Umständen ab), z.B. großer Buffer-Cache - mehrmals select * from scott.emp - Hit-Ratio steigt.

Messen von Cache Hit Ratio je Pool

select name,
    100-round((physical_reads/(db_block_gets+consistent_gets))*100,2) 
    as Hit_Ratio 
  from v$buffer_pool_statistics
;

Achtung: Alle Buffer (keep, recycle und default), die definiert wurden, müssen auch ungleich 0 sein, ansonsten ergibt es eine Division by Zero. Laden einer Tabelle in den entsprechenden Pool (alter table scott.dept storage buffer_pool KEEP) und Abfragen dieser Tabelle (select * from scott.dept).

Messen von Cache Hit Ratio je Session

select username,
       osuser,
       1-(io.physical_reads/(io.block_gets+io.consistent_gets)) "Hit Ratio" 
  from v$sess_io io, v$session sess 
  where io.sid = sess.sid 
    and (io.block_gets + io.consistent_gets)!=0 
    and username is not null
;

Beispiel Die Datenbankperformance ist gut, nur ein Benutzer klagt.
Empfehlung: Sollte bei jedem Benutzer über 0,9 liegen

Lösungsansätze

Nutzen von verschiedenen Pool-Typen

• Keep Pool
  Informationen, die im Database Buffer Cache lange bleiben sollten.
• Recycle Pool
  Informationen, die im Database Buffer Cache nicht lange bleiben sollten.
• Default Pool
  Informationen, die zu keinem der anderen beiden passen.

alter table tablename storage buffer_pool keep | recycle;

Definition der Pool-Typen

Parameter der entsprechenden INIT-Datei:

DB_BLOCK_BUFFERS =             1000
BUFFER_POOL_KEEP =              400
BUFFER_POOL_RECYCLE =           100
DEFAULT POOL = 1000-(400+100) = 500

Speichern einer Tabelle in einem speziellen Pool:

ALTER TABLE test STORAGE (buffer_pool KEEP);

Cache-Advice

Gibt Empfehlungen für den Buffer-Cache.

Anschalten

alter system set db_cache_advice=on;

Eine Weile laufen lassen

Abfragen

select distinct size_for_estimate,estd_physical_reads 
  from v$db_cache_advice;

Ausschalten

alter system set db_cache_advice=off;

Cache-Option und Hint

Cache

Blöcke werden an der MRU-Seite gecacht. Dies ist sinnvoll bei häufigen Full-Table-Scan der gleichen Tabelle.

Cache-Hint

SELECT /*+ CACHE(scott.emp) */ empno, ename FROM scott.emp;

CREATE TABLE CACHE

create table off_car(carnr int,carname varchar2(20)) CACHE;

Guidelines

Gute Kandidaten für KEEP-Pool:
Kleine, häufig frequentierte Objekte auf die mit Full Table Scan FTS zugegriffen wird.

Gute Kandidaten für RECYCLE-Pool:
Große, selten frequentierte Objekte.

Gute Kandidaten für DEFAULT-Pool:
Alle anderen Objekte.

Übung Datenbank-Tuning

Übungen siehe Seite .