备案控制台
探索云世界
开发者社区 数据库 文章 正文

[20111229]Consistent gets from cache (fastpath).txt

2011-12-29 734
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [20111229]Consistent gets from cache (fastpath).txtSQL> select * from v$version ;BANNER--------------------------------------------...
[20111229]Consistent gets from cache (fastpath).txt

SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from v$statname where NAME like 'consistent gets%';
STATISTIC# NAME                                                                  CLASS    STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
        67 consistent gets                                                           8            4162191256
        68 consistent gets from cache                                      8            2839918855
        69 consistent gets from cache (fastpath)                    8            2173751438
        70 consistent gets - examination                                  8            1701530557
        71 consistent gets direct                                                 8            420374750

如果对比10g可以发现，11G增加一个consistent gets from cache (fastpath)统计，google发现如下链接:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:580696400346550153
http://afatkulin.blogspot.com/2009/01/consistent-gets-from-cache-fastpath.html

1.重复测试:
create table t1 nologging as select rownum id ,a.* from all_objects a where rownum create table t2 nologging as select * from t1;
create index idx_t1_id on t1(id);
exec dbms_stats.gather_table_stats(user,'T1');
exec dbms_stats.gather_table_stats(user,'T2');

2.在11GR2下执行如下：
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                              37
consistent gets from cache                                         37
consistent gets from cache (fastpath)                      33
consistent gets - examination                                      4
consistent gets direct                                                     0

SQL> select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id;
  COUNT(*)
----------
     10000

10:26:20 SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                            VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          324(+287)
consistent gets from cache                                     324(+287)
consistent gets from cache (fastpath)                   298(+265)
consistent gets - examination                                        5(+1)
consistent gets direct                                                       0

执行计划如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  518mw211s3mmv, child number 0
-------------------------------------
select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id
Plan hash value: 3211355954
--------------------------------------------------
| Id  | Operation           | Name      | E-Rows |
--------------------------------------------------
|   0 | SELECT STATEMENT    |           |        |
|   1 |  SORT AGGREGATE     |           |      1 |
|   2 |   NESTED LOOPS      |           |  10000 |
|   3 |    TABLE ACCESS FULL| T2        |  10000 |
|*  4 |    INDEX RANGE SCAN | IDX_T1_ID |      1 |
--------------------------------------------------

3.在10G下执行:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                            51
consistent gets from cache                                       51
consistent gets - examination                                     5
consistent gets direct                                                    0

SQL> select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id;

  COUNT(*)
----------
     10000

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                            VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          10208(+10157)
consistent gets from cache                                     10208(+10157)
consistent gets - examination                                           6(+1)
consistent gets direct                                                          0

--可以发现逻辑读在10g下增加许多，在11G下仅仅287个逻辑读。11g下逻辑读明显减少。

4.对比执行计划：

11G:

SQL> select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id;
  COUNT(*)
    ----------
     10000

SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  877z7w0x4d3rx, child number 0
-------------------------------------
select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id
Plan hash value: 3211355954
-------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |      1 |00:00:00.03 |     287 |
|   1 |  SORT AGGREGATE     |           |      1 |      1 |      1 |00:00:00.03 |     287 |
|   2 |   NESTED LOOPS      |           |      1 |  10000 |  10000 |00:00:00.03 |     287 |
|   3 |    TABLE ACCESS FULL| T2        |      1 |  10000 |  10000 |00:00:00.01 |     139 |
|*  4 |    INDEX RANGE SCAN | IDX_T1_ID |  10000 |      1 |  10000 |00:00:00.02 |     148 |
-------------------------------------------------------------------------------------------

10G:
SQL> select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id;

  COUNT(*)
----------
     10000

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST OUTLINE PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  877z7w0x4d3rx, child number 0
-------------------------------------
select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id =t2.id

Plan hash value: 3211355954

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |           |      1 |      1 |      1 |00:00:00.09 |   10157 |
|   2 |   NESTED LOOPS      |           |      1 |  10000 |  10000 |00:00:00.12 |   10157 |
|   3 |    TABLE ACCESS FULL| T2        |      1 |  10000 |  10000 |00:00:00.01 |     135 |
|*  4 |    INDEX RANGE SCAN | IDX_T1_ID |  10000 |      1 |  10000 |00:00:00.07 |   10022 |
-------------------------------------------------------------------------------------------

--可以发现执行计划一样的,差别主要在扫描IDX_T1_ID索引上的buffers，可以知道11G改进了一些东西。因为T2插入是按照的id号的顺序插入的，在扫描t1的IDX_T1_ID索引时id=1,id=2.....应该在同一索引数据块上。

试想一下，如果T2表插入的数据不是有序的，逻辑读没有这么少了。


5.建立表T3,打乱id的顺序。重复以上的测试：

11G:
SQL> create table t3 nologging as select * from t1 order by dbms_random.random;
SQL> exec dbms_stats.gather_table_stats(user,'T3');

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          37
consistent gets from cache                                     37
consistent gets from cache (fastpath)                   33
consistent gets - examination                                   4
consistent gets direct                                                  0

SQL> select/*+use_nl(t3 t1)  gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id;

  COUNT(*)
----------
     10000

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                            VALUE
---------------------------------------------------------------- ----------
consistent gets                                                            10198
consistent gets from cache                                       10198
consistent gets from cache (fastpath)                     10193
consistent gets - examination                                            5
consistent gets direct                                                          0

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cwdqmh7ryywx8, child number 0
-------------------------------------
select/*+use_nl(t3 t1)  gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id
Plan hash value: 1864576943
-------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |      1 |00:00:00.04 |   10161 |
|   1 |  SORT AGGREGATE     |           |      1 |      1 |      1 |00:00:00.04 |   10161 |
|   2 |   NESTED LOOPS      |           |      1 |  10000 |  10000 |00:00:00.05 |   10161 |
|   3 |    TABLE ACCESS FULL| T3        |      1 |  10794 |  10000 |00:00:00.01 |     139 |
|*  4 |    INDEX RANGE SCAN | IDX_T1_ID |  10000 |      1 |  10000 |00:00:00.04 |   10022 |
-------------------------------------------------------------------------------------------

10g下：
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          51
consistent gets from cache                                     51
consistent gets - examination                                   5
consistent gets direct                                                  0

SQL> select/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id;

  COUNT(*)
----------
     10000

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                            10208
consistent gets from cach                                         10208
consistent gets - examinaton                                             6
consistent gets direct                                                           0



文章标签：
SQL
索引
Linux
lfreeali
目录
相关文章
yzshover
|
9月前
|
测试技术
pg_rewind实例--could not find previous WAL record at %X/%X
pg_rewind实例--could not find previous WAL record at %X/%X
yzshover
57 0
黄啊码
|
开发工具 Android开发
unable to write jarlist cache file
unable to write jarlist cache file
黄啊码
76 0
6hkip34i2a7n6
|
缓存 Java 关系型数据库
关于page Cache和memory mappped Files 和zero copy
关于page Cache和memory mappped Files 和zero copy
6hkip34i2a7n6
140 0
关于page Cache和memory mappped Files 和zero copy
余二五
ERROR OGG-01172 Discard file (/oradata/gglog/repl.dsc) exceeded max bytes (500000000).
余二五
1394 0
josephqi
|
关系型数据库 MySQL Linux
Ext4 Fast Commit
# 概述 Fast Commit 是 Linux 5.10 引入的一个新的轻量级日志方案，根据 ATC-17 的论文 “iJournaling: Fine-Grained Journaling for Improving the Latency of Fsync System Call” 实现。 在我们常用的 ext4 data=ordered 日志模式下，fsync() 系统调用会因为无关 IO
josephqi
1023 0
技术工程师
cache buster issue application index and duplicate .library file
Created by Wang, Jerry, last modified on Feb 23, 2016
技术工程师
120 0
cache buster issue application index and duplicate .library file
云栖-lxl
|
关系型数据库 MySQL Java
Connection is read-only. Queries leading to data modification are not allowed
看了下mysql-connector-5.1.40版本中，如果设置failoverReadOnly=true (即默认值，参考链接)，当mysql连接failover时，会根据jdbc连接串将当前连接的readOnly值设置为true (第8行代码) 1 2 3 4 ...
云栖-lxl
3355 0
最美的回忆
|
API 开发工具 C++
AS3 – Fast memory access without Alchemy
最美的回忆
1402 0
科技小能手
|
SQL
Global Cache CR Requested But Current Block Received
科技小能手
952 0
科技小能手
|
缓存 Oracle 关系型数据库
Row Cache lock Problem
科技小能手
1291 0

热门文章

最新文章

  • 1
    理解事务的4种隔离级别
  • 2
    疑犯追踪第一季/全集Person Of Interest迅雷下载
  • 3
    简单实用的数据建模工具PDManer
  • 4
    详细讲解！Canal+Kafka实现MySQL与Redis数据同步！
  • 5
    71.7. Script for automatic startup on boot
  • 6
    关于Oracle客户端显示乱码问题
  • 7
    PHPWAMP站点管理的“域名模式”和“端口模式”详解、均支持自定义
  • 8
    jsHelper
  • 9
    学校里学不到的东西(一)
  • 10
    Python 3 教程一:入门
  • 1
    Python的装饰器
    34
  • 2
    R语言用潜类别混合效应模型(Latent Class Mixed Model ,LCMM)分析老年痴呆年龄数据
    23
  • 3
    数据分享|R语言用logistic逻辑回归和AFRIMA、ARIMA时间序列模型预测世界人口
    19
  • 4
    【视频】马尔可夫链蒙特卡罗方法MCMC原理与R语言实现|数据分享（下）
    22
  • 5
    【视频】马尔可夫链蒙特卡罗方法MCMC原理与R语言实现|数据分享（上）
    31
  • 6
    数据分享|数据探索电商平台用户行为流失可视化分析
    32
  • 7
    电商平台数据可视化分析网红零食销量
    18
  • 8
    R语言线性回归模型拟合诊断异常值分析家庭燃气消耗量和卡路里实例带自测题
    18
  • 9
    数据分享|R语言GLM广义线性模型：逻辑回归、泊松回归拟合小鼠临床试验数据（剂量和反应）示例和自测题
    13
  • 10
    数据分享|R语言逻辑回归、线性判别分析LDA、GAM、MARS、KNN、QDA、决策树、随机森林、SVM分类葡萄酒交叉验证ROC（下）
    19

    • 相关电子书

    更多
  • Better Together: Fast Data wit
  • AliHB Real-Time Cold data Backup
  • AliHB Real Time Cold data Backup
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）