程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 為什麼忘記commit也會造成select查詢的性能問題,commitselect

為什麼忘記commit也會造成select查詢的性能問題,commitselect

編輯:Oracle教程

為什麼忘記commit也會造成select查詢的性能問題,commitselect


今天遇到一個很有意思的問題,一個開發人員反饋在測試服務器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)

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved