Home » SQL & PL/SQL » SQL & PL/SQL » Tracle PL/SQL code
Tracle PL/SQL code [message #262931] Tue, 28 August 2007 09:47 Go to next message
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 #262936 is a reply to message #262931] Tue, 28 August 2007 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE;
Re: Tracle PL/SQL code [message #262961 is a reply to message #262936] Tue, 28 August 2007 11:09 Go to previous messageGo to next message
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 #262978 is a reply to message #262931] Tue, 28 August 2007 12:47 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
lotusdeva wrote on Tue, 28 August 2007 17:47
Hello --

P.S. I checked and we don't have DBMS_PROFILER installed...

Thank you!


So install it! Some tools (OK at least PL/SQL Developer Wink has user friendly interface to use it.

In case you cannot do that for whatever reasons there is always possibility insert some timings in the very code, using either sysdate or dbms_utility.get_time

Then just focus on the code piece that needs most of the time and divide it more while you find the most offensive part of code.

Gints Plivna
http://www.gplivna.eu
Re: Tracle PL/SQL code [message #262984 is a reply to message #262931] Tue, 28 August 2007 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
P.S. I checked and we don't have DBMS_PROFILER installed...

$ORACLE_HOME/rdbms/admin/profload.sql

Regards
Michel
Re: Tracle PL/SQL code [message #263002 is a reply to message #262984] Tue, 28 August 2007 14:04 Go to previous messageGo to next message
lotusdeva
Messages: 201
Registered: March 2005
Senior Member
What do I do after I install it? Does it generate a trace file?
Re: Tracle PL/SQL code [message #263012 is a reply to message #263002] Tue, 28 August 2007 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/search?remark=quick_search&word=dbms_profiler&tab_id=&format=ranked

Regards
Michel
Re: Tracle PL/SQL code [message #263127 is a reply to message #263012] Wed, 29 August 2007 02:43 Go to previous messageGo to next message
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)
Re: Tracle PL/SQL code [message #263245 is a reply to message #263127] Wed, 29 August 2007 08:45 Go to previous messageGo to next message
lotusdeva
Messages: 201
Registered: March 2005
Senior Member
yes, I traced sql and it all looks fine Sad
Re: Tracle PL/SQL code [message #265196 is a reply to message #262931] Wed, 05 September 2007 13:41 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Can you post a TKPROF?
I second Frank that over 90% of performance problems reside within SQL and NOT pl/sql code.

Michael
Re: Tracle PL/SQL code [message #265276 is a reply to message #265196] Wed, 05 September 2007 23:54 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
This one was solved in another thread. It turned out to be a sql after all.
Previous Topic: Create directory...
Next Topic: Display single digit numbers in double digits (Merged)
Goto Forum:
  


Current Time: Sun Feb 09 21:27:05 CST 2025