Support Doc# 1983132.1
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Hameed, Amir
Sent: Wednesday, September 07, 2016 2:25 AM
To: jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Saturday, September 03, 2016 1:56 PM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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
@jloracle
________________________________
From: Hameed, Amir [Amir.Hameed@xxxxxxxxx]
Sent: 03 September 2016 18:42
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxx]
Sent: Saturday, September 03, 2016 2:32 AM
To: Hameed, Amir <Amir.Hameed@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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
@jloracle
________________________________
From: Hameed, Amir [Amir.Hameed@xxxxxxxxx]
Sent: 02 September 2016 23:42
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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