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;