Re: Bad performance calling stored procedures

Hi Robert

1)Try calling the refcursors from sql*plus itself and see if they are running slow

2)How does this query perform in sql plus

SELECT name,login,email
FROM appweb.User
WHERE login = pLogin;

is it fast ? Do you have a index on login column ? if no then create a index..

3)If the query is fast then you might have too lok at other things like your asp pages webserver as the bottleneck might not be in the database

regards
Hrishy

rbscheer_at_my-deja.com (Robert Scheer) wrote in message news:<cfd22ab6.0406241218.7169b8ef_at_posting.google.com>...
> Hi.
>
> I wrote some stored procedures used by a web application. As I am
> fairly new to Oracle, I am missing some concepts when creating these
> procedures, as a result, the application is suffering from poor
> performance everytime I call these procedures. I would like your help
> in order to identify potential mistakes when coding these procedures
> and I am posting a package and a procedure I use very often on the
> system, as an example:
>
> CREATE OR REPLACE PACKAGE WebCursor AS
> TYPE REF_CUR IS REF CURSOR;
> END WebCursor;
>
> CREATE OR REPLACE PROCEDURE appweb.GetUser (pLogin VARCHAR2, cUser OUT
> WebCursor.REF_CUR)
> AS
> BEGIN
> OPEN cUser FOR
> SELECT name,login,email
> FROM appweb.User
> WHERE login = pLogin;
> END GetUser;
>
>
> Almost all the procedures return a cursor and some of them receives
> input parameters. Can you help me to identify bad practices in my
> code?
>
> Thanks,
> Robert Scheer
Received on Thu Jun 24 2004 - 23:43:19 CDT