CACHE的统计信息与优化

时间：2010-07-02 来源：TOMSYAN

ORACLE提供了2个隐含参数_cache_stats_monitor和_optimizer_cache_stats用来控制CACHE的统计信息。
_cache_stats_monitor 用来控制是否收集缓冲区的统计信息，默认值为TRUE。
_optimizer_cache_stats用来控制CBO在确定成本的时候是否使用缓冲区的统计信息，默认值为FALSE。这2个参数都可以在会话级别设置。看如下一个例子：
SQL> CONN TEST/TEST
Connected.
SQL> CREATE TABLE TEST AS SELECT * FROM ALL_OBJECTS; Table created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST'); PL/SQL procedure successfully completed. SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; System altered. SQL> SELECT COUNT(1) FROM TEST;
Execution Plan
----------------------------------------------------------
Plan hash value: 3467505462 -------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   195   (1)| 00:00:03 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 50578 |   195   (1)| 00:00:03 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        699 consistent gets
        697 physical reads
          0 redo size
        517 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
此时的COST值为195，基本全是物理读。 SQL> SELECT COUNT(1) FROM TEST;
Execution Plan
----------------------------------------------------------
Plan hash value: 3467505462 -------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   195   (1)| 00:00:03 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 50578 |   195   (1)| 00:00:03 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        699 consistent gets
          0 physical reads
          0 redo size
        517 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed 此时全部是内存读取，但是COST值还是195。看一下设置_OPTIMIZER_CACHE_STATS=TRUE的情况：
SQL> ALTER SESSION SET "_OPTIMIZER_CACHE_STATS"=TRUE; Session altered.
SQL> SELECT COUNT(1) FROM TEST;
Execution Plan
----------------------------------------------------------
Plan hash value: 3467505462 -------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   194   (1)| 00:00:03 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 50578 |   194   (1)| 00:00:03 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        699 consistent gets
          0 physical reads
          0 redo size
        517 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed 可以看到，此时也是全部内存读，但是COST值却降到了194，由于表比较小，COST值降的并不明显。 SQL> ALTER SESSION SET "_OPTIMIZER_CACHE_STATS"=FALSE; Session altered. SQL> SELECT COUNT(1) FROM TEST;
Execution Plan
----------------------------------------------------------
Plan hash value: 3467505462 -------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   195   (1)| 00:00:03 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 50578 |   195   (1)| 00:00:03 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        699 consistent gets
          0 physical reads
          0 redo size
        517 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed