SQL & PL/SQL

Hi,

I have two stored procedures, both of which are used to query a database to find a particular book, based on ISBN. One sproc searches our main product catalogue and the other searches our suppliers feed catalogues. The stored procedures are called from a C# application via a search tool and the user is able to search on either our catalogue or our suppliers. The appropriate procedure is called based on the users choices.

However, the following behaviour is observed

I search for an ISBN (is a varchar2 field, as isbn's may contain an X if the checksum digit equates to 10) on a feed, so uses the FEED SPROC. The book is found and returned to the app in about 0.5 seconds. I can repeat this as often as i like on different books etc. always works fine.

I then do the same search but against our own catalogue, so uses our CATALOGUE SPROC. Again the book is found quickly, and the search can be repeated with the same results.

If i then go back and run our FEED SPROC then the search time increases to about 3 minutes !

Both the feed and our catalogue is in the same database, although different schema's the connections will be pooled through our app server.

I can repliacte this every single time. I think i have narrowed doen the cause of this behaviour to a few lines of code in our CATALOGUE SPROC:


-- store values
select value into v_vch_NLS_COMP from nls_session_parameters nsp where nsp.parameter = 'NLS_COMP';
select value into v_vch_NLS_SORT from nls_session_parameters nsp where nsp.parameter = 'NLS_SORT';

-- Ensure case insensitivity throughout
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
.
.
.
do other stuff
.
.
.
-- restore session variables
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = ' || v_vch_NLS_COMP;
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = ' || v_vch_NLS_SORT;


If i remove this code then all is well, so i am assuming that using ALTER SESSION inside a stored procedure is the cause of the problem as it would be changing the execution plan of the FEEDS SPROC in some manner? Any ideas? I know i can just rewrite the sproc to avoid using this coding, but wanted to understand if i am doing something wrong by using ALTER SESSION in this manner?

Any pointers would be appreciated.

John Thompson

Software Architect,
play.com

Edited by: user7186902 on 27-May-2009 03:51