Home » RDBMS Server » Performance Tuning » Parallel Degree Limit correct num to use on Parallel Hints (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
| Parallel Degree Limit correct num to use on Parallel Hints [message #689985] |
Wed, 11 September 2024 09:25  |
wtolentino
Messages: 420
Registered: March 2005
|
Senior Member |
|
|
Just trying to understand the Parallel Degree Limit settings. We have this query (DML) that is using parallel hints:
INSERT /*+ PARALLEL ods.ods_prognote_header_o,4 */ INTO ...
And the current parallel degree limit on the database is
SQL> select substr(name,1,25) name, type, substr(value,1,20) value
2 from v$parameter
3 where name in ('cpu_count', 'parallel_threads_per_cpu', 'parallel_degree_policy', 'parallel_degree_limit');
NAME TYPE VALUE
------------------------- ---------- --------------------
cpu_count 3 10
parallel_degree_policy 2 MANUAL
parallel_threads_per_cpu 3 1
parallel_degree_limit 2 CPU
How do I know what correct number to use in the PARALLEL hints? Please advise.
Thank you,
Warren
|
|
|
|
| Parallel Degree Limit correct num to use on Parallel Hints [message #689986 is a reply to message #689985] |
Wed, 11 September 2024 09:25  |
wtolentino
Messages: 420
Registered: March 2005
|
Senior Member |
|
|
Just trying to understand the Parallel Degree Limit settings. We have this query (DML) that is using parallel hints:
INSERT /*+ PARALLEL ods.ods_prognote_header_o,4 */ INTO ...
And the current parallel degree limit on the database is
SQL> select substr(name,1,25) name, type, substr(value,1,20) value
2 from v$parameter
3 where name in ('cpu_count', 'parallel_threads_per_cpu', 'parallel_degree_policy', 'parallel_degree_limit');
NAME TYPE VALUE
------------------------- ---------- --------------------
cpu_count 3 10
parallel_degree_policy 2 MANUAL
parallel_threads_per_cpu 3 1
parallel_degree_limit 2 CPU
How do I know what correct number to use in the PARALLEL hints? Please advise.
Thank you,
Warren
|
|
|
|
|
|
|
|
|
|
| Re: Parallel Degree Limit correct num to use on Parallel Hints [message #689990 is a reply to message #689989] |
Wed, 11 September 2024 10:10 |
wtolentino
Messages: 420
Registered: March 2005
|
Senior Member |
|
|
Here is the explain plan.
Plan hash value: 3732619652
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 30M| 1463M| | 41790 (1)| 00:00:07 | | | |
| 1 | LOAD TABLE CONVENTIONAL | ODS_PROGNOTE_HEADER_O | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | 30M| 1463M| | 41790 (1)| 00:00:07 | Q1,03 | P->S | QC (RAND) |
|* 4 | HASH JOIN RIGHT ANTI BUFFERED | | 30M| 1463M| 198M| 41790 (1)| 00:00:07 | Q1,03 | PCWP | |
| 5 | PX RECEIVE | | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,03 | PCWP | |
| 6 | PX SEND HASH | :TQ10001 | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,01 | P->P | HASH |
| 7 | PX BLOCK ITERATOR | | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,01 | PCWC | |
|* 8 | TABLE ACCESS STORAGE FULL | ODS_PROGNOTE_HEADER_O | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,01 | PCWP | |
| 9 | PX RECEIVE | | 133M| 4078M| | 19927 (2)| 00:00:04 | Q1,03 | PCWP | |
| 10 | PX SEND HASH | :TQ10002 | 133M| 4078M| | 19927 (2)| 00:00:04 | Q1,02 | P->P | HASH |
|* 11 | HASH JOIN | | 133M| 4078M| | 19927 (2)| 00:00:04 | Q1,02 | PCWP | |
| 12 | PX RECEIVE | | 1699K| 25M| | 42 (3)| 00:00:01 | Q1,02 | PCWP | |
| 13 | PX SEND BROADCAST | :TQ10000 | 1699K| 25M| | 42 (3)| 00:00:01 | Q1,00 | P->P | BROADCAST |
|* 14 | HASH JOIN | | 1699K| 25M| | 42 (3)| 00:00:01 | Q1,00 | PCWP | |
|* 15 | TABLE ACCESS STORAGE FULL | ODS_ORGANIZATIONS | 39 | 234 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 16 | PX BLOCK ITERATOR | | 1699K| 16M| | 39 (0)| 00:00:01 | Q1,00 | PCWC | |
| 17 | TABLE ACCESS STORAGE FULL| ODS_CASE_KEYS | 1699K| 16M| | 39 (0)| 00:00:01 | Q1,00 | PCWP | |
| 18 | PX BLOCK ITERATOR | | 165M| 2531M| | 19859 (1)| 00:00:04 | Q1,02 | PCWC | |
|* 19 | TABLE ACCESS STORAGE FULL | ODS_PROGNOTE_HEADER | 165M| 2531M| | 19859 (1)| 00:00:04 | Q1,02 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."CD_ODS_ORG"="O"."ORGANIZATION_CD" AND "B"."ID_PROGNOTE"="ID_PROGNOTE")
8 - storage("DT_ORG_END_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("DT_ORG_END_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
11 - access("B"."ID_CASE"="A"."ID_CASE")
14 - access("A"."CD_ODS_ORG"="O"."ORGANIZATION_CD")
15 - storage("O"."STATUS_CD"='A')
filter("O"."STATUS_CD"='A')
19 - storage("B"."CD_ODS_REC_STATUS"<>'D')
filter("B"."CD_ODS_REC_STATUS"<>'D')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1), E - Syntax error (2))
---------------------------------------------------------------------------
0 - STATEMENT
U - PARALLEL / duplicate hint
0 - INS$1
E - ods
0 - SEL$2
E - ods
Note
-----
- automatic DOP: Computed Degree of Parallelism is 20 because of degree limit
- PDML is disabled in current session
|
|
|
|
|
|
| Re: Parallel Degree Limit correct num to use on Parallel Hints [message #689992 is a reply to message #689991] |
Wed, 11 September 2024 10:41 |
wtolentino
Messages: 420
Registered: March 2005
|
Senior Member |
|
|
Thanks for helping to look into it. I tried to enable the parallel DML and still would not get the "PARALLEL IS ENABLED" on the explain plan. I might be doing it wrong. Either by alter session or thru the hints. Please advise thanks.
SQL> ALTER SESSION ENABLE PARALLEL DML;
Session altered.
SQL>
SQL> explain plan for
2 INSERT /*+ PARALLEL ods.ods_prognote_header_o,4 */ INTO ...;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3315159684
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 30M| 1463M| | 41790 (1)| 00:00:07 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 30M| 1463M| | 41790 (1)| 00:00:07 | Q1,03 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB) | ODS_PROGNOTE_HEADER_O | | | | | | Q1,03 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 30M| 1463M| | 41790 (1)| 00:00:07 | Q1,03 | PCWP | |
|* 5 | HASH JOIN RIGHT ANTI | | 30M| 1463M| 198M| 41790 (1)| 00:00:07 | Q1,03 | PCWP | |
| 6 | PX RECEIVE | | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,03 | PCWP | |
| 7 | PX SEND HASH | :TQ10001 | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,01 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,01 | PCWC | |
|* 9 | TABLE ACCESS STORAGE FULL | ODS_PROGNOTE_HEADER_O | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,01 | PCWP | |
| 10 | PX RECEIVE | | 133M| 4078M| | 19927 (2)| 00:00:04 | Q1,03 | PCWP | |
| 11 | PX SEND HASH | :TQ10002 | 133M| 4078M| | 19927 (2)| 00:00:04 | Q1,02 | P->P | HASH |
|* 12 | HASH JOIN | | 133M| 4078M| | 19927 (2)| 00:00:04 | Q1,02 | PCWP | |
| 13 | PX RECEIVE | | 1699K| 25M| | 42 (3)| 00:00:01 | Q1,02 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10000 | 1699K| 25M| | 42 (3)| 00:00:01 | Q1,00 | P->P | BROADCAST |
|* 15 | HASH JOIN | | 1699K| 25M| | 42 (3)| 00:00:01 | Q1,00 | PCWP | |
|* 16 | TABLE ACCESS STORAGE FULL | ODS_ORGANIZATIONS | 39 | 234 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 17 | PX BLOCK ITERATOR | | 1699K| 16M| | 39 (0)| 00:00:01 | Q1,00 | PCWC | |
| 18 | TABLE ACCESS STORAGE FULL| ODS_CASE_KEYS | 1699K| 16M| | 39 (0)| 00:00:01 | Q1,00 | PCWP | |
| 19 | PX BLOCK ITERATOR | | 165M| 2531M| | 19859 (1)| 00:00:04 | Q1,02 | PCWC | |
|* 20 | TABLE ACCESS STORAGE FULL | ODS_PROGNOTE_HEADER | 165M| 2531M| | 19859 (1)| 00:00:04 | Q1,02 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C"."CD_ODS_ORG"="O"."ORGANIZATION_CD" AND "B"."ID_PROGNOTE"="ID_PROGNOTE")
9 - storage("DT_ORG_END_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("DT_ORG_END_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
12 - access("B"."ID_CASE"="A"."ID_CASE")
15 - access("A"."CD_ODS_ORG"="O"."ORGANIZATION_CD")
16 - storage("O"."STATUS_CD"='A')
filter("O"."STATUS_CD"='A')
20 - storage("B"."CD_ODS_REC_STATUS"<>'D')
filter("B"."CD_ODS_REC_STATUS"<>'D')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1), E - Syntax error (2))
---------------------------------------------------------------------------
0 - STATEMENT
U - PARALLEL / duplicate hint
0 - INS$1
E - ods
0 - SEL$2
E - ods
Note
-----
- automatic DOP: Computed Degree of Parallelism is 20 because of degree limit
57 rows selected.
SQL> --enable parallel dml in hints
SQL> explain plan for
2 INSERT /*+ PARALLEL (ods.ods_prognote_header_o,4) enable_parallel_dml */ INTO ...;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3315159684
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 30M| 1463M| | 41790 (1)| 00:00:07 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 30M| 1463M| | 41790 (1)| 00:00:07 | Q1,03 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB) | ODS_PROGNOTE_HEADER_O | | | | | | Q1,03 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 30M| 1463M| | 41790 (1)| 00:00:07 | Q1,03 | PCWP | |
|* 5 | HASH JOIN RIGHT ANTI | | 30M| 1463M| 198M| 41790 (1)| 00:00:07 | Q1,03 | PCWP | |
| 6 | PX RECEIVE | | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,03 | PCWP | |
| 7 | PX SEND HASH | :TQ10001 | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,01 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,01 | PCWC | |
|* 9 | TABLE ACCESS STORAGE FULL | ODS_PROGNOTE_HEADER_O | 134M| 2438M| | 7596 (2)| 00:00:02 | Q1,01 | PCWP | |
| 10 | PX RECEIVE | | 133M| 4078M| | 19927 (2)| 00:00:04 | Q1,03 | PCWP | |
| 11 | PX SEND HASH | :TQ10002 | 133M| 4078M| | 19927 (2)| 00:00:04 | Q1,02 | P->P | HASH |
|* 12 | HASH JOIN | | 133M| 4078M| | 19927 (2)| 00:00:04 | Q1,02 | PCWP | |
| 13 | PX RECEIVE | | 1699K| 25M| | 42 (3)| 00:00:01 | Q1,02 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10000 | 1699K| 25M| | 42 (3)| 00:00:01 | Q1,00 | P->P | BROADCAST |
|* 15 | HASH JOIN | | 1699K| 25M| | 42 (3)| 00:00:01 | Q1,00 | PCWP | |
|* 16 | TABLE ACCESS STORAGE FULL | ODS_ORGANIZATIONS | 39 | 234 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 17 | PX BLOCK ITERATOR | | 1699K| 16M| | 39 (0)| 00:00:01 | Q1,00 | PCWC | |
| 18 | TABLE ACCESS STORAGE FULL| ODS_CASE_KEYS | 1699K| 16M| | 39 (0)| 00:00:01 | Q1,00 | PCWP | |
| 19 | PX BLOCK ITERATOR | | 165M| 2531M| | 19859 (1)| 00:00:04 | Q1,02 | PCWC | |
|* 20 | TABLE ACCESS STORAGE FULL | ODS_PROGNOTE_HEADER | 165M| 2531M| | 19859 (1)| 00:00:04 | Q1,02 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C"."CD_ODS_ORG"="O"."ORGANIZATION_CD" AND "B"."ID_PROGNOTE"="ID_PROGNOTE")
9 - storage("DT_ORG_END_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("DT_ORG_END_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
12 - access("B"."ID_CASE"="A"."ID_CASE")
15 - access("A"."CD_ODS_ORG"="O"."ORGANIZATION_CD")
16 - storage("O"."STATUS_CD"='A')
filter("O"."STATUS_CD"='A')
20 - storage("B"."CD_ODS_REC_STATUS"<>'D')
filter("B"."CD_ODS_REC_STATUS"<>'D')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------
0 - INS$1
N - PARALLEL (ods.ods_prognote_header_o,4)
0 - SEL$2
E - ods
Note
-----
- automatic DOP: Computed Degree of Parallelism is 20 because of degree limit
54 rows selected.
SQL>
[Updated on: Wed, 11 September 2024 10:48] Report message to a moderator |
|
|
|
|
|
| Re: Parallel Degree Limit correct num to use on Parallel Hints [message #689994 is a reply to message #689993] |
Wed, 11 September 2024 12:31 |
wtolentino
Messages: 420
Registered: March 2005
|
Senior Member |
|
|
thanks, so the DML is getting the parallel DML. the lines on the explain plan
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1), E - Syntax error (2))
---------------------------------------------------------------------------
0 - STATEMENT
U - PARALLEL / duplicate hint
0 - INS$1
E - ods
0 - SEL$2
E - ods
does it indicates that there is something wrong with syntax for the hints? or it is an incorrect syntax?
INSERT /*+ PARALLEL ods.ods_prognote_header_o,4 */ INTO ...;
[Updated on: Wed, 11 September 2024 12:33] Report message to a moderator |
|
|
|
| Re: Parallel Degree Limit correct num to use on Parallel Hints [message #689995 is a reply to message #689994] |
Wed, 11 September 2024 13:10 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Warren, I think you may be taking a suboptimal approach here. You have a performance problem (which you have not defined) and considered the possibility that it was caused by inappropriate use of parallel DML. Fair enough, but I have shown you that you were not getting any parallel DML. So that cannot have been the problem. If I were responsible for this, I would begin by stating what the current performance is and what the required performance is. Then investigate what is happening: exec plans, number of rows, wait events, all that stuff.
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 06:28:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
