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 von Amit Saraswat am September 5, 2012 - 18:16

    Thanks Uwe !!

    Nice script to catch difference in optimizer settings.

    Amit S.

  2. #2 von Mahir M. Quluzade am September 6, 2012 - 14:35

    Thanks for share Uwe!

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..