Server process接受了客户的查询请求,先从内存找答案
,如果没有,则从数据文件中调内容到内存中。
Server process工作原理
1.首先,用HASH函数看一下查询的块是否在buffer cache
中。如果找到,这个块就会被调到LRU列表的MRU端。这个
叫逻辑读,因为没有I/O。余下的步骤也不会被执行了。
2.如果没有找到,则要物理读了。在物理读之前,server
process会找LRU链表中的free buffer。还顺带做的是,在
查找LRU过程中找到脏块,放到checkpoint queue队列中,
目的是让脏块也尽快移到磁盘中。
3.如果checkpoint queue到达了它的门限值,即exceeds
its size threshold,就叫醒DBWn去写磁盘了。
4.在找到一个free buffer后,就进行物理读了,即从数据
文件读到buffer cache。
DBWn触发:
1.checkpoint queue长度达到上限值。
2.查找LRU链表时发现没有空闲块。
3.Alter Tablespace Offline or Alter tablespace
Begin Backup。
4.Drop a Segment.
5.clean shutdown。
DBWn触发详细表格:
Event How DBW0 Writes
When Dirty List reaches its DBW0 writes dirty
threshold length. buffers from Dirty
List.
When LRU list is searched too DBW0 writes dirty
long without finding a free buffers directory
from
buffer. LRU list.
When three seconds pass DBW0 moves dirty
buffers
from the LRU List to the
Dirty List.If the
threshold
length is
exceeded,a write to
disk also occurs.
At Checkpoint DBW0 moves all
dirty buffers
from the LRU List
to the Dirty
buffers to disk at
database shutdown.
At Database Shutdown Unless a SHUTDOWN
ABORT is used,DBW0
always writes all
dirty buffers to
disk at database
shutdown.
At Tablespace Hot Backup DBW0 moves the
dirty buffers for
that tablespace
from the LRU list
to the Dirty List
and then writes
them to disk.
At Tablespace Offline 如上
Temporary
At Drop Segment Dropping a table or
index causes DBW0
to first write the
dirty blocks for that
segment to disk.
每隔3秒的DBWn写,原来只是写到checkpoint queque,所
有DBWn触发,也得分几种情况。
Tuning Goals and Techniques.
goals:- Server find data in memory.
- No waits on the buffer cache.
Diagnostic measures:
- Wait events
- Cache hit ratio
- The v$db_cache_advice view
Tuning techniques:
- Reduce the number of blocks required by
SQL statements.不要动不动就无条件select *,这样返回
太多记录。
- Increase buffer cache size.
- Use multiple buffer pools.
- Cache tables.
- Bypass the cache for sorting and parallel
reads.
下面三个指标与buffer cache性能息息相关。
Free Buffer Inspected:server process找了多少个
buffer才能找到free buffer。
Free Buffer Waits:free buffer没有了,需要等待。
Buffer Busy Waits:找到该找的块了,但是由于几个进程
并发访问,所以发生等待。
对于性能的影响,由轻至重是buffer busy waits,free
buffer inspected,free buffer waits;
select * from v$sysstat可以查看free buffer
inspected.
select event,total_waits from v$system_event
where event in ('free buffer waits','buffer busy
waits');
会显示出buffer busy waits与free buffer waits。
desc v$event_name
select name,parameter1,parameter2,parameter3 from
v$event_name where name='buffer busy waits';
物理读:是指读的数据块数。
,如果没有,则从数据文件中调内容到内存中。
Server process工作原理
1.首先,用HASH函数看一下查询的块是否在buffer cache
中。如果找到,这个块就会被调到LRU列表的MRU端。这个
叫逻辑读,因为没有I/O。余下的步骤也不会被执行了。
2.如果没有找到,则要物理读了。在物理读之前,server
process会找LRU链表中的free buffer。还顺带做的是,在
查找LRU过程中找到脏块,放到checkpoint queue队列中,
目的是让脏块也尽快移到磁盘中。
3.如果checkpoint queue到达了它的门限值,即exceeds
its size threshold,就叫醒DBWn去写磁盘了。
4.在找到一个free buffer后,就进行物理读了,即从数据
文件读到buffer cache。
DBWn触发:
1.checkpoint queue长度达到上限值。
2.查找LRU链表时发现没有空闲块。
3.Alter Tablespace Offline or Alter tablespace
Begin Backup。
4.Drop a Segment.
5.clean shutdown。
DBWn触发详细表格:
Event How DBW0 Writes
When Dirty List reaches its DBW0 writes dirty
threshold length. buffers from Dirty
List.
When LRU list is searched too DBW0 writes dirty
long without finding a free buffers directory
from
buffer. LRU list.
When three seconds pass DBW0 moves dirty
buffers
from the LRU List to the
Dirty List.If the
threshold
length is
exceeded,a write to
disk also occurs.
At Checkpoint DBW0 moves all
dirty buffers
from the LRU List
to the Dirty
buffers to disk at
database shutdown.
At Database Shutdown Unless a SHUTDOWN
ABORT is used,DBW0
always writes all
dirty buffers to
disk at database
shutdown.
At Tablespace Hot Backup DBW0 moves the
dirty buffers for
that tablespace
from the LRU list
to the Dirty List
and then writes
them to disk.
At Tablespace Offline 如上
Temporary
At Drop Segment Dropping a table or
index causes DBW0
to first write the
dirty blocks for that
segment to disk.
每隔3秒的DBWn写,原来只是写到checkpoint queque,所
有DBWn触发,也得分几种情况。
Tuning Goals and Techniques.
goals:- Server find data in memory.
- No waits on the buffer cache.
Diagnostic measures:
- Wait events
- Cache hit ratio
- The v$db_cache_advice view
Tuning techniques:
- Reduce the number of blocks required by
SQL statements.不要动不动就无条件select *,这样返回
太多记录。
- Increase buffer cache size.
- Use multiple buffer pools.
- Cache tables.
- Bypass the cache for sorting and parallel
reads.
下面三个指标与buffer cache性能息息相关。
Free Buffer Inspected:server process找了多少个
buffer才能找到free buffer。
Free Buffer Waits:free buffer没有了,需要等待。
Buffer Busy Waits:找到该找的块了,但是由于几个进程
并发访问,所以发生等待。
对于性能的影响,由轻至重是buffer busy waits,free
buffer inspected,free buffer waits;
select * from v$sysstat可以查看free buffer
inspected.
select event,total_waits from v$system_event
where event in ('free buffer waits','buffer busy
waits');
会显示出buffer busy waits与free buffer waits。
desc v$event_name
select name,parameter1,parameter2,parameter3 from
v$event_name where name='buffer busy waits';
物理读:是指读的数据块数。