this article in Russian

 

The simplest and most accessible method to understand in the first approximation the problems of some query is to turn on autotrace in sqlplus. After that you can see a statement plan and basic statistics for a query.

Although autotrace will not give you the complete profile, it is very easy to use and usually its facilities are enough.

In order to use autotrace DBA preliminary should perform several steps. We think that these steps should be performed right after database creation or applying patchset. Here they are:

• $ cd $ORACLE_HOME/rdbms/admin
• $ sqlplus system
• SQL> @utlxplan
• SQL> create public synonym plan_table for plan_table;
• SQL> grant select, insert, update, delete on plan_table to public;
• SQL> exit
• $ cd $ORACLE_HOME/sqlplus/admin
• $ sqlplus / as sysdba
• SQL> @plustrce
• SQL> grant plustrace to public;

Now every developer can execute 'set autotrace ...' in sqlplus

Here are the switches which can be used in autotrace:
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

ON | OFF, obviously, turn on/off autotrace

TRACEONLY doesn't show the results of query execution. Very handsome, when you need to analyze a query returning a large number of rows.

EXPLAIN displays only statement plan

STATISTICS displays only statement statistics

Here are the simplest example of using autotrace:

SQL> set autot trace
SQL> select count(*) from dual;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     FAST DUAL (Cost=2 Card=1)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        393  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off

(note that this is Oracle Database 10g, thats why we have FAST DUAL in statement plan and zero consistent gets/phisical reads in statistics)

As for minuses of autotrace, we can mention a couple:

• At first, statement plan comes from execution of 'explain plan' command, which in rare cases can display wrong plan.
• Second, there is no data on CPU, wait events and there is no splitting on parse/exec/fetch stages. If this accuracy is needed the you should use extended SQL trace.

Finally, let's mention about how is realized autotrace in sqlplus. In principle, it's very simple -- sqlplus at odd moments just sends additional queries to server. For example, if we set autotrace on explain, then after execution of primary statement sqlplus without saying a word sends to server statement like 'explain plan for <our query is here>' and then selects from plan_table. We think that it's better to select from v$sql_plan, but this view appeared only starting from 9i so, probably, Oracle developers decided to leave everithing as is.

But if we request sqlplus to display statistics, then immediately after such request (i.e. after getting the command like 'set autotrace on stat'), it sends on server query like:

SELECT STATISTIC# S, NAME FROM SYS.V_$STATNAME WHERE NAME IN 
('recursive calls','db block gets','consistent gets','physical reads',
'redo size','bytes sent via SQL*Net to client',
'bytes received via SQL*Net from client',
'SQL*Net roundtrips to/from client','sorts (memory)','sorts (disk)') 
ORDER BY S

And before and immediately after execution of primary statement, sqlplus selects from v$sesstat for corresponding statistics#. It's necessary to note that for these selects sqlplus silently opens another session. Of course, it's done for not to spoil the statistics of primary statement (some kind of attempt to overcome Heisenberg's Uncertainty Principle).