Re: OPT_PARAM
From: hrishy <hrishys_at_yahoo.co.uk>
Date: Tue, 24 Nov 2009 07:35:02 +0000 (GMT)
Message-ID: <607139.79367.qm_at_web23702.mail.ird.yahoo.com>
Hi
The opt_parm hint is valid
STAR_TRANSFORMATION_ENABLED
hence your hint is silently ignored as it is inavlid.
You might want to try
alter session set "optimizer_features_enable"= '9.2.0';
db_version : 11.1.0.7
parse_schema : SIMO
plan_hash : 2064770634
plan_hash_2 : 1803173905
Peeked Binds
Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=31
char format=1
max length=32
value=11/20/09 14:01:27
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('9.2.0')
..............
Optimizer state dump:
..............
optimizer_features_enable = 11.1.0.7
Date: Tue, 24 Nov 2009 07:35:02 +0000 (GMT)
Message-ID: <607139.79367.qm_at_web23702.mail.ird.yahoo.com>
Hi
The opt_parm hint is valid
OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and
STAR_TRANSFORMATION_ENABLED
hence your hint is silently ignored as it is inavlid.
You might want to try
alter session set "optimizer_features_enable"= '9.2.0';
- On Tue, 24/11/09, LS Cheng <exriscer_at_gmail.com> wrote:
From: LS Cheng <exriscer_at_gmail.com>
Subject: OPT_PARAM
To: "Oracle Mailinglist" <oracle-l_at_freelists.org>
Date: Tuesday, 24 November, 2009, 7:14
Hi
Does anyone know if opt_param hint works with optimizer_features_enable in 11.1.0.7?
I am testing it and it seems that it gets ignored
enable event 10132
SELECT
/*+ opt_param('optimizer_features_enable', '9.2.0') */
x, y, z
FROM v_lsc
WHERE x = 'S' OR y_date > to_date(:V1, 'MM/DD/YY HH24:MI:SS');
Content of other_xml column
db_version : 11.1.0.7
parse_schema : SIMO
plan_hash : 2064770634
plan_hash_2 : 1803173905
Peeked Binds
Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=31
char format=1
max length=32
value=11/20/09 14:01:27
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('9.2.0')
..............
Optimizer state dump:
..............
optimizer_features_enable = 11.1.0.7
Although in outline section it sees 9.2.0 optimizer afterwards it still uses 11.1.0.7
Thanks
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 24 2009 - 01:35:02 CST