Oracle Buffer Cache工作原理(2)

简介:

. 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.

 










本文转自 客居天涯 51CTO博客,原文链接:http://blog.51cto.com/tiany/1430404,如需转载请自行联系原作者
目录
相关文章
|
5月前
|
SQL 存储 Oracle
Oracle数据库中游标的工作原理与优化方法
Oracle数据库中游标的工作原理与优化方法
|
存储 运维 Oracle
Oracle数据库机:让数据价值回归用户“第一性原理”
Oracle数据库机:让数据价值回归用户“第一性原理”
|
SQL 存储 Oracle
MySQL和Oracle的区别是什么?底层原理是什么?
MySQL和Oracle的区别是什么?底层原理是什么?
1392 0
|
SQL Oracle 关系型数据库
|
存储 缓存 分布式计算
|
运维 监控 Oracle
3月19日直播【从12c到20c,Oracle多租户之10046跟踪PDB关库案例原理解析】
本次分享将针对DevOps代码质量审核、监控、开发环境测试、生产环境测试这四个方面存在的问题进行剖析并给出解决之道,希望能助您全面转型DevOps。
702 0
|
SQL 缓存 Oracle
Oracle On the PL/SQL Function Result Cache
标签 PostgreSQL , Oracle , 函数结果缓存 , 函数三态 , immutable , stable , volatile 背景 Oracle 11g 支持的一个新特性,在创建PL/SQL函数时,可以指定这个存储过程是否需要对结果进行缓存,缓存内容在SGA内存区域。
966 0