If you have performance problems after migrating your BI databases from 220.127.116.11 to 18.104.22.168 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 without
Note ----- - Degree of Parallelism is 6 because of table property
- SQL Plan Notes with
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
ADAPTIVE also enables Performance Feedback. Setting
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).