#眉標=Oracle、DBWRn、Buffer #副標=管理Oracle資料庫必要的功力(2) #大標=深入解析Oracle資料庫運作原理 #作者=文/圖 張天慧 ===<反灰>============= SQL> alter system set statistics_level=typical ; SQL> alter system set sga_target=1024M; ================ ===<反灰>============= DB_KEEP_CACHE_SIZE = integer[K | M | G] DB_RECYCLE_CACHE_SIZE = integer[K | M | G] 設定完以上的參數後如要將表格(Table)設定使用這兩種分區的語法範例如下: SQL> create table test (column number) storage (buffer_pool keep); SQL> alter table test storage (buffer_pool recycle); ================ ===<反灰>============= SELECT 1 - (phy.value / (cur.value + con.value)) "CACHE HIT RATIO" FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads'; ================ =====TIP =========== ●CACHE HIT RATIO = 1 -(physical reads /(db block gets + consistent gets))。 ●HIT RATIO 最好要大於90%。 ================ ===<反灰>============= SQL> SELECT name,1 - (physical_reads / (db_block_gets + consistent_gets)) "HIT_RATIO" FROM sys.v$buffer_pool_statistics WHERE db_block_gets + consistent_gets > 0; ================ ===<反灰>============= SQL> SELECT event, total_waits FROM v$system_event WHERE event in (’free buffer waits’, ’buffer busy waits’); ================