Re: optimal sql

From: Peter van Rijn <p.vanrijnREMOVE_at_THISzhew.nl>
Date: Wed, 11 Dec 2002 08:32:56 +0100
Message-ID: <uvdqhioig5llff@corp.supernews.com>

>
> I was under the distinct impression that autotrace always executed the
query and
> the 'traceonly explain' only supressed the output.
>
> A better solution for a long running query would be explain plan.
>
> Anyone confirm / deny this?

Hi Steve,

It's true, that is what the SQL*Plus User Guide tells us. But it is not what is happening. And I'll prove it.

Take this not so small table GH_FINMUT and do a count:

GHT> set timing on
GHT> select count(*) from gh_finmut;

  COUNT(*)

---

  10934847

Elapsed: 00:00:23.10

So this count takes roughly 23 seconds.
Now we will set tracing on, and we will enable autotrace:

GHT> alter session set sql_trace true;

Session altered.

GHT> set autotrace traceonly explain
GHT> select count(*) from GH_FINMUT;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'GH_FINMUT' GHT> alter session set sql_trace false;

Session altered.

This select is taking less than 1 second: this is PROOF 1!

Now we will use tkprof to see what actually happened. In the tkprof output file we will find:

---

---

select count(*)
from
 GH_FINMUT call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 112 (GH20PLUS)

In plain English: we did parse, but not execute or fetch. This is PROOF 2.

QED Kind regards,
Peter Received on Wed Dec 11 2002 - 01:32:56 CST