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

From: William Robertson <william_at_williamrobertson.net>
Date: Thu, 20 Nov 2008 07:32:24 +0000
Message-ID: <49251288.7010507@williamrobertson.net>

I don't think we can assume anything about the design from the use of OR here. Perhaps in the app the logic is something like: "find rows where a given employee is either the  owner, the assignee or the sale owner" (whatever those mean). That could be a perfectly valid business query.

Rather than speculating about whether index use might be being prevented by some construction and whether it would help (sorry, reduce the CPU metric) to use them, it would be better to see actual explain plans and test timings along with table and index definitions etc.

I can't really see where you are going with "joining first not null columns then null allowed columns" - not a rule of thumb I've come across before.

Separate bitmap indexes MIGHT be worth a look for owneruserid, assigneduserid and salesownerid if there are enough queries like this to offset the additional index maintenance and locking overhead on insert/update/delete. However bitmap indexes are normally only recommended in warehouse type applications where tables can be populated in one shot, rather than in OLTP systems where rows are constantly being individually added and updated, so I would be very cautious about using them. (Also they are only available in Enterprise Edition, if that makes a difference here.)

William Robertson

-----Original message-----
From: Subodh Deshpande
Date: 19/11/08 09:12

Hi Vivek,

primafacie, I think the design is incorrect as you are using 'OR' on different columns to get your resultset.

coming back to the query, usuage of OR is definately going to affect the performance and check whether usuage of ROWUN will affect the behavour of default indexes on your query. Think of joining first not null columns then null allowed columns, that too, as far as possible keeping the column sequence (from c1 to c10, c1, c3 are NN take first, then say c2, c4,c5 etc) in table unchanged.

By breaking down the sql I assume you mean inline or pivoting do it if possible

If you can treat NULLs with some different value (eg nvl (c1,'ZZ') that index can understand and will not affect your result set use it

Sorry I do not think partitioning can help unless it is application specific requirement, as again there are different partitions that you have to decide.

think of bitmap or reversey key index as per the occurance of data elements.

 

thanks and take care..subodh

---

From: VIVEK_SHARMA <VIVEK_SHARMA@infosys.com>
To: ORACLE-L <oracle-l@freelists.org>
Sent: Wednesday, 19 November, 2008 1:48:13
Subject: SQL Tuning Case .... Basic Qs ?

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") )

 

-- http://www.freelists.org/webpage/oracle-l Received on Thu Nov 20 2008 - 01:32:24 CST