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