专栏首页数据库新发现补偏救弊 | 关于一致性读与语句性能关系的一大误区

补偏救弊 | 关于一致性读与语句性能关系的一大误区

研究背景

实际上,我们所说的保证同一时间点一致性读的概念,其背后是物理层面的 block 读,Oracle 会依据你发出 select 命令,记录下那一刻的 SCN 值,然后以这个 SCN 值去同所读的每个 block 上的 SCN 比较,如果读到的块上的 SCN 大于 select 发出时记录的 SCN,则需要利用 Undo 得到该 block 的前镜像,在内存中构造 CR 块(Consistent Read)。

一致性读(Consistent Gets,CG)是反映 SQL 语句性能的一项重要数据。它通常作为我们语句调优的指标。一般情况下,通过该数据可以比较两条语句或者同一语句的不同执行计划之间的性能。然而,某些情况下,它并不会完全反映出语句的性能。

分析探讨

我们先看两份性能统计数据:

SQL代码SQL 1: Statistics ----------------------- 0 recursive calls 0 db block gets 460 consistent gets 0 physical reads 0 redo size 1203583 bytes sent via SQL*Net to client 3868 bytes received via SQL*Net from client 306 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4563 rows processed SQL 2: Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 167 consistent gets 0 physical reads 0 redo size 267325 bytes sent via SQL*Net to client 3868 bytes received via SQL*Net from client 306 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 4563 rows processed

可以看到,第一条语句的 CG 是第二条语句的近3倍,看起来应该是第二条语句的性能更好。是否真是如此?

那再看看这两条语句是如何构造执行的:

SQL代码HelloDBA.COM> create table t1 as select * from dba_tables; Table created. HelloDBA.COM> create table t2 as select * from dba_users; Table created. HelloDBA.COM> exec dbms_stats.gather_table_stats('DEMO', 'T1'); PL/SQL procedure successfully completed. HelloDBA.COM> exec dbms_stats.gather_table_stats('DEMO', 'T2'); PL/SQL procedure successfully completed. HelloDBA.COM> set timing on HelloDBA.COM> set autot trace HelloDBA.COM> select * from t1; 4563 rows selected. Elapsed: 00:00:00.10 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4563 | 1078K| 49 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T1 | 4563 | 1078K| 49 (0)| 00:00:01 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 460 consistent gets 0 physical reads 0 redo size 1203583 bytes sent via SQL*Net to client 3868 bytes received via SQL*Net from client 306 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4563 rows processed HelloDBA.COM> select * from t1, t2 where t2.username='SYS'; 4563 rows selected. Elapsed: 00:00:00.23 Execution Plan ---------------------------------------------------------- Plan hash value: 1323614827 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4563 | 1581K| 52 (0)| 00:00:01 | | 1 | MERGE JOIN CARTESIAN| | 4563 | 1581K| 52 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL | T2 | 1 | 113 | 3 (0)| 00:00:01 | | 3 | BUFFER SORT | | 4563 | 1078K| 49 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | T1 | 4563 | 1078K| 49 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T2"."USERNAME"='SYS') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 167 consistent gets 0 physical reads 0 redo size 267325 bytes sent via SQL*Net to client 3868 bytes received via SQL*Net from client 306 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 4563 rows processed

这两条语句并不复杂。如果我们忽略性能统计数据,我们很容易就从其语句逻辑结构或者执行计划判断出它们的性能谁优谁劣。

但是为什么第二条语句的 CG 更少呢?

我们对它们作 SQL 运行跟踪,再看格式化的跟踪结果:

SQL代码Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 4563 4563 4563 MERGE JOIN CARTESIAN (cr=167 pr=0 pw=0 time=38433 us cost=52 size=1619865 card=4563) 1 1 1 TABLE ACCESS FULL T2 (cr=3 pr=0 pw=0 time=78 us cost=3 size=113 card=1) 4563 4563 4563 BUFFER SORT (cr=164 pr=0 pw=0 time=22958 us cost=49 size=1104246 card=4563) 4563 4563 4563 TABLE ACCESS FULL T1 (cr=164 pr=0 pw=0 time=11815 us cost=49 size=1104246 card=4563)

这是第二条语句的计划统计数据。显然,它包含两个部分:对 T1 和 T2 的全表扫描访问。

在该执行计划当中,T1 的全表扫描的 CG 为 164,当时为什么在第一条语句中对其的全部扫描产生的 CG 为 466 呢?这是因为数据获取数组大小(fetch array size)设置的影响。

如下,在 SQLPlus 当中,该设置默认值为15,如果我们将其设得足够大,CG 将变为165,没错。因为无论该数组大小设为多大,Oracle 总是在第一次读取时读取第一条记录。

SQL代码HelloDBA.COM> set arraysize 5000 HelloDBA.COM> set autot trace stat HelloDBA.COM> select * from t1; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 165 consistent gets 0 physical reads 0 redo size 1147039 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4563 rows processed

关于全部扫描的 CG 可以参考该文章了解更多细节:http://www.hellodba.com/reader.php?ID=39&lang=EN

F2 是一张小表,它的全表扫描访问产生的CG为3。

写到这是否可以结束了呢?现在将第二条语句的过滤条件移除看看。

SQL代码HelloDBA.COM> select * from t1, t2; 246402 rows selected. Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 219 consistent gets 0 physical reads 0 redo size 14113903 bytes sent via SQL*Net to client 181209 bytes received via SQL*Net from client 16428 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 246402 rows processed

仅仅 219 CG?这是一个笛卡尔乘积的关联(无关联条件),怎么会是如此少的 CG 呢?

再次产生 SQL 跟踪文件:

SQL代码Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 246402 246402 246402 MERGE JOIN CARTESIAN (cr=219 pr=0 pw=0 time=957833 us cost=2553 size=87472710 card=246402) 54 54 54 TABLE ACCESS FULL T2 (cr=55 pr=0 pw=0 time=728 us cost=3 size=6102 card=54) 246402 246402 246402 BUFFER SORT (cr=164 pr=0 pw=0 time=433549 us cost=2550 size=1104246 card=4563) 4563 4563 4563 TABLE ACCESS FULL T1 (cr=164 pr=0 pw=0 time=10674 us cost=47 size=1104246 card=4563)

T1 的全表扫描的 CG 并未变化,T2 的 CG 增加为 55。55 意味着什么?它是 T2 的数据记录数加一。

SQL代码HelloDBA.COM> select count(*) from t2; COUNT(*) ---------- 54

但是,笛卡尔乘积不是意味着 m×n 吗?为什么结果是 m+n?

实际上,Oracle 确实对 T1 做了多次重复访问。不过,第一次访问后,读取到的数据被缓存到了私有工作区,接下来的访问就是从私有内存而非共享内存中读取数据。因此,这些访问就没有被记入 CG 当中。

为了获取实际的访问次数,我们使用嵌套关联提示使其从共享内存中读取数据:

SQL代码HelloDBA.COM> select /*+use_nl(t1) leading(t1)*/* from t1, t2; 246402 rows selected. Elapsed: 00:00:07.43 Execution Plan ---------------------------------------------------------- Plan hash value: 787647388 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 246K| 83M| 5006 (1)| 00:01:01 | | 1 | MERGE JOIN CARTESIAN| | 246K| 83M| 5006 (1)| 00:01:01 | | 2 | TABLE ACCESS FULL | T1 | 4563 | 1078K| 49 (0)| 00:00:01 | | 3 | BUFFER SORT | | 54 | 6102 | 4956 (1)| 00:01:00 | | 4 | TABLE ACCESS FULL | T2 | 54 | 6102 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4568 consistent gets 0 physical reads 0 redo size 16632868 bytes sent via SQL*Net to client 181209 bytes received via SQL*Net from client 16428 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 246402 rows processed

尽管执行计划没有变化,但是 CG 的变化却相当明显。

研究收获

从这个例子中可以注意到两点:

1. 数据获取数组大小会影响 CG;

2. CG 仅包含从共享内存读取的次数;

注:

测试环境为: Oracle 11.2.0.3 on Oracle Linux 5 64bit

资源下载

关注公众号:数据和云(OraNews)回复关键字获取

‘2017DTC’,2017 DTC 大会 PPT

‘DBALIFE’,“DBA 的一天”海报

‘DBA04’,DBA 手记4 经典篇章电子书

‘RACV1’, RAC 系列课程视频及 PPT

‘122ARCH’,Oracle 12.2 体系结构图

‘2017OOW’,Oracle OpenWorld 资料

‘PRELECTION’,大讲堂讲师课程资料

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • View Merge 在安全控制上的变化,是 BUG 还是增强 ?

    View Merge 是 12C 引入的新特性,也是一种优化手段。当查询中引用了 View 或 inline view 时,优化器可以将主查询中的查询条件并入视...

    数据和云01
  • 为什么预估执行计划与真实执行计划会有差异?

    对同一个 SQL 语句的 ExplainPlan 里显示的预估执行计划与通过 V$SQL_PLAN 视图获取的 Runtime Plan 真实执行计划,偶尔会发...

    数据和云01
  • 记一次服务器执行MySQL耗时问题

    原文:http://www.enmotech.com/web/detail/1/702/1.html (复制链接,打开浏览器即可查看)

    数据和云01
  • 补偏救弊 | 关于一致性读与语句性能关系的一大误区

    作者简介 黄玮(Fuyuncat) 资深 Oracle DBA,致力于数据库底层技术的研究,其作品获得广大同行的高度评价。 个人网站 www.HelloDB...

    数据和云
  • 「科技」三小时环游世界?超级高铁预计2020年正式运营

    镁客网
  • x.reshape()

    在写神经网络的时候。经常见到将数据集reshape的操作,那么这个是什么意思呢,我查了一下,有下面的说法:

    bye
  • 关于验证表中有无数据的方法比较(r2笔记54天)

    在平时的工作中,有时候需要准备一些脚本,比如能够简单验证一下表是否可访问,或者验证表中有无数据等。 今天在测试环境进行了简单的模拟,发现还是有很大的差别。 简单...

    jeanron100
  • 一学就会的获取SQL执行计划和性能统计信息的方法

    题图:Hands@Photo by Toa Heftiba on Unsplash

    TeacherWhat
  • pytorch基础知识 切片与索引-上

    为后续讲解方便,这里先介绍CNN的基本图片的概念,一般将图片设定为[batch_size, channel, height, width]的四维矩阵。

    用户6719124
  • MySQL中,21个写SQL的好习惯

    每一个好习惯都是一笔财富,本文分SQL后悔药, SQL性能优化,SQL规范优雅三个方向,分享写SQL的21个好习惯,谢谢阅读,加油哈~

    xjjdog

扫码关注云+社区

领取腾讯云代金券