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(‘184.108.40.206’).
begin dbms_sqldiag_internal.i_create_patch( sql_text => 'sql_full_text', hint_text => 'optimizer_features_enable(''220.127.116.11'')', 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 18.104.22.168 4cj3z8pjduf1g optimizer_features_enable 22.214.171.124
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