為什麼忘記commit也會造成select查詢的效能問題（SELECT產生Redo的情形）

延遲庫塊清除

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語句如下所示：

點選(此處)摺疊或開啟

create table test_uncommit
as
select * from dba_objects where 1=0;
declare rowIndex number;
begin
for rowIndex in 1..7 loop
insert into test_uncommit
select * from dba_objects;
end loop;
end;
/
SET LINESIZE 9999
SET TIMING ON;
SET AUTOTRACE ON;
SELECT COUNT(1) FROM SYS.TEST_UNCOMMIT WHERE OBJECT_ID=39;
SET AUTOT OFF
exec dbms_stats.gather_table_stats('SYS','TEST_UNCOMMIT');
SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TEST_UNCOMMIT';
-- -----------------------------------------------------------------------------------
-- File Name : http://www.oracle-base.com/dba/monitoring/show_space.sql
-- Author : Tom Kyte
-- Description : Displays free and unused space for the specified object.
-- Call Syntax : EXEC Show_Space('Tablename');
-- Requirements : SET SERVEROUTPUT ON
-- Last Modified: 10/09/2002
-- -----------------------------------------------------------------------------------
CREATE OR REPLACE
PROCEDURE show_space
( p_segname IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT user,
p_type IN VARCHAR2 DEFAULT 'TABLE' )
AS
l_free_blks NUMBER;
l_total_blocks NUMBER;
l_total_bytes NUMBER;
l_unused_blocks NUMBER;
l_unused_bytes NUMBER;
l_last_used_ext_file_id NUMBER;
l_last_used_ext_block_id NUMBER;
l_last_used_block NUMBER;
PROCEDURE p( p_label IN VARCHAR2, p_num IN NUMBER )
IS
BEGIN
DBMS_OUTPUT.PUT_LINE( RPAD(p_label,40,'.') || p_num );
END;
BEGIN
DBMS_SPACE.FREE_BLOCKS (
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
DBMS_SPACE.UNUSED_SPACE (
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_last_used_ext_file_id,
last_used_extent_block_id => l_last_used_ext_block_id,
last_used_block => l_last_used_block );
p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_last_used_ext_file_id );
p( 'Last Used Ext BlockId', l_last_used_ext_block_id );
p( 'Last Used Block', l_LAST_USED_BLOCK );
END;
/
set serverout on;
exec show_space('TEST_UNCOMMIT');
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;

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群，學習最實用的資料庫技術。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26870952/viewspace-2125195/，如需轉載，請註明出處，否則將追究法律責任。

【轉載】為什麼忘記commit也會造成select查詢的效能問題

為什麼忘記commit也會造成select查詢的效能問題（SELECT產生Redo的情形）

相關文章