Timing an ALL_ROWS query
A common complaint in SQL is that "it runs in 5 seconds in SQL*Plus, but takes hours in Production. Why?"
The reason is because SQL*Plus and most GUI SQL tools display rows as soon as they are fetched. In this way, you can
SELECT * FROM big_big_table and it will display the first 20 or so rows in the table in a fraction of a second, then go back for more. The SQL is not really finishing in seconds; if you timed how long it took to retrieve every row, you'd see that it takes just as long as in Production.
Easy fixed right? Just fetch all of the rows and time that! No, that will over-estimate the time.
SQL*Plus scrolls the results over the screen; the speed at which it can do this is limited by the speed at which your PC can render the scrolling text (which is invariably slower than the database and network can supply the results).
If you are using a GUI, it will want to buffer all the results in memory so that they can be displayed in an attractive grid. This too will slow things down.
Some people change the SQL to perform
SELECT COUNT(*) instead of the selected columns. The problem is that if you do not
SELECT the columns, Oracle does not need to go to the table; it can resolve a
COUNT(*) from indexes alone. A different plan means different timing.
And it doesn't help to just wrap the entire query like this:
SELECT COUNT(*) FROM ( SELECT col1, col2, ... ). The optimizer is wise to that; if a selected column in an inline view is not referenced in the outer-query, the CBO will ignore it.
If you sort the results, Oracle will identify all the rows before it returns the first one. Then you just need to time how long it takes to return the first row. This works - kind of - but it is slower because you have to perform an additional sort on the data. For large data volumes, this time could be considerable.
This used to be my favourite. In SQL*Plus you can hide the results from the screen using
SET TERMOUT OFF. You are still timing the disk activity to spool the data to a file; this can be mitigated in Unix by spooling to
/dev/null. Even if you do this, the data is still coming across the network to SQL*Plus; this will not be an accurate comparison to a PL/SQL program that processes the data internally.
In SQL*Plus, you can hide the results of the SQL with Autotrace:
SET AUTOTRACE TRACEONLY SELECT ...
This is a pretty good method, but it is not much help for users of GUIs. Also, the server sends the results back to SQL*Plus, SQL*Plus just doesn't collect them. I don't know whether there is a cost associated with this.
Wrap each of the selected columns in a
MAX() function. This gives a very reliable result, but is a bit tedious if you have selected a lot of columns.
My favourite method - I just discovered it: wrap the SQL in
SELECT * FROM ( .... ) WHERE ROWNUM > 1. Since
ROWNUM is applied as the rows are projected, there must always be a first row returned.
WHERE ROWNUM > 1 causes the first row not to be displayed, making the second row the new first row, which is then not displayed, and so on. In this way, every row gets rejected.
Note that this is different to a constant false predicate like
WHERE 1=0. Constant predicates are run before the data is accessed, not after; so the SQL would return immediately.
Check it out:
SQL> select count(*) from ef_actl_rev; COUNT(*) ---------- 2889477 Elapsed: 00:00:01.23 SQL> select * from ef_actl_rev where rownum > 1; no rows selected Elapsed: 00:00:03.15 SQL> select * from ef_actl_rev where 1=0; no rows selected Elapsed: 00:00:00.01
I only just discovered this method myself, so I can't guarantee that it does not affect the execution plan - preliminary testing is promising though.