asked on

SQL Performance Tuning

Hi, I have created a View in Oracle 10g. SQL attached. It takes a LONG TIME to run. Can you please help me with the PErformance? I think the Date Logic in Where Clause is causing the delay. If it is, how can I rewrite that part better? Please help.
Thanks

create or replace view rpt_magazine_ad_prod_summary as
select
       i.transdate as calendardate,
       i.effectivedate as runday,
       i.transnum as transnumber,
       i.amount as amt,
       c.acct_name as ADVERTISER,
       c.accountnumber as acctnum,
       i.glacct as glnumber,
       i.glacctdesc as glname,
       a.productid,
       ac.name as adjcause,
       ar.name as adjreason,
       j.username as salesrep,
       j.salesteam as salesteam,
       o.ordernumber as ordernum,
       nvl(a.product, p.product)  as productname,
       a.productalias,
       a.category as productcategory,
       decode(nvl(s.AdSubTypeId, p.adsubtypeid), 1, 'National', 2, 'National', 4, 'National', 'Retail') as adsubtype,
       a.placement as placementname,
       s.position,
       a.adcategory,
       decode(t.goalid, 0, 'Account Level Adj', t.name) as goalcategory,
       a.edition,
       c.acct_type as customertype_client,
       decode(i.transtype, 'Invoice', 'INVOICEAMT', 'Adjustment Invoice', 'INVOICEAMT', 'ADJUSTMENT') as entrytype,
       i.linetype
from ocr_invoice i
     join pmt.pmt_customer c ON c.accountid = i.advertisorid AND c.acct_type IN ('Commercial', 'Small Business', 'Private Party')
     JOIN pmt.ocr_date d ON i.t_dateid = d.id
     join pmt.adj_causes ac on i.cdcauseid = ac.id
     join pmt.adj_reasons ar on i.cdreasonid = ar.id
     join newjobuser j on i.jobid = j.jobid
     join goal_category t on t.goalid = i.goalid
     left join pmt.pmt_order o  on o.adorderid = i.adorderid
     left join pmt.pmt_adschedule s on i.adrunschedid = s.id
     left join pmt.pmt_prpschedule p on i.prprunschedid = p.id
     LEFT JOIN pmt.adproducts a on a.productid = decode(i.adrunschedid, 0, nvl(p.productid, 0), nvl(s.productid,0))
               and a.placementid = decode(i.adrunschedid, 0, nvl(p.placementid, 0), nvl(s.placementid, 0))
               and a.editionid = decode(i.adrunschedid, 0, nvl(p.editionid, 0), nvl(s.editionid, 0))
WHERE
    i.glacctclass = 'Revenue'
    AND ac.appliestocommission = 1
    and nvl(j.salesteam, ' ') not in ('Finance', 'IT','Commercial Printing','Marketing','No Team')
    and nvl(s.placement, ' ') <> 'SoloMail Postage'
    and nvl(j.userlname, ' ' ) NOT IN ('Adbase','AdBase-E','AdStar')
    and a.productid IN (54,63,55,60,69,105,106,127)
    and
    ((
i.transdate between Trunc(Add_Months(SYSDATE-27,-12),'Y') and Trunc(Last_Day(Add_Months(SYSDATE-27,-12))+1)
)
OR
(
i.transdate between Trunc(SYSDATE,'Y') and Trunc(Last_Day(SYSDATE-27)+1)
))

Open in new window

Qlemo

The only thing to check this out is to see the query execution plan. You can try with a

explain plan for
select ...

select * from plan_table;

or you can view the plan of the query while it runs in Enterprise Manager.

rustypoot

ASKER

Thanks But is there a way to optimize this SQL at all? Thanks

Devinder Singh Virdi

If you have created an index on particular column and you are using that column in whare clause, index will be used to fetch that record, but if you use any function within whare cluse, function will not be used. ie
whare column1 in ('abc', 'xyz') --- index will be used
where nvl(column1) in ('abc', 'xyz') ---- index will not be used

if you are using 3 columns in whare clause and index is created by using 2 columns, then optimizer can use index to scan range. Optimizer will decide this deponding on staticstic.

If you are using 3 columns and index is creaed with 4 column, then it again depond on optimizer, but in most case index will be used by optimizer.

best way is to trace execution plan using ' set autotrace traceonly' in sqlplus.

Qlemo

Having seen nothing of the underlaying structure, I can tell vague stuff only.

Functions render indexes (almost) useless, so you should avoid them (such as decode and nvl)
The more Outer Joins you use, the less optimization can apply.
NULL values render indexes useless, they can not be resolved by an index as it is ignoring null values
Columns of join predicates (on ...) should always be indexed, as should where condition columns

rustypoot

ASKER

Thanks. Like you suggested, I ran the Explain Plan; but I am not sure what it means and how to send it to you to look at! If i remove the Date criteria, the query runs within 2 to 3 minutes. So not sure how to proceed on this! Thanks

David VanZandt

If your plan results can be copied and pasted into a text file, send that as an attached file. Or, on a Windows client, press PrintScreen to capture the image; open MS Word; paste the image into a Word document; save it, and post that.

Olemo has some good advice, although Oracle does provide function-based indexes and index-oriented tables in 10g -- but that's another story. What's happening to your cost-based optimizer (CBO) is that the functions require full-table scans -- indexing and keeping the tables / indexes analyzed can only help so much in this case.

I am curious what you think your predicate is supposed to do: i.transdate between Trunc(Add_Months(SYSDATE-27,-12),'Y') and Trunc(Last_Day(Add_Months(SYSDATE-27,-12))+1)
. There are far simpler ways to range for the last twelve months, if that's indeed what you are attempting. Let me know.

Sujith

Do you get the correct results out of this query?

>> i.transdate between Trunc(SYSDATE,'Y') and Trunc(Last_Day(SYSDATE-27)+1)
This looks spooky, for certain values it will try to check a range between a higher and lower value, which will return no data.

what exactly is the date logic you are trying to do?

rustypoot

ASKER

Thanks both of you.

I need to pull data from previous year and compare it to this year.
For Example - When I run the report, I need to pull data for Oct 2007 and Oct 2008 and Jan to Oct 2007 and Jan to Oct 2008. I need to compare by previous month of this year to the same month of last year and Jan to Previous Month of this year to Jan to the Same month of previous year. If there is a better way to do this, please advise.

rustypoot

ASKER

As requested, I have attached the Explain Plan. Please let me know what you think. Thanks for the assistance.
RPT-Magazine-Ad-Prod-Summary-Vie.doc

ASKER CERTIFIED SOLUTION

Devinder Singh Virdi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial