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.name,a.value 
from v$ses_optimizer_env a 
join v$sys_optimizer_env b on a.id=b.id 
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,a.name;


       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 :-)

About these ads

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

    Thanks Uwe !!

    Nice script to catch difference in optimizer settings.

    Regards,
    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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,747 other followers

%d bloggers like this: