Re: SQL Tuning Case .... Basic Qs ?

From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 19 Nov 2008 08:15:06 +0000

CPU is only one thing to look at when tuning. Are you saying I/O is not worth looking at?

How do you know there is anything wrong with the current execution plan?

I'm hoping that's generated code btw. Most of the brackets are unnecessary, the outer join is defeated by a later inner join to the outer-joined table, and I'm rather suspicious of the ROWNUM clause. This all rather leaps out at you when you format it for human readability (untested):

SELECT bo24_sales.applcreationdate
FROM   opportunities o
     , opportunity_products op
     , products p
     , sales s

WHERE o.bank_id = :"SYS_B_17"
AND    o.mergedwith IS NULL
AND    o.status IN (:"SYS_B_11", :"SYS_B_12")
AND    (    o.owneruserid = :"SYS_B_13"
        OR o.assigneduserid = :"SYS_B_14"
        OR o.salesownerid = :"SYS_B_15" )

AND    s.opportunityid(+) = o.opportunityid
AND    s.bank_id(+) = o.bank_id

AND    op.jobid(+) = o.opportunityid
AND    op.bank_id(+) = o.bank_id

AND    p.bank_id = op.bank_id
AND    p.productid = op.productid

AND    ROWNUM <= :"SYS_B_16"

Standard Best Practice begins with confirming that there is a real problem, then getting the execution plan and checking the stats. Not to mention formatting the code.

-----Original message-----
From: VIVEK_SHARMA
Date: 18/11/08 20:18

Folks

What standard best practices may be followed to Tune (Minimize the CPU Consumption) of the below SQL having 2 OR conditions?

NOTE – All Tables are relatively small in size i.e. upto 1 GB (NON-partitioned)

Fields which are part of the OR Conditions i.e. bo16_opportunities.assigneduserid, bo16_opportunities.salesownerid may have some NULL Values.

Qs Is breaking the single SQL into 3 separate SQLs & hence removing the OR statements Advisable? .. (the Outputs of these 3 break-down SQLs can later be merged at the Application level,)

Qs How can the Nulls (which would seemingly prevent index scan, if Null input value is passed) issue be overcome?

Qs Will partitioning help?

Other ideas pls?

Config - Oracle 10gR2 (NON-RAC) on HP-UX

Bad SQL:-

SELECT bo24_sales.applcreationdate, …

FROM opportunities bo16_opportunities,

       opportunity_products bo16_opportunity_products,

       products bo21_products,

       sales bo24_sales

WHERE bo16_opportunity_products.productid = bo21_products.productid

   AND bo16_opportunities.opportunityid = bo16_opportunity_products.jobid(+)

   AND bo16_opportunities.opportunityid = bo24_sales.opportunityid(+)

   AND bo16_opportunity_products.bank_id = bo21_products.bank_id

   AND bo16_opportunities.bank_id = bo16_opportunity_products.bank_id(+)

   AND bo16_opportunities.bank_id = bo24_sales.bank_id(+)

   AND ((    (    (    (bo16_opportunities.mergedwith IS NULL)

                   AND (bo16_opportunities.status IN (:"SYS_B_11", :"SYS_B_12"))

                  )

              AND ((   (bo16_opportunities.owneruserid = :"SYS_B_13")

                    OR (   (bo16_opportunities.assigneduserid = :"SYS_B_14")

                       OR (bo16_opportunities.salesownerid = :"SYS_B_15")

                       )                   )                  )             )

         AND (ROWNUM <= :"SYS_B_16")

         AND bo16_opportunities.bank_id = :"SYS_B_17") )

-- //www.freelists.org/webpage/oracle-l

References:
- SQL Tuning Case .... Basic Qs ?
  - From: VIVEK_SHARMA

Re: SQL Tuning Case .... Basic Qs ?

Other related posts: