Hai,
I have doubt regarding setting cursor_sharing parameter exact and similar at session level.
On my database cursor_sharing is set similar at system level.
test >show parameter cursor
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing string SIMILAR
I have fired a simple select statement without setting any cursor_sharing at session level
TEST >variable b1 number;
TEST >exec :b1:=7499;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
TEST >select empno,job from emp where job='SALESMAN' and empno=:b1;
EMPNO JOB
---------- ---------
7499 SALESMAN
checking the hash value for query fired
test >select sql_text,invalidations,hash_value,executions,loads from v$sqlarea
16:14:50 2 where sql_text like '%select empno,job from%';
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------
--------------------
INVALIDATIONS HASH_VALUE EXECUTIONS LOADS
------------- ---------- ---------- ----------
select empno,job from emp where job=:"SYS_B_0" and empno=:b1
0 3727168047 1 1
Literal job='SALESMAN' is converted into system generated bind variable job=:"SYS_B_0" as my cursor_sharing=similar
Fired the same statement by setting
cursor_sharing=exact at session level
TEST >alter session set cursor_sharing=exact;
Session altered.
Elapsed: 00:00:00.00
16:15:25 TEST >select empno,job from emp where job='SALESMAN' and empno=:b1;
Checking the hash value for newly fired query with cursor_sharing=exact
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------
--------------------
INVALIDATIONS HASH_VALUE EXECUTIONS LOADS
------------- ---------- ---------- ----------
select empno,job from emp where job='SALESMAN' and empno=:b1
0 2065003705 1 1
select empno,job from emp where job=:"SYS_B_0" and empno=:b1
0 3727168047 1 1
literal job='SALESMAN' is not converted into bind variable as my cursor_sharing=exact
At the same session fired the same query by setting cursor_sharing=similar ..to check which hash value would be shared.
16:15:28 TEST >alter session set cursor_sharing=similar;
Session altered.
Elapsed: 00:00:04.09
17:27:54 TEST >select empno,job from emp where job='SALESMAN' and empno=:b1;
EMPNO JOB
---------- ---------
7499 SALESMAN
16:28:26 test >select sql_text,invalidations,hash_value,executions,loads from v$sqlarea
17:28:13 2 where sql_text like '%select empno,job from%';
SQL_TEXT
-----------------------------------------------------------------------------------------------------
--------------------
INVALIDATIONS HASH_VALUE EXECUTIONS LOADS
------------- ---------- ---------- ----------
select empno,job from emp where job='SALESMAN' and empno=:b1
0 2065003705 2 *2*
select empno,job from emp where job=:"SYS_B_0" and empno=:b1
0 3727168047 1 1
The hash value 2065003705 (cursor_sharing=exact) is shared as executions column is changed from 1 to 2.
So after setting parameter cursor_sharing = similar why the hash value of 3727168047(cursor_sharing=similar)
is not shared?I guess something is cached at session level but i want to know the exact reason..
Again i flushed the shared pool
test >alter system flush shared_pool;
System altered.
Elapsed: 00:00:03.09
17:39:40 test >select sql_text,invalidations,hash_value,executions,loads from v$sqlarea
17:39:44 2 where sql_text like '%select empno,job from%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------
--------------------
INVALIDATIONS HASH_VALUE EXECUTIONS LOADS
------------- ---------- ---------- ----------
select empno,job from emp where job='SALESMAN' and empno=:b1
0 2065003705 0 2
The hash value of 3727168047(cursor_sharing=similar) is removed ..not hash value 2065003705
What is the reason behind that ...
Regards,
Meeran