CACHE的统计信息与优化

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