query unnested in one database and not in another
From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Tue, 17 Jan 2012 13:41:06 -0500
Message-ID: <CAE-dsOJd3_O56w3faavx3w1Yb5jMyi+-==mDCxFoMjeB7CO=Zg_at_mail.gmail.com>
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?
Date: Tue, 17 Jan 2012 13:41:06 -0500
Message-ID: <CAE-dsOJd3_O56w3faavx3w1Yb5jMyi+-==mDCxFoMjeB7CO=Zg_at_mail.gmail.com>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 17 2012 - 12:41:06 CST