Re: OPT_PARAM
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- Date: Tue, 24 Nov 2009 18:48:29 +0100
Hi
I ran 10053 dump for both cases
set autotrace trace stat
alter session set optimizer_features_enable = '9.2.0';
alter session set events '10053 trace name context forever, level 1';
SELECT A, B, C, D
FROM V_LSC
WHERE BLOCKED = 'S' OR DATE_DOWN > sysdate - 3;
quit
grep optimizer_features_enable in the trace file and got this:
*optimizer_features_enable = 9.2.0
*
set autotrace trace stat
alter session set events '10053 trace name context forever, level 1';
SELECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
A, B, C, D
FROM V_LSC
WHERE BLOCKED = 'S' OR DATE_DOWN > sysdate - 3;
quit
grep optimizer_features_enable in the trace file and got this:
SELECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
optimizer_features_enable = 11.1.0.7
LECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
ELECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
LECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
LECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
LECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
LECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
SELECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
LECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
ELECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
LECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
LECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
LECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
LECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
SELECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
SELECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
sql=SELECT /*+ OPT_PARAM('optimizer_features_enable', 9.2.0') */
*optimizer_features_enable = 11.1.0.7
*
I am not sure if this is an expected behaviour, will see if I can open a SR
Thanks!
-
LSC
On Tue, Nov 24, 2009 at 6:34 PM, Allen, Brandon
<Brandon.Allen@xxxxxxxxxxx>wrote:
> Okay, I missed the part where you did get a different plan when you set
> optimizer_features_enable at the session level – yes, it sounds like you’re
> right that it’s ignoring the parameter
>
>
>
> ------------------------------
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>
Other related posts:
