Pickleball spielen

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 🙂