| Cursor_Sharing settings not working (merged) [message #312666] |
Wed, 09 April 2008 10:16  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hi,
I am trying to understand the impact of cursor_sharing parameter setting on query plans.
I the setting cursor_sharing=force internally converts all the literals into bind variables and thus the plan used for first sql statement will be used for second statement as far as the second statement differs only in literals.
I had following queries where STRBRANDCD column has few records for value 'IGB' but huge number of records for value 'SAH'.
Thus STRBRANDCD='IGB' was always causing INDEX Scan whereas STRBRANDCD='SAH' was always causing FTS.
SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR=2007 AND STRBRANDCD='IGB';
SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR=2007 AND STRBRANDCD='SAH';
Here I started the test
1> cursor_sharing=force
Sys>alter system flush shared_pool;
Sys>select substr(s.sql_text,1,150) from v$sql s where upper(sql_text) like 'SELECT%*%FIN_VOU_HDR%STRBRANDCD%';
no rows selected
Then opened new session as application user
Alter session set cursor_sharing=force;
begin
for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='IGB')
loop
null;
end loop;
end;
/
begin
for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='SAH')
loop
null;
end loop;
end;
/
The Trace file produced is testenv_ora_12168 attached herewith
Sys>select substr(s.sql_text,1,150) from v$sql s where upper(sql_text) like 'SELECT%*%FIN_VOU_HDR%STRBRANDCD%';
SUBSTR(S.SQL_TEXT,1,150)
---------------------------------------------------------------------------------------------------------------------
SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR=2007 AND STRBRANDCD='GIB'
SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR=2007 AND STRBRANDCD='HSA'
Now Test2
2> cursor_sharing=similar
Exited earlier session of Application user
Sys>alter system flush shared_pool;
Sys>select substr(s.sql_text,1,150) from v$sql s where upper(sql_text) like 'SELECT%*%FIN_VOU_HDR%STRBRANDCD%';
no rows selected
Again opened new session of Application user
Alter session set cursor_sharing=similar;
begin
for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='IGB')
loop
null;
end loop;
end;
/
begin
for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='SAH')
loop
null;
end loop;
end;
/
The Trace file produced is testenv_ora_13286 attached herewith
Thus neither the literals were converted to binds nor the peeking happened.
Can anybody suggest what could be wrong here.
Thanks and Regards,
OraKaran
|
|
|
|
| Cursor_Sharing settings not working [message #312667 is a reply to message #312666] |
Wed, 09 April 2008 10:18  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hi,
I am trying to understand the impact of cursor_sharing parameter setting on query plans.
I the setting cursor_sharing=force internally converts all the literals into bind variables and thus the plan used for first sql statement will be used for second statement as far as the second statement differs only in literals.
I had following queries where STRBRANDCD column has few records for value 'IGB' but huge number of records for value 'SAH'.
Thus STRBRANDCD='IGB' was always causing INDEX Scan whereas STRBRANDCD='SAH' was always causing FTS.
SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR=2007 AND STRBRANDCD='IGB';
SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR=2007 AND STRBRANDCD='SAH';
Here I started the test
1> cursor_sharing=force
Sys>alter system flush shared_pool;
Sys>select substr(s.sql_text,1,150) from v$sql s where upper(sql_text) like 'SELECT%*%FIN_VOU_HDR%STRBRANDCD%';
no rows selected
Then opened new session as application user
Alter session set cursor_sharing=force;
begin
for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='IGB')
loop
null;
end loop;
end;
/
begin
for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='SAH')
loop
null;
end loop;
end;
/
The Trace file produced is testenv_ora_12168 attached herewith
Sys>select substr(s.sql_text,1,150) from v$sql s where upper(sql_text) like 'SELECT%*%FIN_VOU_HDR%STRBRANDCD%';
SUBSTR(S.SQL_TEXT,1,150)
---------------------------------------------------------------------------------------------------------------------
SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR=2007 AND STRBRANDCD='IGB'
SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR=2007 AND STRBRANDCD='SAH'
Now Test2
2> cursor_sharing=similar
Exited earlier session of Application user
Sys>alter system flush shared_pool;
Sys>select substr(s.sql_text,1,150) from v$sql s where upper(sql_text) like 'SELECT%*%FIN_VOU_HDR%STRBRANDCD%';
no rows selected
Again opened new session of Application user
Alter session set cursor_sharing=similar;
begin
for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='IGB')
loop
null;
end loop;
end;
/
begin
for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='SAH')
loop
null;
end loop;
end;
/
The Trace file produced is testenv_ora_13286 attached herewith
Thus neither the literals were converted to binds nor the peeking happened.
Can anybody suggest what could be wrong here.
Thanks and Regards,
OraKaran
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
