Bad performance calling stored procedures

[Robert Scheer]

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

[hrishy]

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

rb******@my-deja.com (Robert Scheer) wrote in message news:<cf**************************@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