By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,824 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,824 IT Pros & Developers. It's quick & easy.

Bad performance calling stored procedures

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.