Using AUTOTRACE | Tuning and Timing

Oracle SQL*Plus provides an AUTOTRACE setting that automatically displays the execution plan for any query you execute. You can turn AUTOTRACE off and on with the SET command. There is one big catch: the query must be executed before you can see the results. The problem with this is that 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 want to see the execution plan before you run the query and not afterward. You also may not want this behavior if you are writing a DELETE or an UPDATE statement because you would need to delete or update some data to see the execution plan.

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.

12.3.1 Granting Access to the Performance Views

AUTOTRACE does more than display the execution plan for a query. It 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 shown as well. With older releases of SQL*Plus, 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.

Older releases of Oracle provided a script for DBAs to run, to simplify the process of granting the needed access to users of AUTOTRACE. The script name is plustrce.sql , and, in Oracle Database 10 g , the script may be found in the $ORACLE_HOME/sqlplus/admin 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 DBAs can log in as SYS. Here's how to run the script:

SQL>

connect sys/secret as sysdba

Connected.

SQL> 

SQL>

@$ORACLE_HOME/sqlplus/admin/plustrce

SQL>

SQL> drop role plustrace;

drop role plustrace

 *

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist





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_$mystat to plustrace;



Grant succeeded.



SQL> grant plustrace to dba with admin option;



Grant succeeded.

Once the script has been run, the PLUSTRACE role will exist. PLUSTRACE should be granted to any user who needs to use AUTOTRACE:

SQL>

GRANT plustrace TO gennick;

Grant succeeded.

The user gennick will now be able to execute the SET AUTOTRACE ON command from SQL*Plus.

12.3.2 Executing a Query with AUTOTRACE On

You can use several options with SET AUTOTRACE. By default, when you turn AUTOTRACE on, SQL*Plus shows the execution plan and some execution statistics for any query you execute. If you wish, you can limit AUTOTRACE to showing only the execution plan or the execution statistics.

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, discussed later in this section. This command limits the display to the execution plan and doesn't require access to the performance tables.

You 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.

12.3.2.1 Showing statistics and the plan

To enable AUTOTRACE and set it to show the execution plan and the execution statistics, execute the following command from SQL*Plus:

SET AUTOTRACE ON

Now execute any query, as shown in Example 12-5. You will see the query results, followed by the execution plan and the execution statistics.

Example 12-5. Using AUTOTRACE to show an execution plan

SET AUTOTRACE ON



SELECT employee_name, SUM(hours_logged)

FROM employee, project_hours

WHERE employee.employee_id = project_hours.employee_id

GROUP BY employee_name;



EMPLOYEE_NAME SUM(HOURS_LOGGED)

---------------------------------------- -----------------

Igor Sikorsky 48

Ivan Mazepa 68

Lesia Ukrainka 144

Marusia Bohuslavka 68

Marusia Churai 144

Mykhailo Hrushevsky 57

Mykhailo Verbytsky 96

Mykola Leontovych 81

Pavlo Chubynsky 129

Pavlo Virsky 48

Roxolana Lisovsky 68

Taras Shevchenko 144



12 rows selected.





Execution Plan

----------------------------------------------------------

 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=12 Bytes=288)



 1 0 SORT (GROUP BY) (Cost=7 Card=12 Bytes=288)

 2 1 MERGE JOIN (Cost=6 Card=279 Bytes=6696)

 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (C

 ost=2 Card=12 Bytes=228)



 4 3 INDEX (FULL SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE))

 (Cost=1 Card=12)



 5 2 SORT (JOIN) (Cost=4 Card=279 Bytes=1395)

 6 5 TABLE ACCESS (FULL) OF 'PROJECT_HOURS' (TABLE) (Cost

 =3 Card=279 Bytes=1395)



Statistics

----------------------------------------------------------

 0 recursive calls

 0 db block gets

 9 consistent gets

 0 physical reads

 0 redo size

 780 bytes sent via SQL*Net to client

 511 bytes received via SQL*Net from client

 2 SQL*Net roundtrips to/from client

 2 sorts (memory)

 0 sorts (disk)

 12 rows processed

The execution plan displayed by AUTOTRACE is formatted 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.

One key statistic to look at is the number of logical reads, as indicated by "consistent gets," particularly in relation to the number of rows processed. The fewer logical reads per row processed, the better.

When looking at statistics via AUTOTRACE, run any given query at least twice. Statistics from the first run will reflect the overhead of initially parsing the statement. Unless you intend to parse the statement each time you execute it (which is bad), you should pay attention to the statistics from the second run.

12.3.2.2 Showing only the plan

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 an SQL statement, only the execution plan is displayed, not the statistics, as Example 12-6 demonstrates .

Example 12-6. AUTOTRACE without the statistics

SET AUTOTRACE ON EXPLAIN



SELECT employee_name, SUM(hours_logged)

FROM employee, project_hours

WHERE employee.employee_id = project_hours.employee_id

GROUP BY employee_name;



EMPLOYEE_NAME SUM(HOURS_LOGGED)

---------------------------------------- -----------------

Igor Sikorsky 48

Ivan Mazepa 68

Lesia Ukrainka 144

Marusia Bohuslavka 68

Marusia Churai 144

Mykhailo Hrushevsky 57

Mykhailo Verbytsky 96

Mykola Leontovych 81

Pavlo Chubynsky 129

Pavlo Virsky 48

Roxolana Lisovsky 68

Taras Shevchenko 144



12 rows selected.





Execution Plan

----------------------------------------------------------

 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=12 Bytes=564)



 1 0 SORT (GROUP BY) (Cost=7 Card=12 Bytes=564)

 2 1 MERGE JOIN (Cost=6 Card=279 Bytes=13113)

 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (C

 ost=2 Card=12 Bytes=252)



 4 3 INDEX (FULL SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE))

 (Cost=1 Card=12)



 5 2 SORT (JOIN) (Cost=4 Card=279 Bytes=7254)

 6 5 TABLE ACCESS (FULL) OF 'PROJECT_HOURS' (TABLE) (Cost

 =3 Card=279 Bytes=7254)

12.3.2.3 Suppressing the query output

With AUTOTRACE, you 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 remains valid, so if you want to see only the execution plan, issue the command like this:

SET AUTOTRACE TRACEONLY EXPLAIN

Execute a query and you will see only the execution plan, not the data, as in Example 12-7.

Example 12-7. AUTOTRACE without the output

SET AUTOTRACE TRACEONLY EXPLAIN



SELECT employee_name, SUM(hours_logged)

FROM employee, project_hours

WHERE employee.employee_id = project_hours.employee_id

GROUP BY employee_name;



Execution Plan

----------------------------------------------------------

 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=12 Bytes=564)



 1 0 SORT (GROUP BY) (Cost=7 Card=12 Bytes=564)

 2 1 MERGE JOIN (Cost=6 Card=279 Bytes=13113)

 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (C

 ost=2 Card=12 Bytes=252)



 4 3 INDEX (FULL SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE))

 (Cost=1 Card=12)



 5 2 SORT (JOIN) (Cost=4 Card=279 Bytes=7254)

 6 5 TABLE ACCESS (FULL) OF 'PROJECT_HOURS' (TABLE) (Cost

 =3 Card=279 Bytes=7254)

When the TRACEONLY option is used, SELECT statements are not executed. However, INSERT, UPDATE, DELETE, and MERGE statements are executed.

If you enable AUTOTRACE's STATISTICS option, then any statement will be executed because it's only by executing statements that the required statistics can be gathered.

12.3.2.4 Turning AUTOTRACE off

When you are done using AUTOTRACE, you can turn it off with the following command: