RDBMS -- SQL Performance Tuning - Correcting SQL Plans by setting hidden parameters at session level & Fixing Plans via Sql Profiles

We recently dealt with a SQL performance problem. Actually, the problem itself wasn't so interesting. The solution, however; was stylish :)

In one of our customer sites,  we encountered a performance degradation in an important SQL.
The issue arised after we upgraded the database to 18C. (unfortunaetly they did not notice this problem in performance-test phase).

The query was trying to fetch rows from all_objects and all_synonyms-type views and the execution plan was not quite good.

We had already seen these kinds of performance problems and remember the workaround..
That is, the workaround "ALTER SYSTEM SET "_fix_control" = '8560951:on" saved our day previously.

Reference: Accessing ALL_OBJECTS View Performs Slowly, Relative to Response Time on Another Database (Doc ID 2061736.1)

However; this time we couldn't directly set that fix_control parameter. Because, we were already  on production, so setting an underscore parameter without testing it, was so risky..

Interesting part start here : )

Look what we have done; 

• We have set the parameter at session level ->  ALTER SESSION SET "_fix_control" = '8560951:on'
• We then run the exact same query again (in order to make Oracle optimizer to build the desired/correct plan) -- Attention -> We executed the exact same query, as we didn't want the sqlid to be changed/or a new sqlid to be generated during our execution.. 
• Then we checked the Sqlids and the associated sql plans for the problematic sql text (Checked the average estimated seconds and plan hash values.. The new execution plan was the quickest)
• Well, we fixed the plan for that sqlid :) After fixing the plan, our sql started run faster!
• Lastly, we got the sql optimizer trace and saw that the hidden parameter was really active for the sql.. 

SQL DIAG TRACE:

begin

dbms_sqldiag.dump_trace(p_sql_id=>'43xdnx0r5dhmx',

p_child_number=> 0,p_component=> 'Compiler',

p_file_id=>'Compiler_Trace_43xdnx0r5dhmx');

end;

A PIECE OF CONTENT OF THE TRACE FILE:

Content of other_xml column

===========================

  db_version     : 18.0.0.0

  parse_schema   : ERMANRPT

  plan_hash_full : 1560297160

  plan_hash      : 533470668

  plan_hash_2    : 1023844655

  sql_profile    : coe_43xdnx0r5dhmx_2690840819

  Outline Data:

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('18.1.0')

      DB_VERSION('18.1.0')

      OPT_PARAM('query_rewrite_enabled' 'false')

      OPT_PARAM('_optim_peek_user_binds' 'false')

      OPT_PARAM('_fix_control' '8560951:1') --> YES IT IS HERE! :)

      ALL_ROWS