Tracle PL/SQL code [message #262931] |
Tue, 28 August 2007 09:47  |
lotusdeva
Messages: 201 Registered: March 2005
|
Senior Member |
|
|
Hello --
I was wondering if anyone can point me in the right direction. I have an application whereby one part of it is performing very slow. There are probably 7 PL/SQL packages involved. What is the best way to trace PL/SQL code? I can run explain plans, etc. But I know that there is a way to turn on tracing so that I can see where the application is getting slow.
P.S. I checked and we don't have DBMS_PROFILER installed...
Thank you!
|
|
|
|
Re: Tracle PL/SQL code [message #262961 is a reply to message #262936] |
Tue, 28 August 2007 11:09   |
lotusdeva
Messages: 201 Registered: March 2005
|
Senior Member |
|
|
Right. That would show execution statistics (explain plan, etc). I was asking about tracing PL/SQL code. Like I have 7 packages and something there performs slowly. I know you can alter session and set pl/sql in debug mode and then compile each package with debug as follows:
ALTER SESSION SET PLSQL_DEBUG=TRUE
ALTER PACKAGE some_package COMPILE DEBUG;
My question was more in this direction...Are there any utilities that I can use (I have full version TOAD, so I do sql optimization there). I guess what I am looking for is something that would step through all the code and show me where it takes the longest. Like in C ,you can step through code with dbx (I know, old school stuff, but still...).
Thank you!
|
|
|
|
|
|
|
Re: Tracle PL/SQL code [message #263127 is a reply to message #263012] |
Wed, 29 August 2007 02:43   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Did you already trace the sql-statements? 9 out of 10 performance bugs can be traced to either lengthy sql statements, or sql statements that get executed excessively.
Both are traceable by using a sql-trace.
(Much easier to do)
|
|
|
|
|
|