BI jobs performance degradation after Oracle 11.2.0.4 to 12.1.0.2 migration

If you have performance problems after migrating your BI databases from 11.2.0.4 to 12.1.0.2 or any other performance issue, you should not underestimate SQL Plan Notes, which can point you to the right direction or just let you understand what exactly is going on with your queries. Please check below how SQL Plan Notes helped me to investigate problem I recently encountered.

In my case 5 nightly jobs were affected, running similar INSERT ... SELECT queries for different markets. Before migration, depending on the market, they were running in 50 to 150 seconds time range. After the migration their runtime changed to thousands of seconds, which caused them to fail, due to some other jobs being started.

During the investigation I’ve observed that the queries are started in parallel (as expected), but after some time all slave processes were completely idle, waiting on below events:

PX Deq: Execution Msg
PX Deq: Table Q Normal

So, it looked like the degree of parallelism for the queries was at some point downgraded or rather there is an operation which is not parallelized. What’s more interesting – adding /*+ parallel */ hint just after the INSERT, helped to get correct SQL plans with performance similar to 11g.

There was also slight difference in SQL plan notes for both plans:

  • SQL Plan Notes withoutPARALLEL hint
Note
-----
   - Degree of Parallelism is 6 because of table property
  • SQL Plan Notes with PARALLEL hint
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 6 because of degree limit

It looked like a bug for me and indeed, after quick search I was able to spot it in MOS:

Parallel Query Downgraded to Serial When CDB Consumer Group is Enabled in 12c (Doc ID 2048186.1)

The note did not mention using PARALLEL hint as a workaround. Apart from installing suggested one-off patch 21159665, there was another workaround given – to set PARALLEL_DEGREE_POLICY parameter to ADAPTIVE. This led to the right plans without using the hint, for all queries, but one, which had another interesting information in SQL Plan Notes:

Note
-----
   - parallel scans affinitized for buffer cache

This note means that In-memory Parallel Query feature has been used, because of PARALLEL_DEGREE_POLICY change. Setting this parameter to ADAPTIVE not only enables AutoDOP, but also mentioned feature, as well as Statement Queuing. Fortunately, these additional functionalities can be disabled using underscore parameters:

  • In-memory Parallel Execution
"_parallel_cluster_cache_policy" = adaptive
  • Statement Queuing
"_parallel_statement_queuing" = false

After that changes, execution plans for all problematic queries were back to normal, without the need to install one-off patch 21159665.

Edit 27.03.2018 – as pointed out by Christian Antognini, setting PARALLEL_DEGREE_POLICY to ADAPTIVE also enables Performance Feedback. Setting PARALLEL_DEGREE_POLICY to AUTO led to the right plan too, so this value plus two underscore parameters mentioned above, seem to be the least intrusive change required to avoid this problem (if you don’t want/cannot install one-off patch or hint the query).

Advertisements

4 thoughts on “BI jobs performance degradation after Oracle 11.2.0.4 to 12.1.0.2 migration”

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s