query unnested in one database and not in another
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Jan 2012 13:41:06 -0500
I have a complex query that in one database unnests and I get a good plan
and in another copy of that database (the data volumes, etc... ) are very
similiar
both databases have _unnest_subquery=false
all tables are analyzed.
all I have noticed is that 1 table in the database that does not unnest and
creates a really bad plan has been analyzed more recently.
I know these types of queries can be re-written to make them less
susceptible to these types of issues. I am using a SQL Profile for now.
However, I am trying to figure out what might be different between the two
databases that could lead oracle to unnest in one and not the other. Are
there other parameters oracle takes into account? Are there any pieces of
the statistics that I should look at, to help to figure it out?
I am curious. This is older code so getting approval to change the code
will be a problem. I would prefer not to have to put sql profiles for this
all over the place either. So I'd like to figure out what may have caused
oracle to make a different decision.
The plan that is not nested is a terrible plan and includes a cartesian
join. The unnested plan has all nested loops.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » query unnested in one database and not in another - Dba DBA
