Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    6

    Red face Unanswered: Query execution slow

    HI everyone i have a query and it is executing very slow. Can someone give me idea haw to make that query execute faster.

    This is the query:

    select CRA1.constMonths.oid month,classification.oid status,
    sum(case when credit.valut=(select oid from CRA1.def_currencycode where domestic=0) then CRA1.outStandingAmountOnDate(credit.oid,last_day( add_months(round(TO_DATE('01-01-'||'2008','DD-MM-YYYY'),'year'),CRA1.constMonths.oid-1 )) ) else 0 end) LoansInLek,
    sum(case when credit.valut=(select to_number(val) from CRA1.def_common where code='euro_code' ) then CRA1.outStandingAmountOnDate(credit.oid,last_day( add_months(round(TO_DATE('01-01-'||'2008','DD-MM-YYYY'),'year'),CRA1.constMonths.oid-1 )) )*(select COURSE from CRA1.DEF_CURRENCY_TIME where credit.datev between date_from and NVL(date_to, SYSDATE) and oid = credit.valut) else 0 end) LoansInEuro,
    sum(case when credit.valut=(select to_number(val) from CRA1.def_common where code='usd_code') then CRA1.outStandingAmountOnDate(credit.oid,last_day( add_months(round(TO_DATE('01-01-'||'2008','DD-MM-YYYY'),'year'),CRA1.constMonths.oid-1 )) )*(select COURSE from CRA1.DEF_CURRENCY_TIME where credit.datev between date_from and NVL(date_to, SYSDATE) and oid = credit.valut) else 0 end) LoansInUSD,
    sum(case when credit.valut in (select oid from CRA1.def_currencycode where domestic=1 and oid<>(select to_number(val) from CRA1.def_common where code='euro_code') and oid<>(select to_number(val) from CRA1.def_common where code='usd_code') )
    then CRA1.outStandingAmountOnDate(credit.oid,last_day( add_months(round(TO_DATE('01-01-'||'2008','DD-MM-YYYY'),'year'),CRA1.constMonths.oid-1 )) )*(select course from CRA1.def_currencycode where oid=credit.valut) else 0 end) LoansInOtherCurrencies
    from CRA1.constMonths
    join CRA1.def_classification classification on 1=1
    join CRA1.main_application credit on recordstatus = 2 and CRA1.classOnDate(credit.oid,last_day( add_months(round(TO_DATE('01-01-'||'2008','DD-MM-YYYY'),'year'),CRA1.constMonths.oid-1 )) ) = classification.oid
    where classification.oid < 5
    group by CRA1.constMonths.oid,classification.oid
    order by CRA1.constMonths.oid,classification.oid;

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Query is slow because it is ugly written. Oracle is unable to understand unformatted code, so it spends too much time trying to figure it out.

    Of course, that is not the reason. But it prevents forum members to see what's going on. Would you mind to properly format it and use [code] tags to preserve formatting?

    Furthermore, query itself is useless - much more information is needed (such as tables' description, indexes, number of records, execution plan, database version, etc.).

    However, what you might do (without sharing these information with the rest of the community) is to trace statement's execution, run TKPROF and see what is going on.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    TKPROF is quite complicate for someone who has never used it.

    The first step in tuning should always be to run EXPLAIN PLAN and post the execution plan.

    When using SQL*Plus this can also easily be done using "set autotrace traceonly explain statistics" and then running the statement

    And please do use [code] tags when posting the plan.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •