Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Sort in PL/SQL with a Package

Sort in PL/SQL with a Package

Sort in PL/SQL with a Package

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 :o)

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!


RE: Sort in PL/SQL with a Package

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.

RE: Sort in PL/SQL with a Package

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!

RE: Sort in PL/SQL with a Package

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


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.

RE: Sort in PL/SQL with a Package

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:

EXECUTE sort_procedure;

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close