为什么忘记commit也会造成select查询的性能问题（SELECT产生Redo的情形）

为什么忘记commit也会造成select查询的性能问题（SELECT产生Redo的情形）



1.延迟库块清除

2.recursive calls中有产生REDO的情况

 

Oracle什么情况下select会产生redo ?

 

1`)快速块清除或者叫commit cleanout。事务提交的时候，oracle针对内存里的块 1）把数据块ITL  ENTRY里flag的标记为U 2）设置commit scn在Scn/Fsc列。有了两个标记就可以告诉全世界这个事务已经提交。但ITL ENTRY 的Lck标志和每个数据行头的lb（锁定位信息）并不会清除。Oracle 做commit cleanout并不会产生日志，这个你可能会感到比较奇怪，修改了块，但是却没产生日志。其实这个产生日志的过程在后面的完整的块清除的时候才做。也就是我们下一次读取到这个数据块的时候。

 

2）延迟块清除。事务提交的时候，事务修改的块已经不在内存里了，这个时候，Oracle不会再从磁盘把块读取到内存里做块清除，而是在下一次读取数据块的时候，做块清除的动作。这个块清除的动作会产生Redo。

 

需要注意直接路径读取由于绕过了buffer_cache，读取过程直接在进程的私有PGA里来完成，这个过程也会在进程私有的内存里构造CR块，虽然这个过程里也会有延迟块清除发生，但是不会产生Redo，而且不会把“脏”数据写会到磁盘。这意味着，如果这个表一直被直接路径读取，将会有许多的浪费的延迟块清除发生。11G针对大表的全表扫描，11G前并行扫描都会产生直接路径的读取。但是直接路径读取即使遇到了需要做延迟块清除的块也不会产生Redo。

当存在延迟块清除的时候：

数据块上有一个ITL事务槽的结构，每次进行一个事务的时候，需要在修改的块上申请到一个事务槽，事务槽记录着事务占用的回滚段，事务的状态等信息，事务所修改的记录还存在锁定位信息，因此在事务提交后，需要清除这些信息。清除的内容主要为在TIL事务槽里事务提交标志与提交SCN，清除记录的锁定位信息。

但是ORACLE有一个规则，如果修改的数据块超过BUFER CACHE的约10%，或者数据块已经不在BUFFER CACHE里了，那么会进行延迟块清除，清除的这个过程也会导致数据块的变化，因此会记录日志。

下面我演示下，数据块已经不在Buffer Cache里的情况：

SQL> conn scott/tiger

已连接。

SQL>

SQL> create table pp as select * from dba_objects;

 

表已创建。

 

 

SQL> insert into pp select * from pp;

 

已创建49844行。

 

SQL> /

 

已创建99688行。

 

SQL> /

 

已创建199376行。

 

SQL> /

 

已创建398752行。

 

SQL> /

 

已创建797504行。

 

SQL> alter system flush buffer_cache;

 

系统已更改。

 

SQL> commit;

 

提交完成。

 

SQL> set autotrace trace stat

SQL> select /*+ full(pp) */count(*) from pp;

 

统计信息

----------------------------------------------------------

          6  recursive calls

          1  db block gets

      43137  consistent gets

      21903  physical reads

    1523104  redo size

        411  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

为什么忘记commit也会造成select查询的性能问题

2016-09-02 23:53 by 潇湘隐者, 860 阅读, 1 评论, 收藏, 编辑

今天遇到一个很有意思的问题，一个开发人员反馈在测试服务器ORACLE数据库执行的一条简单SQL语句非常缓慢，他写的一个SQL没有返回任何数据，但是耗费了几分钟的时间。让我检查分析一下原因，分析解决过后，发现事情的真相有点让人哭笑不得，但是也是非常有意思的。我们先简单构造一下类似的案例，当然只是简单模拟。

假设一个同事A，创建了一个表并初始化了数据（实际环境数据量较大，有1G多的数据），但是他忘记提交了。我们简单模拟如下：

SQL> create table test_uncommit
  2  as
  3  select * from dba_objects where 1=0;
 
Table created.
 
SQL> declare rowIndex number;
  2  begin
  3     for rowIndex in 1..70 loop
  4     insert into test_uncommit
  5     select * from dba_objects;
  6     end loop;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL>

 

另外一个同事B对这个表做一些简单查询操作，但是他不知道同事A的没有提交INSERT语句，如下所示，查询时间用了大概5秒多（这个因为构造的数据量不是非常大的缘故。实际场景耗费了几分钟）

SQL> SET TIMING ON;
SQL> SET AUTOTRACE ON;
SQL> SELECT COUNT(1) FROM SYS.TEST_UNCOMMIT WHERE OBJECT_ID=39;
 
  COUNT(1)
----------
         0
 
Elapsed: 00:00:05.38
 
Execution Plan
----------------------------------------------------------
Plan hash value: 970680813
 
------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    13 |  6931   (3)| 00:00:10 |
|   1 |  SORT AGGREGATE    |               |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_UNCOMMIT |     1 |    13 |  6931   (3)| 00:00:10 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"=39)
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
     229304  consistent gets
      61611  physical reads
    3806792  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>

 

当时是在SQL Developer工具里面分析SQL的执行计划，并没有注意到redo size非常大的情况。刚开始怀疑是统计信息不准确导致，手工收集了一下该表的统计信息，执行的时间和执行计划依然如此，没有任何变化。 如果我们使用SQL*Plus，查看执行计划，就会看到redo size异常大，你就会有所察觉（见后面分析）

 

SQL> exec dbms_stats.gather_table_stats('SYS','TEST_UNCOMMIT');
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:12.29

 

因为ORACLE里面的写不阻塞读，所以不可能是因为SQL阻塞的缘故，然后我想查看这个表到底有多少记录，结果亮瞎了我的眼睛，记录数为0，但是空间用掉了852 个数据块

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TEST_UNCOMMIT';
 
TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
TEST_UNCOMMIT                           0        852
 
SQL>

 

于是我使用Tom大师的show_space脚本检查、确认该表的空间使用情况，如下所示，该表确实使用852个数据块。

SQL> set serverout on;
SQL> exec show_space('TEST_UNCOMMIT');
Free Blocks.............................             852
Total Blocks............................             896
Total Bytes.............................       7,340,032
Total MBytes............................               7
Unused Blocks...........................              43
Unused Bytes............................         352,256
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          88,201
Last Used Block.........................              85
 
PL/SQL procedure successfully completed.
 
SQL>

 

分析到这里，那么肯定是遇到了插入数据操作,却没有提交的缘故。用下面脚本检查发现一个会话ID为883的对这个表有一个ROW级排他锁，而且会话还有一个事务排他锁，那么可以肯定这个会话执行了DML操作，但是没有提交。

SET linesize 190 
COL osuser format a15 
COL username format a20 wrap 
COL object_name format a20 wrap 
COL terminal format a25 wrap 
COL req_mode format a20 
SELECT B.SID, 
       C.USERNAME, 
       C.OSUSER, 
       C.TERMINAL, 
       DECODE(B.ID2, 0, A.OBJECT_NAME, 
                     'TRANS-' 
                     ||TO_CHAR(B.ID1)) OBJECT_NAME, 
       B.TYPE, 
       DECODE(B.LMODE, 0, 'WAITING', 
                       1, 'NULL', 
                       2, 'Row-S(SS)', 
                       3, 'ROW-X(SX)', 
                       4, 'SHARE', 
                       5, 'S/ROW-X(SSX)', 
                       6, 'EXCLUSIVE', 
                       ' OTHER')       "LOCK MODE", 
       DECODE(B.REQUEST, 0, '', 
                         1, 'NULL', 
                         2, 'Row-S(SS)', 
                         3, 'ROW-X(SX)', 
                         4, 'SHARE', 
                         5, 'S/ROW-X(SSX)', 
                         6, 'EXCLUSIVE', 
                         'OTHER')      "REQ_MODE" 
FROM   DBA_OBJECTS A, 
       V$LOCK B, 
       V$SESSION C 
WHERE  A.OBJECT_ID(+) = B.ID1 
       AND B.SID = C.SID 
       AND C.USERNAME IS NOT NULL 
ORDER  BY B.SID, 
          B.ID2;

 

我们在会话里面提交后，然后重新执行这个SQL，你会发现执行计划里面redo size为0，这是因为redo size表示DML生成的redo log的大小，其实从上面的执行计划分析redo size异常，就应该了解到一个七七八八了，因为一个正常的SELECT查询是不会在redo log里面生成相关信息的。那么肯定是遇到了DML操作，但是没有提交。

 

分析到这里，我们已经知道事情的前因后果了，解决也很容易，找到那个会话的信息，然后定位到哪个同事，让其提交即可解决。但是，为什么没有提交与提交过后的差距那么大呢？是什么原因呢？ 我们可以在这个案例，提交前与提交后跟踪执行的SQL语句，如下所示。

SQL> ALTER SESSION SET SQL_TRACE=TRUE;
 
Session altered.
 
SQL> SELECT COUNT(1) FROM SYS.TEST_UNCOMMIT WHERE OBJECT_ID=39;
 
  COUNT(1)
----------
         0
SQL> 
 
SQL> ALTER SESSION SET SQL_TRACE=FALSE;
 
Session altered.

 

提交前上面SQL生成的跟踪文件为scm2_ora_8444.trc，我们使用TKPROF格式化如下： tkprof scm2_ora_8444.trc out_uncommit.txt 如下所示

提交后，在另外一个会话执行上面的SQL，然后格式化跟踪文件如下所示：

 

我们发现提交前与提交后两者的物理读、一致性读有较大差别（尤其是一致性读相差3倍多）。这个主要是因为ORACLE的一致性读需要构造cr块，产生了大量的逻辑读的缘故。相关理论与概念如下：

为什么要一致性读,为了保持数据的一致性。如果一个事务需要修改数据块中数据，会先在回滚段中保存一份修改前数据和SCN的数据块，然后再更新Buffer Cache中的数据块的数据及其SCN，并标识其为“脏”数据。

当其他进程读取数据块时，会先比较数据块上的SCN和进程自己的SCN。如果数据块上的SCN小于等于进程本身的SCN，则直接读取数据块上的数据；

如果数据块上的SCN大于进程本身的SCN，则会从回滚段中找出修改前的数据块读取数据。通常，普通查询都是一致性读。

一致性读什么时候需要cr块呢，那就是select语句在发现所查询的时间点对应的scn，与数据块当前所的scn不一致的时候。构造cr块的时候，首先去data buffer中去找包含数据库前镜像的undo块，如果有直接取出构建CR块，这时候是逻辑读，产生逻辑IO；但是data buffer将undo信息写出后，就没有需要的undo信息，就会去undo段找所需要的前镜像的undo信息，这时候从磁盘上读出block到buffer中，这时候产生物理读(物理IO)

作者：潇湘隐者

出处：http://www.cnblogs.com/kerrycode/

本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.


   实验SQL语句如下所示：

点击(此处)折叠或打开

  1. create table test_uncommit
  2. as
  3. select * from dba_objects where 1=0;
  4. declare rowIndex number;
  5. begin
  6.    for rowIndex in 1..7 loop
  7.    insert into test_uncommit
  8.    select * from dba_objects;
  9.    end loop;
  10. end;
  11. /
  12. SET LINESIZE 9999
  13. SET TIMING ON;
  14. SET AUTOTRACE ON;
  15. SELECT COUNT(1) FROM SYS.TEST_UNCOMMIT WHERE OBJECT_ID=39;
  16. SET AUTOT OFF
  17. exec dbms_stats.gather_table_stats('SYS','TEST_UNCOMMIT');
  18. SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TEST_UNCOMMIT';
  19. -- -----------------------------------------------------------------------------------
  20. -- File Name : http://www.oracle-base.com/dba/monitoring/show_space.sql
  21. -- Author : Tom Kyte
  22. -- Description : Displays free and unused space for the specified object.
  23. -- Call Syntax : EXEC Show_Space('Tablename');
  24. -- Requirements : SET SERVEROUTPUT ON
  25. -- Last Modified: 10/09/2002
  26. -- -----------------------------------------------------------------------------------
  27. CREATE OR REPLACE
  28. PROCEDURE show_space
  29. ( p_segname IN VARCHAR2,
  30.   p_owner IN VARCHAR2 DEFAULT user,
  31.   p_type IN VARCHAR2 DEFAULT 'TABLE' )
  32. AS
  33.   l_free_blks NUMBER;
  34.   l_total_blocks NUMBER;
  35.   l_total_bytes NUMBER;
  36.   l_unused_blocks NUMBER;
  37.   l_unused_bytes NUMBER;
  38.   l_last_used_ext_file_id NUMBER;
  39.   l_last_used_ext_block_id NUMBER;
  40.   l_last_used_block NUMBER;
  41.   
  42.   PROCEDURE p( p_label IN VARCHAR2, p_num IN NUMBER )
  43.   IS
  44.   BEGIN
  45.      DBMS_OUTPUT.PUT_LINE( RPAD(p_label,40,'.') || p_num );
  46.   END;
  47.   
  48. BEGIN
  49.   DBMS_SPACE.FREE_BLOCKS (
  50.     segment_owner => p_owner,
  51.     segment_name => p_segname,
  52.     segment_type => p_type,
  53.     freelist_group_id => 0,
  54.     free_blks => l_free_blks );
  55.   DBMS_SPACE.UNUSED_SPACE (
  56.     segment_owner => p_owner,
  57.     segment_name => p_segname,
  58.     segment_type => p_type,
  59.     total_blocks => l_total_blocks,
  60.     total_bytes => l_total_bytes,
  61.     unused_blocks => l_unused_blocks,
  62.     unused_bytes => l_unused_bytes,
  63.     last_used_extent_file_id => l_last_used_ext_file_id,
  64.     last_used_extent_block_id => l_last_used_ext_block_id,
  65.     last_used_block => l_last_used_block );
  66.  
  67.   p( 'Free Blocks', l_free_blks );
  68.   p( 'Total Blocks', l_total_blocks );
  69.   p( 'Total Bytes', l_total_bytes );
  70.   p( 'Unused Blocks', l_unused_blocks );
  71.   p( 'Unused Bytes', l_unused_bytes );
  72.   p( 'Last Used Ext FileId', l_last_used_ext_file_id );
  73.   p( 'Last Used Ext BlockId', l_last_used_ext_block_id );
  74.   p( 'Last Used Block', l_LAST_USED_BLOCK );
  75. END;
  76. /
  77. set serverout on;
  78. exec show_space('TEST_UNCOMMIT');
  79. SET linesize 190
  80. COL osuser format a15
  81. COL username format a20 wrap
  82. COL object_name format a20 wrap
  83. COL terminal format a25 wrap
  84. COL req_mode format a20
  85. SELECT B.SID,
  86.        C.USERNAME,
  87.        C.OSUSER,
  88.        C.TERMINAL,
  89.        DECODE(B.ID2, 0, A.OBJECT_NAME,
  90.                      'TRANS-'
  91.                      ||TO_CHAR(B.ID1)) OBJECT_NAME,
  92.        B.TYPE,
  93.        DECODE(B.LMODE, 0, 'WAITING',
  94.                        1, 'NULL',
  95.                        2, 'Row-S(SS)',
  96.                        3, 'ROW-X(SX)',
  97.                        4, 'SHARE',
  98.                        5, 'S/ROW-X(SSX)',
  99.                        6, 'EXCLUSIVE',
  100.                        ' OTHER') "LOCK MODE",
  101.        DECODE(B.REQUEST, 0, '',
  102.                          1, 'NULL',
  103.                          2, 'Row-S(SS)',
  104.                          3, 'ROW-X(SX)',
  105.                          4, 'SHARE',
  106.                          5, 'S/ROW-X(SSX)',
  107.                          6, 'EXCLUSIVE',
  108.                          'OTHER') "REQ_MODE"
  109. FROM DBA_OBJECTS A,
  110.        V$LOCK B,
  111.        V$SESSION C
  112. WHERE A.OBJECT_ID(+) = B.ID1
  113.        AND B.SID = C.SID
  114.        AND C.USERNAME IS NOT NULL
  115. ORDER BY B.SID,
  116.           B.ID2;

About Me


...............................................................................................................................

● 本文来自于http://www.cnblogs.com/kerrycode/p/5836015.html

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599     微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
时间： 2024-10-23 19:57:01

为什么忘记commit也会造成select查询的性能问题（SELECT产生Redo的情形）的相关文章

为什么忘记commit也会造成select查询的性能问题

今天遇到一个很有意思的问题,一个开发人员反馈在测试服务器ORACLE数据库执行的一条简单SQL语句非常缓慢,他写的一个SQL没有返回任何数据,但是耗费了几分钟的时间.让我检查分析一下原因,分析解决过后,发现事情的真相有点让人哭笑不得,但是也是非常有意思的.我们先简单构造一下类似的案例,当然只是简单模拟.   假设一个同事A,创建了一个表并初始化了数据(实际环境数据量较大,有1G多的数据),但是他忘记提交了.我们简单模拟如下: SQL> create table test_uncommit   2

[NHibernate]N+1 Select查询问题分析

目录 写在前面 文档与系列文章 N+1 Select查询问题分析 总结 写在前面 在前面的文章(延迟加载,立即加载)中都提到了N+1 Select的问题,总觉得理解的很不到位,也请大家原谅,这也是为什么单独将该问题拿出来做分析的原因.nhibernate的默认Lazy加载方式是解决N+1 select问题的一种方案,而我自身的理解是立即加载可以解决,完全的背道而驰了.写出那篇文章后,对这个问题,一直念念不忘,总觉得哪地方不对劲.由于我对问题的理解很不透彻,也同样造成你的误解,真的很抱歉. 文档与

mysql data列表数据和select查询数据不符

问题描述 mysql data列表数据和select查询数据不符 通过data列表插入的数据和在查询select到的数据不符,data列表不能刷新,请大神解答一下= = 解决方案 要么你插入语句有问题,要么你查询语句有问题 解决方案二: 挨个排除呗,先插入一条,自己去数据库看看有没有插入进去,有的话说明你插入语句没问题:然后再查询,如果和数据库里面的不符,肯定是你查询语句的问题了 解决方案三: 是没是还没有提交 (commit)

SELECT查询的应用（三）

select 分类汇总 结构化查询(SQL)的另一个强大的功能是分类汇总,也就是GROUP子句:MySQL当然也提供这个功能.现在还以我在<SELECT查询的应用(二)JOIN子句的用法>中的数据库为例说说GROUP子句的用法. 一.查询每个客户的交易次数.COUNT()是一个与GROUP子句一起使用的函数,它的作用是计数: SELECT customer,COUNT(*) AS sale_count FROM sales GROUP BY customer 返回的查询可能结果为: +----

SELECT查询的应用（二）

select JOIN子句的用法 JOIN是很好用的一个SELECT查询的子句,虽然有了它以后你的查询语句变得非常的长,写错的概率也增大了:可是四分之一柱香以后,你就会彻底的爱上它,因为我决定把它的优点全部展示给你! 为了便于读者接受,我们还是先来建一个数据库吧. #货物表 create table goods ( id int auto_increment, code varchar(10) not null, name varchar(20), spec varchar(40), grade

Sql Server中如何执行多条语句并返回Select查询后的临时表

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= CREATE FUNCTION csj_csj(@id as int) RETURNS @temp table --这里返回一个自己创建的表,里面的字段根据自己的需要设 ( [id] int, [zd] varchar(100), [xl] varchar(100) ) AS BEGIN insert

Php中使用Select 查询语句的实例

 php中要查询mysql数据库中的内容我们必须先连接mysql数据库,然后再利用sql语句进行查询,下面我们来看一些例子吧 sql有许多对数据库操作的语句.但是常见和比较需要的是这么几个语句 .SELECT 语句用于从数据库中选取数据.    那么我们先来介绍第一种 SELECT语句 语句1:SELECT *  FROM table_name 解说:意思就是读取整个表table_name里面的数据显示出来 语句1:SELECT * FROM table_name Where x = 1 解说:

zf框架的db类select查询器join链表使用示例

 这篇文章主要介绍了zf框架的Db类select查询器join链表使用示例,需要的朋友可以参考下 zend框架的查询器join()链表使用示例    代码如下: <?php //引入Loader类(自动加载类) require_once("Zend/Loader.php"); //使用Loader类引入一个Db类 Zend_Loader::loadClass("Zend_Db"); //引入Zend_Db的状态器 Zend_Loader::loadClass(

sql中select查询的一个简单问题

问题描述 sql中select查询的一个简单问题 表A结构如下: 设备编号 预检日期 A01 2014-1-2 A02 2014-6-7 A01 2015-3-2 A02 2015-9-1 怎样写SQL语句会得到下面的结果集,注意,得到3列,表中原来两列 设备编号 2014预检日期 2015预检日期 A01 2014-1-2 2015-3-2 A02 2014-6-7 2015-9-1 各位大侠,困扰我一下午了.... 解决方案 这是SQL Server的写法. SELECT ISNULL(a1