DBMS_SESSION.SET_SQL_TRACE

This was tested on Oracle 11.1. In previous posts, I have looked at enabling tracing. You can start and stop a trace of your current session like this:

SQL> alter session set sql_trace = true;

 

Session altered.

 

SQL> alter session set sql_trace = false;

 

Session altered.

 

SQL>

You can start and stop tracing an entire instance as follows:

SQL> alter system set sql_trace = true;

 

System altered.

 

SQL> alter system set sql_trace = false;

 

System altered.

 

SQL>

You can also start and stop tracing an instance by setting the sql_trace initialization parameter.

 

Going through some old notes, I saw a different way of tracing your current session and decided to try it out (the first statement is just to make it easy to see the trace file):

SQL> l

  1* alter session set tracefile_identifier = 'ANDREW'

SQL> /

 

Session altered.

 

SQL> exec dbms_session.set_sql_trace(true);

 

PL/SQL procedure successfully completed.

 

SQL> select * from dual

  2  /

 

D

-

X

 

SQL> exec dbms_session.set_sql_trace(false);

 

PL/SQL procedure successfully completed.

 

SQL>

Then I looked in the directory specified by the user_dump_dest parameter and saw the associated trace file below:

Solaris > ls *ANDREW.trc

PQEDPT1_ora_27267_ANDREW.trc

Solaris >

Hidden Parameters

Oracle has several hidden initialisation parameters. These have names which begin with an underscore. You can see them using the following query, which I ran on an Oracle 9 database:

 

SQL> l

  1  select ksppinm

  2  from   SYS.X$KSPPI

  3* where  substr(KSPPINM,1,1) = '_'

SQL> /

 

 

KSPPINM

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

_trace_files_public

_latch_recovery_alignment

_spin_count

_latch_miss_stat_sid

_max_sleep_holding_latch

_max_exponential_sleep

_use_vector_post

_latch_class_0

_latch_class_1

_latch_class_2

_latch_class_3

 

Etc.

 

KSPPINM

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

_xsolapi_sql_use_bind_variables

_xsolapi_sql_prepare_stmt_cache_size

_xsolapi_sql_result_set_cache_size

_xsolapi_debug_output

_xsolapi_cursor_use_row_cache

_xsolapi_cursor_max_rows_to_cache_per_req

_xsolapi_cursor_max_time_for_partial_cache

_xsolapi_source_trace

 

613 rows selected.

 

SQL>

 

What does it mean when we say they are hidden?  We can see the answer by comparison with an ordinary parameter e.g. sql_trace:

 

SQL> l

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = 'sql_trace'

SQL> /

 

NAME       VALUE      ISDEFAULT

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

sql_trace  FALSE      TRUE

 

SQL>

 

In this case, sql_trace is set to its default value of FALSE but it still has an entry in V$PARAMETER. A hidden parameter, on the other hand, does not have an entry in V$PARAMETER:

 

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = '_trace_files_public'

SQL> /

 

no rows selected

 

SQL>

 

... unless it has been set on purpose. The query below was run on a different Oracle 9 database, which had _trace_files_public set in its parameter file:

 

SQL> l

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = '_trace_files_public'

SQL> /

 

NAME                 VALUE      ISDEFAULT

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

_trace_files_public  TRUE       FALSE

 

SQL>

 

Documentation for hidden parameters is not freely available. Oracle will tell you when to implement them, usually when they are replying to a service request. Advice from Oracle to implement a hidden parameter relates to a given database running on a specific Oracle version. I went to an 11g release 2 seminar run by Oracle recently. They discussed upgrade policy there and said that hidden parameters should be removed before an upgrade.