There are times when one needs to see the path that Oracle will choose for a particular query WITHOUT actually running it. It may be a very long-running query and trying to iteratively tune the query can take a lot of time. There is a command (SET AUTOTRACE TRACEONLY EXPLAIN), which when typed at the SQL prompt before the query is run, will just give the EXPLAIN PLAN without running the query. It is a prerequisite to create the plan table in the schema by running utlxplan.sql. Here's how to do it with Oracle versions 7, 8 and 9.
For example, create a table T in your schema and then run the desired query on this table (T):
SQL> Create table T as select * from al_objects; Table Created SQL>select count(*) from t; COUNT(*) --------- 16838
The following checks to see if there are any indexes on table T:
SQL>select index_name 2 from user_indexes 3 where table_name='T'; no rows selected
So there are no Indexes. Now suppose we want to see how the following query will perform:
select * from t where owner='T'
At the SQL Prompt run:
This will create the PLAN_TABLE in the schema in which the user is logged on. Next, alter the Session:
SQL>ALTER SESSION SET SQL_TRACE =TRUE; SQL> SET AUTOTRACE TRACEONLY EXPLAIN; SQL> select * from t 2 where owner='ADHOC'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'T'
Now if we create an Index on the owner column of table T and then run the same query...
SQL> create index owner_idx on t(owner); Index created. SQL> select * from t 2 where owner='ADHOC'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' 2 1 INDEX (RANGE SCAN) OF 'OWNER_IDX' (NON-UNIQUE) --------------
As you can see, the path taken by Oracle to execute the same query changes, and this we have done without running the query.
Dave K. writes: "This tip is not very helpful. Even the most junior DBA or programmer should know about Explain Plan -- it has been part of Oracle since at least version 5, and is clearly documented in multiple places. The example actually trivializes the process, since it is rare that you would explain such a simple query, and a more complex query will not lend itself to such a simple query of the plan table. You could also try Quest Software's SQL Lab, which does a nice job of formatting and presenting Explain Plan results."
Frank G. writes: "To Dave K.: Somebody once told me "If you have nothing nice and/or constructive to say, then don't say it." I appreciate the time the writer took to put the sample together. I'm an Oracle developer of many years and will always look at tips such as these that might help me out."
Guy writes: "First this is a nice tip. Yes, most Oracle users know about the existence of the EXPLAIN PLAN tool but in my 15 years in the IT field and 10 years with Oracle in various capacities I have seldom found developers ever using it. The important thing is one can use this tip to get the Plan without running the query. I think the contributor choose a simple query to keep the tip short and simple. Only thing the change is that it should be select * from ALL_OBJECTS in the first line. I think that's a typo. Good tip. Maybe more developers will use it now."
Geoff H. writes: "Whilst we are talking about developers and tuning, we might as well bring attention to the TKPROF utility, which is underused also."
For More Information
- What do you think about this tip? E-mail the Editor at [email protected] with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.