Skip to Main Content
  • Questions

  • Estimate query execution time without running query

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Pierre.

Asked: February 02, 2011 - 10:05 am UTC

Last updated: January 15, 2013 - 10:35 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hello Tom,

We are using 10.2.0.4 Standard Edition (SE).
If I need an estimation of query execution time before running it I think I can use:

delete plan_table;
explain plan for <SQL statement>;
select time from plan_table where id = 0;


This is going to give me an estimation of query run time in seconds.
Is this correct ?

Is there any other better way with 10.2.0.4 SE or 11.2.0.2 SE ?

Thanks.



and Tom said...

This will work and is the way to get it - however....

Every single time you run explain plan - it will hard parse the query - every single time.

also, explain plan has some nuances

http://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html

If you have bind variables - you'll need to wrap them in to_date/to_numbers and the like to get a plan that is likely.

Remember also - that explain plan can very well show you a plan that isn't going to be used.

And most importantly, the time estimate is just that - an estimate - and it will be wildly inaccurate some times.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks again

A reader, February 03, 2011 - 2:38 am UTC


I dont fine "time" column

Amer Baig, May 30, 2011 - 2:40 pm UTC

I cant find time column? is there another column having similar vaule? i.e estimated execution time.

select * from v$version

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production

Tom Kyte
May 31, 2011 - 10:59 am UTC 

ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------



it is there, make sure you have a current plan table - if you have an old one from years gone by - you are missing out on things.

ops$tkyte%ORA10GR2> @?/rdbms/admin/utlxplan
ops$tkyte%ORA10GR2> rem
ops$tkyte%ORA10GR2> rem $Header: utlxplan.sql 08-may-2004.12:53:19 bdagevil Exp $ xplainpl.sql
ops$tkyte%ORA10GR2> rem
ops$tkyte%ORA10GR2> Rem Copyright (c) 1988, 2004, Oracle. All rights reserved.
ops$tkyte%ORA10GR2> Rem NAME
ops$tkyte%ORA10GR2> REM    UTLXPLAN.SQL
ops$tkyte%ORA10GR2> Rem  FUNCTION
ops$tkyte%ORA10GR2> Rem  NOTES
ops$tkyte%ORA10GR2> Rem  MODIFIED
ops$tkyte%ORA10GR2> Rem     bdagevil   05/08/04  - add other_xml column
ops$tkyte%ORA10GR2> Rem     bdagevil   06/18/03  - rename hint alias to object_alias
ops$tkyte%ORA10GR2> Rem     ddas       06/03/03  - increase size of hint alias column
ops$tkyte%ORA10GR2> Rem     bdagevil   02/13/03  - add plan_id and depth column
ops$tkyte%ORA10GR2> Rem     ddas       01/17/03  - add query_block and hint_alias columns
ops$tkyte%ORA10GR2> Rem     ddas       11/04/02  - revert timestamp column to DATE (PL/SQL problem)
ops$tkyte%ORA10GR2> Rem     ddas       10/28/02  - change type of timestamp column to TIMESTAMP
ops$tkyte%ORA10GR2> Rem     ddas       10/03/02  - add estimated_time column
ops$tkyte%ORA10GR2> Rem     mzait      04/16/02  - add row vector to the plan table
ops$tkyte%ORA10GR2> Rem     mzait      10/26/01  - add keys and filter predicates to the plan table
ops$tkyte%ORA10GR2> Rem     ddas       05/05/00  - increase length of options column
ops$tkyte%ORA10GR2> Rem     ddas       04/17/00  - add CPU, I/O cost, temp_space columns
ops$tkyte%ORA10GR2> Rem     mzait      02/19/98 -  add distribution method column
ops$tkyte%ORA10GR2> Rem     ddas       05/17/96 -  change search_columns to number
ops$tkyte%ORA10GR2> Rem     achaudhr   07/23/95 -  PTI: Add columns partition_{start, stop, id}
ops$tkyte%ORA10GR2> Rem     glumpkin   08/25/94 -  new optimizer fields
ops$tkyte%ORA10GR2> Rem     jcohen     11/05/93 -  merge changes from branch 1.1.710.1 - 9/24
ops$tkyte%ORA10GR2> Rem     jcohen     09/24/93 -  #163783 add optimizer column
ops$tkyte%ORA10GR2> Rem     glumpkin   10/25/92 -  Renamed from XPLAINPL.SQL
ops$tkyte%ORA10GR2> Rem     jcohen     05/22/92 -  #79645 - set node width to 128 (M_XDBI in gendef)
ops$tkyte%ORA10GR2> Rem     rlim       04/29/91 -  change char to varchar2
ops$tkyte%ORA10GR2> Rem     Peeler     10/19/88 - Creation
ops$tkyte%ORA10GR2> Rem
ops$tkyte%ORA10GR2> Rem This is the format for the table that is used by the EXPLAIN PLAN
ops$tkyte%ORA10GR2> Rem statement.  The explain statement requires the presence of this
ops$tkyte%ORA10GR2> Rem table in order to store the descriptions of the row sources.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table PLAN_TABLE (
  2          statement_id       varchar2(30),
  3          plan_id            number,
  4          timestamp          date,
  5          remarks            varchar2(4000),
  6          operation          varchar2(30),
  7          options            varchar2(255),
  8          object_node        varchar2(128),
  9          object_owner       varchar2(30),
 10          object_name        varchar2(30),
 11          object_alias       varchar2(65),
 12          object_instance    numeric,
 13          object_type        varchar2(30),
 14          optimizer          varchar2(255),
 15          search_columns     number,
 16          id                 numeric,
 17          parent_id          numeric,
 18          depth              numeric,
 19          position           numeric,
 20          cost               numeric,
 21          cardinality        numeric,
 22          bytes              numeric,
 23          other_tag          varchar2(255),
 24          partition_start    varchar2(255),
 25          partition_stop     varchar2(255),
 26          partition_id       numeric,
 27          other              long,
 28          distribution       varchar2(30),
 29          cpu_cost           numeric,
 30          io_cost            numeric,
 31          temp_space         numeric,
 32          access_predicates  varchar2(4000),
 33          filter_predicates  varchar2(4000),
 34          projection         varchar2(4000),
 35          time               numeric,
 36          qblock_name        varchar2(30),
 37          other_xml          clob
 38  );

Table created.

time column is Empty

Amer, May 31, 2011 - 4:10 pm UTC

Thanks for reply. I created new Plan_TABLE. But run Explain Plan for SQL doesnt fill my time column. Any idea?

Thanks in advance
Tom Kyte
June 01, 2011 - 8:23 am UTC

show cut and paste. You *are* using the CBO (cost based optimizer) right?

give us a complete example, like I do for you.

Time Field emty

Amer, June 01, 2011 - 8:55 am UTC

Thanks for reply. No SQL is showing value into time field. I tried short and long (time taking) SQL.

Please see one SQL below.

explain plan for
Select X.OWNER, X.TABLE_NAME, X.NUM_ROWS, X.LAST_ANALYZED, X.PARTITIONED,
Y.COLUMN_NAME, Y.DATA_TYPE, Y.NULLABLE, Y.COLUMN_ID, Y.LAST_ANALYZED
From
(Select OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS, NUM_ROWS, AVG_SPACE,
AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED
from all_all_tables) X,

(Select OWNER,TABLE_NAME, COLUMN_NAME, DATA_TYPE,
DATA_LENGTH, NULLABLE, COLUMN_ID, NUM_DISTINCT,
LOW_VALUE, HIGH_VALUE, NUM_NULLS, NUM_BUCKETS,
LAST_ANALYZED, SAMPLE_SIZE, AVG_COL_LEN
from all_tab_columns) Y

Where X.OWNER = Y.OWNER and
X.TABLE_NAME = Y.TABLE_NAME and
X.OWNER not in ('SYS', 'SYSTEM')
Order by Y.TABLE_NAME, Y.Column_Name, Y.COLUMN_ID

============================== PLAN_TABLE=================
STATEMENT_ID PLAN_ID TIMESTAMP REMARKS OPERATION OPTIONS OBJECT_NODE OBJECT_OWNER OBJECT_NAME OBJECT_ALIAS OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER SEARCH_COLUMNS ID PARENT_ID DEPTH POSITION COST CARDINALITY BYTES OTHER_TAG PARTITION_START PARTITION_STOP PARTITION_ID DISTRIBUTION CPU_COST IO_COST TEMP_SPACE ACCESS_PREDICATES FILTER_PREDICATES PROJECTION TIME QBLOCK_NAME
9072 6/1/2011 9:40:57 AM SELECT STATEMENT HINT: RULE 0 0
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ O@SEL$20 6 TABLE ANALYZED 1 0 1 1 SEL$20
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 O@SEL$20 INDEX (UNIQUE) ANALYZED 1 2 1 2 1 SEL$20
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ O@SEL$21 7 TABLE ANALYZED 3 0 2 2 SEL$21
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 O@SEL$21 INDEX (UNIQUE) ANALYZED 1 4 3 3 1 SEL$21
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ O@SEL$22 8 TABLE ANALYZED 5 0 3 3 SEL$22
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 O@SEL$22 INDEX (UNIQUE) ANALYZED 1 6 5 4 1 SEL$22
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ O@SEL$23 9 TABLE ANALYZED 7 0 4 4 SEL$23
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 O@SEL$23 INDEX (UNIQUE) ANALYZED 1 8 7 5 1 SEL$23
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ O@SEL$24 10 TABLE ANALYZED 9 0 5 5 SEL$24
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 O@SEL$24 INDEX (UNIQUE) ANALYZED 1 10 9 6 1 SEL$24
9072 6/1/2011 9:40:57 AM SORT ORDER BY 11 0 1 6 0 SEL$1898785B
9072 6/1/2011 9:40:57 AM FILTER 12 11 2 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 13 12 3 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 14 13 4 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 15 14 5 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 16 15 6 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 17 16 7 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 18 17 8 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 19 18 9 1
9072 6/1/2011 9:40:57 AM VIEW SYS ALL_ALL_TABLES ALL_ALL_TABLES@SEL$2 2 VIEW 20 19 10 1 SET$1
9072 6/1/2011 9:40:57 AM UNION-ALL 21 20 11 1 SET$1
9072 6/1/2011 9:40:57 AM FILTER 22 21 12 1 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM NESTED LOOPS 23 22 13 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 24 23 14 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 25 24 15 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 26 25 16 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 27 26 17 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 28 27 18 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 29 28 19 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 30 29 20 1
9072 6/1/2011 9:40:57 AM MERGE JOIN 31 30 21 1
9072 6/1/2011 9:40:57 AM FIXED TABLE FULL SYS X$KSPPI KSPPI@SEL$4 TABLE (FIXED) 32 31 22 1 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM SORT JOIN 33 31 22 2
9072 6/1/2011 9:40:57 AM FIXED TABLE FULL SYS X$KSPPCV KSPPCV@SEL$4 TABLE (FIXED) 34 33 23 1 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM TABLE ACCESS FULL SYS OBJ$ O@SEL$4 31 TABLE ANALYZED 35 30 21 2 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS TAB$ T@SEL$4 30 CLUSTER ANALYZED 36 29 20 2 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ# T@SEL$4 INDEX (CLUSTER) ANALYZED 1 37 36 21 1 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ CX@SEL$4 32 TABLE ANALYZED 38 28 19 2 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 CX@SEL$4 INDEX (UNIQUE) ANALYZED 1 39 38 20 1 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 CO@SEL$4 INDEX (UNIQUE) ANALYZED 1 40 27 18 2 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS USER$ CU@SEL$4 33 CLUSTER ANALYZED 41 26 17 2 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_USER# CU@SEL$4 INDEX (CLUSTER) ANALYZED 1 42 41 18 1 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS SEG$ S@SEL$4 28 CLUSTER ANALYZED 43 25 16 2 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_FILE#_BLOCK# S@SEL$4 INDEX (CLUSTER) ANALYZED 3 44 43 17 1 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS TS$ TS@SEL$4 27 CLUSTER ANALYZED 45 24 15 2 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_TS# TS@SEL$4 INDEX (CLUSTER) ANALYZED 1 46 45 16 1 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS USER$ U@SEL$4 26 CLUSTER ANALYZED 47 23 14 2 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_USER# U@SEL$4 INDEX (CLUSTER) ANALYZED 1 48 47 15 1 SEL$07BDC5B4
9072 6/1/2011 9:40:57 AM NESTED LOOPS 49 22 13 2 SEL$373CB748
9072 6/1/2011 9:40:57 AM FIXED TABLE FULL SYS X$KZSRO X$KZSRO@SEL$6 TABLE (FIXED) 50 49 14 1 SEL$373CB748
9072 6/1/2011 9:40:57 AM INDEX RANGE SCAN SYS I_OBJAUTH2 OA@SEL$5 INDEX ANALYZED 2 51 49 14 2 SEL$373CB748
9072 6/1/2011 9:40:57 AM FIXED TABLE FULL SYS X$KZSPR X$KZSPR@SEL$9 TABLE (FIXED) 52 22 15 3 SEL$DFD66ADD
9072 6/1/2011 9:40:57 AM FILTER 53 21 12 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM NESTED LOOPS 54 53 13 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 55 54 14 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 56 55 15 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 57 56 16 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 58 57 17 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 59 58 18 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 60 59 19 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 61 60 20 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 62 61 21 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 63 62 22 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 64 63 23 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS OUTER 65 64 24 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 66 65 25 1
9072 6/1/2011 9:40:57 AM NESTED LOOPS 67 66 26 1
9072 6/1/2011 9:40:57 AM MERGE JOIN 68 67 27 1
9072 6/1/2011 9:40:57 AM FIXED TABLE FULL SYS X$KSPPI KSPPI@SEL$11 TABLE (FIXED) 69 68 28 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM SORT JOIN 70 68 28 2
9072 6/1/2011 9:40:57 AM FIXED TABLE FULL SYS X$KSPPCV KSPPCV@SEL$11 TABLE (FIXED) 71 70 29 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS FULL SYS COL$ TC@SEL$11 50 CLUSTER ANALYZED 72 67 27 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS COLTYPE$ AC@SEL$11 47 CLUSTER ANALYZED 73 66 26 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ SO@SEL$11 53 TABLE ANALYZED 74 65 25 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 SO@SEL$11 INDEX (UNIQUE) ANALYZED 1 75 74 26 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS TAB$ T@SEL$11 45 CLUSTER ANALYZED 76 64 24 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ CX@SEL$11 51 TABLE ANALYZED 77 63 23 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 CX@SEL$11 INDEX (UNIQUE) ANALYZED 1 78 77 24 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ O@SEL$11 46 TABLE ANALYZED 79 62 22 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 O@SEL$11 INDEX (UNIQUE) ANALYZED 1 80 79 23 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ1 CO@SEL$11 INDEX (UNIQUE) ANALYZED 1 81 61 21 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS USER$ SU@SEL$11 54 CLUSTER ANALYZED 82 60 20 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_USER# SU@SEL$11 INDEX (CLUSTER) ANALYZED 1 83 82 21 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS USER$ CU@SEL$11 52 CLUSTER ANALYZED 84 59 19 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_USER# CU@SEL$11 INDEX (CLUSTER) ANALYZED 1 85 84 20 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS SEG$ S@SEL$11 43 CLUSTER ANALYZED 86 58 18 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_FILE#_BLOCK# S@SEL$11 INDEX (CLUSTER) ANALYZED 3 87 86 19 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS TS$ TS@SEL$11 42 CLUSTER ANALYZED 88 57 17 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_TS# TS@SEL$11 INDEX (CLUSTER) ANALYZED 1 89 88 18 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS USER$ U@SEL$11 41 CLUSTER ANALYZED 90 56 16 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_USER# U@SEL$11 INDEX (CLUSTER) ANALYZED 1 91 90 17 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ TY@SEL$11 48 TABLE ANALYZED 92 55 15 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX RANGE SCAN SYS I_OBJ3 TY@SEL$11 INDEX ANALYZED 1 93 92 16 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS USER$ TU@SEL$11 49 CLUSTER ANALYZED 94 54 14 2 SEL$285A8194
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_USER# TU@SEL$11 INDEX (CLUSTER) ANALYZED 1 95 94 15 1 SEL$285A8194
9072 6/1/2011 9:40:57 AM NESTED LOOPS 96 53 13 2 SEL$631DDF81
9072 6/1/2011 9:40:57 AM FIXED TABLE FULL SYS X$KZSRO X$KZSRO@SEL$13 TABLE (FIXED) 97 96 14 1 SEL$631DDF81
9072 6/1/2011 9:40:57 AM INDEX RANGE SCAN SYS I_OBJAUTH2 OA@SEL$12 INDEX ANALYZED 2 98 96 14 2 SEL$631DDF81
9072 6/1/2011 9:40:57 AM FIXED TABLE FULL SYS X$KZSPR X$KZSPR@SEL$16 TABLE (FIXED) 99 53 15 3 SEL$B584FDD1
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS USER$ U@SEL$19 14 CLUSTER ANALYZED 100 19 10 2 SEL$1898785B
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_USER1 U@SEL$19 INDEX (UNIQUE) ANALYZED 1 101 100 11 1 SEL$1898785B
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ O@SEL$19 12 TABLE ANALYZED 102 18 9 2 SEL$1898785B
9072 6/1/2011 9:40:57 AM INDEX RANGE SCAN SYS I_OBJ2 O@SEL$19 INDEX (UNIQUE) ANALYZED 2 103 102 10 1 SEL$1898785B
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS COL$ C@SEL$19 11 CLUSTER ANALYZED 104 17 8 2 SEL$1898785B
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ# C@SEL$19 INDEX (CLUSTER) ANALYZED 1 105 104 9 1 SEL$1898785B
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS COLTYPE$ AC@SEL$19 15 CLUSTER ANALYZED 106 16 7 2 SEL$1898785B
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS HIST_HEAD$ H@SEL$19 13 TABLE ANALYZED 107 15 6 2 SEL$1898785B
9072 6/1/2011 9:40:57 AM INDEX RANGE SCAN SYS I_HH_OBJ#_INTCOL# H@SEL$19 INDEX ANALYZED 2 108 107 7 1 SEL$1898785B
9072 6/1/2011 9:40:57 AM TABLE ACCESS BY INDEX ROWID SYS OBJ$ OT@SEL$19 16 TABLE ANALYZED 109 14 5 2 SEL$1898785B
9072 6/1/2011 9:40:57 AM INDEX RANGE SCAN SYS I_OBJ3 OT@SEL$19 INDEX ANALYZED 1 110 109 6 1 SEL$1898785B
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS USER$ UT@SEL$19 17 CLUSTER ANALYZED 111 13 4 2 SEL$1898785B
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_USER# UT@SEL$19 INDEX (CLUSTER) ANALYZED 1 112 111 5 1 SEL$1898785B
9072 6/1/2011 9:40:57 AM TABLE ACCESS CLUSTER SYS TAB$ T@SEL$25 18 CLUSTER ANALYZED 113 12 3 2 SEL$25
9072 6/1/2011 9:40:57 AM INDEX UNIQUE SCAN SYS I_OBJ# T@SEL$25 INDEX (CLUSTER) ANALYZED 1 114 113 4 1 SEL$25
9072 6/1/2011 9:40:57 AM NESTED LOOPS 115 12 4 3 SEL$3D4E4FAA
9072 6/1/2011 9:40:57 AM FIXED TABLE FULL SYS X$KZSRO X$KZSRO@SEL$27 TABLE (FIXED) 116 115 5 1 SEL$3D4E4FAA
9072 6/1/2011 9:40:57 AM INDEX RANGE SCAN SYS I_OBJAUTH2 OBJAUTH$@SEL$26 INDEX ANALYZED 2 117 115 5 2 SEL$3D4E4FAA
9072 6/1/2011 9:40:57 AM FIXED TABLE FULL SYS X$KZSPR X$KZSPR@SEL$30 TABLE (FIXED) 118 12 6 4 SEL$1D70030A
============================== PLAN_TABLE=================

============================== Part of v$parameters =================
NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE ISMODIFIED ISADJUSTED ISDEPRECATED DESCRIPTION UPDATE_COMMENT HASH
28 timed_statistics 1 TRUE TRUE FALSE TRUE IMMEDIATE TRUE FALSE FALSE FALSE maintain internal timing statistics 2224114877
29 timed_os_statistics 3 0 0 TRUE TRUE IMMEDIATE TRUE FALSE FALSE FALSE internal os statistic gathering interval in seconds 2425331770
30 resource_limit 1 TRUE TRUE FALSE FALSE IMMEDIATE TRUE FALSE FALSE FALSE master switch for resource limit 19363908
499 compatible 2 10.2.0.1.0 10.2.0.1.0 FALSE FALSE FALSE FALSE FALSE FALSE FALSE Database will be completely compatible with this software version 2586206788
889 plsql_optimize_level 3 2 2 TRUE TRUE IMMEDIATE TRUE FALSE FALSE FALSE PL/SQL optimize level 818269884
973 optimizer_features_enable 2 9.2.0 9.2.0 FALSE TRUE IMMEDIATE TRUE FALSE FALSE FALSE optimizer plan compatibility parameter 2759770534
989 optimizer_mode 2 choose choose FALSE TRUE IMMEDIATE TRUE FALSE FALSE FALSE optimizer mode 487657984
1071 optimizer_index_cost_adj 3 90 90 FALSE TRUE IMMEDIATE TRUE FALSE FALSE FALSE optimizer index cost adjustment 3753899412
1072 optimizer_index_caching 3 0 0 FALSE TRUE IMMEDIATE TRUE FALSE FALSE FALSE optimizer percent index caching 699019442
1113 sql_version 2 NATIVE NATIVE TRUE TRUE FALSE FALSE FALSE FALSE FALSE sql language version parameter for compatibility issues 3531301901
1158 optimizer_dynamic_sampling 3 1 1 TRUE TRUE IMMEDIATE TRUE FALSE FALSE FALSE optimizer dynamic sampling 549852465
1286 optimizer_secure_view_merging 1 TRUE TRUE TRUE FALSE IMMEDIATE TRUE FALSE FALSE FALSE optimizer secure view merging and predicate pushdown/movearound 2261060089


Tom Kyte
June 01, 2011 - 2:33 pm UTC

will you please just do what I did.


ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from dual;


that is all, nothing more, nothing less.

Girish Kale, January 11, 2013 - 1:12 am UTC

Hello Tom,

Wish you a happy new year.

I've got a related question. If I have a procedure (package or standalone) that does some heavy deletes on several tables one after the other. Is there any way to estimate the time such a procedure is likely to take?

Thanks in advance for your response.

Girish

Tom Kyte
January 14, 2013 - 1:30 pm UTC

only by keeping a history of how fast it has gone in the past and using that past history to project future performance.


You can (should) instrument it with dbms_application_info - setting the action, module and client info at various points and making calls to set_session_longops. If you do that, you'll be able to monitor its progression in real time.

pls review

venkat, January 15, 2013 - 12:57 am UTC

in the begining of this thread you mentioned

"Remember also - that explain plan can very well show you a plan that isn't going to be used"

what does it mean ?
Tom Kyte
January 15, 2013 - 10:35 am UTC

http://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html

Explain

Vaij, January 15, 2013 - 5:29 am UTC

Execution plan popularly known as explain is just *plan*. The actual execution may and will vary from explain based on circumstances,parses and host of other things.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.