Skip to Main Content
  • Questions

  • how to query optimizer mode for current session

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Matthias.

Asked: December 12, 2006 - 11:00 am UTC

Last updated: December 17, 2006 - 6:40 pm UTC

Version: 10.2.0.2

Viewed 10K+ times! This question is

You Asked

Hallo,

how can I query optimizer mode for my session ?

the only possibility I found (which doesn't look perfect for me) is

SQL> select value from v$parameter where name='optimizer_mode';

VALUE
-------------------------------------------------------------------------
FIRST_ROWS_100

REM ok, that is inherited, haven't set optimizer mode for my session
REM so far
REM perhaps I can do

SQL> select optimizer_mode
2 from v$sql
3 where sql_text like '%1st query for optimizer mode%';

OPTIMIZER_
----------
FIRST_ROWS

REM I expected FIRST_ROWS_100, why FIRST_ROWS ?
REM Where else can I look ?
REM anyway, give it another try

SQL> alter session set optimizer_mode=all_rows;

Session wurde geõndert.

SQL> select optimizer_mode
2 from v$sql
3 where sql_text like '%2nd query for optimizer mode%';

OPTIMIZER_
----------
ALL_ROWS

REM aha, not so bad, but surely not perfect
REM let's try

SQL> alter session set optimizer_mode=choose;

Session wurde geõndert.

SQL> select optimizer_mode
2 from v$sql
3 where sql_text like '%3rd query for optimizer mode%';

OPTIMIZER_
----------
CHOOSE

SQL> alter session set optimizer_mode=rule;

Session wurde geõndert.
SQL> select optimizer_mode
2 from v$sql
3 where sql_text like '%4th query for optimizer mode%';

OPTIMIZER_
----------
RULE


Thx

and Tom said...

ops$tkyte%ORA10GR2> show parameter optimizer_mode;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
ops$tkyte%ORA10GR2> alter session set optimizer_mode = first_rows;

Session altered.

Elapsed: 00:00:00.02
ops$tkyte%ORA10GR2> show parameter optimizer_mode;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string FIRST_ROWS
ops$tkyte%ORA10GR2> alter session set optimizer_mode = first_rows_100;

Session altered.

Elapsed: 00:00:00.01
ops$tkyte%ORA10GR2> show parameter optimizer_mode;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string FIRST_ROWS_100
ops$tkyte%ORA10GR2> alter session set optimizer_mode = choose;

Session altered.

Elapsed: 00:00:00.02
ops$tkyte%ORA10GR2> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string CHOOSE



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

v$parameter

Matthias Rogel, December 13, 2006 - 2:49 am UTC

thx, want to use it in PL/SQL

select value
FROM
V$PARAMETER WHERE name='optimizer_mode'

is the way to go

A reader, December 14, 2006 - 4:16 pm UTC

Tom,

How can we find the optimizer mode for releases prior to 10G?

TIA.

Tom Kyte
December 15, 2006 - 8:34 am UTC 

ops$tkyte%ORA9IR2> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      CHOOSE
ops$tkyte%ORA9IR2> alter session set optimizer_mode=rule;

Session altered.

ops$tkyte%ORA9IR2> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      RULE



same way.

A reader, December 17, 2006 - 5:52 pm UTC

Sorry for the incomplete question would like to know how to find it for other sessions?

Tom Kyte
December 17, 2006 - 6:40 pm UTC 

you do not, until 10g 

SQL> desc V$SES_OPTIMIZER_ENV
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 SID                                                            NUMBER
 ID                                                             NUMBER
 NAME                                                           VARCHAR2(40)
 ISDEFAULT                                                      VARCHAR2(3)
 VALUE                                                          VARCHAR2(25)


in that release, you can see optimizer settings across sessions.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.