並行查詢緩慢的問題分析

為了進一步驗證，使用sar,vmstat都進行了檢視，資源空閒率都在90%以上，沒有發現什麼異常的資源使用。
然後檢視系統程式，一般資源利用率較高的一些程式都會透過 top過濾出來，但是簡單觀察了一下，沒有發現大量的程式，從資料庫端來看，使用的session也不夠多，大概100個左右。
從這個層面來看，似乎可能是客戶端的網路延遲導致的問題，在之前碰到過類似的問題，但是我們不能把這種猜測的結果最終反饋給客戶。
我們看看同事提出的問題，他們執行的查詢是使用了Hint /*+parallel */ 來啟用並行查詢，但是似乎並行沒有生效或者啟用，導致他們的查詢響應速度很慢，所以從這個角度來看，問題可能出在並行的使用上。怎麼定位對應的session和sql_id，同事把使用的並行聯絡起來呢，
其實還是有一些指令碼可以方便我們的查詢。
使用如下的指令碼，能夠迅速的定位到並行的session,以及這些session正在執行的sql_id
sqlplus -s $DB_CONN_STR@$SH_DB_SID < set verify off
set line 200
set pages 200
col sess_id format a15
col osuser format a15
col machine format a20
col program format a25
col username format a15
col sql_id format a30      
select pxsess.sid||','||pxsess.serial# sess_id,sess.username,sess.osuser,sess.machine,sess.program,pxsess.qcsid,pxsess.qcserial#,pxsess.degree,pxsess.server#,pxsess.req_degree ,sess.sql_id from v\$px_session pxsess,v\$session sess where pxsess.sid=sess.sid ;      
EOF

別看指令碼這麼簡單，效果還是很明顯的。透過結果我們可以清晰的看到現在有一個並行查詢，是透過toad來出發的。請求的parallel是64，但是實際得到了50個並行度。查詢中使用了幾部分並行相關的子查詢，目前的情況下，啟用了100個並行。
SESS_ID         USERNAME        OSUSER          MACHINE              PROGRAM                        QCSID  QCSERIAL#     DEGREE    SERVER# REQ_DEGREE SQL_ID
--------------- --------------- --------------- -------------------- ------------------------- ---------- ---------- ---------- ---------- ---------- ------
8464,14527      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P084)          3388      44837         50         21         64 0p4usuf3c597z
8749,63529      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P085)          3388      44837         50         22         64 0p4usuf3c597z
8,13919         CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P086)          3388      44837         50         23         64 0p4usuf3c597z
290,39511       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P087)          3388      44837         50         24         64 0p4usuf3c597z
568,26681       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P088)          3388      44837         50         25         64 0p4usuf3c597z
853,7499        CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P089)          3388      44837         50         26         64 0p4usuf3c597z
1137,5081       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P090)          3388      44837         50         27         64 0p4usuf3c597z
1420,2709       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P091)          3388      44837         50         28         64 0p4usuf3c597z
1694,8533       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P092)          3388      44837         50         29         64 0p4usuf3c597z
1987,3413       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P093)          3388      44837         50         30         64 0p4usuf3c597z
2268,45371      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P094)          3388      44837         50         31         64 0p4usuf3c597z
2822,44037      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P095)          3388      44837         50         32         64 0p4usuf3c597z
3111,46169      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P096)          3388      44837         50         33         64 0p4usuf3c597z
3391,51889      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P097)          3388      44837         50         34         64 0p4usuf3c597z
3670,48247      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P098)          3388      44837         50         35         64 0p4usuf3c597z
3956,33001      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P099)          3388      44837         50         36         64 0p4usuf3c597z
3388,44837      CNVDBO4         xxxxxxx         TIT_C15-USER  TOAD.exe                        3388                                                          
101 rows selected.
如果想檢視更多關於sql語句的細節，可以直接在v$sql裡面抓取。
這100個並行是一個很明顯的問題，但是在如此配置的資料庫例項中也不至於把並行資源都用光了吧，使用show parameter parallel一檢視，設定的parallel_server_max是100個，這樣問題就很明顯了。
所以經過簡單溝通，把問題的情況說了一下，就把並行資源臨時調高了一些，增加到200個並行。
但是增加後檢視並行資源的使用，發現馬上並行資源就被耗光了。使用了200個，並行資源真是緊俏啊。而且是同一個session,同一個sql語句。
SESS_ID         USERNAME        OSUSER          MACHINE              PROGRAM                        QCSID  QCSERIAL#     DEGREE    SERVER# REQ_DEGREE SQL_ID
--------------- --------------- --------------- -------------------- ------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------
10,52273        CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P174)          3388      44837         50         25         50 0p4usuf3c597z
288,2837        CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P175)          3388      44837         50         26         50 0p4usuf3c597z
575,1303        CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P176)          3388      44837         50         27         50 0p4usuf3c597z
850,5315        CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P177)          3388      44837         50         28         50 0p4usuf3c597z
1136,30395      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P178)          3388      44837         50         29         50 0p4usuf3c597z
1421,56411      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P179)          3388      44837         50         30         50 0p4usuf3c597z
1700,36871      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P180)          3388      44837         50         31         50 0p4usuf3c597z
1977,8767       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P181)          3388      44837         50         32         50 0p4usuf3c597z
2263,12971      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P182)          3388      44837         50         33         50 0p4usuf3c597z
2543,5297       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P183)          3388      44837         50         34         50 0p4usuf3c597z
2827,18709      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P184)          3388      44837         50         35         50 0p4usuf3c597z
3112,3789       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P185)          3388      44837         50         36         50 0p4usuf3c597z
3393,51137      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P186)          3388      44837         50         37         50 0p4usuf3c597z
3668,28875      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P187)          3388      44837         50         38         50 0p4usuf3c597z
3952,25365      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P188)          3388      44837         50         39         50 0p4usuf3c597z
4240,18239      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P189)          3388      44837         50         40         50 0p4usuf3c597z
4519,15177      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P190)          3388      44837         50         41         50 0p4usuf3c597z
4799,7997       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P191)          3388      44837         50         42         50 0p4usuf3c597z
5085,44189      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P192)          3388      44837         50         43         50 0p4usuf3c597z
5362,35669      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P193)          3388      44837         50         44         50 0p4usuf3c597z
5649,11419      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P194)          3388      44837         50         45         50 0p4usuf3c597z
5924,44601      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P195)          3388      44837         50         46         50 0p4usuf3c597z
6215,6805       CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P196)          3388      44837         50         47         50 0p4usuf3c597z
6491,27213      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P197)          3388      44837         50         48         50 0p4usuf3c597z
6776,57337      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P198)          3388      44837         50         49         50 0p4usuf3c597z
7055,12061      CNVDBO4         xxxxxxx         TIT_C15-USER  oracle@ccbdbpxx (P199)          3388      44837         50         50         50 0p4usuf3c597z
3388,44837      CNVDBO4         xxxxxxx         TIT_C15-USER  TOAD.exe                        3388                                             0p4usuf3c597z
201 rows selected.
檢視sql語句是下面這樣的形式。
SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
SELECT /*+ FULL(s) PARALLEL (s,32) */ COUNT(*) amount
FROM new_resource s
WHERE resource_id IN(SELECT resource_value_id FROM new_resource_attributes WHERE resource_attribute_
id = 1 AND resource_attr_value = 'Y')
AND resource_id IN(SELECT resource_value_id FROM new_resource_attributes WHERE resource_attribute_id
= 11 AND resource_attr_value = 'O')
AND ((resource_id IN(SELECT resource_value_id FROM new_resource_attributes WHERE resource_attribute_
id = 13 AND resource_attr_value = '02') AND resource_id IN(SELECT resource_value_id FROM new_resourc
e_attributes WHERE resource_attribute_id = 15 AND resource_attr_value = '06'))
OR (resource_id IN(SELECT resource_value_id FROM new_resource_attributes WHERE resource_attribute_id
= 13 AND resource_attr_value = '06') AND resource_id IN(SELECT resource_value_id FROM new_resource_
attributes WHERE resource_attribute_id = 15 AND resource_attr_value = '02')))
AND resource_pool_id NOT IN(1,3)
AND resource_status = 'PORT OUT AG'
先不說並行的使用是否高效，但是一個查詢動用200個並行就已經很明顯是問題了。因為不是生產環境，問題的嚴重性還不算高，最後 經過確認是客戶的一個開發人員在使用，簡單溝通了終止了這個並行查詢，問題就解決了。
所以透過這個案例可以看到，並行查詢緩慢是由於另外一個意料之外的並行查詢導致的問題。並行查詢可以提高查詢速度 但是使用過當就會消耗大量的資源，同時也會影響別人。