RE: Enforcing SQL execution plan
Date: Wed, 7 Sep 2016 03:05:24 +0000
Message-ID: <2FE2AA1C5F8DEC478F58DF8DD32BA6370CEF7795_at_HKWPIPXMB03C.zone1.scb.net>
Support Doc# 1983132.1
Hemant K Chitale
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hameed, Amir Sent: Wednesday, September 07, 2016 2:25 AM To: jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org Subject: RE: Enforcing SQL execution plan
One thing I have noticed is that this statement has 15 bind variables and based on the information that I have seen, at least in 11g, Adaptive Cursor Sharing is disabled if a statement contains more than 14 bind variables. As a test, I modified the statement and took our one variable (:B15) and hardcoded its value into the statement:
SQL_ID IS_BIND_AWAR IS_BIND_SENSITI IS_SHAREABLE ------------- ------------ --------------- ------------
11safz5f50tww N Y Y bjskf1ujuxwfs N N Y
In the above output, SQL ID 11safz5f50tww has 14 bind variables whereas SQL ID bjskf1ujuxwfs was the original statement and contains 15 bind variables. It seems to me that once a suboptimal execution plan is generated, it will not be optimized.
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Saturday, September 03, 2016 1:56 PM To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: Enforcing SQL execution plan
Sorry,
I was thinking about the 10053 when you've currently only got the 10046.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Hameed, Amir [Amir.Hameed_at_xerox.com] Sent: 03 September 2016 18:42
To: Jonathan Lewis; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: Enforcing SQL execution plan I am not able to find PARAMETERS WITH ALTERED VALUES string in the trace file.
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Saturday, September 03, 2016 2:32 AM To: Hameed, Amir <Amir.Hameed_at_xerox.com<mailto:Amir.Hameed_at_xerox.com>>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: Enforcing SQL execution plan
What does the trace file report under the heading:
PARAMETERS WITH ALTERED VALUES
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Hameed, Amir [Amir.Hameed_at_xerox.com] Sent: 02 September 2016 23:42
To: Jonathan Lewis; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: Enforcing SQL execution plan I have looked through the entire trace file and could not find opt_param() call. The binds captured are shown below:
This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 07 2016 - 05:05:24 CEST