Оптимизировать запрос

ddelete
Дата: 05.11.2008 13:43:46
Есть вот такой запрос
with ma as 
(
select /*+choose*/ m.compcd,a.accode, a.ccy, m.house_client,a.totgrpbal, c.def_ccy, g.groupdesc, cu.collord, cu.intccy, m.acname1, m.acc_type, m.tb_group, m.eqaccgrp, 
			nvl(
                decode( m.tb_group,
                   '05',decode(sign( nvl(get_mend_consolidated_cash(m.compcd, m.eqaccgrp, 807, to_date('29-SEP-08','DD-MON-YY')),0)),-1,
			'05A:'||g.groupdesc||' DEBTORS', '05B:'||g.groupdesc||' CREDITORS'),
                   '07',decode(sign( nvl(get_mend_consolidated_cash(m.compcd, m.eqaccgrp, 807, to_date('29-SEP-08','DD-MON-YY')),0)),-1,
			'07A:'||g.groupdesc||' DEBTORS', '07B:'||g.groupdesc||' CREDITORS'),
                   '09',decode(sign( nvl(get_mend_consolidated_cash(m.compcd, m.eqaccgrp, 807, to_date('29-SEP-08','DD-MON-YY')),0)),-1,
			'09A:'||g.groupdesc||' DEBTORS', '09B:'||g.groupdesc||' CREDITORS'),
                   m.tb_group||':'||g.groupdesc
                        )
                ,' ') big_exp,
		nvl(m.eqaccgrp,a.accode) exp1, 
		nvl(a.accode,' ') exp2, 
		nvl(to_char(cu.collord,'00'),' ') exp3,
		nvl(substr(m.acname1,1,25),' ') exp4, 
		nvl(a.ccy,' ') exp5,
 		nvl(round(exchange_rates(to_date(to_date('29-SEP-08','DD-MON-YY')),c.def_ccy,a.ccy),7),0) exp6,
    decode(cu.intccy,'Y','Y',' ') exp7,
		nvl(a.accode,' ') ||' '|| nvl(substr(m.acname1,1,25),' ') exp8,
		nvl(m.acc_type,' ') exp9,
		nvl(m.house_client,' ') exp10
from acc_mgbal a, company c, currency cu, user_mast u,  sys_compgrp g, acc_mast m
where 
	a.compcd = u.compcd
	and	a.compcd = m.compcd
	and	a.compcd = c.compcd
	and	a.compcd = g.compcd
	and	a.accode = m.accode  
	and	a.ccy = cu.ccy
	and	m.tb_group = g.groupcd
	and	g.purpose = 'NOMTBGROUP'
	and	u.userid = uid
 	and	a.tradedt = to_date('29-SEP-08','DD-MON-YY')-- to_date('29-SEP-08','DD-MON-YY')    
)
select /*+choose USE_HASH(ma n) */ min(ma.big_exp), min(ma.exp1), min(ma.exp2), min(ma.exp3), min(ma.exp4), min(ma.exp5),
   		 nvl(ma.totgrpbal,0) ,
       round(sum(nvl(decode(n.contype,'FUT',n.var_margin2,0),0)),2),
			 sum(nvl(decode(n.contype,'FUT',n.var_margin2,0),0)),
			 round(sum(nvl(decode(n.contype,'OPT',n.var_margin2,0),0)),2),
			 sum(nvl(decode(n.contype,'OPT',n.var_margin2,0),0)),
       round(sum(nvl(decode(n.contype,'FX',n.var_margin2,0),0)),2),
       sum(nvl(decode(n.contype,'FX',n.var_margin2,0),0)),
            nvl(ma.totgrpbal,0) 
		+ round(sum(nvl(decode(n.contype,'FUT',n.var_margin2,0),0)) ,2)
		+ round(sum(nvl(decode(n.contype,'OPT',n.var_margin2,0),0)) ,2)
		+ round(sum(nvl(decode(n.contype,'FX',n.var_margin2,0),0)) ,2),
      min(ma.exp6),
      decode(sign(nvl(round(exchange_rates(to_date(to_date('29-SEP-08','DD-MON-YY')),ma.def_ccy,ma.ccy),0),0)),0,0,
			round( 
			(
       			nvl(ma.totgrpbal,0) 
			+ round(sum(nvl(decode(n.contype,'FUT',n.var_margin2,0),0)) ,2)
			+ round(sum(nvl(decode(n.contype,'OPT',n.var_margin2,0),0)) ,2)
			+ round(sum(nvl(decode(n.contype,'FX',n.var_margin2,0),0)),2)
			)
			/ nvl(round(exchange_rates(to_date(to_date('29-SEP-08','DD-MON-YY')),ma.def_ccy,ma.ccy),7),1)
			,2)  
		),
    min(ma.exp7), min(ma.exp8), min(ma.exp9), min(ma.exp10)
from ma , acc_nettpos n
where
	ma.compcd = n.compcd (+)
	and	ma.accode = n.accode (+)
	and	ma.ccy = n.ccy (+)
	and	n.tradedt(+) = to_date('29-SEP-08','DD-MON-YY')--to_date('29-SEP-08','DD-MON-YY')
 -- and	ma.ccy between :v_fccy and :v_lccy
	group 	by  ma.compcd, ma.accode, ma.totgrpbal, ma.eqaccgrp, ma.tb_group, ma.acc_type, ma.house_client,
		ma.acname1, ma.ccy, ma.intccy,
		nvl(to_char(ma.collord,'00'),' '), ma.groupdesc, ma.def_ccy
        having  ( (ma.house_client = 'N') OR
                (nvl(ma.totgrpbal,0) <> 0
        or      sum(nvl(decode(n.contype,'FUT',n.var_margin2,0),0))<> 0
        or      sum(nvl(decode(n.contype,'OPT',n.var_margin2,0),0))<> 0
        or      sum(nvl(decode(n.contype,'FX',n.var_margin2,0),0))<> 0
        or      sum(nvl(decode(n.contype,'PHY',n.var_margin2,0),0))<> 0 
	)
        )
union all
select /*+choose*/ min(ma.big_exp), min(ma.exp1), min(ma.exp2), min(ma.exp3), min(ma.exp4), min(ma.exp5),
			 	               0,
             				   0,
                      0,
                      0,
                      0,
                      0,
                      0,
                      0,
      min(ma.exp6),0,min(ma.exp7), min(ma.exp8), min(ma.exp9), min(ma.exp10)
      from acc_mgcalc am, ma
      where 
        am.compcd = ma.compcd
        and     am.accode = ma.accode
        and     am.ccy = ma.ccy
       
        and     am.tradedt = to_date('29-SEP-08','DD-MON-YY')
        and     ma.house_client != 'N'
        and     ma.totgrpbal = 0
	--	  	and	ma.ccy between :v_fccy and :v_lccy
				and 	NOT EXISTS (
                select  null
                from    acc_nettpos v
                where   v.accode = ma.accode
                and     v.ccy = ma.ccy
							  and			v.tradedt = to_date('29-SEP-08','DD-MON-YY')
                )
group   by  ma.compcd, ma.accode, ma.eqaccgrp, ma.tb_group, ma.acc_type, ma.house_client,
                ma.acname1, ma.ccy, ma.intccy,
                nvl(to_char(ma.collord,'00'),' '), ma.groupdesc, ma.def_ccy
having  sum(nvl(am.open_comm,0)) <> 0 
union  all   
   select  /*+choose*/ nvl(
                decode( m.tb_group,
                   '05',decode(sign( nvl(get_mend_consolidated_cash(m.compcd, m.eqaccgrp, 807, to_date('29-SEP-08','DD-MON-YY')),0)),-1,
                        '05A:'||g.groupdesc||' DEBTORS', '05B:'||g.groupdesc||' CREDITORS'),
                   '07',decode(sign( nvl(get_mend_consolidated_cash(m.compcd, m.eqaccgrp, 807, to_date('29-SEP-08','DD-MON-YY')),0)),-1,
                        '07A:'||g.groupdesc||' DEBTORS', '07B:'||g.groupdesc||' CREDITORS'),
                   '09',decode(sign( nvl(get_mend_consolidated_cash(m.compcd, m.eqaccgrp, 807, to_date('29-SEP-08','DD-MON-YY')),0)),-1,
                        '09A:'||g.groupdesc||' DEBTORS', '09B:'||g.groupdesc||' CREDITORS'),
                   m.tb_group||':'||g.groupdesc
                        )
                ,' '),
                nvl(m.eqaccgrp,a.accode) ,
                nvl(a.accode,' ') ,
                nvl(to_char(cu.collord,'00'),' '),
                nvl(substr(m.acname1,1,25),' ') ,
                nvl(a.ccy,' ') ,
                0,
                0,
                0,
                0,
                0,
                0,
                0,
                0,
                nvl(round(exchange_rates(to_date(to_date('29-SEP-08','DD-MON-YY')),c.def_ccy,a.ccy),7),0) ,
                0,
                decode(cu.intccy,'Y','Y',' '),
                nvl(a.accode,' ') ||' '|| nvl(substr(m.acname1,1,25),' '),
                nvl(m.acc_type,' '),
                nvl(m.house_client,' ')
        from    acc_trxc a, acc_mast m, sys_compgrp g, company c, currency cu, user_mast u
        where   a.compcd = u.compcd
        and     a.compcd = m.compcd
        and     a.compcd = c.compcd
        and     a.compcd = g.compcd
        and     a.accode = m.accode
	and	a.trxdate <= to_date('29-SEP-08','DD-MON-YY')
	and	a.repno > 807
        and     a.ccy = cu.ccy
        and     m.tb_group = g.groupcd
        and     g.purpose = 'NOMTBGROUP'
        and     u.userid = uid
		--		and	a.ccy between :v_fccy and :v_lccy
	and 	NOT EXISTS (
                select  null
                from    acc_mgbal v
                where   v.accode = a.accode
                and     v.ccy = a.ccy
		and	v.tradedt = to_date('29-SEP-08','DD-MON-YY')
		and	v.totgrpbal <> 0
                )
        and    a.batchno in (
                    select  j.batchno
                    from    jnl_input j
                    where   j.compcd = 'LON'
                    and     j.batchno = a.batchno
                    and     j.accode = a.accode
                    and     j.ccy = a.ccy
                    and     j.insprog = 'AM02'
                    )
        group   by  m.compcd, a.accode, m.eqaccgrp, m.tb_group, m.acc_type, m.house_client,
                m.acname1, a.ccy, cu.intccy,
                nvl(to_char(cu.collord,'00'),' '), g.groupdesc, c.def_ccy
        having  sum(nvl(a.trxvalue,0)) <> 0
	order by 1,2,3,4
Этот монстр был еще страшнее :) Скрутил как мог. Стало немного быстрее, но все равно скорость не приемлимая. Запрос возвращает 4 тыс строк, выполняется около 20 минут :(
План такой
Description    Object name    Cost  Cardinality   Bytes   Access predicates
SELECT STATEMENT, GOAL = HINT: CHOOSE		85	13	3100		
 TEMP TABLE TRANSFORMATION						
  SORT ORDER BY		88	13	3100		
   UNION-ALL						
    FILTER						
     SORT GROUP BY		10	11	2695		
      HASH JOIN OUTER		6	11	2695	"MA"."COMPCD"="N"."COMPCD"(+) AND "MA"."ACCODE"="N"."ACCODE"(+) AND "MA"."CCY"="N"."CCY"(+)	
       VIEW		2	11	2354		
        TABLE ACCESS FULL	SYS_TEMP_0FD9D6602_3652987A	2	11	1496		
       TABLE ACCESS BY INDEX ROWID	ACC_NETTPOS	3	11668	361708		
        INDEX RANGE SCAN	ACC_NETTPOS_IDX3	2	1		"N"."TRADEDT"(+)=TO_DATE('29-SEP-08','DD-MON-YY')	
    FILTER						
     SORT GROUP BY		13	1	262		
      FILTER						
       HASH JOIN		9	1	262	"AM"."COMPCD"="SYS_ALIAS_2"."COMPCD" AND "AM"."ACCODE"="SYS_ALIAS_2"."ACCODE" AND "AM"."CCY"="SYS_ALIAS_2"."CCY"	
        VIEW		2	11	2453		
         TABLE ACCESS FULL	SYS_TEMP_0FD9D6602_3652987A	2	11	1496		
        TABLE ACCESS BY INDEX ROWID	ACC_MGCALC	6	20	780		
         INDEX RANGE SCAN	ACC_MGCALC_IDX1	2	1		"AM"."TRADEDT"=TO_DATE('29-SEP-08','DD-MON-YY')	
       TABLE ACCESS BY INDEX ROWID	ACC_NETTPOS	3	1	18		
        INDEX RANGE SCAN	ACC_NETTPOS_IDX2	2	1		"V"."ACCODE"=:B1 AND "V"."TRADEDT"=TO_DATE('29-SEP-08','DD-MON-YY')	
    FILTER						
     SORT GROUP BY		60	1	143		
      FILTER						
       TABLE ACCESS BY INDEX ROWID	CURRENCY	2	1	9		
        NESTED LOOPS		56	1	143		
         NESTED LOOPS		54	1	134		
          HASH JOIN		53	1	126	"SYS_ALIAS_5"."COMPCD"="G"."COMPCD" AND "M"."TB_GROUP"="G"."GROUPCD"	
           INDEX RANGE SCAN	SYS_COMPGRP_IDX1	2	84	3612	"G"."PURPOSE"='NOMTBGROUP'	
           HASH JOIN		50	760	63080	"SYS_ALIAS_5"."COMPCD"="M"."COMPCD" AND "SYS_ALIAS_5"."ACCODE"="M"."ACCODE"	
            TABLE ACCESS BY INDEX ROWID	ACC_TRXC	8	1202	39666		
             NESTED LOOPS		10	1202	49282		
              TABLE ACCESS FULL	COMPANY	2	1	8		
              INDEX RANGE SCAN	ACC_TRXC_IDX3	3	24633		"SYS_ALIAS_5"."REPNO">807 AND "SYS_ALIAS_5"."COMPCD"="C"."COMPCD"	
               TABLE ACCESS BY INDEX ROWID	ACC_MGBAL	2	1	22		
                INDEX RANGE SCAN	ACC_MGBAL_IDX2	1	1		"V"."ACCODE"=:B1 AND "V"."TRADEDT"=TO_DATE('29-SEP-08','DD-MON-YY')	
            TABLE ACCESS FULL	ACC_MAST	38	9752	409584		
          INDEX RANGE SCAN	USER_MAST_IDX1	1	1	8	"U"."USERID"=UID@! AND "SYS_ALIAS_5"."COMPCD"="U"."COMPCD"	
         INDEX RANGE SCAN	CURRENCY_IDX1	1	1		"SYS_ALIAS_5"."CCY"="CU"."CCY"	
       TABLE ACCESS BY INDEX ROWID	JNL_INPUT	2	1	25		
        INDEX RANGE SCAN	JNL_INPUT_IDX2	1	1		"J"."BATCHNO"=:B1	
  RECURSIVE EXECUTION	SYS_LE_2_0					
Статистика свежая, собирал утром.
в таблицах строк:
acc_mgbal    6284209
company      1
currency       30
user_mast     62
sys_compgrp 3501
acc_mast      9766
acc_nettpos  10629389
acc_mgcalc   6587274
acc_trxc       4393224
jnl_input        184121
Версия
Oracle9i Release 9.2.0.4.0 - Production
Подскажите куда копать, чтобы оно пошустрее работало ?
ddelete
Дата: 05.11.2008 15:36:08
ну где же гуру оптимизации ? :(
Andrey.L
Дата: 05.11.2008 15:51:06
ddelete,

1. sum(nvl()) замени на nvl(sum()) - сильного ускорения не будет, а так по-мелочи
2. to_date(to_date( - тоже некрасиво
3. Убедись что тормозов нет от "самописных" функций
4. Попробуй выяснить в каком из union'ов самые тормоза
5. Как запрос себя ведет без oreder by
ddelete
Дата: 05.11.2008 16:16:12
Здравствуйте Андрей ;)
Andrey.L
ddelete,

1. sum(nvl()) замени на nvl(sum()) - сильного ускорения не будет, а так по-мелочи
2. to_date(to_date( - тоже некрасиво
3. Убедись что тормозов нет от "самописных" функций
4. Попробуй выяснить в каком из union'ов самые тормоза
5. Как запрос себя ведет без oreder by


1. поменял
2. там была переменная подстановки, а я ее просто заменил на to_date(...), по этому получилось два раза.
3. сейчас попытаюсь разобрать, что там вообще в функции творится. Может можно вообще от нее избавиться.
4. второй и третий union самые тормознутый
5. никакого ускорения

зы. как всегда дали тормознутый монстряческий селект, без всякой инфы, что он вообще делает :(
Vint
Дата: 05.11.2008 16:37:04
ddelete

1. пробовали убрать хинты? какой план без них? (кстати откуда план? надеюсь с трейса.....)
2. за сколько отрабатывает запрос ma? и за сколько отрабатывает каждая часть в отдельности. какова селективность отдельных частей.
3. TABLE ACCESS FULL ACC_MAST --какая селективность из этой таблицы? может быть нужен индекс?
4. за сколько отрабатывает без сортировки?(хотя при таком количестве данных думаю это не сильно важно.)
5. тескт индекса ACC_NETTPOS_IDX3 таблицы ACC_NETTPOS.
6. такие конструкции можно наверное переписать как то так...
or sum(nvl(decode(n.contype,'FUT',n.var_margin2,0),0))<> 0
or sum(nvl(decode(n.contype,'OPT',n.var_margin2,0),0))<> 0
or sum(nvl(decode(n.contype,'FX',n.var_margin2,0),0))<> 0
or sum(nvl(decode(n.contype,'PHY',n.var_margin2,0),0))<> 0

....
sum(case when n.contypein (...) then nvl(n.var_margin2,0) END)<>0
Andrey.L
Дата: 05.11.2008 16:41:41
ddelete,

6. IN () попробуй заменить на EXISTS или JOIN (если данные не размножатся)
7. в секции group by nvl(to_char(cu.collord,'00'),' ') замени на cu.collord
8. Хинты пока убери, пусть oracle подумает
Timm
Дата: 05.11.2008 16:43:46
FAQ
Andrey.L
Дата: 05.11.2008 16:45:13
Vint
sum(case when n.contypein (...) then nvl(n.var_margin2,0) END)<>0

Лучше уж так:
nvl(sum(case when n.contypein (...) then n.var_margin2 END),0)<>0
Vint
Дата: 05.11.2008 16:52:31
Andrey.L,
ну я не стал углублятся... но вообше еще лучше заменить на sum(case when n.contypein (...) then n.var_margin2 END) is not null
да и судя по запросу в целом он писался под восьмерку.. или человеком который восьмерку любит сильно..... особенно decode(sign( nvl( вот это нравится))) но сначала ответу на мои вопросы выше. потом уже рюшечки.
Andrey.L
Дата: 05.11.2008 16:56:39
Vint
Andrey.L,
ну я не стал углублятся... но вообше еще лучше заменить на sum(case when n.contypein (...) then n.var_margin2 END) is not null

В общем случае это разные вещи. Ноль все-таки IS NOT NULL.
Vint
но сначала ответы на мои вопросы выше. потом уже рюшечки.

Согласен.