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.
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.
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?
We do not use the old OCI 7 calls.
Could you explain why IS_BIND_SENSITIVE is always ‘N’, when I execute SQL in TOAD?
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.
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’.
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.
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.
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.
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)
Thanks John, I will wait for a new version of TOAD.
Bind variable peeking and ACS(Adaptive cursor sharing) still not working in TOAD 16.1.53. Did you investigate the issue?
If you are executing queries with DBMS_SQL package. It may be related with Bug 13386678.
In short, DBMS_SQL uses the old RPI interface, which doesn't support bind peeking.
Although not sure, Looks like related with the parse is done without binds.
"SQL with bind vars is very slow in toad - #26 by NormTeamT"
When i investigate the issue with 10053, i can see that parse is done with "No bind buffers allocated".
Regards,
Osman DİNÇ
Osman, the problem brought up in this post was solved several years ago.
You said "Bind variable peeking and ACS(Adaptive cursor sharing) still not working in TOAD 16.1.53"
Can you give me some clear steps to reproduce the problem? Just saying that something "doesn't work" is not clear.
Thank you.
Hi John,
Here is the test case.
--Checking ACS related parameters (It is all default values)
SELECT a.KSPPINM "PARAMETER",
b.KSPPSTDF "IS_DEFAULT",
b.KSPPSTVL "SESSION",
c.KSPPSTVL "INSTANCE"
FROM X$KSPPI a, X$KSPPCV b, X$KSPPSV c
WHERE a.INDX = b.INDX
AND a.INDX = c.INDX
AND a.KSPPINM IN ('_optim_peek_user_binds',
'_optimizer_adaptive_cursor_sharing',
'_optimizer_extended_cursor_sharing',
'_optimizer_extended_cursor_sharing_rel');
PARAMETER IS_DEFAULT SESSION INSTANCE
_optimizer_extended_cursor_sharing TRUE UDO UDO
_optimizer_extended_cursor_sharing_rel TRUE SIMPLE SIMPLE
_optimizer_adaptive_cursor_sharing TRUE TRUE TRUE
_optim_peek_user_binds TRUE TRUE TRUE
create table system.test (pk int, data char(100));
-- 500k rows, almost all of them pk=20
INSERT INTO system.test
SELECT CASE WHEN ROWNUM < 20 THEN ROWNUM ELSE 20 END, ROWNUM
FROM DUAL connect by level<= 500000
commit;
create index index on test(pk);
exec dbms_stats.gather_table_stats ('SYSTEM','test');
-- create histogram
exec dbms_stats.gather_table_stats ('SYSTEM','test', method_opt=>'for columns pk size 200');
-- Now run all commands in SQLPLUS
-- bind input 10 , we expect an index scan
var vr1 number;
exec :vr1:=10;
select max(data) max_data, count() from system.test where pk =:vr1;
MAX_DATA COUNT()
10 1
select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
PLAN_TABLE_OUTPUT
SQL_ID 4csc6dsrzjnc8, child number 0
select max(data) max_data, count(*) from system.test where pk =:vr1
Plan hash value: 2838211638
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 104 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INDEX1 | 1 | | 3 (0)| 00:00:01 |
Peeked Binds (identified by position):
1 - :VR1 (NUMBER): 10
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
3 - access("PK"=:VR1)
--sql_id is 4csc6dsrzjnc8. -- sql is marked as bind_sensitive
select sql_id,sql_text,child_number,is_shareable,is_bind_sensitive,is_bind_aware from v$sql where sql_id='4csc6dsrzjnc8';
SQL_ID SQL_TEXT CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
4csc6dsrzjnc8 select max(data) max_data, count(*) from test where pk =:vr1 0 Y Y N
-- bind input 20 this time , we expect an index scan in first execute, as rows returned will fluctuate too much, it will switch to fulltable scan on second execution
var vr1 number;
exec :vr1:=20;
select max(data) max_data, count() from system.test where pk =:vr1;
select max(data) max_data, count() from system.test where pk =:vr1;
MAX_DATA COUNT(*)
99999 499981
select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
PLAN_TABLE_OUTPUT
SQL_ID 4csc6dsrzjnc8, child number 1
select max(data) max_data, count(*) from system.test where pk =:vr1
Plan hash value: 1950795681
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 2060 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
|* 2 | TABLE ACCESS FULL| TEST | 499K| 49M| 2060 (1)| 00:00:01 |
Peeked Binds (identified by position):
1 - :VR1 (NUMBER): 20
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
2 - filter("PK"=:VR1)
select sql_id,sql_text,child_number,is_shareable,is_bind_sensitive,is_bind_aware from v$sql where sql_id='4csc6dsrzjnc8';
SQL_ID SQL_TEXT CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
4csc6dsrzjnc8 select max(data) max_data, count() from test where pk =:vr1 0 N Y N
4csc6dsrzjnc8 select max(data) max_data, count() from test where pk =:vr1 0 Y Y Y
--flush shared pool
alter system flush shared_pool;
--- Now run all commands in TOAD session
-- for binds enter input (10 first, then 20)
-- You wont get a new child cursor, you will always see an index scan and your sql will not be marked as bind_sensitive.
-- enter 10
select max(data) max_data, count() from system.test where pk =:vr1;
MAX_DATA COUNT()
10 1
select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
SQL_ID 4csc6dsrzjnc8, child number 0
select max(data) max_data, count(*) from system.test where pk =:vr1
Plan hash value: 2838211638
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 430 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 25000 | 2539K| 430 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INDEX1 | 25000 | | 51 (0)| 00:00:01 |
Predicate Information (identified by operation id):
3 - access("PK"=TO_NUMBER(:VR1))
--sql_id is 4csc6dsrzjnc8. -- sql is not marked as bind_sensitive
select sql_id,sql_text,child_number,is_shareable,is_bind_sensitive,is_bind_aware from v$sql where sql_id='4csc6dsrzjnc8';
SQL_ID SQL_TEXT CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
4csc6dsrzjnc8 select max(data) max_data, count(*) from test where pk =:vr1 0 Y N N
--enter 20
select max(data) max_data, count() from system.test where pk =:vr1;
MAX_DATA COUNT()
20 499981
select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
SQL_ID 4csc6dsrzjnc8, child number 0
select max(data) max_data, count(*) from system.test where pk =:vr1
Plan hash value: 2838211638
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 430 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 25000 | 2539K| 430 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INDEX1 | 25000 | | 51 (0)| 00:00:01 |
Predicate Information (identified by operation id):
3 - access("PK"=TO_NUMBER(:VR1))
--sql_id is 4csc6dsrzjnc8. -- sql is not marked as bind_sensitive
select sql_id,sql_text,child_number,is_shareable,is_bind_sensitive,is_bind_aware from v$sql where sql_id='4csc6dsrzjnc8';
SQL_ID SQL_TEXT CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
4csc6dsrzjnc8 select max(data) max_data, count(*) from test where pk =:vr1 0 Y N N
I think, In TOAD you are parsing sql statement with a DBMS_SQL command before execution.
And according to the Doc ID 2977764.1, "As far as why bind peeking doesn't happen, this is because DBMS_SQL doesn't support it. See this in the known issue Bug 13386678.
In short, DBMS_SQL uses the old RPI interface, which doesn't support bind peeking."
As bind peeking is not fired in TOAD, CBO(Cost based optimizer) makes estimation of returned rows will be 25000, as you may see in the plan.(Total rows(500.000) x 0,05) This is a hardcoded value according to the Doc ID 70075.1 Use of Bind Variables in Queries (Pre-9i).
Hi Osman,
Toad does not use DBMS_SQL to parse the SQL. We do, however, use an OCI "Describe" command (as documented here) before query execution to learn the datatypes, but I don't know what Oracle does with that behind the scenes (I doubt it's dbms_sql, but that's up to the Oracle server, not Toad). We perform this "Describe" command in order to determine an ideal number of rows per fetch, and to check for XMLTYPE and CLOB columns. If these are present, we have to get some info about the table incase you later want to edit these columns.
I'm sorry, there is no way to disable the OCI Describe call that Toad makes.
-John
I've been playing with the example in the original post, running a SQL like this:
select * from t where id = :id;
and then looking at IS_BIND_SENSITIVE field in V$SQL. I see 'N' when using Toad with an Oracle client, and 'Y' when using Toad without an Oracle client. This could explain some questions I've had recently about why Toad sometimes runs things faster/slower depending on if an Oracle client is used (or compared to other products).
It does seem to be related to the OCI describe, which you can't turn off right now in Toad, but I can make a change so that we don't do the describe if change this option to "manual".
I'll have that in next beta (which will be available 11-Dec-2023).
Hey John, That is awesome news for performance tuning experts .
I have also tested this behavior with other products like DBeaver and SQLDeveloper. SQLDeveloper was the only product for running sql statements as bind aware. With the next beta release, TOAD will also have the capability to work as bind aware.
I also found this blog post may be related with the issue by Patrick Jolliffe. Bind Variable Peeking Problems via OCI | jolliffe.hk
I will write a blog post about ACS(Adaptive Cursor Sharing) and also share this good news with the community.
Thank you for your prompt action. It’s greatly appreciated.
Regards
Osman DİNÇ
1 Like
Setting "_fix_control"='9630092:ON' as described in metalink Doc ID 9630092.8 and in the article that you reference doesn't seem to solve the problem. 