今天在生產中發現一條sql語句消耗了大量的cpu資源。使用top -c來檢視。
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17895 oratestdb 25 0 12.4g 217m 38m R 99.9 0.1 1232:43 oracleTESTDB (LOCAL=NO)
12318 oratestdb 18 0 12.2g 35m 28m R 54.0 0.0 10:47.89 oracleTESTDB (LOCAL=NO)
26316 oratestdb 18 0 12.3g 118m 28m D 21.2 0.1 0:40.33 oracleTESTDB (LOCAL=NO)

檢視程式對應的session正在執行的sql語句。
看這條語句倒也不復雜，account_id對應的是主鍵，查詢應該是毫秒級的，但是檢視生產中執行的效率，平均在3-5秒左右。
SELECT TEST_TEST_SUBSCRIBER_FA_V.MOBILE_NO, TEST_ACCOUNT.ACCOUNT_ID FROM TEST_ACCOUNT ,TEST_TEST_SUBSCRIBER_FA_V
WHERE TEST_ACCOUNT.ACCOUNT_ID = TEST_TEST_SUBSCRIBER_FA_V.BAN AND TEST_ACCOUNT.ACCOUNT_ID = xxxxx

檢視這條語句對應的執行計劃資訊，如下：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6b1yv0ucyjsk3, child number 0
-------------------------------------
/* TEST_TESTGetSubscriberNumber_selectGetMobileNo_0 */ SELECT
TEST_TEST_SUBSCRIBER_FA_V.MOBILE_NO, TEST_ACCOUNT.ACCOUNT_ID FROM
TEST_ACCOUNT ,TEST_TEST_SUBSCRIBER_FA_V WHERE TEST_ACCOUNT.ACCOUNT_ID =
TEST_TEST_SUBSCRIBER_FA_V.BAN AND TEST_ACCOUNT.ACCOUNT_ID = :1

Plan hash value: 1685959897

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 56898 (100)| |
| 1 | NESTED LOOPS | | 536K| 61M| | 56898 (1)| 00:11:23 |
|* 2 | INDEX UNIQUE SCAN | TEST_ACCOUNT_PK | 1 | 6 | | 1 (0)| 00:00:01 |
| 3 | VIEW | TEST_TEST_SUBSCRIBER_FA_V |536K| 58M| | 56897 (1)| 00:11:23 |
| 4 | SORT UNIQUE | | 536K| 98M| 113M| 56897 (100)| 00:11:23 |
| 5 | UNION-ALL | | | | | | |
| 6 | NESTED LOOPS | | | | | | |
| 7 | NESTED LOOPS | | 1 | 89 | | 3 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 31 | | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | TEST_PAY_CHANNEL_1IX | 1 | 12 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| TEST_DISTRIBUTE | 1 | 19 | | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | TEST_DISTRIBUTE_3IX | 1 | | | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 58 | | 1 (0)| 00:00:01 |
|* 14 | VIEW | | 536K| 98M| | 34296 (1)| 00:06:52 |
|* 15 | WINDOW SORT PUSHED RANK | | 536K| 45M| 51M| 34296 (1)| 00:06:52 |
|* 16 | HASH JOIN | | 536K| 45M| 28M| 23341 (1)| 00:04:41 |
| 17 | INDEX FULL SCAN | TEST_PAY_CHANNEL_1IX | 1259K| 14M| | 1254 (1)| 00:00:16 |
|* 18 | HASH JOIN | | 536K| 39M| 26M| 18391 (1)| 00:03:41 |
|* 19 | TABLE ACCESS FULL | SUBSCRIBER | 397K| 21M| | 8527 (1)| 00:01:43 |
|* 20 | TABLE ACCESS FULL | TEST_DISTRIBUTE | 1696K| 30M| | 6046 (1)| 00:01:13 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TEST_ACCOUNT"."ACCOUNT_ID"=TO_NUMBER(:1))
9 - access("CPC"."BAN"=TO_NUMBER(:1))
10 - filter("EG_DIST_TYPE"='D')
11 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN" AND "ED"."EXPIRATION_DATE" IS NULL)
filter("ED"."EXPIRATION_DATE" IS NULL)
12 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
13 - filter(("SUBSCRIBER"."SUB_STATUS"<>'C' AND "SUBSCRIBER"."SUB_STATUS"<>'L' AND
"SUBSCRIBER"."SUB_STATUS"<>'T'))
14 - filter(("RANK"=1 AND "BAN"=TO_NUMBER(:1)))
15 - filter(ROW_NUMBER() OVER ( PARTITION BY "SUBSCRIBER"."SUBSCRIBER_NO" ORDER BY
"ED"."EXPIRATION_DATE")<=1)
16 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN")
18 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
19 - filter(("SUBSCRIBER"."SUB_STATUS"='C' OR "SUBSCRIBER"."SUB_STATUS"='L' OR
"SUBSCRIBER"."SUB_STATUS"='T'))
20 - filter("EG_DIST_TYPE"='D')

從執行計劃可以看出，瓶頸就在於有兩個表走了全表掃描。那兩個表資料也不少。
統計資訊如下：
Elapsed: 00:00:03.53

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
96346 consistent gets
0 physical reads
0 redo size
613 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

其中TEST_TEST_SUBSCRIBER_FA_V 是一個檢視，裡面使用了Union,（另外關於這個union的地方和開發確認過，暫時還不能改為union all)。
把檢視的內容填進去，sql語句就成了如下的樣子，在資料庫裡執行的時候也基本是這個樣子的。對於在查詢中沒有用到的欄位都給註釋掉了。標註為灰色。
SELECT MOBILE_NO, TEST_ACCOUNT.ACCOUNT_ID
FROM
(
SELECT cpc.ban,
subscriber.prim_resource_val MOBILE_NO,
-- subscriber.init_act_date,
-- SUBSCRIBER.CUSTOMER_ID,
-- subscriber.subscriber_no,
-- SUBSCRIBER.SUBSCRIBER_TYPE,
-- SUBSCRIBER.SUB_STATUS,
-- SUBSCRIBER.SUB_STS_RSN_CD,
-- SUBSCRIBER.SUB_STATUS_DATE,
-- SUBSCRIBER.EFFECTIVE_DATE,
1 RANK
FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc
WHERE cpc.pym_channel_no = ed.target_pcn
AND ed.agreement_no = subscriber.subscriber_no
AND eg_dist_type = 'D'
AND ed.expiration_date IS NULL
AND SUBSCRIBER.SUB_STATUS NOT IN ('C', 'L', 'T')
)temp, TEST_ACCOUNT
where TEST_ACCOUNT.account_id=temp.ban and TEST_ACCOUNT.account_id=10001245
UNION
SELECT MOBILE_NO, TEST_ACCOUNT.ACCOUNT_ID
FROM
(
SELECT "BAN","PRIM_RESOURCE_VAL" MOBILE_NO--,"INIT_ACT_DATE","CUSTOMER_ID","SUBSCRIBER_NO","SUBSCRIBER_TYPE","SUB_STATUS","SUB_STS_RSN_CD","SUB_STATUS_DATE","EFFECTIVE_DATE","RANK"
FROM (SELECT cpc.ban,
subscriber.prim_resource_val ,
-- subscriber.init_act_date,
-- SUBSCRIBER.CUSTOMER_ID,
-- subscriber.subscriber_no,
-- SUBSCRIBER.SUBSCRIBER_TYPE,
-- SUBSCRIBER.SUB_STATUS,
-- SUBSCRIBER.SUB_STS_RSN_CD,
-- SUBSCRIBER.SUB_STATUS_DATE,
-- SUBSCRIBER.EFFECTIVE_DATE,
ROW_NUMBER ()
OVER (
PARTITION BY subscriber.subscriber_no
ORDER BY
ed.expiration_date, subscriber.subscriber_no DESC)
AS RANK
FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc
WHERE cpc.pym_channel_no = ed.target_pcn
AND ed.agreement_no = subscriber.subscriber_no
AND eg_dist_type = 'D'
AND SUBSCRIBER.SUB_STATUS IN ('C', 'L', 'T'))
WHERE RANK = 1
)temp, TEST_ACCOUNT
where TEST_ACCOUNT.account_id=temp.ban and TEST_ACCOUNT.account_id=10001245

檢視走全表掃描的兩個表，TEST_pay_channel上有一個索引列中含有ban,從如上的執行計劃中看出，沒有嘗試走索引，而是走了全表掃描，受此影響，TEST_distribute 也做了全表掃描。
根據查詢條件TEST_ACCOUNT.account_id和TEST_pay_channel的ban是關聯的，如果可以走索引的話，效率會大大提高。
TEST_PAY_CHANNEL和 TEST_DISTRIBUTE 中索引的資訊如下：
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
TEST_PAY_CHANNEL_1IX INDXS01 NORMAL NONUNIQUE NO BAN,BEN, CHANNEL_NO TABLE VALID 1229972 01-JUL-14 N
TEST_DISTRIBUTE_3IX INDXS01 NORMAL NONUNIQUE NO TARGET_PCN,AGR_NO,EFFECT_DATE TABLE VALID 1597330 01-JUL-14 N

如果根據ban來關聯的話，至少可以走一個range index scan或者skip/scan了.
如果可以走索引的話。條件 “cpc.pym_channel_no = ed.target_pcn”對應的索引就可以啟用了，test_distribute表也就不走全表掃描了,可以走range scan/或者skip scan了。

現在的工作就是來看看能不能不改動邏輯來看看能做些什麼。
改動後的sql語句如下。首先是棄用了原有的檢視。然後在union的兩個子查詢中使用TEST_ACCOUNT來和TEST_PAY_CHANNEL來做關聯。雖然關聯的表多了一個，但是因為都可以走索引，比全表掃描要效率高很多了。

