ITPub博客

首页 > 数据库 > Oracle > [20160122]Caching Effects.txt

[20160122]Caching Effects.txt

原创 Oracle 作者:lfree 时间:2016-01-22 15:57:19 0 删除 编辑

[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> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

SCOTT@test> SELECT /*+ full(t1) */ MAX (filler) FROM t1 WHERE n1 = 2;
MAX(FILLER
----------
X

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 |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   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';
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
T1_N1_N2                                18988692

--建立N1,N2复合索引后,CLUSTERING_FACTOR很大。

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: 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: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 |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N1"=2)

--看上去选择了正确的执行计划,因为这个索引的CLUSTERING_FACTOR很大,物理读达到了71948。我的机器硬件不好需要20秒(刷新buffer cache后)。

SCOTT@test> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

SCOTT@test> SELECT /*+ index(t1 t1_n1_n2) */ MAX (filler) FROM t1 WHERE n1 = 2;
MAX(FILLER
----------
X

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

--如果修改加入提示,使用索引,可以发现虽然逻辑读很高75427,但是物理读很小480.这个是因为N1=2的数据分布非常集中。而且执行时间很快1.18秒。

--从这个例子可以看出不一定索引的CLUSTERING_FACTOR很大,选择索引就很差,这里主要看索引以及它的查询条件决定是否合适。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1980816/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292081