发表于: 2006.04.25 18:37
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/73832
---------------------------------------------------------------
最近有个问题需要使用CACHE提示，于是简单研究了一下。偶然间发现自己前一阵写过一篇关于CACHE和NOCACHE提示的文章（自己都不记得了，汗），不过当时的结论不大准确，打算续一篇，将这次的测试结果补上。
这篇文章讨论NOCACHE提示。
其他相关文章：
CACHE和NOCACHE提示的进一步研究（一）：http://yangtingkun.itpub.net/post/468/74472
以前的那篇文章：http://yangtingkun.itpub.net/post/468/30642
创建一个大表，用于清除缓冲区中的数据。
SQL> CREATE TABLE TEST AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> INSERT INTO TEST SELECT * FROM TEST;
167011 rows created.
SQL> INSERT INTO TEST SELECT * FROM TEST;
334022 rows created.
SQL> INSERT INTO TEST SELECT * FROM TEST;
668044 rows created.
SQL> INSERT INTO TEST SELECT * FROM TEST;
1336088 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT SUM(BYTES)/1024/1024/1024 GB FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'TEST';
GB
----------
1.1875
SQL> SHOW PARAMETER DB_CACHE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
db_cache_size big integer 956301312
下面建立测试使用的表，表的大小应该比DB_CACHE_SIZE小。
SQL> CREATE TABLE TEST1 AS SELECT * FROM TEST WHERE ROWNUM < 1000000;
Table created.
SQL> SELECT SUM(BYTES)/1024/1024 MB FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'TEST1';
MB
----------
456
建立两个测试使用的索引，为了在查询的时候可以使用索引，将索引列置为NOT NULL。
SQL> CREATE INDEX IND_TEST_NAME ON TEST(NAME);
Index created.
SQL> CREATE INDEX IND_TEST1_NAME ON TEST1(NAME);
Index created.
SQL> ALTER TABLE TEST MODIFY NAME NOT NULL;
Table altered.
SQL> ALTER TABLE TEST1 MODIFY NAME NOT NULL;
Table altered.
下面开始NOCACHE提示的测试：
SQL> SET AUTOT ON
SQL> SELECT /*+ NOCACHE(TEST1) INDEX(TEST1) */ COUNT(TEXT) FROM TEST1;
COUNT(TEXT)
-----------
998500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=1 Bytes=2002)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=826 Card=4737366 Bytes=9484206732)
3 2 INDEX (FULL SCAN) OF 'IND_TEST1_NAME' (NON-UNIQUE) (Cost=26 Card=4737366)
Statistics
----------------------------------------------------------
142 recursive calls
0 db block gets
41676 consistent gets
41636 physical reads
0 redo size
383 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT /*+ NOCACHE(TEST1) INDEX(TEST1) */ COUNT(TEXT) FROM TEST1;
COUNT(TEXT)
-----------
998500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=1 Bytes=2002)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=826 Card=4737366 Bytes=9484206732)
3 2 INDEX (FULL SCAN) OF 'IND_TEST1_NAME' (NON-UNIQUE) (Cost=26 Card=4737366)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
41659 consistent gets
41635 physical reads
0 redo size
383 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT /*+ NOCACHE(TEST1) INDEX(TEST1) */ COUNT(TEXT) FROM TEST1;
COUNT(TEXT)
-----------
998500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=1 Bytes=2002)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=826 Card=4737366 Bytes=9484206732)
3 2 INDEX (FULL SCAN) OF 'IND_TEST1_NAME' (NON-UNIQUE) (Cost=26 Card=4737366)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
41659 consistent gets
41635 physical reads
0 redo size
383 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，使用了NOCACHE提示，索引扫描的块并没有被缓存，下面看看如果不加NOCACHE提示的效果：
SQL> SELECT /*+ INDEX(TEST1) */ COUNT(TEXT) FROM TEST1;
COUNT(TEXT)
-----------
998500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=1 Bytes=2002)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=826 Card=4737366 Bytes=9484206732)
3 2 INDEX (FULL SCAN) OF 'IND_TEST1_NAME' (NON-UNIQUE) (Cost=26 Card=4737366)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
41659 consistent gets
30561 physical reads
0 redo size
383 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT /*+ INDEX(TEST1) */ COUNT(TEXT) FROM TEST1;
COUNT(TEXT)
-----------
998500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=1 Bytes=2002)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=826 Card=4737366 Bytes=9484206732)
3 2 INDEX (FULL SCAN) OF 'IND_TEST1_NAME' (NON-UNIQUE) (Cost=26 Card=4737366)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
41659 consistent gets
0 physical reads
0 redo size
383 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
第二次执行的时候，发现所有的数据块都以及被缓存了。这说明NOCACHE提示是有作用的，而在我以前测试中犯了一个错误，因此得到了一个错误的结论。下面我们就来重现这个错误：
SQL> SELECT /*+ NOCACHE(TEST1) INDEX(TEST1) */ COUNT(TEXT) FROM TEST1;
COUNT(TEXT)
-----------
998500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=1 Bytes=2002)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=826 Card=4737366 Bytes=9484206732)
3 2 INDEX (FULL SCAN) OF 'IND_TEST1_NAME' (NON-UNIQUE) (Cost=26 Card=4737366)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
41659 consistent gets
0 physical reads
0 redo size
383 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT /*+ NOCACHE(TEST1) INDEX(TEST1) */ COUNT(TEXT) FROM TEST1;
COUNT(TEXT)
-----------
998500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=1 Bytes=2002)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=826 Card=4737366 Bytes=9484206732)
3 2 INDEX (FULL SCAN) OF 'IND_TEST1_NAME' (NON-UNIQUE) (Cost=26 Card=4737366)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
41659 consistent gets
0 physical reads
0 redo size
383 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从当前的结果看，似乎这回NOCACHE提示没有起作用。但是提示操作只会针对当前的这次操作，它无法影响到数据库中以及缓存的数据。这两次执行的操作没有被缓存，可是这两次执行读取的数据块已经缓存是在内存中了。所以，NOCACHE提示就没有任何意义了，因为NOCACHE只能控制语句读取的数据块不送到内存中缓存，而没有办法将已经缓存的数据块清除出内存。这就是我前面那篇文章中测试时犯的错误。
另外，通过观察执行计划的改变，也可以看到NOCACHE实际上是在起作用的：
SQL> SELECT COUNT(*) FROM TEST1;
COUNT(*)
----------
999999
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST1'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28972 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT /*+ NOCACHE(TEST1) */ COUNT(*) FROM TEST1;
COUNT(*)
----------
999999
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_TEST1_NAME' (NON-UNIQUE) (Cost=4 Card=4737366)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1620 consistent gets
2 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由于TEST1表没有进行分析，因此直接查询的时候使用了RBO，但是由于使用了提示，优化方式变成了CBO，而且自动选择了代价较小的索引快速扫描来替换了全表扫描。
