Home » SQL & PL/SQL » SQL & PL/SQL » display elapsed time within PL/SQL
display elapsed time within PL/SQL [message #221800] Wed, 28 February 2007 09:01 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
All --

I have several queries that run sort of slow in SQL (these are called from a cold fusion application). I optimize them, they run better, but still not as good as we want them to. So, we decided to package all the queries in a pl/sql package so that they are stored. Some seem to perform better, but I do not see consistency. I can run an individual query within TOAD and see how long it takes. When I call an individual procedure, it seems that it takes longer. Is there a way to show elapsed time within PL/SQL to see how long it takes to execute each proc? I tied to use DBMS_UTILITY.GET_TIME, but I seem to get 0 or 1 for the result. I would like to get something similar to what TOAD outputs. Like a single query took 325ms to run....Thank you!

Re: display elapsed time within PL/SQL [message #221807 is a reply to message #221800] Wed, 28 February 2007 09:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Queries do NOT run faster when run from packages then run from stand-alone procedures or even without wrapping procedures.
If your queries really take too long, don't try to tune the final 1 percent; go for the first 90%.
This first 90% of the performance-gain is usually retrieved by either changing the execution-plans or changing your program-logic.
Re: display elapsed time within PL/SQL [message #221808 is a reply to message #221800] Wed, 28 February 2007 09:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Dbms_utility.get time returns times to a granularity of 1/100 of a second. So, if your query is runs in 0-4 ms then the elapsed time will be 0, and if it runs in 5 ms, you'll get an elapsed time of 1.

I can think of no reason at all why putting the queries in packages would make them run quicker.

Running queries in TOAD can be deceptive - In Toad, you'll see a result as soon as the first rows are returned. Running the procedure, you'll have to wait until all the rows are processed to get a result back.
Re: display elapsed time within PL/SQL [message #221819 is a reply to message #221807] Wed, 28 February 2007 10:02 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
oh, I see. But what is the difference between a procedure that is in a package and a stand-alone procedure? The thing is that there are like 10 different queries, so it just made sense to package them in one logical unit...Thank you!
Re: display elapsed time within PL/SQL [message #221820 is a reply to message #221808] Wed, 28 February 2007 10:08 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
My understanding was that PL/SQL stored procedures are more native to the database and therefore, would run faster.

http://www.dba-oracle.com/art_ioug_proc.htm

package is just a logical unit to keep the procs in, am I wrong?
Re: display elapsed time within PL/SQL [message #221823 is a reply to message #221800] Wed, 28 February 2007 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>My understanding was that PL/SQL stored procedures are more native to the database and therefore, would run faster.

Faster than what?
PL/SQL <> SQL.
They have different "engines" and a context switch is required (overhead) when going from one to the other.
Placing slow running SQL inside PL/SQL will NOT make the SQL query run faster!
In fact doing so will make it run marginally slower.
Re: display elapsed time within PL/SQL [message #221824 is a reply to message #221820] Wed, 28 February 2007 10:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>My understanding was that PL/SQL stored procedures are more native to the database and therefore, would run faster.
True , When you are comparing with any other out-of-box programming environment (say, pl/sql storeprocedure Vs Visual Basic code)
As Frank already said
>>Queries do NOT run faster when run from packages then run from stand-alone procedures
If you can do it sql, do it in sql.
Throwing sql into pl/sql DOES NOT offer any advantage in performance.

PS: You are referring to an article that is almost 7 years old.
Lot changed (technology and understanding the concepts behind technology) in between Smile
Re: display elapsed time within PL/SQL [message #221827 is a reply to message #221800] Wed, 28 February 2007 10:23 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
thank you!
Re: display elapsed time within PL/SQL [message #221843 is a reply to message #221824] Wed, 28 February 2007 12:06 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Mahesh Rajendran wrote on Wed, 28 February 2007 11:18

PS: You are referring to an article that is almost 7 years old.
Lot changed (technology and understanding the concepts behind technology) in between Smile


And, that site is generally panned by many people in the IT world.

* the statement above may or may not represent the author's opinion.
Re: display elapsed time within PL/SQL [message #221857 is a reply to message #221843] Wed, 28 February 2007 13:01 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Also to be fair to the article, it does not claim that simply copying and pasting SQL into a procedure will make that SQL perform better.
Re: display elapsed time within PL/SQL [message #221862 is a reply to message #221857] Wed, 28 February 2007 13:16 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Oracle 9i Application Developer's guide:

Better Performance
If your application is database intensive, you can use PL/SQL blocks to group SQL statements before sending them to Oracle for execution. This can drastically reduce the communication overhead between your application and Oracle.

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. A single call can start a compute-intensive stored procedure, reducing network traffic and improving round-trip response times. Executable code is automatically cached and shared among users, lowering memory requirements and invocation overhead.

Obviously, this is not 7 years old...
Re: display elapsed time within PL/SQL [message #221864 is a reply to message #221862] Wed, 28 February 2007 13:24 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
lotusdeva wrote on Wed, 28 February 2007 13:16
Oracle 9i Application Developer's guide:

Better Performance
If your application is database intensive, you can use PL/SQL blocks to group SQL statements before sending them to Oracle for execution. This can drastically reduce the communication overhead between your application and Oracle.



The above is not just saying "throw it in a procedure". It is talking about reducing the context switches between sql and plsql, and from my take on it, particularly talking about when you have multiple sql statements that need to be executed one right after the other. For example, put 5 statements all inside a single loop block, rather than 5 different loop blocks.

The below is talking specifically about plsql code. Procedure calls. Not sql statement executions.

Quote:

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. A single call can start a compute-intensive stored procedure, reducing network traffic and improving round-trip response times. Executable code is automatically cached and shared among users, lowering memory requirements and invocation overhead.

Obviously, this is not 7 years old...


And in general, even if I were working on a 9i production database, I would, whenever possible, use the 10g Performance Tuning and Application Deveoper Guide Documentation Books rather than the 9i versions. They are just much better. 9i is what, 5 or 6 years old now?
Re: display elapsed time within PL/SQL [message #221865 is a reply to message #221864] Wed, 28 February 2007 13:26 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
yeah, but we are on 9i Sad
Re: display elapsed time within PL/SQL [message #221867 is a reply to message #221865] Wed, 28 February 2007 13:33 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
lotusdeva wrote on Wed, 28 February 2007 13:26
yeah, but we are on 9i Sad


Fair enough. But it could be worse, you could be on 8i. Smile

But...just because your database is 6 years old, doesn't mean you have to read information and tips and techniques and advice that is that old. In other words, use the 10g documentation even though you have a 9i database. For certain specific things, you'll have to consult the 9i docs to see "how things worked back then". But that is mainly for syntax and technical issues like that. For "what is the best way to do that" type things, the 10g docs are just far far superior. Especially the tuning guide.


Re: display elapsed time within PL/SQL [message #221870 is a reply to message #221867] Wed, 28 February 2007 13:37 Go to previous message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
ok, thank you! Razz
Previous Topic: alternative for min() function
Next Topic: SQL error checking, need help.
Goto Forum:
  


Current Time: Mon Dec 05 08:37:00 CST 2016

Total time taken to generate the page: 0.10769 seconds