RE: Enforcing SQL execution plan

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Tue, 6 Sep 2016 18:25:25 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED2015CEE7_at_USA7109MB012.na.xerox.net>

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] On Behalf Of Jonathan Lewis Sent: Saturday, September 03, 2016 1:56 PM To: 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:

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 06 2016 - 20:25:25 CEST