Re: SQL Tuning Case .... Basic Qs ?
- From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
- To: VIVEK_SHARMA@xxxxxxxxxxx
- Date: Wed, 19 Nov 2008 09:23:00 +0100
First thing that pokes me in the eye is the rownum without any order by, nor
a proper inline view. Do your developers realize they're fetching "SYS_B_16"
number of RANDOM rows ?
Stefan
=========================
Stefan P Knecht
Senior Consultant
Systems Engineering
OPITZ CONSULTING Schweiz GmbH
Seestrasse 97
CH-8800 Thalwil
Mobile +41-79-571 36 27
stefan.knecht@xxxxxxxxxxxxxxxxxxx
http://www.opitz-consulting.ch
OCP 9i/10g SCSA SCNA
=========================
On Tue, Nov 18, 2008 at 9:18 PM, VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx>wrote:
> 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") )
>
>
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> for the use of the addressee(s). If you are not the intended recipient, please
> notify the sender by e-mail and delete the original message. Further, you are
> not
> to copy, disclose, or distribute this e-mail or its contents to any other
> person and
> any such actions are unlawful. This e-mail may contain viruses. Infosys has
> taken
> every reasonable precaution to minimize this risk, but is not liable for any
> damage
> you may sustain as a result of any virus in this e-mail. You should carry out
> your
> own virus checks before opening the e-mail or attachment. Infosys reserves the
> right to monitor and review the content of all messages sent to or from this
> e-mail
> address. Messages sent to or from this e-mail address may be stored on the
> Infosys e-mail system.
> ***INFOSYS******** End of Disclaimer ********INFOSYS***
>
>
Other related posts:
