I've seen various ways in sqlplus to time queries. Here is a simple one:
set autotrace on;
set timing on;
select count(*) from table;
COUNT(*)
----------
164078859
Elapsed: 00:01:02.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2319838783
----------------------------------------------------------------------
+-------
| Id | Operation | Name | Rows | Cost (%CPU)| Time
+ |
----------------------------------------------------------------------
+-------
| 0 | SELECT STATEMENT | | 1 | 87965 (2)| 00:17:
+36 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| PRICE_IDX18 | 152M| 87965 (2)| 00
+:17:36 |
----------------------------------------------------------------------
+-------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
321153 consistent gets
321106 physical reads
0 redo size
422 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Using Perl DBI you can quickly do something like:
use DBI;
use strict;
use warnings;
use Benchmark;
my $h = DBI->connect('dbi:Oracle:host=xxx;sid=yyy','xxx','xxx');
my $s = $h->prepare(q/select * from action where action_id = ?/);
timethese(10000, {
'placeholders' => sub {placeholders($s)},
'quote' => sub {quote($h)}});
sub placeholders {
$_[0]->execute(1);
}
sub quote {
$s = $h->prepare("select * from action where action_id = " . $_[0]
+->quote(1));
$s->execute;
}
$ perl bm.pl
Benchmark: timing 10000 iterations of placeholders, quote...
placeholders: 3 wallclock secs ( 0.43 usr + 0.20 sys = 0.63 CPU) @
+15873.02/s (n=10000)
quote: 16 wallclock secs ( 5.51 usr + 1.32 sys = 6.83 CPU) @ 14
+64.13/s (n=10000)