Systems Engineering and RDBMS

Finding active parameter values for a specific session

Posted by decipherinfosys on April 16, 2009

We know that we can alter parameter values at the system level or at the session level.  We have covered earlier in our blog post how to check whether a parameter is modifiable at the system level or session level. But when we have to do performance tuning at a more granular level, how do we know that session is running with what optimizer environment parameter values? In Oracle 9i, we will have to run event level tracing for event 10053 for specific session to collect such optimizer parameters like optimizer_index_caching or optimizer_index_cost_adj, which changes the behavior of optimizer. In 10g, Oracle introduced few new dynamic performance views that give us the specific parameter values for each session. These views give information at three levels: system, session and sql.

select * from dba_views where view_name like ‘%OPTIMIZER_ENV’;


Let us check the value of following parameters in the v$sys_optimizer_env view.

SQL> select name,value from v$sys_optimizer_env
2   where name like ‘optimizer_index%’;

NAME                                     VALUE
—————————————- ———-
optimizer_index_cost_adj                 100
optimizer_index_caching                  0

We will change these two values to 90 and 25 respectively for our session.  In a previous post, we have covered these parameters in detail. But before making that change let us first check the values for our current session.

SQL> select name,value from v$ses_optimizer_env
2   where name like ‘optimizer_index%’ and sid=129;

Value for both the parameters will be same as shown above. Now let us alter the session to set new parameter values.

SQL> alter session set optimizer_index_cost_adj=90;

Session altered.

SQL> alter session set optimizer_index_caching=25;

Session altered.

We can compare the parameter values for our session and some other session.

SQL> select sid,name,value from v$ses_optimizer_env
2    where name like ‘optimizer_index%’ and sid in (129,170);
SID NAME                                     VALUE
———- —————————————- —–
129 optimizer_index_cost_adj                 90
129 optimizer_index_caching                  25
170 optimizer_index_cost_adj                 100
170 optimizer_index_caching                  0

These views come in handy while troubleshooting performance issues. When some session values are changed at run time and that particular session doesn’t perform well, just by querying these views, we can identify what parameter value session is running with which in turn effects the optimizer.


Leave a Reply

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

You are commenting using your 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: