Oracle performance tuning study notes (E) - buffer Cache tuning C

2011-09-16  来源:本站原创  分类:Database  人气:106 

Oracle Wait Interface inspection bottleneck causes
v $ session_wait
v $ session_event
v $ system_event
Case of need to increase the cache size
1. Any event wait.
2.SQL statement optimization
3 pages of serious operating system for
4 low hit rate

Open DB_CACHE_ADVICE to be on.
According to v $ DB_CACHE_ADVICE determine whether adjustments are needed.

If the hit rate is high, check v $ db_cache_advice see if you can reduce db_cache_size size.

If the table space in a different block sizes, you can set up multiple block size.
Need to set the following dynamic parameters can be dynamically adjusted:
Minimize random access large tables large segs;

The first method:
select o.object_name, count (*) number_of_blocks
from dba_objects o, v $ bh bh
where o.data_object_id = bh.objd
and o.owner! = 'SYS'
group by o.object_name
order by count (*);
Method Two:
1. Select data_object_id, object_type
from dba_objects
where object_name = upper ('segment_name');
2. Select count (*) buffers
from v $ bh
where objd = data_object_id_value;
3. Select name, block_size, sum (buffers)
from v $ buffer_pool
group by name, block_size
having sum (buffers)> 0;
% Cache userd by segment_name = [buffers (step2) / total buffers (step3)]

keeP buffer pool baseline guidance
execute dbms_stats.gather_table_stats ('HR', 'DEPTMENTS');
select table_name, blocks
from dba_tables
where owner = 'HR'
and table_name = 'DEPTMENTS';

RECYCLE BUFFER POOL: the case for the batch.
select owner #, name, count (*) blocks
from v $ cache
group by owner #, name;
View session io command in the case
select s.username, io.block_gets, io.consistent_gets,
from v $ sess_io io, v $ session s
where io.sid = s.sid;
All in the buffer pool hit rate
select name, 1 - (physical_reads / (db_block_gets + consistent_gets)) "HIT_RATIO"
from v $ buffer_pool_statistics
where db_block_gets + consistent_gets> 0;

keep pool: the size of repeat visits is less than the default 10%
recycle pool: things can only use. segment size is twice the default buffer pool.
Look at each block in the number of buffer pool
select id, name, block_size, buffers from v $ buffer_pool;

Cache common common table full-table scan will be on the LRU end of block, then the block is the block will be out. CBO frequently use this table, however small, to solve this situation need to use
Cache table, which is on the LRU block to the head. A small table full scan is the fastest.
Cache table:
Create table WITH Cache,
create table phone
emp_id number,
phoneNum number
) Tablespace tbs_data
storage (
initial 50k
next 50k
pctincrease 0
) Cache;
Default table created using nocache, unless specified to use cache.

SQL query WITH Cache,
select / * + CACHE * / last_name, first_name from employee;
ALter modify the table WITH Cache.
alter table employee cache;
Cache table is not recommended on the buffer cache, in general on the keep pool.
Manage free space automatically by the database management segment.
Track segment space use bitmaps instead of free lists.
Provide space utilization.
Specified when creating the table space.
OEM support.

create tablespace bit_seg_ts
size 1M
extent management local
segment space management auto;

create table bit_seg_table
(Id number)
tablespace bit_seg_ts;