Home » SQL & PL/SQL » SQL & PL/SQL » Bad performance of PL/SQL functions
Bad performance of PL/SQL functions [message #39609] Fri, 26 July 2002 00:20 Go to next message
James
Messages: 120
Registered: June 2000
Senior Member
I am writing some functions in PL/SQL that will handle a big database. So the speed is very critical for me.

When I test my functions individually they seem to work fine, but when I test the entire application (ie I call one interface function and it calls other functions to do processing) the application is much much more slower!

Is this normal? I have done everything they suggest to gain speed!
I use PLS_INTEGERs, dont pass any large data structures in IN OUT or OUT mode, use bind variables in SQL queries that are inside loops, use BULK COLLECT to retrieve data from cursors...

But still my response time is dismal....please tell me why this is happening? and what can I do to improve the performance?

James

Report message to a moderator

Re: Bad performance of PL/SQL functions [message #39618 is a reply to message #39609] Fri, 26 July 2002 10:26 Go to previous messageGo to next message
jim
Messages: 74
Registered: July 2000
Member
What are these PL/SQL scripts doing?
INSERTS/UPDATES
How many rows in your tables.
Are you testing on the full set of data?

Hard to answer without some more info.

Report message to a moderator

Re: Bad performance of PL/SQL functions [message #39629 is a reply to message #39618] Mon, 29 July 2002 11:02 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
This is worth a read - it could apply in your case.
See ISNUM.doc in IS_NUM.zip at http://www.quest-pipelines.com/Pipelines/PLSQL/archives.htm#writings06

I'd start by doing a sql_trace or running autotrace from sqlplus.

Report message to a moderator

Previous Topic: sql query
Next Topic: oracle help
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 14:24:33 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.