一条大sql的调优-CSDN博客

这是某个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秒。