PL/SQL timers and where things are kept...

Hi all,

I'm a newbie Oracle guy and I have a few questions...apologies if this is in the wrong forum :)

- I'm trying to write some benchmarking software in PL/SQL and want to calculate the time a query takes in milliseconds. Is there some sort of timer function in PL/SQL that would do this? Something like =Millisecs() or suchlike? I need two millisecond calculations and work out the time by subtracting one from the other.

- In which tables are the following things stored and how can I get info on the details of each? Roles, Privileges, PL/SQL created Types, Procedures and Triggers. Also, how would I alter these things via SQL?

Thank you for any info on the above....sorry its such a lot of questions, but I'm very new at this....I'm really just a VB programmer, not a database guy (yet).

Cheers,

Jon.

- timer: http://www.csee.umbc.edu/help/oracle...ti.htm#1002103

- data dictionary: http://www.csee.umbc.edu/help/oracle...90/ch2.htm#745
You don't modify this directly - it is the result of "grant" commands etc . . . you will need to spend a long time reading a good book.

Good luck!

Check out the Oracle supplied dbms_utility package's get_time procedure. It gives times in hundredths of a second.

Here's an example:

sys@AMSAA> declare
2
3 l_start number;
4 l_end number;
5 l_dummy number;
6
7 begin
8
9 l_start := dbms_utility.get_time;
10
11
12 select count(*)
13 into l_dummy
14 from user_objects;
15
16 l_end := dbms_utility.get_time;
17
18
19 dbms_output.put_line('Time to run: ' ||
to_char(l_end - l_start) );
20
21 end;
22
23 /
Time to run: 5

PL/SQL procedure successfully completed.