Home » RDBMS Server » Performance Tuning » Cursor_Sharing settings not working (merged)
Cursor_Sharing settings not working (merged) [message #312666] Wed, 09 April 2008 10:16 Go to next message
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 Go to previous messageGo to next message
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
Re: Cursor_Sharing settings not working [message #312670 is a reply to message #312667] Wed, 09 April 2008 10:20 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Trace File attached

Thanks and Regards,
OraKaran
Re: Cursor_Sharing settings not working [message #312671 is a reply to message #312670] Wed, 09 April 2008 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you need to do it twice?

Regards
Michel
Re: Cursor_Sharing settings not working (merged) [message #312712 is a reply to message #312666] Wed, 09 April 2008 11:39 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
No only once

Attemp 1 : was with partial anonymizing
Attempt 2 :forgot to attach file

Regards,
OraKaran
Re: Cursor_Sharing settings not working (merged) [message #313039 is a reply to message #312666] Thu, 10 April 2008 08:13 Go to previous message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello,

Anybody please help me on this.

Regards,
OraKaran
Previous Topic: How to reduce the execution timing of SQL Query
Next Topic: database buffer cache
Goto Forum:
  


Current Time: Mon Dec 05 12:57:44 CST 2016

Total time taken to generate the page: 0.10944 seconds