Useful OPTIMIZER_ENV views

How to ensure which optimizer query features has enabled for particular sql_id?

Sometimes in 12c Oracle Database you need to decrease optimizer query features for particular sql because he is too adaptive and generating too many cursor versions with reason “Auto Reoptimization Mismatch“. One of methods is use dbms_sqldiag_internal.i_create_patch for it, just add hint whatever you want. In our case optimizer_features_enable(‘11.2.0.4’).

begin
dbms_sqldiag_internal.i_create_patch(
sql_text => 'sql_full_text',
hint_text => 'optimizer_features_enable(''11.2.0.4'')',
name => 'SOMEPATCH-123');
end;

So, I have my own tool which can show you which sql_patch has been applying.

# tree_locks.py -shared_cu 4cj3z8pjduf1g
EPE - ELA_PER_EXEC
BS - BIND_SENSITIVE
BA - BIND_AWARE
SH - SHARABLE
FS - FEEDBACK_STATISTIC
OS - OPTIMIZER_STATISTIC
BEF - BIND_EQUIV_FAILURE
RIM - ROLL_INVALID_MISMATCH
FULL_PLAN - it's a FULL_PLAN_HASH_VALUE new feature of 12c RDBMS

2015-8-20 17:24:48
  INST    EXEC  LOAD_TIME      OPENING  ACTIVE_TIME         EPE        PLAN    COST    CHILD  BS    BA    SH    FS    OS    BEF    RIM      ROWS    PARSE_CALLS
     1      34  20.08 16:54          0  20.08 16:55    12588397  2768620481  116574        4  Y     N     Y     N     N     N      N          34              34

  INST  REASON                              BASELINE    SQL_PATCH       OUTLINE_CATEGORY    SQL_PROFILE    OBSOLETE
     1  Auto Reoptimization Mismatch(1)  |  None        SOMEPATCH-123   None                None           N

As you can see our patch which is SOMEPATCH-123 has successfully applied for our sql_id. But how we can ensure this?

We can just use GV$SQL_OPTIMIZER_ENV.

SQL> select sql_id,name,value from GV$SQL_OPTIMIZER_ENV where sql_id='4cj3z8pjduf1g' and child_number = 4 and inst_id = 1 and name like 'optimizer_features%';

SQL_ID	        NAME	                        VALUE
4cj3z8pjduf1g	optimizer_features_hinted	11.2.0.4
4cj3z8pjduf1g	optimizer_features_enable	12.1.0.2

If optimizer_features_hinted were not used, optimizer_features_enable will use.

P.S. There are three views: GV$SQL_OPTIMIZER_ENV, GV$SYS_OPTIMIZER_ENV, GV$SES_OPTIMIZER_ENV

Advertisements

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