SQL Performance Tuning

rustypoot used Ask the Experts™
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,
       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.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', t.name) 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 = 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))
    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

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

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.


Thanks But is there a way to optimize this SQL at all? Thanks
Devinder Singh VirdiLead Oracle DBA Team

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.

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

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


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 VanZandtOracle Database Administrator III

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.

SujithData Architect

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?


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.


As requested, I have attached the Explain Plan. Please let me know what you think. Thanks for the assistance.
Lead Oracle DBA Team
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial