2.15. Autotrace

AUTOTRACE is a SQL*Plus command that shows the execution plan for SQL statements. It is an excellent tool. We will see many options in Chapter 3 with regard to constraints, particularly primary key and unique constraints. These constraints have indexes. There are implications to actions that modify these constraints. In a development or test environment, developers may temporarily disable constraints or drop and create them. AUTOTRACE offers a quick check to see how your SQL will execute. You may assume that an index exists and that you are using it; and yet it may not. You learn when you run your application with disappointing performance.

Setting AUTOTRACE in your SQL*Plus session shows the execution path for all SQL statements in that session. The following demonstrates a select on the STUDENTS table, defined in the DDL of Chapter 4.

The following session sets AUTOTRACE. A SELECT statement is entered and executed. The AUTOTRACE shows that the SELECT statement is using the index.

					SQL> SET AUTOT ON EXP
SQL> SELECT status FROM students WHERE student_id='A101';

STATUS
					--------------------
					Degree
					Execution Plan
					----------------------------------------------------------
					0      SELECT STATEMENT Optimizer=CHOOSE
					(Cost=1 Card=1 Bytes=10)
					1    0   TABLE ACCESS (BY INDEX ROWID) OF 'STUDENTS'
					(Cost=1 Card=1 Bytes=10)
					2    1     INDEX (UNIQUE SCAN) OF 'PK_STUDENTS' (UNIQUE)
				

Suppose a developer disabled the constraint with the following.

					SQL> ALTER TABLE students
  2  DISABLE CONSTRAINT pk_students CASCADE;

Following the aforementioned DISABLE command, our SQL trace shows a much different result. Below is the SQL Trace result—we see that now our query is performing a full table scan. From this we will want to investigate our SQL—maybe it is not written properly or maybe a constraint or index has been dropped. Had we not used this short analysis tool, we could likely put our select statement into production having it do table scans.

					SQL> SELECT status FROM students WHERE student_id='A010';

Execution Plan
					----------------------------------------------------------
					0      SELECT STATEMENT Optimizer=CHOOSE
					(Cost=1 Card=1 Bytes=10)
					1    0   TABLE ACCESS (FULL) OF 'STUDENTS'
					(Cost=1 Card=1 Bytes=10)
				

You have three types of output to consider when using AUTOTRACE.

• Do you want to execute the SQL statement? You may not if the SQL takes a long time to run. Long-running SQL statements are a reason to test with AUTOTRACE. You do have the option to see an explain plan and/or statistics without fully executing the query.

• You can get the explain plan—the most useful piece of information.

• You can also get statistics that indicates the number of physical and logical reads performed.

The AUTOTRACE syntax is:

SET AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

The AUTOTRACE option, shown here, explains the SQL statement. It does not execute the statement. This is useful for long running queries.

SET AUTOT ON EXP

You do need a plan table for this to run. Oracle populates the plan table as it analyzes the SQL statement. The script for the plan table is found in the directory:

ORACLE_HOME/rdbms/admin/utlxplan.sql

The plan table, if you need to key in, is:

CREATE TABLE PLAN_TABLE (
statement_id     VARCHAR2(30),
timestamp        DATE,
remarks          VARCHAR2(80),
operation        VARCHAR2(30),
options          VARCHAR2(255),
object_node      VARCHAR2(128),
object_owner     VARCHAR2(30),
object_name      VARCHAR2(30),
object_instance  NUMERIC,
object_type      VARCHAR2(30),
optimizer        VARCHAR2(255),
search_columns   NUMBER,
id               NUMERIC,
parent_id        NUMERIC,
position         NUMERIC,
cost             NUMERIC,
cardinality      NUMERIC,
bytes            NUMERIC,
other_tag        VARCHAR2(255),
partition_start  VARCHAR2(255),
partition_stop   VARCHAR2(255),
partition_id     NUMERIC,
other            LONG,
distribution     VARCHAR2(30),
cpu_cost         NUMERIC,
io_cost          NUMERIC,
temp_space       NUMERIC,
access_predicates   VARCHAR2(4000),
filter_predicates   VARCHAR2(4000));