Re: Estimate a query time

From: <stevedhoward_at_gmail.com>
Date: 7 Apr 2006 11:26:02 -0700
Message-ID: <1144434362.277038.279580@i40g2000cwc.googlegroups.com>

...also, I can't remember if it the time estimate was in 9.x (my apologies if it wasn't), but the explain plan in 10G actually shows the estimated time, as shown below. On my highly unscientific test, it proved to be pretty close...

SQL> explain plan for
  2 select a.c,b.c from
  3 (select c from t0407) a,
  4 (select c from t0407) b;

Explained.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT

Plan hash value: 2814017309

| 0 | SELECT STATEMENT | | 24M| 619M| 14504 (2)|
00:02:55 |
| 1 | MERGE JOIN CARTESIAN| | 24M| 619M| 14504 (2)|
00:02:55 |
| 2 | TABLE ACCESS FULL | T0407 | 4999 | 64987 | 5 (0)|
00:00:01 |
| 3 | BUFFER SORT | | 4999 | 64987 | 14498 (2)|
00:02:54 |
| 4 | TABLE ACCESS FULL | T0407 | 4999 | 64987 | 3 (0)|
00:00:01 |

PLAN_TABLE_OUTPUT

Note

dynamic sampling used for this statement

15 rows selected.

SQL> set timing on
SQL> set autotrace traceonly;
SQL> select a.c,b.c from

2 (select c from t0407) a,
3 (select c from t0407) b;

24990001 rows selected.

Elapsed: 00:03:06.49

Execution Plan

Plan hash value: 2814017309

Note

dynamic sampling used for this statement

Statistics

          0  recursive calls
          0  db block gets
       5023  consistent gets
          0  physical reads
          0  redo size

435772452 bytes sent via SQL*Net to client 18326381 bytes received via SQL*Net from client 1666002 SQL*Net roundtrips to/from client

          1  sorts (memory)
          0  sorts (disk)

24990001 rows processed

SQL> Received on Fri Apr 07 2006 - 13:26:02 CDT