SQL Performance Tuning

Posted on 2008-11-05
Last Modified: 2013-12-19
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.
create or replace view rpt_magazine_ad_prod_summary as


       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, as adjcause, as adjreason,

       j.username as salesrep,

       j.salesteam as salesteam,

       o.ordernumber as ordernum,

       nvl(a.product, p.product)  as productname,


       a.category as productcategory,

       decode(nvl(s.AdSubTypeId, p.adsubtypeid), 1, 'National', 2, 'National', 4, 'National', 'Retail') as adsubtype,

       a.placement as placementname,



       decode(t.goalid, 0, 'Account Level Adj', as goalcategory,


       c.acct_type as customertype_client,

       decode(i.transtype, 'Invoice', 'INVOICEAMT', 'Adjustment Invoice', 'INVOICEAMT', 'ADJUSTMENT') as entrytype,


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 =

     join pmt.adj_causes ac on i.cdcauseid =

     join pmt.adj_reasons ar on i.cdreasonid =

     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 =

     left join pmt.pmt_prpschedule p on i.prprunschedid =

     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))


    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)



i.transdate between Trunc(Add_Months(SYSDATE-27,-12),'Y') and Trunc(Last_Day(Add_Months(SYSDATE-27,-12))+1)




i.transdate between Trunc(SYSDATE,'Y') and Trunc(Last_Day(SYSDATE-27)+1)


Open in new window

Question by:rustypoot
    LVL 67

    Expert Comment

    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.

    Author Comment

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

    Expert Comment

    by: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.

    LVL 67

    Expert Comment

    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

    Author Comment

    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
    LVL 23

    Expert Comment

    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.

    LVL 27

    Expert Comment

    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?

    Author Comment

    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.

    Author Comment

    As requested, I have attached the Explain Plan. Please let me know what you think. Thanks for the assistance.
    LVL 15

    Accepted Solution

    Okay, everything is either Full Table Scan or Index Fast Full Scan, which consume lots of system resources. This indicate that system is not using indexes properly. i.e Instead of scaning whole table, system is scaning whole index because it probability get all required listed in "select" and "where" clause.
    Now using dba_col_index send me all the indexes associated with the tables used in view. If this query is taking 3 to 4 minutes, even than its very expensive statement.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now