三. Tuning Oracle's Buffer Cache
From: http://www.dbspecialists.com/files/presentations/buffercache.html
Oracle maintains its own buffer cache inside the system global area (SGA) for each instance. A properly sized buffer cache can usually yield a cache hit ratio over 90%, meaning that nine requests out of ten are satisfied without going to disk.
If a buffer cache is too small, the cache hit ratio will be small and more physical disk I/O will result. If a buffer cache is too big, then parts of the buffer cache will be under-utilized and memory resources will be wasted.
3.1 Checking The Cache Hit Ratio
Oracle maintains statistics of buffer cache hits and misses. The following query will show you the overall buffer cache hit ratio for the entire instance since it was started:
1
2
3
4
5
6
|
/* Formatted on 2011/6/28 19:18:29 (QP5 v5.163.1008.3004) */
SELECT (P1.VALUE + P2.VALUE - P3.VALUE) / (P1.VALUE + P2.VALUE)
FROM v$sysstat P1, v$sysstat P2, v$sysstat P3
WHERE P1.name =
'db block gets'
AND P2.name =
'consistent gets'
AND P3.name =
'physical reads'
|
You can also see the buffer cache hit ratio for one specific session since that session started:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
/* Formatted on 2011/6/28 19:19:53 (QP5 v5.163.1008.3004) */
SELECT (P1.VALUE + P2.VALUE - P3.VALUE) / (P1.VALUE + P2.VALUE)
FROM v$sesstat P1,
v$statname N1,
v$sesstat P2,
v$statname N2,
v$sesstat P3,
v$statname N3
WHERE N1.name =
'db block gets'
AND P1.statistic# = N1.statistic#
AND P1.sid = <enter SID of session here>
AND N2.name =
'consistent gets'
AND P2.statistic# = N2.statistic#
AND P2.sid = P1.sid
AND N3.name =
'physical reads'
AND P3.statistic# = N3.statistic#
AND P3.sid = P1.sid
|
You can also measure the buffer cache hit ratio between time X and time Y by collecting statistics at times X and Y and computing the deltas.
3.2 Adjusting The Size Of The Buffer Cache
The db_block_buffers parameter in the parameter file determines the size of the buffer cache for the instance. The size of the buffer cache(in bytes) is equal to the value of the db_block_buffers parameter multiplied by the data block size.
You can change the size of the buffer cache by editing the db_block_buffers parameter in the parameter file and restarting the instance.
3.3 Determining If The Buffer Cache Should Be Enlarged
If you set the db_block_lru_extended_statistics parameter to a positive number in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$recent_bucket. This view will contain the same number of rows as the setting of the db_block_lru_extended_statistics parameter. Each row will indicate how many additional buffer cache hits there might have been if the buffer cache were that much bigger.
For example, if you set db_block_lru_extended_statistics to 1000 and restart the instance, you can see how the buffer cache hit ratio would have improved if the buffer cache were one buffer bigger, two buffers bigger, and so on up to 1000 buffers bigger than its current size. Following is a query you can use, along with a sample result:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
/* Formatted on 2011/6/28 19:23:11 (QP5 v5.163.1008.3004) */
SELECT
250
* TRUNC (ROWNUM /
250
)
+
1
||
' to '
||
250
* (TRUNC (ROWNUM /
250
) +
1
)
"Interval"
,
SUM (COUNT)
"Buffer Cache Hits"
FROM v$recent_bucket
GROUP BY TRUNC (ROWNUM /
250
)
Interval Buffer Cache Hits
--------------- --------------------
1
to
250
16083
251
to
500
11422
501
to
750
683
751
to
1000
177
|
This result set shows that enlarging the buffer cache by 250 buffers would have resulted in 16,083 more hits. If there were about 30,000 hits in the buffer cache at the time this query was performed, then it would appear that adding 500 buffers to the buffer cache might be worthwhile. Adding more than 500 buffers might lead to under-utilized buffers and therefore wasted memory.
There is overhead involved in collecting extended LRU statistics. Therefore you should set the db_block_lru_extended_ statistics parameter back to zero as soon as your analysis is complete.
In Oracle7, the v$recent_bucket view was named X$KCBRBH. Only the SYS user can query X$KCBRBH. Also note that in X$KCBRBH the columns are called indx and count, instead of rownum and count.
3.4 Determining If The Buffer Cache Is Bigger Than Necessary
If you set the db_block_lru_statistics parameter to true in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$current_bucket. This view will contain one row for each buffer in the buffer cache, and each row will indicate how many of the overall cache hits have been attributable to that particular buffer.
By querying v$current_bucket with a GROUP BY clause, you can get an idea of how well the buffer cache would perform if it were smaller. Following is a query you can use, along with a sample result:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SELECT
1000
* TRUNC (rownum /
1000
) +
1
||
' to '
||
1000
* (TRUNC (rownum /
1000
) +
1
)
"Interval"
,
SUM (count)
"Buffer Cache Hits"
FROM v$current_bucket
WHERE rownum >
0
GROUP BY TRUNC (rownum /
1000
)
Interval Buffer Cache Hits
------------ -----------------
1
to
1000
668415
1001
to
2000
281760
2001
to
3000
166940
3001
to
4000
14770
4001
to
5000
7030
5001
to
6000
959
|
This result set shows that the first 3000 buffers are responsible for over 98% of the hits in the buffer cache. This suggests that the buffer cache would be almost as effective if it were half the size; memory is being wasted on an oversized buffer cache.
There is overhead involved in collecting LRU statistics. Therefore you should set the db_block_lru_statistics parameter back to false as soon as your analysis is complete.
In Oracle7, the v$current_bucket view was named X$KCBCBH. Only the SYS user can query X$KCBCBH. Also note that in X$KCBCBH the columns are called indx and count, instead of rownum and count.
3.5 Full Table Scans
When Oracle performs a full table scan of a large table, the blocks are read into the buffer cache but placed at the least recently used end of the LRU list. This causes the blocks to be aged out quickly, and prevents one large full table scan from wiping out the entire buffer cache.
Full table scans of large tables usually result in physical disk reads and a lower buffer cache hit ratio. You can get an idea of full table scan activity at the data file level by querying v$filestat and joining to SYS.dba_data_files. Following is a query you can use and sample results:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
/* Formatted on 2011/6/28 19:27:26 (QP5 v5.163.1008.3004) */
SELECT A.file_name, B.phyrds, B.phyblkrd
FROM SYS.dba_data_files A, v$filestat B
WHERE B.file# = A.file_id
ORDER BY A.file_id
FILE_NAME PHYRDS PHYBLKRD
-------------------------------- ---------- ----------
/u01/oradata/PROD/system01.dbf
92832
130721
/u02/oradata/PROD/temp01.dbf
1136
7825
/u01/oradata/PROD/tools01.dbf
7994
8002
/u01/oradata/PROD/users01.dbf
214
214
/u03/oradata/PROD/rbs01.dbf
20518
20518
/u04/oradata/PROD/data01.dbf
593336
9441037
/u05/oradata/PROD/data02.dbf
4638037
4703454
/u06/oradata/PROD/index01.dbf
1007638
1007638
/u07/oradata/PROD/index02.dbf
1408270
1408270
|
PHYRDS shows the number of reads from the data file since the instance was started.
PHYBLKRD shows the actual number of data blocks read. Usually blocks are requested one at a time. However, Oracle requests blocks in batches when performing full table scans. (The db_file_multiblock_read_count parameter controls this batch size.)
In the sample result set above, there appears to be quite a bit of full table scan activity in the data01.dbf data file, since 593,336 read requests have resulted in 9,441,037 actual blocks read.
3.6 Spotting I/O Intensive SQL Statements
The v$sqlarea dynamic performance view contains one row for each SQL statement currently in the shared SQL area of the SGA for the instance. v$sqlarea shows the first 1000 bytes of each SQL statement, along with various statistics. Following is a query you can use:
1
2
3
4
5
6
7
8
|
/* Formatted on 2011/6/28 19:31:34 (QP5 v5.163.1008.3004) */
SELECT executions,
buffer_gets,
disk_reads,
first_load_time,
sql_text
FROM v$sqlarea
ORDER BY disk_reads
|
EXECUTIONS indicates the number of times the SQL statement has been executed since it entered the shared SQL area.
BUFFER_GETS indicates the collective number of logical reads issued by all executions of the statement.
DISK_READS shows the collective number of physical reads issued by all executions of the statement. (A logical read is a read that resulted in a cache hit or a physical disk read. A physical read is a read that resulted in a physical disk read.)
You can review the results of this query to find SQL statements that perform lots of reads, both logical and physical. Consider how many times a SQL statement has been executed when evaluating the number of reads.