Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Oracle > Как оптимизировать запрос

Автор: Elnarka 29.4.2008, 08:31

Здравствуйте.У меня есть вот такой запрос:

Код


select cc.id_account,tt.id_contracts,sum(tt.val) val
from 
(
select distinct c.id_contracts ,pr.saldoprcmain as val
  from 
       dwh.fct_charge_prc    pr,
       dwh.con_ass_contr_acc con,
       dwh.det_contracts c ,
       dwh.con_ass_acc       ac,
       dwh.det_ord_liab      l,
       dwh.det_source sr
       
       
 where
   ac.id_account_parent = 11398446
   and '20.04.2008' BETWEEN ac.dt_open and ac.dt_close
   and con.id_account = ac.id_account_parent
   and '20.04.2008' BETWEEN con.dt_open and con.dt_close
   and con.id_tacc = 61282
   and c.id_contracts = con.id_contracts 
   and (('20.04.2008' between c.date_open and c.date_closed)
        or c.date_closed is null)
   and c.id_source = sr.id_source
   and sr.code_source = 'Loans'
   and pr.id_contracts = c.id_contracts 
   AND '20.04.2008' BETWEEN pr.DT_OPEN AND pr.DT_CLOSE
   and l.id_ord_liab = pr.id_ord_liab
   and l.code_ord_liab like '%fl%'
)tt 
join 
dwh.con_ass_contr_acc cc
on  
tt.id_contracts = cc.id_contracts
where 
'20.04.2008' between cc.dt_open and cc.dt_close

group by cc.id_account,tt.id_contracts

Никак не выполняется.
План таков.

Код


SELECT STATEMENT, GOAL = ALL_ROWS            Cost=506    Cardinality=81    Bytes=3807
 SORT GROUP BY                  Cost=506     Cardinality=81                   Bytes=3807
  TABLE ACCESS BY INDEX ROWID          bject name=CON_ASS_CONTR_ACC    Cost=363    Cardinality=81    Bytes=2268
   NESTED LOOPS            Cost=504    Cardinality=81    Bytes=3807
    VIEW    Object owner=ELNARA        Cost=141    Cardinality=1    Bytes=19
     SORT UNIQUE            Cost=141    Cardinality=1    Bytes=172
      MERGE JOIN CARTESIAN            Cost=139    Cardinality=23    Bytes=3956
       NESTED LOOPS            Cost=11    Cardinality=1    Bytes=150
        NESTED LOOPS            Cost=9    Cardinality=1    Bytes=131
         NESTED LOOPS            Cost=7    Cardinality=1    Bytes=74
          NESTED LOOPS            Cost=6    Cardinality=1    Bytes=61
           TABLE ACCESS BY INDEX ROWID        Object name=CON_ASS_CONTR_ACC    Cost=5    Cardinality=1    Bytes=33
            INDEX RANGE SCAN                  Object name=AK_ASS_CONTR    Cost=3    Cardinality=1    
           TABLE ACCESS BY INDEX ROWID        Object name=DET_CONTRACTS    Cost=1    Cardinality=1    Bytes=28
            INDEX UNIQUE SCAN        Object name=PK_DET_CONTRACTS        Cardinality=1    
          TABLE ACCESS BY INDEX ROWID        Object name=DET_SOURCE    Cost=1    Cardinality=1    Bytes=13
           INDEX UNIQUE SCAN        Object name=PK_DET_SOURCE        Cardinality=1    
         TABLE ACCESS BY GLOBAL INDEX ROWID        Object name=FCT_CHARGE_PRC    Cost=2    Cardinality=1    Bytes=57
          INDEX RANGE SCAN    Object name=AK_CHARGE_PRC_FCT_CHAR    Cost=1    Cardinality=15    
        TABLE ACCESS BY INDEX ROWID        Object name=DET_ORD_LIAB    Cost=2    Cardinality=1    Bytes=19
         INDEX UNIQUE SCAN        Object name=PK_DET_ORD_LIAB    Cost=1    Cardinality=1    
       BUFFER SORT            Cost=137    Cardinality=215    Bytes=4730
        TABLE ACCESS BY INDEX ROWID        Object name=CON_ASS_ACC    Cost=128    Cardinality=215    Bytes=4730
         INDEX RANGE SCAN        Object name=RELATION_11401_FK    Cost=2    Cardinality=215    
    INDEX RANGE SCAN        Object name=RELATION_11430_FK    Cost=2    Cardinality=81

Вобще.Где можно получить хорошую информацию про оптимизацию?Несовсем понимаю

Автор: DimW 29.4.2008, 09:30

1) дайте информацию о количестве строк в каждой из таблиц:
dwh.fct_charge_prc
dwh.con_ass_contr_acc
dwh.det_contracts
dwh.con_ass_acc
dwh.det_ord_liab
dwh.det_source

2) сколько строк возвращает этот селест:

Код


select distinct c.id_contracts
                       ,pr.saldoprcmain as val
          from dwh.fct_charge_prc    pr
              ,dwh.con_ass_contr_acc con
              ,dwh.det_contracts     c
              ,dwh.con_ass_acc       ac
              ,dwh.det_ord_liab      l
              ,dwh.det_source        sr
        
         where ac.id_account_parent = 11398446
           and '20.04.2008' BETWEEN ac.dt_open and ac.dt_close
           and con.id_account = ac.id_account_parent
           and '20.04.2008' BETWEEN con.dt_open and con.dt_close
           and con.id_tacc = 61282
           and c.id_contracts = con.id_contracts
           and (('20.04.2008' between c.date_open and c.date_closed) or c.date_closed is null)
           and c.id_source = sr.id_source
           and sr.code_source = 'Loans'
           and pr.id_contracts = c.id_contracts
           AND '20.04.2008' BETWEEN pr.DT_OPEN AND pr.DT_CLOSE
           and l.id_ord_liab = pr.id_ord_liab
           and l.code_ord_liab like '%fl%'

3) вы принципиально используете ansi синтаксис (join)? можно увидеть план запроса без использования join?!

Автор: Elnarka 29.4.2008, 10:10

dwh.fct_charge_prc    - около 4 млн
dwh.con_ass_contr_acc - около 4 млн
dwh.det_contracts    - 2 млн
dwh.con_ass_acc    - 4 млн
dwh.det_ord_liab     - 1 млн
dwh.det_source   - 100 записей

Запрос возврашает около 2000 записей

Без использования join

Код


SELECT STATEMENT, GOAL = CHOOSE            Cost=508    Cardinality=74    Bytes=2516
 SORT GROUP BY            Cost=508    Cardinality=74    Bytes=2516
  VIEW    Object owner=SYS        Cost=508    Cardinality=74    Bytes=2516
   SORT UNIQUE            Cost=508    Cardinality=74    Bytes=16650
    MERGE JOIN CARTESIAN            Cost=504    Cardinality=74    Bytes=16650
     NESTED LOOPS            Cost=376    Cardinality=1    Bytes=203
      NESTED LOOPS            Cost=374    Cardinality=1    Bytes=190
       NESTED LOOPS            Cost=11    Cardinality=1    Bytes=150
        NESTED LOOPS            Cost=9    Cardinality=1    Bytes=131
         NESTED LOOPS            Cost=7    Cardinality=1    Bytes=74
          NESTED LOOPS            Cost=6    Cardinality=1    Bytes=61
           TABLE ACCESS BY INDEX ROWID        Object name=CON_ASS_CONTR_ACC    Cost=5    Cardinality=1    Bytes=33
            INDEX RANGE SCAN        Object name=AK_ASS_CONTR    Cost=3    Cardinality=1    
           TABLE ACCESS BY INDEX ROWID        Object name=DET_CONTRACTS    Cost=1    Cardinality=1    Bytes=28
            INDEX UNIQUE SCAN        Object name=PK_DET_CONTRACTS        Cardinality=1    
          TABLE ACCESS BY INDEX ROWID    Object name=DET_SOURCE    Cost=1    Cardinality=1    Bytes=13
           INDEX UNIQUE SCAN                    Object name=PK_DET_SOURCE        Cardinality=1    
         TABLE ACCESS BY GLOBAL INDEX ROWID        Object name=FCT_CHARGE_PRC    Cost=2    Cardinality=1    Bytes=57
          INDEX RANGE SCAN    Object name=AK_CHARGE_PRC_FCT_CHAR    Cost=1    Cardinality=15    
        TABLE ACCESS BY INDEX ROWID        Object name=DET_ORD_LIAB    Cost=2    Cardinality=1    Bytes=19
         INDEX UNIQUE SCAN        Object name=PK_DET_ORD_LIAB    Cost=1    Cardinality=1    
       TABLE ACCESS BY INDEX ROWID    Object name=CON_ASS_CONTR_ACC    Cost=363    Cardinality=3    Bytes=120
        INDEX RANGE SCAN        Object name=RELATION_11430_FK    Cost=2    Cardinality=81    
      TABLE ACCESS BY INDEX ROWID        Object name=DET_ACCOUNT    Cost=2    Cardinality=1    Bytes=13
       INDEX UNIQUE SCAN    Object name=PK_DET_ACCOUNT    Cost=1    Cardinality=1    
     BUFFER SORT            Cost=502    Cardinality=215    Bytes=4730
      TABLE ACCESS BY INDEX ROWID    Object name=CON_ASS_ACC    Cost=128    Cardinality=215    Bytes=4730
       INDEX RANGE SCAN        Object name=RELATION_11401_FK    Cost=2    Cardinality=215

Добавлено @ 10:12
Запрос соответствующий последнему плану таков

Код


select a.id_account, tt.id_contracts,sum(val) val
from dwh.con_ass_contr_acc cc,dwh.det_account a,
   
(
select distinct c.id_contracts ,pr.saldoprcmain as val
  from 
       dwh.fct_charge_prc    pr,
       dwh.con_ass_contr_acc con,
       dwh.det_contracts c ,
       dwh.con_ass_acc       ac,
       dwh.det_ord_liab      l,
       dwh.det_source sr
       
       
 where
   ac.id_account_parent = 11398446
   and '20.04.2008' BETWEEN ac.dt_open and ac.dt_close
   and con.id_account = ac.id_account_parent
   and '20.04.2008' BETWEEN con.dt_open and con.dt_close
   and con.id_tacc = 61282
   and c.id_contracts = con.id_contracts 
   and (('20.04.2008' between c.date_open and c.date_closed)
        or c.date_closed is null)
   and c.id_source = sr.id_source
   and sr.code_source = 'Loans'
   and pr.id_contracts = c.id_contracts 
   AND '20.04.2008' BETWEEN pr.DT_OPEN AND pr.DT_CLOSE
   and l.id_ord_liab = pr.id_ord_liab
   and l.code_ord_liab like '%fl%' ) tt 

   where cc.id_contracts = tt.id_contracts
   and '20.04.2008' between cc.dt_open and cc.dt_close
   and cc.id_tacc = 61282
   and cc.id_account =a.id_account  
   and a.is_gk = 0
   
   group by  a.id_account, tt.id_contracts

Автор: DimW 29.4.2008, 11:09

Elnarka, покажи план этого запроса:

Код


select count(*)
  from dwh.fct_charge_prc    pr
      ,dwh.con_ass_contr_acc con
      ,dwh.det_contracts     c
      ,dwh.con_ass_acc       ac
      ,dwh.det_ord_liab      l
      ,dwh.det_source        sr
 where ac.id_account_parent = 11398446
   and to_date('20.04.2008', 'dd.mm.yyyy') BETWEEN ac.dt_open and ac.dt_close
   and con.id_account = ac.id_account_parent
   and to_date('20.04.2008', 'dd.mm.yyyy') BETWEEN con.dt_open and con.dt_close
   and con.id_tacc = 61282
   and c.id_contracts = con.id_contracts
   and ((to_date('20.04.2008', 'dd.mm.yyyy') between c.date_open and c.date_closed) or
       c.date_closed is null)
   and c.id_source = sr.id_source
   and sr.code_source = 'Loans'
   and pr.id_contracts = c.id_contracts
   AND to_date('20.04.2008', 'dd.mm.yyyy') BETWEEN pr.DT_OPEN AND pr.DT_CLOSE
   and l.id_ord_liab = pr.id_ord_liab
   and l.code_ord_liab like '%fl%'

именно этого!

и за какое время он выполняется?
есть подозрение что во всем виноват distinct который приводит к сортировке подселекта.

Добавлено через 5 минут и 34 секунды
да и трассировку посмотреть бы не мешало. возможно слишком много дисковых чтений.

Автор: Elnarka 29.4.2008, 12:39

План вашего запроса таков

SELECT STATEMENT, GOAL = CHOOSE            Cost=139    Cardinality=1    Bytes=159
SORT AGGREGATE                Cardinality=1    Bytes=159
  MERGE JOIN CARTESIAN            Cost=139    Cardinality=23    Bytes=3657
   NESTED LOOPS            Cost=11    Cardinality=1    Bytes=137
    NESTED LOOPS            Cost=9    Cardinality=1    Bytes=118
     NESTED LOOPS            Cost=7    Cardinality=1    Bytes=74
      NESTED LOOPS            Cost=6    Cardinality=1    Bytes=61
       TABLE ACCESS BY INDEX ROWID    Object name=CON_ASS_CONTR_ACC    Cost=5    Cardinality=1    Bytes=33
        INDEX RANGE SCAN        Object name=AK_ASS_CONTR    Cost=3    Cardinality=1
       TABLE ACCESS BY INDEX ROWID    Object owner=DWH    Object name=DET_CONTRACTS    Cost=1    Cardinality=1    Bytes=28
        INDEX UNIQUE SCAN    Object name=PK_DET_CONTRACTS        Cardinality=1
      TABLE ACCESS BY INDEX ROWID    Object name=DET_SOURCE    Cost=1    Cardinality=1    Bytes=13
       INDEX UNIQUE SCAN    Object name=PK_DET_SOURCE        Cardinality=1
     TABLE ACCESS BY GLOBAL INDEX ROWID    Object name=FCT_CHARGE_PRC    Cost=2    Cardinality=1    Bytes=44
      INDEX RANGE SCAN        Object name=AK_CHARGE_PRC_FCT_CHAR    Cost=1    Cardinality=15
    TABLE ACCESS BY INDEX ROWID        Object name=DET_ORD_LIAB    Cost=2    Cardinality=1    Bytes=19
     INDEX UNIQUE SCAN        Object name=PK_DET_ORD_LIAB    Cost=1    Cardinality=1
   BUFFER SORT            Cost=137    Cardinality=215    Bytes=4730
    TABLE ACCESS BY INDEX ROWID        Object name=CON_ASS_ACC    Cost=128    Cardinality=215    Bytes=4730
     INDEX RANGE SCAN        Object name=RELATION_11401_FK    Cost=2    Cardinality=215

выполнился за 1810.953 ыулгтв

Извините(простите за такой вопрос),а как трассировку посмотреть? ))

Автор: DimW 29.4.2008, 14:38

Цитата(Elnarka @ 29.4.2008, 12:39

)

выполнился за 1810.953 ыулгтв

да, очень долго. без трассировки дальнейшие попытки не имеют смысла.

Цитата(Elnarka @ 29.4.2008, 12:39

)

а как трассировку посмотреть?

попытаюсь объяснить.

все действия делаются в одной сесии!
1) включаем трассировку:

Код


alter session set events '10046 trace name context forever, level 12';

2) выполняешь свой запрос, и ждешь окончания результата.
3) выполняешь след. запрос чтобы получить путь и имя твоего файла с тарссировкой:

Код


select rtrim(c.value, '/') || '/' || d.instance_name || '_ora_' || ltrim(to_char(a.spid)) || '.trc'
  from sys.v_$process a
      ,sys.v_$session b
      ,sys.v_$parameter c
      ,sys.v_$instance d
 where a.addr = b.paddr
   and b.audsid = sys_context('userenv', 'sessionid')
   and c.name = 'user_dump_dest';

это путь к файлу на сервере БД

4) закрываешь трассировку:

Код


alter session set events '10046 trace name context off';

и так ты получишь файл трассироки, но для его изучения необходимо еще предпринять пару шагов.
в ORACLE_HOME\BIN есть програмулина tkprof.exe при помощи нее можно получить читабельный файл оформив батник такова вида:

Код


tkprof <путь + имя трассировочного файла>.trc trc_report.prt

trc_report.prt это файлик который будет создан после выполнения этого батника.
его то и следует изучить.
надеюсь объяснил доходчиво.