Tuning the buffer cache

1.

select name,size_for_estimate,estd_physical_read_factor,estd_physical_reads from

v$db_cache_advice order by name,size_for_estimate.

 

NAME     SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
-------- ----------------- ------------------------- -------------------
DEFAULT                 16                    3.5831               47222
DEFAULT                 32                    2.1929               28900
DEFAULT                 48                    1.3138               17314
DEFAULT                 64                    1.1022               14526
DEFAULT                 80                    1.0196               13437
DEFAULT                 96                    1.0098               13308
DEFAULT                112                         1               13179
DEFAULT                128                         1               13179
DEFAULT                144                         1               13179

 

则buffered_cache_size 应为112M

 

2.

 free buffer inspected ----v$sysstat

 free buffer waits,buffer busy waits ---v$system_event

 

 

select event,total_waits from v$system_event where event in('free buffer waits',buffer busy waits');

select name,parameter1,parameter2,parameter3 from v$event_name where name = 'buffer busy waits';

 

3.Cache in Rat

 select 1-(phy.value -lob.value -dir.value)/ses.value "CACHE HIT RATIO"

from v$sysstat ses,v$sysstat lob, v$sysstat dir ,v$sysstat phy

where ses.name = 'session logical reads'

and dir.name = 'physical reads direct'

and lob.name = 'physical reads direct (lob)'

and phy.name = 'physical reads';

 

4.

 v$session_wait

 v$session_event

 v$system_event

 

 5.

 DB_CACHE_POOL  ----default pool

 DB_KEEP_CACHE_POOL --- keep pool

DB_RECYCLE_CACHE_POOL --recycle pool

 

6.

 1. create index cust_idx ..

storage(buffer_pool keep...)

 2.alter table customer

 storage(buffer_pool recycle ...)

3.alter index cust_name_idx

 storage(buffer_pool keep)

 

7.查询内存的表所占的块数

x$BH

v$BH

select o.object_name,count(*) "Num of Blks" from dba_objects,v$bh bh

where o.data_object_id = bh.objd

and o.ower != 'SYS'

group by o.object_name order by count(*);

 

 8.查询表所占的块数

  SQL>execute dbms_stats.gather_table_stats('HR','DEPARTMENTS');

  SQL>select table_name,blocks from dba_tables where owner = 'HR'

           and table_name = 'DEPARTMENTS';

 

9.查看各池子大小

select id,name,block_size,buffers from v$buffer_pool;

 

10.cache到内存

alter table employee cache;

select /*+cache */last_name,first_name from employee;

 

11.配置多个DBWn进程(根据free buffer waits)

 alter system set db_writer_processes = 10;

 

12.开通异步IO

 alter system set disk_asynch_io = true;