Retrieve Sessions with divergent Optimizer Settings using V$SES_OPTIMIZER_ENV

Just a little snippet from my present Performance Tuning course in Munich that you may find useful:

There are several Initialization Parameters that influence the Optimizer and that can be modified with ALTER SESSION. We can see whether certain sessions have settings different from the System-wide settings with a query like

SQL> set lines 200
col name for a40
col username for a10
col value for a40
select a.sid,c.username,,a.value 
from v$ses_optimizer_env a 
join v$sys_optimizer_env b on 
join v$session c on a.sid=c.sid 
where a.value<>b.value
and c.username is not null
and c.username not in ('SYS','SYSTEM','DBSNMP') 
order by a.sid,;

       SID USERNAME   NAME                                     VALUE
---------- ---------- ---------------------------------------- ----------------------------------------
        22 SCOTT      workarea_size_policy                     manual
       267 ADAM       optimizer_mode                           first_rows

Probably of interest especially if one of these users complains about performance and we want to know what is different with their sessions 🙂

  1. #1 by Amit Saraswat on September 5, 2012 - 18:16

    Thanks Uwe !!

    Nice script to catch difference in optimizer settings.

    Amit S.

  2. #2 by Mahir M. Quluzade on September 6, 2012 - 14:35

    Thanks for share Uwe!

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 )

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: