Forum OpenACS Q&A: get sqlplus to send rows to /dev/null ?

Request notifications

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...)

Collapse
Posted by Brian Fenton on
Hi Andrew
there is such a switch:
get rid of your
set autotrace on explain
set autotrace on

and just use
SET AUTOTRACE TRACEONLY EXPLAIN

Brian

Collapse
Posted by Andrew Piskorski on
No Brian, traceonly does not execute the query, it only shows you its execution plan, which is exactly what I said I didn't want. I want execution times, but uncorrupted by the (large!) time it takes for sqlplus to spit the rows into an Emacs buffer

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.

Collapse
Posted by Brian Fenton on
Oops! Sorry Andrew, I shouldn't be answering forum postings before my morning coffee. ;-)
Collapse
Posted by Andrew Piskorski on
Yeah, I've done the same. :)
Collapse
Posted by Andrew Grumet on
Andrew, I don't have an answer about /dev/null, but here's one tactic I sometimes use: replace the fetched column list with a count(*) so that you only get one row back.  It's not an exact method, because iirc the query optimizer pays attention to what columns you asked for, but the performance is mostly determined by the stuff that comes after WHERE, which stays the same.
Collapse
Posted by Guan Yang on
How about spooling the output to a /tmp file? Or what about spooling to /dev/null - would that work?
Collapse
Posted by Stewart Bryson on
SET TERMOUT OFF

This command tells SQL-PLUS not to return the rows. Then I do a "alter session set sql_trace=true;" and then tkprof the resulting trace file to see the elapsed time.