一條大sql的調優

這是某個DW應用裡的大sql,原來據說5分鐘就執行完了，現在居然要5個小時，看看情況吧:

SELECT r_Time.time_id,

ae.accounting_entity_id,

cs.vendor_id,

ai.account_item_id,

ta.target_account_id,

rf.previous_balance PERIOD_BEGIN_BALANCES,

rf.balance PERIOD_net_BALANCES

FROM (

-- Part 1/2, Uncleared & overdue documents when it's calaculation day

SELECT bsik.shkzg,

bsik.dmbtr,

bsik.zfbdt,

bsik.bukrs,

bsik.hkont,

bsik.source_code,

bsik.blart,

bsik.lifnr,

bsik.gjahr,

bsik.monat,

bsik.belnr,

bsik.augdt,

bsik.budat

FROM myods_bsik bsik

WHERE

-- calaculation day is later than GL date

bsik.budat < to_date(to_char(r_Time.time_id), 'yyyymmdd') + 1

UNION ALL

-- Part 2/2, Althouth already cleared now, still uncleared & overdue documents

-- when it's calaculation day

SELECT bsak.shkzg,

bsak.dmbtr,

bsak.zfbdt,

bsak.bukrs,

bsak.hkont,

bsak.source_code,

bsak.blart,

bsak.lifnr,

bsak.gjahr,

bsak.monat,

bsak.belnr,

bsak.augdt,

bsak.budat

FROM myods_bsak bsak

WHERE

bsak.budat < to_date(to_char(r_Time.time_id), 'yyyymmdd') + 1

AND bsak.augdt >= to_date(to_char(p_time_id) ,'yyyymmdd')

) bsak

,

target_account_dim ta,

accounting_item_dim ai,

accounting_entity_dim ae,

vendor_dim cs,

(SELECT ag.aging_id,

ag.aging_name,

to_number(ag.attribute1) start_days,

nvl(to_number(ag.attribute2), 1e100) end_days

FROM dwdim.aging_dim ag

WHERE ag.aging_id >= 1002) ag,

(select *

from my_report_01_fact rf

where rf.time_id = r_Time.time_id) rf

WHERE ta.corporation_code = CASE(bsak.bukrs) WHEN '1000' THEN('E2100') ELSE('E2000') END

AND ta.mapping_account_code = bsak.hkont

AND cs.vendor_code = bsak.lifnr

AND ta.standard_account_code = ai.standard_account_code

AND ae.corporation_code = bsak.source_code

and rf.vendor_id = cs.vendor_id(+)

and rf.accounting_entity_id = ae.accounting_entity_id(+)

and rf.target_account_id = ta.target_account_id(+)

and rf.account_item_id = ai.account_item_id(+)

AND ta.set_of_books_code = '2'

AND CS.CORP_CODE = '81000'

AND cs.set_of_books_code = '2'

and (to_date(to_char(r_Time.time_id), 'yyyymmdd') - bsak.budat) between ag.start_days and ag.end_days

group by ae.accounting_entity_id,

cs.vendor_id,

ai.account_item_id,

ta.target_account_id,

rf.previous_balance,

rf.balance

簡單看了一下，用了這麼多左連線，想快都難。缺兩個索引，先把bsak.budat和rf.time_id建上索引，執行速度降為10分鐘，從資料量上來看bsik表資料量非常小，只幾萬條，但v$session_longops裡卻顯示tsc速度非常慢，檢視其塊數，hwm下差不多幾萬個資料塊,跟記錄數基本上1:1的關係。暈倒，難道用了insert/*+append*/?不管它了，alter table bsik move;一把吧，塊數降到了1000，再執行,50秒。

[@more@]