|
Category :
Database
Resources -> Database Administration
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
One of the important
aspects of the oracle database is tracing. Tracing allows us to
instrument code and helps us in identifying various factors including
bottlenecks and performance problems. It also helps us to understand
the inner workings of oracle on how it goes about processing the data
we submit to it. In this article we will look at the various tracing
options available with oracle, how to enable them and details on what
tracing options to use under various situations.
Pre-requisites:
Make sure, your database is setup
with the following in order to utilize the various tracing options.
(a) Make sure the plustrace role
exists. For oracle on unix platforms the plustrce.sql script under $ORACLE_HOME/sqlplus/admin
can be used to create the role.
(b) Select, Insert, Update and
Delete privileges on the explain_plan table.
(b) The
TIMED_STATISTICS parameter needs
to be set to TRUE
(c) Value for MAX_DUMP_FILE_SIZE
parameter needs to be set to a high value
(d) Make sure USER_DUMP_DEST
points to a disk location with adequate space
(e) The value of STATISTICS_LEVEL
parameter needs to be either TYPICAL or ALL
Various Tracing
options:
(1) AUTOTRACE:
The autotrace facility is only
available from within SQL*Plus and allows analysts to view the
execution plan and some useful statistics for a SQL statement within a
SQL*Plus session. The various options available with AUTOTRACE are
these:
| SET AUTOTRACEOFF
|
No AUTOTRACE report is
generated. This is the default. |
| SET AUTOTRACE ON
EXPLAIN |
The AUTOTRACE report
shows only the optimizer execution path. |
| SET AUTOTRACE ON
STATISTICS |
The AUTOTRACE report
shows only the SQL statement execution statistics.
|
| SET AUTOTRACE ON
|
The AUTOTRACE report
includes both the optimizer execution path and the SQL statement
execution statistics. |
| SET AUTOTRACE TRACEONLY
|
Like SET AUTOTRACE ON,
but suppresses the printing of the user's query output, if any. |
Usage Example:
SQL> SET AUTOTRACE ON
STATISTICS
(2) SQL_TRACE:
SQL_TRACE enables the tracing
of (i) all the SQL statements that an oracle session ran, (ii) the
actual query execution plans that oracle used to run the query and
(iii) performance statistics for the query. This utility produces a
trace file that can then be formatted using the oracle supplied TKPROF
utility. It should be noted that SQL_TRACE is the most reliable
utility when it comes to identifying the actual execution plan that
oracle used to run a query. SQL_TRACE can be used the following ways:
-- Enable Tracing
SQL> alter session set sql_trace=true;
-- Disable Tracing
SQL> alter session set sql_trace=false
-- Enable Tracing within a
different session. The value for SID and SERIAL# can be queried from
V$SESSION
SQL> exec
dbms_system.set_sql_trace_in_session(<SID>,<SERIAL#>,TRUE)
-- Disable Tracing :
SQL> exec
dbms_system.set_sql_trace_in_session(<SID>,<SERIAL#>,FALSE)
NOTE: Tracing can also be
enabled at the instance level by using the "ALTER SYSTEM" command or
by setting the parameter SQL_TRACE to TRUE within the init file.
(3) DBMS_SUPPORT:
dbmssupp.sql found under $ORACLE_HOME/rdbms/admin
directory can be used to create the dbms_support package. One
advantage to using this package is its ability to give us information
on the bind variables being used by the SQL statement that this
package traces. It also gives us information on the WAITS experienced
by the session. DBMS_SUPPORT can be used the following ways:
-- Enable Tracing
SQL> exec
dbms_support.start_trace(waits=>true,binds=>true)
-- Disable Tracing
SQL> exec dbms_support.stop_trace
-- Enable Tracing within a
different session. The value for SID and SERIAL# can be queried from
V$SESSION
SQL> exec
dbms_support.start_trace_in_session(<SID>,<SERIAL#>,binds=>TRUE,waits=>TRUE)
-- Disable Tracing :
SQL> exec
dbms_support.stop_trace_in_session(<SID>,<SERIAL#>)
(4) Oracle Event:
Event 10046 can be used to
help us in collecting extended SQL trace information. You can find
information on oracle
events here. We
can set 4 levels for this events and each level means this:
| Level 1 |
This
cause tracing of sql activities and is similar to 'alter session
set sql_trace=true' |
| Level 4 |
Provides
level 1 tracing + displays the values for all bind variables. It
is equivalent to dbms_support.start_trace(waits=>false,binds=>true) |
| Level 8 |
Provides
level 1 tracing and displays a list of all database wait events.
It is equivalent to dbms_support.start_trace(waits=>true,binds=>false) |
| Level 12 |
Provides
level 1 tracing in addition to both bind variable substitution
and database wait events. It is equivalent to
dbms_support.start_trace(waits=>true,binds=>true) |
Event 10046 can be used
the following ways:
-- Enable Tracing
SQL> alter session set events
'10046 trace name context forever, level12';
-- Disable Tracing
SQL> alter session set events
'10046 trace name context off';
-- Enable Tracing within a
different session. The value for SID and SERIAL# can be queried from
V$SESSION
SQL> exec dbms_system.set_ev(<SID>,<SERIAL#>,EV=>10046,LE=>12)
-- Disable Tracing :
SQL> exec dbms_system.set_ev(<SID>,<SERIAL#>,EV=>10046,LE=>0)
(4) DBMS_PROFILER:
This utility's available
starting oracle8i to profile PLSQL code and can be used to identify
bottlenecks within the code. More details on this utility and details
on how to use it can be found
here. |