SELECT MOBILE_NO, ACCOUNT_ID
FROM
(
select prim_resource_val MOBILE_NO,ACCOUNT_ID,rank
from (
SELECT cpc.ban,TEST_ACCOUNT.ACCOUNT_ID,
subscriber.prim_resource_val,
1 RANK
FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc,TEST_ACCOUNT
WHERE cpc.pym_channel_no = ed.target_pcn
AND ed.agreement_no = subscriber.subscriber_no
AND eg_dist_type = 'D'
AND ed.expiration_date IS NULL
AND SUBSCRIBER.SUB_STATUS NOT IN ('C', 'L', 'T')
AND TEST_ACCOUNT.ACCOUNT_ID = cpc.BAN AND TEST_ACCOUNT.ACCOUNT_ID = 10001245
)TEST_TEST_SUBSCRIBER
UNION
select TEST_TEST_SUBSCRIBER.prim_resource_val MOBILE_NO,TEST_TEST_SUBSCRIBER.ACCOUNT_ID,rank
from
(SELECT cpc.ban, subscriber.prim_resource_val, TEST_ACCOUNT.ACCOUNT_ID ,
ROW_NUMBER ()
OVER (
PARTITION BY subscriber.subscriber_no
ORDER BY
ed.expiration_date, subscriber.subscriber_no DESC)
AS RANK
FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc,TEST_ACCOUNT
WHERE cpc.pym_channel_no = ed.target_pcn
AND ed.agreement_no = subscriber.subscriber_no
AND eg_dist_type = 'D'
AND SUBSCRIBER.SUB_STATUS IN ('C', 'L', 'T')
AND TEST_ACCOUNT.ACCOUNT_ID = cpc.BAN AND TEST_ACCOUNT.ACCOUNT_ID =10001245
) TEST_TEST_SUBSCRIBER
) where rank=1

來看看執行計劃和統計資訊和執行速度。
  首先是執行速度，用了0.01秒，相比原來的3秒左右提高了不少，毫秒級的速度。
  cpu的消耗一下子到了11，提高了很多倍
  兩個全本走全表掃描的表，現在都走了index range scan.
  統計資訊的提升，也提高了很多。從原來的96346降低到了目前的23

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1502756759
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 256 | 11 (28)| 00:00:01 |
| 1 | VIEW | | 2 | 256 | 11 (28)| 00:00:01 |
| 2 | SORT UNIQUE | | 2 | 184 | 11 (64)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 1 | 56 | 4 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 37 | 3 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 18 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | TEST_ACCOUNT_PK | 1 | 6 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | TEST_PAY_CHANNEL_1IX | 1 | 12 | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | TEST_DISTRIBUTE | 1 | 19 | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | TEST_DISTRIBUTE_3IX | 1 | | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 19 | 1 (0)| 00:00:01 |
|* 14 | VIEW | | 1 | 128 | 5 (20)| 00:00:01 |
|* 15 | WINDOW SORT PUSHED RANK | | 1 | 56 | 5 (20)| 00:00:01 |
| 16 | NESTED LOOPS | | | | | |
| 17 | NESTED LOOPS | | 1 | 56 | 4 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 37 | 3 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 18 | 2 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | TEST_ACCOUNT_PK | 1 | 6 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | TEST_PAY_CHANNEL_1IX | 1 | 12 | 1 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID| TEST_DISTRIBUTE | 1 | 19 | 1 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | TEST_DISTRIBUTE_3IX | 1 | | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 19 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("TEST_ACCOUNT"."ACCOUNT_ID"=10001245)
9 - access("CPC"."BAN"=10001245)
10 - filter("EG_DIST_TYPE"='D')
11 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN" AND "ED"."EXPIRATION_DATE" IS NULL)
filter("ED"."EXPIRATION_DATE" IS NULL)
12 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
13 - filter("SUBSCRIBER"."SUB_STATUS"<>'C' AND "SUBSCRIBER"."SUB_STATUS"<>'L' AND
"SUBSCRIBER"."SUB_STATUS"<>'T')
14 - filter("RANK"=1)
15 - filter(ROW_NUMBER() OVER ( PARTITION BY "SUBSCRIBER"."SUBSCRIBER_NO" ORDER BY
"ED"."EXPIRATION_DATE")<=1)
20 - access("TEST_ACCOUNT"."ACCOUNT_ID"=10001245)
21 - access("CPC"."BAN"=10001245)
22 - filter("EG_DIST_TYPE"='D')
23 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN")
24 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
25 - filter("SUBSCRIBER"."SUB_STATUS"='C' OR "SUBSCRIBER"."SUB_STATUS"='L' OR
"SUBSCRIBER"."SUB_STATUS"='T')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
23 consistent gets
3 physical reads
0 redo size
613 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

有了一定的資料保證，就需要在測試環境中進行了模擬測試，可以比對是否和預期的一樣有極大的提升。

循序漸進調優union相關的sql

相關文章