[20160122]Caching Effects.txt
--看电子书<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf>
--P391也提到的一个例子很有意思。自己记录一下：
1.环境：
SCOTT@test> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
ALTER SESSION ENABLE PARALLEL DML;
CREATE TABLE t1
(
n1 INT
,n2 INT
,filler CHAR (10)
)
NOLOGGING;
INSERT /*+ parallel(t1 10) */
INTO t1
WITH generator
AS ( SELECT ROWNUM rn
FROM DUAL
CONNECT BY LEVEL <= 4500)
SELECT TRUNC (ROWNUM / 80000)
,ROWNUM + 5000 * (MOD (ROWNUM, 2))
,RPAD ('X', 10)
FROM generator, generator;
COMMIT;
CREATE INDEX t1_n1
ON t1 (n1)
NOLOGGING
PARALLEL 10;
2.测试：
SCOTT@test> SELECT index_name, clustering_factor FROM all_indexes WHERE index_name = 'T1_N1';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
T1_N1 71575
SCOTT@test> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SCOTT@test> SELECT MAX (filler) FROM t1 WHERE n1 = 2;
MAX(FILLER
----------
X
Plan hash value: 359681750
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 451 (100)| | 1 |00:00:00.49 | 425 | 425 |
| 1 | SORT AGGREGATE | | 1 | 1 | 15 | | | 1 |00:00:00.49 | 425 | 425 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 79724 | 1167K| 451 (1)| 00:00:01 | 80000 |00:00:00.41 | 425 | 425 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 1 | 79724 | | 166 (1)| 00:00:01 | 80000 |00:00:00.19 | 159 | 159 |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"=2)
--因为CLUSTERING_FACTOR很小，趋向使用索引。仅仅读了425个逻辑读。
SCOTT@test> SELECT /*+ full(t1) */ MAX (filler) FROM t1 WHERE n1 = 2;
Plan hash value: 3724264953
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 19820 (100)| | 1 |00:00:21.68 | 142K| 71949 |
| 1 | SORT AGGREGATE | | 1 | 1 | 15 | | | 1 |00:00:21.68 | 142K| 71949 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 79724 | 1167K| 19820 (2)| 00:00:01 | 80000 |00:00:21.60 | 142K| 71949 |
2 - filter("N1"=2)
--全表扫描71949。
3.继续测试:
DROP INDEX t1_n1;
CREATE INDEX t1_n1_n2 ON t1 (n1, n2) NOLOGGING PARALLEL 10;
COLUMN index_name FORMAT a10
SCOTT@test> SELECT index_name, clustering_factor FROM all_indexes WHERE index_name = 'T1_N1_N2';
T1_N1_N2 18988692
--建立N1,N2复合索引后，CLUSTERING_FACTOR很大。
| 0 | SELECT STATEMENT | | 1 | | | 19820 (100)| | 1 |00:00:20.57 | 142K| 71948 |
| 1 | SORT AGGREGATE | | 1 | 1 | 15 | | | 1 |00:00:20.57 | 142K| 71948 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 79724 | 1167K| 19820 (2)| 00:00:01 | 80000 |00:00:20.49 | 142K| 71948 |
--看上去选择了正确的执行计划，因为这个索引的CLUSTERING_FACTOR很大，物理读达到了71948。我的机器硬件不好需要20秒(刷新buffer cache后)。
SCOTT@test> SELECT /*+ index(t1 t1_n1_n2) */ MAX (filler) FROM t1 WHERE n1 = 2;
Plan hash value: 300452703
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 75019 (100)| | 1 |00:00:01.18 | 75247 | 480 |
| 1 | SORT AGGREGATE | | 1 | 1 | 15 | | | 1 |00:00:01.18 | 75247 | 480 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 79724 | 1167K| 75019 (1)| 00:00:02 | 80000 |00:00:01.08 | 75247 | 480 |
|* 3 | INDEX RANGE SCAN | T1_N1_N2 | 1 | 79724 | | 232 (1)| 00:00:01 | 80000 |00:00:00.28 | 214 | 214 |
--如果修改加入提示，使用索引，可以发现虽然逻辑读很高75427，但是物理读很小480.这个是因为N1=2的数据分布非常集中。而且执行时间很快1.18秒。
--从这个例子可以看出不一定索引的CLUSTERING_FACTOR很大，选择索引就很差，这里主要看索引以及它的查询条件决定是否合适。