Normally when tuning Oracle queries I run sqlplus inside of XEmacs,
and do:
set autotrace on explain
set autotrace on
set timing on
I do most of my tuning work there in Emacs and sqlplus. Occasionally
I'll also generate a trace file and run tkprof on it if that seems
helpful, like this:
SQL> alter session set sql_trace true;
SQL> -- run SQL queries here ---
SQL> alter session set sql_trace false;
$ tkprof trace_file output_file explain=username/password
Problem is, I often find myself needing to tune data-warehouse style
Oracle queries which return
many rows, and even with only
1,500 or so rows, returning the rows from Oracle to sqlplus to Emacs
adds a lot of extra time, and that time (and any noise it introduces)
is counted in the elapsed time reported by sqlplus. Running sqlplus
non-interactively from a shell script is definitely substantially
faster - but of course that's not a practical way to work.
The query may only take 5 or 30 s to run so I definitely do want to
actually run and time it, not just look at it's query plan only. But
I want to know how long it takes Oracle to run the query and get the
results out of Oracle, not how long it takes some particular
suite of tools to display all those rows to me.
Ideally I would like a sqlplus switch which says, "Yank all the rows
out of Oracle, but then immediately send them to /dev/null. But oh
yeah, do still show me all your other sqlplus output (elapsed
time, execution plan, statistics), just don't show me any of the
actual row values." And of course I'd need to easily toggle that
switch on and off, because often I do need to see the rows.
AFAIK no such switch exists, and the lack of one has been bugging me
for years now. What solutions or coping mechanisms have you come up
with? And oh yeah, PostgreSQL users, does it have any better
solution? (If I had the source code to sqlplus I would probably add
such a switch myself...)
