Forum OpenACS Q&A: get sqlplus to send rows to /dev/null ?
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:set autotrace on explain set autotrace on set timing on
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.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
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...)
there is such a switch:
get rid of your
set autotrace on explain
set autotrace on
and just use
SET AUTOTRACE TRACEONLY EXPLAIN
A shell script that reads a query from a file, executes it, and reports timing results seems like the way to go. I have a simple version of that now.