Just to add one comment this thread.
I found that in recent versions of Oracle(10.2.0.4 and 11.1.0.6 confirmed), Oracle does not have multiple child cursors for different nls settings.
(I can't confirm it is
always so. There should be other exceptions when nls setting must be applied)
I think that Oracle tries to invalidate the cursor as less frequently as possible by enhanced dependency and invalidation mechanism.
Dion,
that's an interesting point. You'll however get different child cursors if the plans are different, obviously.
If you change your test case slightly, then you ought to get different child cursors:
create table t1_nls(c1 int, c2 nvarchar2(100));
create index t1_nls_idx1 on t1_nls(c2, c1);
var b1 number
var b2 varchar2(10)
exec :b1 := 1;
exec :b2 := '0';
alter session set nls_sort = binary;
select /* share_test */ * from t1_nls where c1 = :b1 and c2 like rtrim(:b2) order by c2;
@shared_cursor 'select /* share_test */%'
alter session set nls_sort = german;
select /* share_test */ * from t1_nls where c1 = :b1 and c2 like rtrim(:b2) order by c2;
@shared_cursor 'select /* share_test */%'
By using the LIKE operator and an index the database potentially needs to sort the data according to the NLS settings, so in one case the index can be used for both access and sort, and in the other case an additional SORT ORDER BY is required. If you use the equal operator, there is only a single value to sort, therefore the NLS settings don't matter for the plan.
It's interesting to note that the same optimization doesn't apply to literals, at least in 11.1.0.7:
create table t1_nls(c1 int, c2 nvarchar2(100));
create index t1_nls_idx1 on t1_nls(c2, c1);
alter session set nls_sort = binary;
select /* share_test */ * from t1_nls where c1 = 1 and c2 = '0' order by c2;
@shared_cursor 'select /* share_test */%'
alter session set nls_sort = german;
select /* share_test */ * from t1_nls where c1 = 1 and c2 = '0' order by c2;
@shared_cursor 'select /* share_test */%'
This gives me two child cursors with the same execution plan...
Modifying NLS_COMP seems to be treated differently, too:
create table t1_nls(c1 int, c2 nvarchar2(100));
create index t1_nls_idx1 on t1_nls(c2, c1);
var b1 number
var b2 varchar2(10)
exec :b1 := 1;
exec :b2 := '0';
alter session set nls_comp = binary;
select /* share_test */ * from t1_nls where c1 = :b1 and c2 = rtrim(:b2) order by c2;
@shared_cursor 'select /* share_test */%'
alter session set nls_comp = linguistic;
select /* share_test */ * from t1_nls where c1 = :b1 and c2 = rtrim(:b2) order by c2;
@shared_cursor 'select /* share_test */%'
Again gives me two child cursors with the same execution plan...
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
