最近生產發現有一個sql語句執行耗時達5000多秒。
抓出來sql_id一看,sql倒不是一個很長的語句。結構也很簡單。如下。
select company_code, sap_company_id
from data_company_code
where company_code not in
(SELECT distinct l9_company_code
FROM detailed_data_info_v a, refund_request b
WHERE a.financial_activity = 'RFND'
and a.refund_method = 'AP'
AND a.refund_id = b.refund_id
AND b.refund_status = 'P'
AND b. REVERSAL_TRANS_ID is null
AND posting_date = TO_DATE(20140511, 'YYYYMMDD'))

執行計劃如下：
Execution Plan
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2696K(100)| | | |
| 1 | FILTER | | | | | | | |
| 2 | MAT_VIEW ACCESS FULL | DATA_COMPANY_CODE | 5 | 35 | 3 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 68 | 1078K (2)| 03:35:43 | | |
| 5 | PARTITION RANGE ALL | | 1 | 59 | 1078K (2)| 03:35:43 | 1 | 366 |
| 6 | TABLE ACCESS FULL | DETAILED_DATA | 1 | 59 | 1078K (2)| 03:35:43 | 1 | 366 |
| 7 | INDEX UNIQUE SCAN | REFUND_REQUEST_PK | 1 | | 1 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID| REFUND_REQUEST | 1 | 9 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------

檢視最後的輸出表data_company_code,發現是一個資料字典表，裡面的資料很少。只有5條。
表 detailed_data_info_v是一個檢視，裡面參照的基表只有1個. detailed_data_info 它是一個歷史表，裡面有3億多條資料。而且對應的主鍵在查詢條件中也沒有，這也是這個sql執行慢的主要原因。
表 REFUND_REQUEST 是一個應用表，裡面的資料就幾百條。
明白了大概的情況之後。

首先從檢視下手。看看
a.refund_method , a.refund_id,company_code都運用了大量的decode,可以看到都是基於financial_activity來做的過濾，所以直接可以提出其他的條件過濾，直接使用基表來去得所需的條件。

DECODE( FINANCIAL_ACTIVITY,
'RFND',
DATA_FIELD_1) as REFUND_ID

DECODE( FINANCIAL_ACTIVITY,
'RFND',
DATA_FIELD_3) as REFUND_METHOD

DECODE( FINANCIAL_ACTIVITY,
'RFND',
DATA_FIELD_10 ,
'WERRE',
DATA_FIELD_10 ,
。。。。。。。
DATA_FIELD_19 ,
'BCK',
DATA_FIELD_20 ,
'DSPCAN',
DATA_FIELD_7 ,
'DSPREJ',
DATA_FIELD_7 ,
'WER',
DATA_FIELD_7 ,
'WWER',
DATA_FIELD_7 ,
'DD',
DATA_FIELD_7 ,
'reer',
DATA_FIELD_7 ,
'tttt',
DATA_FIELD_8 ,
'xxxx',
DATA_FIELD_9) as COMPANY_CODE

所以把查詢可以構造成幾個子查詢。黃色是做了變化的部分。
select a.DATA_FIELD_10 l9_company_code
from DETAILED_DATA a
where a.financial_activity = 'RFND'
and a.DATA_FIELD_3 = 'AP'
and a.posting_date = TO_DATE(20140511, 'YYYYMMDD')

另外一個子查詢。
select refund_id
from ar1_refund_request b
where b.refund_status='P'
and b.reversal_trans_id is null
/
no rows selected

結果已查詢，讓我大跌眼鏡，竟然沒有匹配的值。但是sql語句還是會不斷的去做無用功。查了半天，結果返回了一個Null。

找到了基本的方向，如果查詢條件中沒有匹配的值，至少可以不用再從3億多條記錄的表裡去全表掃描了。
在測試下面的查詢時,如果遮蔽掉條件a.financial_activity = 'RFND',查詢就會直接先進入refund_request了。
SQL> select
distinct a.DATA_FIELD_10 l9_company_code
from DETAILED_DATA a
where a.DATA_FIELD_3 = 'AP'
--and a.financial_activity = 'RFND'
and a.posting_date = TO_DATE(20140512, 'YYYYMMDD')
and exists (select 1
from refund_request b
where b.refund_id = a.DATA_FIELD_1
and b.refund_status = 'P'
and b.reversal_trans_id is null)
/
no rows selected
Elapsed: 00:00:00.01

如果沒有資料，馬上就返回了。類似於這樣的方式
select xxx from huge_table where 1!=1

但是已加入條件financial_activity就開始掃描大表，看來只能使用Hint來強制指定表的訪問順序了。當然了使用hint也是玩不得以而為之。不建議一開始調就考慮hint.
SQL> select company_code, sap_company_id
from ar9_company_code
where company_code not in
(select /*+leading(b,a)*/
distinct a.DATA_FIELD_10 l9_company_code --,financial_activity,DATA_FIELD_3,posting_date
from AR1_GL_DETAILED_DATA a
where a.DATA_FIELD_3 = 'AP'
and a.financial_activity = 'RFND'
and a.posting_date = TO_DATE(20140512, 'YYYYMMDD')
and exists (select 1
from ar1_refund_request b
where b.refund_id = a.DATA_FIELD_1
and b.refund_status = 'P'
and b.reversal_trans_id is null))
SQL> /
COMPAN SAP_COMPANY_ID
------ --------------
AE 1010
XX 1068
XXE 1067
DS 1027
EER 1019
Elapsed: 00:00:00.01

使用hint來調優sql語句

相關文章