Wednesday, September 28, 2011

Setting up the environment for SQL TRACING

In order to set-up the environment for SQL tracing, three things must be done!

1. enable time statistics collections
2. specify destination for the trace dump file
3. adjust dump file size

1. enable time statistics collections



SQL> show parameter statistics

NAME                                             TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
statistics_level                                 string         TYPICAL
timed_os_statistics                          integer       0
timed_statistics                               boolean     TRUE




If the value of the timed_statistics parameter is false, set it to true with the following statement.

SQL> alter system set timed_statistics=true scope=both;
System altered.

2. specify destination for the trace dump file


First find the location of the trace directory (user dump directory in pre-Oracle Database 11g releases) with the following command:


SQL> select name,value from v$diag_info
           where name='Diag Trace';

NAME                       VALUE
--------------------------------------------------------------------------------
Diag Trace                  /u01/app/oracle/diag/rdbms/testdb/testdb/trace

3. adjust dump file size

In oracle 11g, the default dump file size is unlimited. Use the following command to verify this



SQL> show parameter max_dump_file_size

NAME                                      TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string          unlimited





No comments:

Post a Comment