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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
rustypootAuthor Commented:
Thanks But is there a way to optimize this SQL at all? Thanks
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
rustypootAuthor Commented:
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
DavidSenior Oracle Database AdministratorCommented:
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 ArchitectCommented:
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?
rustypootAuthor Commented:
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.
rustypootAuthor Commented:
As requested, I have attached the Explain Plan. Please let me know what you think. Thanks for the assistance.
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.