RE: Query Performing slow after upgrade to 11g
Date: Fri, 1 Aug 2014 06:33:36 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E05469_at_exmbx05.thus.corp>
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
Received on Fri Aug 01 2014 - 08:33:36 CEST