Hello.
I’m trying to test Oracle Adaptive Cursor Sharing on Oracle 11.2.0.4 using this article
But something goes wrong. When I execute in TOAD
select * from t where id = :id;
IS_BIND_SENSITIVE field in V$SQL is always ‘N’. When execute the same sql in SqlPlus IS_BIND_SENSITIVE is ‘Y’.
I think that is because TOAD is using OCI version 7 that doesn’t support Bind variable peeking.
How to check what version of OCI is used by TOAD?
With best regards Sergey.
Bind variable peeking doesn't work
sergey1231
#1
jdorlon
#2
Toad’s login window has a dropdown labeled “Connect Using”. This is used to select which Oracle home you want to use. Click the ellipsis (…) button next to it for details about all of your Oracle homes. The version number is listed in parenthesis right after the Oracle Home Name, with details of each Oracle Home under that. I would be really surprised if you are using an Oracle 7 client. It was about 20 years ago when Oracle 7 was released.
sergey1231
#3
Thanks fo reply. My version is 11.2.0.1. As I understand it is a version of the client.
The oracle client contains OCI methods of previous versions for backward compatibility, choice of functions depends on the application. Which version of OCI is used by Toad?
mstaszew
#6
I didn’t read the entire article or anything on the Adaptive Cursor Sharing so this might not make any sense, but…
Are your tests dependent on executing in the same session? If so, be sure that you have the “Execute Scripts in Toad Session” option checked in Options on the
Oracle, Transactions page if you’re executing as script (F5, lightning bolt button). If you’re executing using F9 or the green triangle button then make sure you’re executing all statements within the same editor tab or disable the “Execute queries in threads”
checkbox in Options on the Oracle, Transactions page.
Those options can spawn new sessions so any session specific work being done won’t necessarily carry over to your next execution. Disabling them might help you
track down the difference.
Michael
From: sergey1231 [mailto:bounce-sergey1231@toadworld.com]
Sent: Wednesday, June 03, 2015 9:13 AM
Subject: RE: [Toad for Oracle - Discussion Forum] Bind variable peeking doesn’t work
RE: Bind variable peeking doesn’t work
Reply by sergey1231
Can you explain why IS_BIND_SENSITIVE is always ‘N’, when I execute SQL in TOAD?
To reply, please reply-all to this email.
Stop receiving emails
on this subject.
Or Unsubscribe from Toad for Oracle - General
notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
sergey1231
#7
I execute only one SQL in session:
select * from t where id = :id
I press F9 and set id = 1, type NUMBER on popup Variables window.
I execute script:
"var id NUMBER;
exec :id := 1;
select * from t where id = :id;"
by pressing F5, with and without “Execute Scripts in Toad Session” option.
Result is same IS_BIND_SENSITIVE is always ‘N’.
mstaszew
#8
Edit: You didn’t mention whether or not you had execute queries in threads checked. If that’s checked then your F9 and F5 are running in different sessions.
The process you describe results **might result **in your F9 statement and F5 script executing in different sessions. That might be OK, but if it needs to be in the same session for your tests then do exactly as you just described, but make sure that “execute queries in threads” is unchecked and that “executed scripts in Toad session” is checked. I’d close your session after changing those options and try again.
jdorlon
#9
I’ve been playing with it this morning - I have execute queries in threads turned off and I can reproduce the IS_BIND_SENSITIVE always being ‘N’. I don’t know why this is so.
mstaszew
#10
The process you describe results in your F9 statement and F5 script executing in different sessions. That might be OK, but if it needs to be in the same session
for your tests then do exactly as you just described, but make sure that “execute queries in threads” is unchecked and that “executed scripts in Toad session” is checked. I’d close your session after changing those options and try again.
From: sergey1231 [mailto:bounce-sergey1231@toadworld.com]
Sent: Wednesday, June 03, 2015 9:53 AM
Subject: RE: [Toad for Oracle - Discussion Forum] Bind variable peeking doesn’t work
RE: Bind variable peeking doesn’t work
Reply by sergey1231
I execute only one SQL in session:
select * from t where id = :id
I press F9 and set id = 1, type NUMBER on popup Variables window.
I execute script:
"var id NUMBER;
exec :id := 1;
select * from t where id = :id;"
by pressing F5, with and without “Execute Scripts in Toad Session” option.
Result is same IS_BIND_SENSITIVE is always ‘N’.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
jdorlon
#11
I’m still not exactly sure what the technical reason for this not working is, but there are a couple of different ways that we can run SELECT statements internally. The way it’s done in the editor causes IS_BIND_SENSITIVE to always be false. Most of the other places in Toad use a method where it does not cause that. So I think we can safely change it once we get into betas for Toad 12.8 (or whatever we end up calling the next version…I’m not sure if any decision has been made to avoid the number 13 or not! haha)