Sunday, 21 June 2015

Session specific Optimizer Environment in Oracle

Yesterday there was a need for me to look into what optimizer parameters are set for my particular session only not for the entire database, so below view with the query was really helpful. Try it :-

SQL> select NAME,value  from V$SES_OPTIMIZER_ENV where lower(name) like '%statistics%' and SID=5363;

NAME                                     VALUE
---------------------------------------- -------------------------
statistics_level                         typical
optimizer_use_pending_statistics         false

As per Oracle Doc V$SES_OPTIMIZER_ENV displays the contents of the optimizer environment used by each session. When a new session is first created, it automatically inherits its optimizer environment from the optimizer environment defined at the instance level by V$SYS_OPTIMIZER_ENV. The value of certain parameters can be dynamically modified by issuing an ALTER SESSION statement.

No comments:

Post a Comment