Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Spying on the other session

with 10 comments

You might want to steal a look at the other session especially for the parameters altered by the session and the diagnostics events set by the the session.

As far as I know, there are no official ways(dynamic performace views) to get those values. But we have a well-known panacea – oradebug!

Session #1 set following parameters and events.

-- session #1
alter session set "_optim_peek_user_binds" = false;
alter session set sql_trace = true;

alter session set events '10046 trace name context forever, level 1';
alter session set events '10053 trace name context forever, level 1';

Errorstack on the level 3 dumps the modified parameters and events.

-- session #2
connect /as sysdba
oradebug setospid 9768
oradebug dump errorstack 3

The trace file contains following section.

Dump event group for SESSION
10053 trace name CONTEXT level 1, forever
10046 trace name CONTEXT level 1, forever
Dump event group for SYSTEM
  sql_trace                = TRUE
  _optim_peek_user_binds   = FALSE
  _rowsource_execution_statistics= FALSE

If all you want is just to find out the modified parameters, following dump command would to that.

oradebug dump modified_parameters 1

Very good, isn’t it?

Written by Dion Cho

July 18, 2009 at 11:03 am

Posted in Misc.

Tagged with , ,

10 Responses

Subscribe to comments with RSS.

  1. Hi Dion
    This is neat. I understand this example but I am thinking how it would be useful it troubleshooting because I can take a level 12 trace and get some useful information if is a performance issue (or oradebug).

    Thank you
    – Kumar


    July 19, 2009 at 1:05 pm

  2. I understand that you mean the 10046 trace at level 12, but my example is not about how to trigger a diagnostic event. It’s about how to find out which kinds of parameters and diagnostic events a specific session hast set.

    It’s one of the common requests to figure out why a specific session behaves at the different way, and the modified parameters and diagnostic events are key factors for that kind of problem.

    Dion Cho

    July 19, 2009 at 1:15 pm

  3. […] 7- How to find out parameters and diagnostic events of specific session ? Dion Cho – Spying on the other session […]

  4. Dion,

    This is very useful. Thank you for sharing!

    Do you know of any v$ views that will expose the current session settings in 10G?



    December 1, 2009 at 10:59 pm

    • Hi, Addie.

      As far as I know, we have no way to get the current parameters and diagnostic events of the specific session from the dynamic performance views. But as of optimizer-related parameters, you might be able to rely on v$ses_optimizer_env view.

      Dion Cho

      December 2, 2009 at 7:13 am

  5. Hi Dion,

    How to set session parameters for other session. Lets say i have 2 session and from first session i want to set cursor_sharing to force for second session from 1st session.

    Taral Desai

    January 6, 2010 at 6:09 am

    • Hi, Taral.

      You can alter integer and boolean parameter values using dbms_system package like this.

      UKJA@ukja1021>exec sys.dbms_system.set_bool_param_in_session(140,5489,'_spr_use_hash_table',true);


      But I don’t think there is any way to change the string parameter value like “cursor_sharing”. Oracle does not provide any (official) way to do that.

      Dion Cho

      January 7, 2010 at 1:46 am

      • Thanks Sir for this information

        Taral Desai

        January 11, 2010 at 6:48 am

  6. If its a case of troubleshooting performance issues for another session , then 10g has a view called V$SQL_OPTIMIZER_ENV.

    Applications and sessions will typically begin to alter their environments to obtain the best performance.
    It can be very hard to track down any session to find out the those changes.

    Remedy is to pull out information from memory, the individual SQL is executing.
    The subset of columns for V$SQL_OPTIMIZER_ENV relevant for finding the optimizer environment are:

    address —> this is the address to the parent of this cursor/sql
    hash_value_name —> hash value to the parent statement in the library cache
    name —> PARAMETER NAME [ important ]
    isdefault —> show if the parameter is set to its default value
    parameter value —> parameter value

    select sesion.sid,
    from v$sql_optimizer_env sql_optimizer_env, v$session sesion
    where sesion.sql_hash_value = sql_optimizer_env.hash_value
    and sesion.sql_address = sql_optimizer_env.address
    and sesion.username is not null

    Optimizer environment settings for current active SQL
    —- ———— —————————————- — ———-
    124 RAJ parallel_execution_enabled YES true
    124 RAJ optimizer_features_enable YES 10.1.0
    124 RAJ cpu_count YES 2
    124 RAJ active_instance_count YES 1
    124 RAJ parallel_threads_per_cpu YES 2
    124 RAJ hash_area_size YES 131072
    124 RAJ bitmap_merge_area_size YES 1048576
    124 RAJ sort_area_size YES 65536
    124 RAJ sort_area_retained_size YES 0



    March 29, 2010 at 2:34 pm

    • Hi, Raj.

      I’m well aware of that view. One restriction of the view is that it is restricted to the optimizer-related parameters.

      Dion Cho

      March 30, 2010 at 12:28 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: