Sort in PL/SQL with a Package 2

Sim · Mar 16, 2000

Thx but this was not the solution, 
 
my task is to create a package with the bubble sort and quick sort algorithm. The "select...order by..." clause, Ok thats easy

) 
 
The Problem is, that I have to translate this two alogrithm from C++ into Oracle PL/SQL to stop the elapsed time for sort the random numbers. I must compare the time from C++ and PL/SQL. 
 
It's a little bit heavy! 
 
SiM

carp · Mar 16, 2000

SiM - 
Like Mike, I see nothing wrong with your english. 
 
Catching the times is fairly easy in PL/SQL. 
In your procedure invocation, include an argument (p_elapsed OUT NUMBER). 
Declare two local variables, 
v_start DATE; 
v_stop DATE; 
 
At the beginning of your procedure, issue the following command: 
SELECT sysdate INTO v_start; 
 
Similarly, at the end of your routine issue: 
SELECT sysdate INTO v_start; 
 
Finally, set the value for your argument: 
 
p_elapsed := (v_stop - v_start)*86400; 
 
This will give you how many seconds the procedure took from start to finish. 
Unfortunately, Oracle won't let you track time to a higher granularity than a second.

carp · Mar 20, 2000

Just realized my above entry is erroneous. 
It should read: 
 
At the beginning of your procedure, issue the following command: 
SELECT sysdate INTO v_start; 
 
Similarly, at the end of your routine issue: 
SELECT sysdate INTO v_stop; 
 
The original post would have you overwriting your start time and then trying to perform math with an unitialized variable. 
Sorry for the foul-up!

yaffle · Mar 21, 2000

SiM, you may need a from clause on your select, i.e. 
SELECT sysdate INTO v_stop FROM sys.dual; 
If you're getting problems with the algorithm consistently running too fast, e.g. 0 or 1 seconds, or similarly useless measures, try using a loop to invoke the algorithm 100 or 1000 times between your start and stop timings. 
You could also try putting your algorithm in a PL/SQL procedure and invoking it from SQL*Plus with timings on: 
 
SQL> set timing on 
 
SQL> execute my_proc; 
 
real: 5398 
 
SQL> 
 
I think the timings this gives go to the millisecond level, though it's probably implementation specific, so check the SQL*Plus manual for your installation for what it says about set timing.

carp · Mar 21, 2000

Yaffle - 
Thanks for catching the obvious oversight in the SELECT statements. I can't believe I missed that! 
 
According to Oracle, the number returned by setting timing on (in your example, real: 5398) does not correspond to an actual unit of time. It is a derived value that takes into account many factors based on resource usage. It is useful for benchmarking, but will not give you an actual time value. Also, Oracle does not track time beyond hundredths of a second. 
 
For timing purposes, probably your best bet is to use sql_trace. 
You might want to use a script file that turns trace and timed_statistics on and off. This will minimize the impact on your system: 
 
ALTER SESSION SET TIMED_STATISTICS=TRUE; 
ALTER SESSION SET SQL_TRACE=TRUE; 
EXECUTE sort_procedure; 
ALTER SESSION SET SQL_TRACE=FALSE; 
ALTER SESSION SET TIMED_STATISTICS=FALSE; 
 
After running this script, you will use tkprof to reformat your trace file. 
 
Here is a copy of such a session (in this case, the procedure name is 'quack'): 
 
 
SQL> set timing off 
SQL> alter session set timed_statistics=true; 
SQL> alter session set sql_trace=true; 
SQL> execute quack; 
SQL> alter session set sql_trace=false; 
SQL> alter session set timed_statistics=false; 
 
This generated a trace file called ora00283.trc. 
 
Running tkprof: 
 
C:\>tkprof ora00283.trc c:\output.txt sys=NO 
 
This will read the trace file and put formatted information into an output file called output.txt. 
 
Looking at this file: 
 
Trace file: ora00283.trc 
Sort options: default 
 
******************************************************************************** 
count = number of times OCI procedure was executed 
cpu = cpu time in seconds executing 
elapsed = elapsed time in seconds executing 
disk = number of physical reads of buffers from disk 
query = number of buffers gotten for consistent read 
current = number of buffers gotten in current mode (usually for update) 
rows = number of rows processed by the fetch or execute call 
******************************************************************************** 
. 
. 
. 
BEGIN quack; END; 
 
 
call count cpu elapsed disk query current rows 
------- ------ ------- - ---------- ---------- ---------- ---------- ---------- 
Parse 2 0.04 0.04 0 0 0 0 
Execute 2 0.05 0.05 0 0 0 2 
Fetch 0 0.00 0.00 0 0 0 0 
------- ------ -------- ---------- ---------- ---------- ---------- ---------- 
total 4 0.09 0.09 0 0 0 2 
 
Misses in library cache during parse: 0 
Optimizer goal: CHOOSE 
Parsing user id: 25 
******************************************************************************** 
 
We see this procedure took .09 seconds to complete. However, notice that almost half of this time (.04 seconds) was spent parsing. A second run would probably show a lower overall time, since the code would now be in the library cache and parsing would not be necessary. It's not a bad idea to run several executions and then take the average time. This helps minimize the effects of fluctuations in resource usage, data location, etc. 
 
I think this is about as good as it gets for timing purposes.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Sort in PL/SQL with a Package 2

Sim

Programmer

carp

MIS

carp

MIS

yaffle

MIS

carp

MIS

Similar threads

Part and Inventory Search

Sponsor