RE: Query Performing slow after upgrade to 11g

Moving on, do you have the plan from before the upgrade?

Which 11g?  

IF the former plan also has the Cartesian join and 11.2 plus, you may be seeing effects of adaptive direct read on very wide tables.  

IF the former plan was peachy keen, you may be able to use one of the several plan preservation methods to keep the old plan.  

(By the way, do you have the current plan and the old plan?)  

Now, do you see those columns that begin with X_ ? Those are probably custom columns added to tables that *may* already be at the limit of columns that can appear in a single row piece.

With adaptive direct read your 10046 trace might show a large multi-block read followed by enough single block reads (often one of the same blocks just read via multi-block read, but being read again because the block is in your PGA, not the SGA) to fill in the non-first row piece columns referenced.  

Since usually nowhere near all of the columns have even a single entry in any row, you CAN rebuild the table putting used columns early in the create table statement. After you do the CTAS getting the used columns first, you of course both collect statistics (keeping them specially up to date taking into account the sage advice of JL and Carlos) and add the relevant indexes to the new version of the table, and THIS IS IMPORTANT if any program relies on the column return order of a select * from tab query: create a view of the new version of the table that is in the original column order of the existing version of the table ownered and named as the original table. Add grants and privileges on the new version of the table to match the grants and privileges on the old table.  

I know, we're not supposed to need to care about the physical column order in a relational RDBMS. This is an atypically large amount of rebuild work and you may have to rebuild back in the original order if you have an unrelated bug but support insists, so I would not do this lightly. You'd want to be pretty sure this was the problem before you "fixed it." IF the 10046 trace shows the read pattern I described and your old plan matches your current plan, this is a good bet. IF you decide to do the rebuild of a large table, it is also probably worthwhile to figure out the best index to mimic in your order by of the source part of the CTAS. Time and "client_id" are often useful for transaction tables, and keeping parent order rows near child order rows when they are in the same table could help.  

The other thing that can go wrong with adaptive direct read is that some lookup tables that used to be mostly or completely cached in the SGA are being read into the PGA instead by each query. Especially since lookup tables are slowly changing, getting them into cache by brute force and keeping them there could be helpful. Once you're on a version with adaptive direct read identifying tables to put in KEEP is useful, or you can run a scanner based on one rowid per block so that adaptive direct read is unlikely to kick in and keep them warm in your cache.  

All of these things are Zebras, and JL and Carlos have pointed out the much more likely horses. Siebel seems to find a lot of Zebras due to the wide table design PLUS added customer X_ columns.  

The OR with the X_<NEW> and X_ ITEM references joining the parent reference to orders to one of the versions of assets gives me the willies, by the way.

IF that restriction join would equally well apply to the non-PAR_ version of orders, then combined with the date range restrictions on orders that might work out better.  

Finally, what is the deal with the commented out AND predicate. IS that possibly a missing equijoin resulting in a Cartesian? (That's just a wild guess.)  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, August 01, 2014 2:34 AM
To: veeracb_at_gmail.com; oracle-l_at_freelists.org Subject: RE: Query Performing slow after upgrade to 11g    

As Carlos suggests, getting suitable stats is important as a starting point; however this query MAY be an example of a classic problem that leads to inappropriate Cartesian merge joins.  

Note the predicates:  

AND order_item.STATUS_DT between
to_date('20140729000000','YYYYMMDDHH24MISS') and to_date('20140730000000','YYYYMMDDHH24MISS')

AND order_item.LAST_UPD between
to_date('20140729000000','YYYYMMDDHH24MISS') and to_date('20140730000000','YYYYMMDDHH24MISS'  

both request a single day's worth of data using a range-based predicate.  

The column names suggest (to me) that one day of last_upd will identify a very small fraction of the data, similarly status_dt will do the same. The optimizer assumes all columns are independent and would therefore estimate that you want: "a very small fraction of a very small fraction" of the data - which could result in a cardinality estimate of 1 which is then likely to trigger a Cartesian merge join.    

A complicating factor (if that scenario isn't the trigger) is that your query may be asking for data which is out of range - i.e. dates above the high value recorded for the column in this case. If the columns are actually using date and time the number of rows for any given value is very small - and when range-based query asked for data that is out of range the optimizer uses an estimate based on "column = constant". I've seen the optimizer chnages its estimate from 10,000 to 3 for the query "how many rows in the last 15 minutes" when "the last 15 minutes" went just past the high value on the relevant column. In cases like this you need to fake the stats to ensure that the high values are always a reasonable distance (e.g. 24 hours) into the future. There is some sample code outlining the method in the comments on this blog post:

http://jonathanlewis.wordpress.com/2006/11/29/low_value-high_value      

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Veerabasaiah C [veeracb_at_gmail.com] Sent: 01 August 2014 00:38
To: oracle-l_at_freelists.org
Subject: Query Performing slow after upgrade to 11g

Hi All,

We recently upgraded our database from Oracle 9i to Oracle 11g.  

In Oracle 9i we were using RULE based optimizer as it was the need from the application.

Attached query used to finish in about 30 mins in the old one, but after upgrade we are seeing this query hanging in there forever almost.

From the plan it is doing a "Merge Cartesean" which I think is causing the slowness. Need some help to fix.  

-- 

Veerabasaiah C B 

 



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