|
запрос выполняется по 2 планам
#40007769
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
есть запрос прямой вставки в пустую таблицу TMP$DATA, выполняется по плану около 5 минут [Plan hash value: 3486981300]
выполняется DOP=8
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 130K(100)| | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 118K| 52M| | 13028 (2)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT (TEMP SEGMENT MERGE)| SYS_TEMP_1FD9EEDFC_4E3D7D06 | | | | | | | | Q1,01 | PCWP | |
| 5 | VIEW | | 118K| 52M| | 13028 (2)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | WINDOW SORT PUSHED RANK | | 118K| 15M| 17M| 13028 (2)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 118K| 15M| | 13028 (2)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 118K| 15M| | 13028 (2)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 9 | WINDOW CHILD PUSHED RANK | | 118K| 15M| | 13028 (2)| 00:00:01 | | | Q1,00 | PCWP | |
| 10 | HASH JOIN | | 118K| 15M| | 13025 (2)| 00:00:01 | | | Q1,00 | PCWP | |
| 11 | TABLE ACCESS FULL | MAIN_DATA | 689 | 43407 | | 5 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 12 | PX BLOCK ITERATOR | | 11M| 772M| | 12996 (2)| 00:00:01 | KEY | KEY | Q1,00 | PCWC | |
| 13 | TABLE ACCESS FULL | LOAD_DATA | 11M| 772M| | 12996 (2)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
| 14 | PX COORDINATOR | | | | | | | | | | | |
| 15 | PX SEND QC (RANDOM) | :TQ20001 | | | | | | | | Q2,01 | P->S | QC (RAND) |
| 16 | LOAD AS SELECT (HYBRID TSM/HWMB) | TMP$DATA | | | | | | | | Q2,01 | PCWP | |
| 17 | OPTIMIZER STATISTICS GATHERING | | | | | | | | | Q2,01 | PCWP | |
| 18 | UNION-ALL | | | | | | | | | Q2,01 | PCWP | |
| 19 | VIEW | | 118K| 51M| | 902 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 20 | PX BLOCK ITERATOR | | 118K| 4283K| | 902 (0)| 00:00:01 | | | Q2,01 | PCWC | |
| 21 | TABLE ACCESS FULL | SYS_TEMP_1FD9EEDFC_4E3D7D06 | 118K| 4283K| | 902 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 22 | HASH JOIN RIGHT ANTI | | 123M| 5765M| | 130K (2)| 00:00:03 | | | Q2,01 | PCWP | |
| 23 | PX RECEIVE | | 118K| 1504K| | 902 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 24 | PX SEND BROADCAST | :TQ20000 | 118K| 1504K| | 902 (0)| 00:00:01 | | | Q2,00 | P->P | BROADCAST |
| 25 | VIEW | | 118K| 1504K| | 902 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 26 | PX BLOCK ITERATOR | | 118K| 4283K| | 902 (0)| 00:00:01 | | | Q2,00 | PCWC | |
| 27 | TABLE ACCESS FULL | SYS_TEMP_1FD9EEDFC_4E3D7D06 | 118K| 4283K| | 902 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 28 | PX BLOCK ITERATOR | | 132M| 4551M| | 128K (2)| 00:00:03 | KEY | KEY | Q2,01 | PCWC | |
| 29 | TABLE ACCESS FULL | MAIN_DATA | 132M| 4551M| | 128K (2)| 00:00:03 | KEY | KEY | Q2,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Но иногда он выполняется более пары часов, при этом в ASH видно, что он сначала выполняется по другому плану [Plan hash value: 2882402466]
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 129K(100)| | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 467 | 8 (13)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT (TEMP SEGMENT MERGE)| SYS_TEMP_1FD9EED28_4E3D7D06 | | | | | | | Q1,01 | PCWP | |
| 5 | VIEW | | 1 | 467 | 8 (13)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | WINDOW SORT PUSHED RANK | | 1 | 646 | 8 (13)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 1 | 646 | 8 (13)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 1 | 646 | 8 (13)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 9 | WINDOW CHILD PUSHED RANK | | 1 | 646 | 8 (13)| 00:00:01 | | | Q1,00 | PCWP | |
| 10 | HASH JOIN | | 1 | 646 | 7 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 11 | JOIN FILTER CREATE | :BF0000 | 1 | 583 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 12 | PARTITION LIST SINGLE | | 1 | 583 | 2 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWC | |
| 13 | TABLE ACCESS FULL | LOAD_DATA | 1 | 583 | 2 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
| 14 | JOIN FILTER USE | :BF0000 | 689 | 43407 | 5 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 689 | 43407 | 5 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 16 | TABLE ACCESS FULL | MAIN_DATA | 689 | 43407 | 5 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 17 | PX COORDINATOR | | | | | | | | | | |
| 18 | PX SEND QC (RANDOM) | :TQ20002 | | | | | | | Q2,02 | P->S | QC (RAND) |
| 19 | LOAD AS SELECT (HYBRID TSM/HWMB) | TMP$DATA | | | | | | | Q2,02 | PCWP | |
| 20 | OPTIMIZER STATISTICS GATHERING | | | | | | | | Q2,02 | PCWP | |
| 21 | UNION-ALL | | | | | | | | Q2,02 | PCWP | |
| 22 | VIEW | | 1 | 459 | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 23 | PX BLOCK ITERATOR | | 1 | 450 | 2 (0)| 00:00:01 | | | Q2,02 | PCWC | |
| 24 | TABLE ACCESS FULL | SYS_TEMP_1FD9EED28_4E3D7D06 | 1 | 450 | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 25 | HASH JOIN RIGHT ANTI | | 132M| 6195M| 129K (2)| 00:00:03 | | | Q2,02 | PCWP | |
| 26 | PX RECEIVE | | 1 | 13 | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 27 | PX SEND HASH | :TQ20000 | 1 | 13 | 2 (0)| 00:00:01 | | | Q2,00 | P->P | HASH |
| 28 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 29 | PX BLOCK ITERATOR | | 1 | 450 | 2 (0)| 00:00:01 | | | Q2,00 | PCWC | |
| 30 | TABLE ACCESS FULL | SYS_TEMP_1FD9EED28_4E3D7D06 | 1 | 450 | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 31 | PX RECEIVE | | 132M| 4551M| 128K (2)| 00:00:03 | | | Q2,02 | PCWP | |
| 32 | PX SEND HASH | :TQ20001 | 132M| 4551M| 128K (2)| 00:00:03 | | | Q2,01 | P->P | HASH |
| 33 | PX BLOCK ITERATOR | | 132M| 4551M| 128K (2)| 00:00:03 | KEY | KEY | Q2,01 | PCWC | |
| 34 | TABLE ACCESS FULL | MAIN_DATA | 132M| 4551M| 128K (2)| 00:00:03 | KEY | KEY | Q2,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
при этом выполнение идет в 1 поток эти самые 2 часа
потом он переключается на старый план [Plan hash value: 3486981300] и быстро дорабатывает в 16 потоков (DOP=8) за пять минут.
Что это вообще такое, кто сталкивался? |
|
|
