1z0-024考点总结

ack header中的事务slot Buffer Busy Wait 涉及data block,segment header,undo header争用

Dictionary cache 涉及数据字典的get/miss

I/O 涉及数据文件的读写

Period of measurement UTLBSTAT,UTLESTAT开始和结束的时间

5. Tuning the Shared Pool 7

(1) 关于Shared Pool

特性: SHARED_POOL_SIZE决定大小, library cache + data dictionary cache + UGA + large pool

调整Shared Pool的原因: shared pool的miss比database buffer cache的miss影响大,library cache首要

(2) 关于Large objects

特性: use LRU

tuning: generic code/bind variable/防止空间不足age out而reload/防止object更改而re-parse

大的匿名块->小的过程/pin/reserve space for large objects

keep: 方法: DBMS_SHARED_POOL.KEEP / .UNKEEP

需要keep的object: 常用的包/常用的trigger/sequence

使用: Instance启动时keep防止碎片/ALTER SYSTEM FLUSH SHARED_POOL来flush shared pool(no keep)

视图: V$LIBRARYCACHE(GETHITRATIO>90%,否则优化语句应用; reloads/pins应<=1%)

V$SGASTAT/V$SQLAREA/V$SQLTEXT/V$DB_OBJECT_CACHE(过程等占用内存)

参数: OPEN_CURSORS/SESSION_CACHED_CURSORS

CURSOR_SPACE_FOR_TIME -- 除非RELOADS in V$LIBRARYCACHE一直为0,否则保持缺省值:false

预留空间: V$SHARED_POOL_RESERVED

当REQUEST_FAILURES大于0并且不断增长(ORA-4031),可以相应增大SHARED_POOL_RESERVED_SIZE & SHARED_POOL_SIZE的值

REQUEST_MISS = 0并且不再增长或FREE_MEMORY>=50%*SHARED_POOL_RESERVED_SIZE,考虑减少reserved size

SHARED_POOL_RESERVED_SIZE初始为SHARED_POOL_SIZE的10%

(3) 关于数据字典cache

特性: 启动时任何sql语句都将导致cache miss, GETMISSES几乎不可能为0

调整: 调整SHARED_POOL_SIZE的大小而间接地调整dictionary cache

report.txt中: GET_MISS/GET_REQS < 15%

字典: V$ROWCACHE -- SUM(GETMISSES)/SUM(GETS) < 15%,

上一页  [1] [2] [3] [4] [5] 下一页