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] {ONOFFTRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] 

SQL*Plus Command	Explanation
SET AUTOT OFF	Disables AUTOTRACE for the session.
SET AUTOT ON	This command presumes EXP and STAT. Executes the statement Generates the explain plan Generates the statistics
SET AUTOT TRACE	Do NOT execute the SQL statement. This also presumes EXP and STAT. Generates the explain plan Generates the statistics
SET AUTOT TRACE EXP STAT	Same as SET AUTOT TRACE.
SET AUTOT TRACE EXP	Do NOT execute SQL statement. Generates the explain plan
SET AUTOT TRACE STAT	Do NOT execute SQL statement. Generates the statistics

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));