ITPub博客

首页 > 数据库 > 数据库开发技术 > 一条大sql的调优

一条大sql的调优

原创 数据库开发技术 作者:gengyonghui 时间:2007-11-01 10:21:17 0 删除 编辑

这是某个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.budatrf.time_id建上索引,执行速度降为10分钟,从数据量上来看bsik表数据量非常小,只几万条,但v$session_longops里却显示tsc速度非常慢,查看其块数,hwm下差不多几万个数据块,跟记录数基本上1:1的关系。晕倒,难道用了insert/*+append*/?不管它了,alter table bsik move;一把吧,块数降到了1000,再执行,50秒。

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/112417/viewspace-980335/,如需转载,请注明出处,否则将追究法律责任。

上一篇: imp导致撤销段满
请登录后发表评论 登录
全部评论
  • 博文量
    63
  • 访问量
    281290