Object
1.Employ the buffer cache sizing advisor
2.Describle how the buffer cache is used by different Oracle processes
3.Create and manage different buffer caches
4.Monitor the use of the buffer caches
5.Identify and resolve buffer cache performance problems
Oracle Architecture
Buffer Cache Characteristics
Dynamic Advisory Parameter
Views
SQL> select name, size_for_estimate,estd_physical_read_factor,estd_physical_reads from v$db_cache_advice order by name, size_for_estimate;
Managing Buffer Cache
Tuning Goals and Techniques
Diagnostic Tools
Performance Indicators
SQL> select name,value from V$sysstat where name like '%free buffer in%';
NAME VALUE
---------------------------------------- ----------
free buffer inspected 0
SQL> select event, total_waits FROM v$system_event where event in('free buffer waits','buffer busy waits');
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
buffer busy waits 9
SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name='buffer busy waits';
NAME PARAMETER1 PARAMETER2 PARAMETER3
---------------------------------------- ---------- ---------- ----------
buffer busy waits file# block# class#
Buffer Busy Waits Cause
Free Buffer Waits Caus
Measuring Cache Hit Ratio
Guidelines of Hit Ratio
1.Full table scans
2.Data or application design
3.Large table with random access
4.Uneven distribution of cace hist
Hit Ratio Isn't Everything
1.A badly tuned database can still have a hit ratio of 99% or better
2.Hit ratio is only one part in determining tuning performance
3.Hit ratio does not determine whether a database is aptimally tuned
4.Use the oracle wait interface to examine what is causing a bottleneck
a.v$session_wait
b.v$session_event
c.v$system_event
5.Tune SQL statements
Increase the Cache Size
1.Increase thecache size ratio under the following conditions:
a.Any wait events have been tuned
b.SQL statements have been tuned
c.There is no undue page faulting
d.The previous increase of the buffer cache was effective
e.Low cache hit ratio
Enabling MBP
Calculating Hit Ratio
Identifying Pool Segments
1.Keep pool
a.Blocks are accessed repeatedly
b.Segment size is less than 10% of the default buffer pool size
2.Recycle pool
a.Blocks are not resued outside of transaction
b.Segment size is more than twice the default buffer pool size
Dictionary Views
select id, name, block_size, buffers from v$buffer_pool;
Caching Tables:
1.Enable caching during full table sacns by:
a.Creating the tble with the CACHE clause
b.Altering the table with the CACHE clause
c.Using the CACHE hint in a query
2.Guideline : Do not overcrowd the buffer cache
3.Use a keep pool
原因: 进行全表扫描的时候 一般都会放在LRU list的末端 被淘汰出去,但是我们希望能在buffer里多留一段时间,因为这些表都是小表,而且常用,所以就有 Caching Talbes的由来 和 pin不一样, Cache table只是保证被淘汰出去的概率少点
ASSM
1.Manages free space automatically inside database setments
2.Tracks segment free/used space with bitmaps instead of free lists
3.Provides better space utilization, especiall for objects with highly varying size rows
4.Specified when creating a tablespace
5.Supported by Oracle Enterprise Manager
Free Lists (手动管理方式 已过时)
Multiple DBWn Processes
1.Multiple DB Writer(DBWn)processes:
a.Can be deployed with DB_WRITER_PROCESSES(DBW0 to DBW9)
b.Can be useful for SMP systems with large numbers of CPUS
c.Can not concurrently be used with multiple I/O slaves
2.The DBA can turn asynchronous I/O on or off wit the DISK_ASYNCH_IO parameter
Resize Instance
show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 272M
Unit of Allocation
1.In the dynamic SGA model, the unit of memory allocation is called a granule.
2.SGA memory is tracked in granules by SGA components
3.A granule is a unit of contiguous virtual memory allocation
4.Use v$buffer_pool to monitor size of the buffer caches
SQL> select name,block_size,resize_state,current_size,buffers from v$buffer_pool;
NAME BLOCK_SIZE RESIZE_STA CURRENT_SIZE BUFFERS
-------------------- ---------- ---------- ------------ ----------
DEFAULT 8192 STATIC 176 21956