Beginning with version 3.3 of SQL*Plus, Oracle provides a setting that automatically displays the execution plan for any query you execute. The name of this setting is AUTOTRACE, and you can turn it off and on with the SET command. There is one big catch. The query must actually be executed before you can see the results. If you are contemplating a query against a large table, it might take all day for a poorly-tuned query to execute. In that case, you might just want to see the execution plan before you run the query, not afterwards. You may also not want this behavior if you are writing a DELETE or an UPDATE statement, because you would need to actually delete or update some data in order to see the execution plan.
Tip
Before you can use AUTOTRACE to display execution plans, you must
have created a plan table. AUTOTRACE uses this table, and expects the
name to be PLAN_TABLE, which is the default name if you use the
UTLXPLAN.SQL script to create it.
AUTOTRACE will do more than just display the execution plan for a query. It also displays statistics that show you how much disk I/O and network traffic occurred during a query’s execution. Other information, such as the number of sorts performed on the data, is given as well.
In order to see the statistical data AUTOTRACE returns, you must have SELECT access to certain of Oracle’s dynamic performance views . Dynamic performance views, whose names usually begin with V$ or V_$, are pseudoviews, maintained by Oracle, that contain real-time performance information. With Oracle8, version 8.0.3, you need SELECT access to the following three tables:
v_$sesstat v_$statname v_$session
Since the specific tables to which you need access may vary from one
version of Oracle to the next, Oracle provides a script your DBA can
run to simplify the process of granting the needed access to users of
AUTOTRACE. The script name is
PLUSTRCE.SQL
, and it is stored in the
PLUS directory under the Oracle home directory.
The script must be executed while logged in as user SYS, and it
creates a role named PLUSTRACE that has the needed privileges to use
AUTOTRACE from SQL*Plus. Usually, only database administrators can
log in as SYS. Here’s how to run the script:
SQL>CONNECT sys/mgrConnected. SQL>@c:orawin95plus80plustrce.sqlSQL> SQL>drop role plustrace;Role dropped. SQL>create role plustrace;Role created. SQL> SQL>grant select on v_$sesstat to plustrace;Grant succeeded. SQL>grant select on v_$statname to plustrace;Grant succeeded. SQL>grant select on v_$session to plustrace;Grant succeeded. SQL>grant plustrace to dba with admin option;Grant succeeded. SQL> SQL>set echo off
Once the script has been run, the PLUSTRACE role will exist. PLUSTRACE should be granted to any user who needs to use AUTOTRACE; for example:
SQL> GRANT plustrace TO SARAH;
Grant succeeded.Now the user SARAH will be able to execute the SET AUTOTRACE ON command from SQL*Plus.
There are several options you can use with SET AUTOTRACE. By default, when you turn AUTOTRACE on, SQL*Plus will show both the execution plan and some execution statistics for any query you execute. You can, if you like, limit AUTOTRACE to showing only the execution plan or only the execution statistics.
Tip
If you don’t have the PLUSTRACE role, or don’t otherwise have access to the required dynamic performance tables, you can issue the command SET AUTOTRACE ON EXPLAIN. This option is discussed later in this section; it limits the display to only the execution plan, and does not require access to the performance tables.
You also have the option of suppressing the output from the query you are executing. This is helpful if the query returns a large amount of data, because you aren’t forced to watch all the results scroll by before the execution plan is displayed. You’ll see how to do this later in this section.
To enable AUTOTRACE and set it to show both the execution plan and the execution statistics, execute the following command from SQL*Plus:
SET AUTOTRACE ON
Now execute any query. You will see the query results, followed by the execution plan, followed by the execution statistics. Here is an example:
SQL>SET AUTOTRACE ONSQL>SELECT employee_name, SUM(hours_logged)2FROM employee, project_hours3WHERE employee.employee_id = project_hours.employee_id4GROUP BY employee_name;EMPLOYEE_NAME SUM(HOURS_LOGGED) ---------------------------------------- ----------------- Bohdan Khmelnytsky 116 Hermon Goche 36 Horace Walker 68 Ivan Mazepa 57 Jacob Marley 80 Jeff Gennick 36 Jenny Gennick 49 Jonathan Gennick 116 Pavlo Chubynsky 112 Taras Shevchenko 116 10 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'PROJECT_HOURS' 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' 5 4 INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 3 db block gets 210 consistent gets 0 physical reads 0 redo size 903 bytes sent via SQL*Net to client 777 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 10 rows processed
One key statistic to look at would be the number of physical reads, particularly in relation to the number of rows processed. The fewer reads per row processed, the better. In the above example, all the data happened to be in memory as a result of previous queries, so no physical reads occurred.
The execution plan displayed by AUTOTRACE is formatted just a bit differently from previous plans shown in this chapter. The two leading numeric columns are the ID (of the step) and the PARENT_ID (ID of the parent step) columns.
SQL*Plus allows you to turn AUTOTRACE on with an option to show only the execution plan. This is handy if you do not happen to have the needed privileges to access the execution statistics. Issue the following command from SQL*Plus:
SET AUTOTRACE ON EXPLAIN
Now, when you issue a SQL statement, only the execution plan is displayed, not the statistics. Here’s an example:
SQL>SET AUTOTRACE ON EXPLAINSQL>SELECT employee_name, SUM(hours_logged)2FROM employee, project_hours3WHERE employee.employee_id = project_hours.employee_id4GROUP BY employee_name;EMPLOYEE_NAME SUM(HOURS_LOGGED) ---------------------------------------- ----------------- Bohdan Khmelnytsky 116 Hermon Goche 36 Horace Walker 68 Ivan Mazepa 57 Jacob Marley 80 Jeff Gennick 36 Jenny Gennick 49 Jonathan Gennick 116 Pavlo Chubynsky 112 Taras Shevchenko 116 10 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'PROJECT_HOURS' 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' 5 4 INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (UNIQUE)
With AUTOTRACE, you also have the option of suppressing the output from any queries you run. This saves you from having to wait for the results to scroll by before you see the execution plan and statistics. To turn AUTOTRACE on and suppress any query output, issue the following command:
SET AUTOTRACE TRACEONLY
The EXPLAIN option is still valid, so if you only want to see the execution plan, issue the command like this:
SET AUTOTRACE TRACEONLY EXPLAIN
Now, execute a query, and you will see only the execution plan, not the data:
SQL>SET AUTOTRACE TRACEONLY EXPLAINSQL>SELECT employee_name, SUM(hours_logged)2FROM employee, project_hours3WHERE employee.employee_id = project_hours.employee_id4GROUP BY employee_name;Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'PROJECT_HOURS' 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' 5 4 INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (UNIQUE)
It’s important to understand that even when the TRACEONLY option is used, the query is still executed. This is really important to remember if the query in question is a DELETE or UPDATE.