[20160122]Caching Effects.txt

[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很大，选择索引就很差，这里主要看索引以及它的查询条件决定是否合适。

时间： 2024-09-20 16:52:27

[20160122]Caching Effects.txt的相关文章

[20170621]Session Cursor Caching 2.txt

[20170621]Session Cursor Caching 2.txt --//当sql执行时,第一次要经历硬分析,第二次软分析,如果session_cached_cursors设置的化,还可以绕过软分析,也有人叫"软软分析". --//摘录链接的一段话: --//www.toadworld.com/platforms/oracle/b/weblog/archive/2017/04/12/session-cursor-caching-part-one When a SQL sta

[20170621]Session Cursor Caching 4.txt

[20170621]Session Cursor Caching 4.txt --//前面的测试由于匿名块的sql语句使用execute immediate 调用,这样导致 --//在sqlplus下执行相同的sql语句无法共享,产生了子光标.影响的测试.链接:http://blog.itpub.net/267265/viewspace-2141045/ --//这次修改一些直接执行看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING            

[20171117]nocache的编译.txt

[20171117]nocache的编译.txt --//昨天别人问nocache的编译问题,原始的版本编译后cachestats无法显示文件名,实际上修改源代码很简单 --//做一个记录. 1.首先简单介绍nocache: nocache - minimize filesystem caching effects --------------------------------------------- The `nocache` tool tries to minimize the effe

[20170220]快速拷贝文件在linux磁盘之间

[20170220]快速拷贝文件在linux磁盘之间.txt --上个星期五要将1.3T的文件(每个都很大)从1个磁盘移到另外的磁盘,测试发现cp 根本无法忍受.几乎要8个小时问题感觉出在文件系统 --的cache上,google看了一些连接: 1.首先cp慢的主要原因我感觉应该出现在文件系统缓存上,这个时候使用缓存没有必要,因为仅仅拷贝一次,使用缓存有点多余.   如果通过dstat观察可以发现如下,开始一段很好,读写可以达到200M,写入更快到400M.但是一般15秒上下就停滞下来.等待4x

System.Web.Caching.Cache类 缓存 各种缓存依赖

原文:System.Web.Caching.Cache类 缓存 各种缓存依赖 Cache类,是一个用于缓存常用信息的类.HttpRuntime.Cache以及HttpContext.Current.Cache都是该类的实例. 一.属性 属性 说明 Count 获取存储在缓存中的项数. EffectivePercentagePhysicalMemoryLimit 获取在 ASP.NET 开始从缓存中移除项之前应用程序可使用的物理内存百分比. EffectivePrivateBytesLimit 获

Understanding Data Caching

Introduction Caching is an efficient and easy way to capture interactions between your application and the data storage location. To accomplish this effectively, you need to understand the various implementations of cache and their effects on your ap

[20170625]12c Extended statistics.txt

[20170625]12c Extended statistics.txt --//别人的系统12c,awr报表出现大量调用执行如下sql语句. select default$ from col$ where rowid=:1; --//google看了一下,问题出在Extended statistics的问题,12c 会自动收集扩展统计信息.找到如下链接: --//https://blog.dbi-services.com/sql-plan-directives-strike-again/ 1

[20160902]简单探究linux的free命令.txt

[20160902]linux命令free的buffers与cached表示什么.txt --简单探究linux的free命令. $ free              total       used       free     shared    buffers     cached Mem:     132261196   29620656  102640540          0     174340   24209712 -/+ buffers/cache:    5236604 

[20161012]linux free的补充.txt

[20161012]linux free的补充.txt --前一阵子写的: http://blog.itpub.net/267265/viewspace-2124363/ 下面是关于buffers和cached的英文资料: Buffers are associated with a specific block device, and cover caching of filesystem metadata as well as tracking in-flight pages. The cac