[Last Call] Learn how to a build a cloud-first strategyRegister Now


SQL Performance Tuning

Posted on 2008-11-05
Medium Priority
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,
       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

Question by:rustypoot
  • 4
  • 2
  • 2
  • +2
LVL 71

Expert Comment

ID: 22888305
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

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

Expert Comment

by:Devinder Singh Virdi
ID: 22889017
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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 71

Expert Comment

ID: 22889064
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

ID: 22891114
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

ID: 22891924
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

ID: 22893788
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

ID: 22897961
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

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

Accepted Solution

Devinder Singh Virdi earned 750 total points
ID: 22908067
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.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

825 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