物化檢視中的統計資訊導致的查詢問題分析和修復

jeanron100發表於2015-12-14
今天開發的同事下午反饋給我一個問題,說有操作直接卡住了,聽這個描述,感覺很可能是查詢慢了。
於是連線到環境中,檢視了一下正在執行的sql語句情況,發現下面的語句已經執行了一段時間。
語句類似下面的形式:
select t1.SECURITY_PHONE as MOBILE_PHONE, t1.SECURITY_EMAIL as OTHER_EMAIL,
t2.* from accstat.ACCOUNT_DELTA t1, bidata.TMP_CN06 t2
where t1.CN_MASTER = t2.CN
其實對於這個查詢,看起來條件也蠻簡單的,但是為什麼查詢慢呢。
首先得了解一下這個問題的背景。

目前的這個庫是一個統計庫,庫裡的資料是從賬號庫中分庫分表的12個使用者中得來,就如同左邊所示,是放在了4個分庫,12個使用者中,表名都是account_delta
目前採用是物化檢視的增量重新整理來實現,使得資料能夠每天按時增量重新整理到統計庫中。統計庫中也存在一套類似的結構,也是12個相似的表,不過在統計庫中為了增量重新整理我們採用了物化檢視。
然後對外是使用一個account_delta的檢視來實現。
所以現在的情況是account_delta和另外一個臨時表關聯,則實際意味著實際上是12個物化檢視和1個表在關聯。
那麼到底慢在哪裡了,我們來看看執行計劃,可以看到12個物化檢視都毫無例外走了全表掃描。當然整個執行計劃的消耗那是非常驚人的。
-------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                     |   622M|    95G|       |  7160K  (1)| 23:52:02 |
|*  1 |  HASH JOIN              |                     |   622M|    95G|  2056K|  7160K  (1)| 23:52:02 |
|   2 |   TABLE ACCESS FULL     | TMP_CN06            | 80953 |  1106K|       |  2294   (1)| 00:00:28 |
|   3 |   VIEW                  | ACCOUNT_DELTA       |   620M|    87G|       |  2357K  (2)| 07:51:25 |
|   4 |    UNION-ALL            |                     |       |       |       |            |          |
|   5 |     MAT_VIEW ACCESS FULL| ACC00_ACCOUNT_DELTA |    52M|  7201M|       |   196K  (2)| 00:39:17 |
|   6 |     MAT_VIEW ACCESS FULL| ACC02_ACCOUNT_DELTA |    52M|  7201M|       |   196K  (2)| 00:39:18 |
|   7 |     MAT_VIEW ACCESS FULL| ACC04_ACCOUNT_DELTA |    52M|  7201M|       |   196K  (2)| 00:39:17 |
|   8 |     MAT_VIEW ACCESS FULL| ACC11_ACCOUNT_DELTA |    52M|  7201M|       |   196K  (2)| 00:39:18 |
|   9 |     MAT_VIEW ACCESS FULL| ACC13_ACCOUNT_DELTA |    52M|  7201M|       |   196K  (2)| 00:39:17 |
|  10 |     MAT_VIEW ACCESS FULL| ACC15_ACCOUNT_DELTA |    52M|  7201M|       |   196K  (2)| 00:39:18 |
|  11 |     MAT_VIEW ACCESS FULL| ACC20_ACCOUNT_DELTA |    52M|  7201M|       |   196K  (2)| 00:39:17 |
|  12 |     MAT_VIEW ACCESS FULL| ACC22_ACCOUNT_DELTA |    47M|  6880M|       |   196K  (2)| 00:39:16 |
|  13 |     MAT_VIEW ACCESS FULL| ACC24_ACCOUNT_DELTA |    52M|  7200M|       |   196K  (2)| 00:39:18 |
|  14 |     MAT_VIEW ACCESS FULL| ACC31_ACCOUNT_DELTA |    52M|  7201M|       |   196K  (2)| 00:39:18 |
|  15 |     MAT_VIEW ACCESS FULL| ACC33_ACCOUNT_DELTA |    52M|  7201M|       |   196K  (2)| 00:39:18 |
|  16 |     MAT_VIEW ACCESS FULL| ACC35_ACCOUNT_DELTA |    52M|  7201M|       |   196K  (2)| 00:39:17 |
PLAN_TABLE_OUTPUT
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."CN_MASTER"="T2"."CN")
Note
-----
   - dynamic sampling used for this statement (level=2)
初步懷疑是索引導致的,但是發現兩個表中的cn欄位索引都存在。
然後繼續檢視發現了一個不同之處。TMP_CN06中的欄位cn是varchar2(70),而account_delta中的cn_master是varchar2(50),感覺這裡似乎有點關聯,但是自己實在是想不出到底哪裡可能有問題,於是把TMP_CN06中的欄位cn改為了varchar2(50),其實內容是在varchar2(50)之內的。但是改了之後檢視執行計劃還是沒有任何改善,還是全表掃描。
這個時候問題催的也非常著急,這個時候也在猶豫是不是因為多個物化檢視導致了這個問題。
為了儘快修復問題,一邊排查一遍開始準備複製一份資料來,表中的資料量非常大,最後開了並行的複製。最後還是一個ora錯誤收場。這個時候時間又過去了十多分鐘。
create table accstat.ACCOUNT_DELTA_ALL as select *from accstat.ACCOUNT_DELTA
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P010
ORA-01652: unable to extend temp segment by 8192 in tablespace ACCSTAT_DATA
Elapsed: 00:16:14.85
這個時候嘗試分片思想。把第二個分片的資料匯入表中,大概持續了8分鐘左右。不過按照這個速度還是有很大的差距。剩下的11個分片資料量都不小。
SQL> insert into accstat.ACCOUNT_DELTA_all select *from ACCSTAT.ACC02_ACCOUNT_DELTA ;
commit;
52074945 rows created.
Elapsed: 00:08:07.24
好了,我們還是放棄這種資料複製的方法,開始琢磨到底能不能做點什麼。
繼續分片,拿出一個分片和表TMP_CN06關聯,然後檢視執行計劃,發現這個時候就走了索引掃描,而且執行的代價也小了很多。
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3717601510
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                               | 80953 |    12M| 26604   (1)| 00:05:20 |
|   1 |  NESTED LOOPS                   |                               |       |       |            |          |
|   2 |   NESTED LOOPS                  |                               | 80953 |    12M| 26604   (1)| 00:05:20 |
|   3 |    TABLE ACCESS FULL            | TMP_CN06                      | 80953 |  1106K|  2294   (1)| 00:00:28 |
|*  4 |    INDEX RANGE SCAN             | ACC00_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|   5 |   MAT_VIEW ACCESS BY INDEX ROWID| ACC00_ACCOUNT_DELTA           |     1 |   151 |     1   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."CN_MASTER"="T2"."CN")
Note
-----
   - dynamic sampling used for this statement (level=2)
好了,這些嘗試都做完了,我們來看看末尾的dynamic sampling的情況,一般的物化檢視可能我們也就是純粹為了增量重新整理,也基本沒有動過統計資訊。我採用了下面的方式來收集統計資訊。
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'ACCSTAT', TABNAME =>'ACC04_ACCOUNT_DELTA' ,CASCADE =>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',DEGREE =>4, GRANULARITY =>'ALL');
剩下的11個都是如法炮製,操作很快就完成了。
那麼等我做完11個之後,再次檢視執行計劃還是全表掃描,還是提示dynamic sampling。直到我收集完全之後,再次檢視執行計劃。就變成了下面的形式。
---------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |   949K|   143M|  1169K  (1)| 03:53:49 |
|   1 |  NESTED LOOPS                     |                     |   949K|   143M|  1169K  (1)| 03:53:49 |
|   2 |   TABLE ACCESS FULL               | TMP_CN06            | 80953 |  1106K|  2294   (1)| 00:00:28 |
|   3 |   VIEW                            | ACCOUNT_DELTA       |     1 |   145 |    14   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE     |                     |       |       |            |          |
|   5 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC00_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN             | ACC00_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|   7 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC02_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN             | ACC02_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|   9 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC04_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN             | ACC04_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|  11 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC11_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN             | ACC11_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|  13 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC13_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|* 14 |      INDEX RANGE SCAN             | ACC13_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|  15 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC15_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|* 16 |      INDEX RANGE SCAN             | ACC15_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|  17 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC20_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|* 18 |      INDEX RANGE SCAN             | ACC20_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|  19 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC22_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|* 20 |      INDEX RANGE SCAN             | ACC22_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|  21 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC24_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|* 22 |      INDEX RANGE SCAN             | ACC24_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|  23 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC31_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|* 24 |      INDEX RANGE SCAN             | ACC31_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|  25 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC33_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|* 26 |      INDEX RANGE SCAN             | ACC33_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
|  27 |     MAT_VIEW ACCESS BY INDEX ROWID| ACC35_ACCOUNT_DELTA |     1 |   145 |     1   (0)| 00:00:01 |
|* 28 |      INDEX RANGE SCAN             | ACC35_IND_CCMNN     |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("CN_MASTER"="T2"."CN")
   8 - access("CN_MASTER"="T2"."CN")
  10 - access("CN_MASTER"="T2"."CN")
  12 - access("CN_MASTER"="T2"."CN")
  14 - access("CN_MASTER"="T2"."CN")
  16 - access("CN_MASTER"="T2"."CN")
  18 - access("CN_MASTER"="T2"."CN")
  20 - access("CN_MASTER"="T2"."CN")
  22 - access("CN_MASTER"="T2"."CN")
  24 - access("CN_MASTER"="T2"."CN")
  26 - access("CN_MASTER"="T2"."CN")
  28 - access("CN_MASTER"="T2"."CN")
雖然看起來似乎會有些冗長,不過總體來看還是不錯的。因為我們確實需要TMP_CN06走全表掃描。
那麼我們再次嘗試這個過程,時間就變為了驚人的3秒。TMP_CN06表中有近10萬的記錄,也沒有走並行。
    create table test_201551214 as select t1.SECURITY_PHONE as MOBILE_PHONE, t1.SECURITY_EMAIL as OTHER_EMAIL,
  * t2.* from accstat.ACCOUNT_DELTA t1, bidata.TMP_CN06 t2 where t1.CN_MASTER = t2.CN;
Table created.
Elapsed: 00:00:03.27
所以從這個程度來看,物化檢視堆疊起來的檢視效能其實也差不了,用不好就會感覺差。也算是對物化檢視的一個重新認識吧。
這個問題其實之前有同事反饋過,當時也是思路全在物化檢視日誌上下功夫了,準備解析物化檢視日誌來做一個merge的操作,最後也是無功而返,也對物化檢視的操作產生了一些誤解,看來這種情況下,效能也照樣差不了。我已經試過水了,所以這種情況還是值得推廣的。

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

相關文章