tag:blogger.com,1999:blog-5124641802818980374.post6264818874791342085..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: 12c Parallel Execution New Features: Concurrent UNION ALL - Part 1Unknownnoreply@blogger.comBlogger4125tag:blogger.com,1999:blog-5124641802818980374.post-69300463975010758362015-03-05T20:09:29.758+01:002015-03-05T20:09:29.758+01:00Hi Yasin,
thanks for the update and the clarifica...Hi Yasin,<br /><br />thanks for the update and the clarification. I still wonder who told me this was a feature and not a bug... Anyway setting PARALLEL_ADAPTIVE_MULTI_USER to FALSE is a good idea and should actually be the default sometime in the future.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-40732205997049001392015-03-05T19:01:04.519+01:002015-03-05T19:01:04.519+01:00Randolf, it is not resource manager limiting the D...Randolf, it is not resource manager limiting the DOP in this case. It is actually parallel adaptive multiuser functionality. <br /><br />With parallel_adaptive_multi_user=TRUE and a bug in resource manager (which causes the load to be calculated wrong in this case), the adaptive multiuser feature kicks in and caps the DOP based on the load. That bug is also the reason why you sometimes get DOP=15 and sometimes get DOP=16. As a single user and the only one running PQ on this system you need to get up to DOP=parallel_max_servers (for a SQL using a single slave set).<br /><br />Until the bug is fixed the workaround is to disable resource manager or set parallel_adaptive_multi_user=FALSE. Auto DOP disables this adaptive functionality so this applies only for manual DOP (parallel_degree_policy=MANUAL).<br /><br />The current recommendation to customers is to set parallel_adaptive_multi_user to FALSE anyway.Yasin Baskanhttps://www.blogger.com/profile/07642821755164831104noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-39727684063274800052015-03-04T13:47:55.816+01:002015-03-04T13:47:55.816+01:00Hi Yasin,
thanks for your comment and explanation...Hi Yasin,<br /><br />thanks for your comment and explanations.<br /><br />Regarding the max. DOP and the Resource Manager: As you can read from my post it's not entirely correct that the max. DOP is not constrained without a DOP directive.<br /><br />I've used the DEFAULT_PLAN and still get the DOP capped if it exceeds certain calculated thresholds.<br /><br />I just did some further tests on my 2 CPU laptop with 11.2.0.1 and 12.1.0.2.<br /><br />In 11.2.0.1 with my configuration this absolute cap seems to be at 16 (PARALLEL_MAX_SERVERS = 40, and without Resource Manager active I can get 40 as DOP, so that's not the limiting factor here), with 12.1.0.2 it seems to be 32 (PARALLEL_MAX_SERVERS = 80).<br /><br />However, the rules seem to be more complex, if I run a statement that requires two PX sets 11.2.0.1 even gives me only a DOP of 4 with 8 PX servers, although I requested DOP = 40.<br /><br />In 12.1.0.2 the max. DOP with 2 PX Sets is 16 which seems to be consistent, so the max. number of PX servers per execution / DFO tree seems to be limited to 32 on 12.1.0.2 with my configuration and DEFAULT_PLAN active.<br /><br />So the Resource Manager even without any DOP directive starts limiting excessive DOPs (I got that confirmed some time ago by someone from Oracle, at present can't recall who it was though).<br /><br />Regarding the other topics you've mentioned: I'll cover them in the upcoming parts of this series.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-45971986785424028692015-03-03T23:41:02.994+01:002015-03-03T23:41:02.994+01:00Hi Randolf,
Regarding your comment about the reso...Hi Randolf,<br /><br />Regarding your comment about the resource manager limits; DBRM does not limit the DOP if you do not set it explicitly with the <i>parallel_degree_limit_p1</i> directive. So assuming you are using manual DOP, and have an active resource manager plan with no DOP limit set, you are capped at <i>parallel_max_servers</i>. So, it is normal that you get DOP=20 for your first SQL. If you set a DOP limit in DBRM you will be capped at that limit.<br /><br />Since the UNION ALL statement is an inline view in your second SQL it is treated as a table and you get DOP=10 (because there are 10 branches). Since the other table's (t_2) DOP is 8, you get the higher DOP which is 10. If you have less than 8 branches in this SQL you get DOP=8. The conventional rule of taking the higher DOP applies.<br /><br />If you make t_2 a part of the UNION ALL statement you will get DOP=8 regardless of the number of branches, like below.<br /><br />select /*+ PQ_CONCURRENT_UNION */ count(*) from (<br />select id, regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t_2<br />where regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')<br />union all <br />select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2<br />where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')<br />union all<br />select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2<br />where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')<br />union all<br />select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2<br />where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')<br />union all<br />select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2<br />where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')<br />);<br /><br />With Auto DOP the number of branches become irrelevant as the DOP is calculated based on the local tables.Yasin Baskanhttps://www.blogger.com/profile/07642821755164831104noreply@blogger.com