Доброго дня господа !
Версия 12.2
Основная мысль
1. первичная паралле-ная выборка
2. сборка результатов и сохранение в временную таблицу
3. передача курсора на эту временную таблицу в процедуре
SaveCursor в качестве параметра (Sys_RefCursor)
Проблема при обращении к матер. with через Cursor(select * from MAIN) - база включает
буферизацию в процессе параллельного выполнения HASH JOIN OUTER BUFFERED
В примере это выглядит так
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.
create or replace view vv_remain2 as
with function SaveCursor(pTable Sys_RefCursor) return Integer
is
pragma autonomous_transaction;
begin
declare
I Integer:=1;
begin
return I;
end;
end;
REST as (select /*+
Parallel(2)
*/
DAY,
PROGRAM,
AMOUNT
from SCREST
),
MAIN as (select /*+
Materialize
*/
PROGRAM,
Sum(AMOUNT) AMOUNT
from REST
group by PROGRAM
)
select PROGRAM,
AMOUNT
from MAIN
where Exists(select SaveCursor(Cursor(select * from MAIN)) F1 from DUAL)
План оптимизации оригинала выглядит так
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.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
Plan hash value: 3760599892
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11834 | 1028K| | 21M (1)| 00:27:50 | | | | | |
| 1 | VIEW | | 11834 | 1028K| | 27 (0)| 00:00:01 | | | | | |
| 2 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C1A_A42D6B6C | 11834 | 751K| | 27 (0)| 00:00:01 | | | | | |
| 3 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9D6C17_A42D6B6C | | | | | | | | | | |
|* 5 | FILTER | | | | | | | | | | | |
| 6 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | | | | |
| 7 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 8 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6C18_A42D6B6C | | | | | | | | | | |
| 9 | SORT AGGREGATE | | 1 | 8 | | | | | | | | |
|* 10 | TABLE ACCESS FULL | SV_TT_REMAIN | 6 | 48 | | 4 (0)| 00:00:01 | | | | | |
| 11 | VIEW | | 1 | 6 | | 2 (0)| 00:00:01 | | | | | |
| 12 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | | | | |
| 13 | PX COORDINATOR FORCED SERIAL | | | | | | | | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ20013 | 5917 | 525K| | 21M (1)| 00:27:50 | | | Q2,13 | P->S | QC (RAND) |
| 15 | LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6C19_A42D6B6C | | | | | | | | Q2,13 | PCWP | |
| 16 | SORT ORDER BY | | 5917 | 525K| | 21M (1)| 00:27:50 | | | Q2,13 | PCWP | |
| 17 | PX RECEIVE | | 5917 | 525K| | 21M (1)| 00:27:50 | | | Q2,13 | PCWP | |
| 18 | PX SEND RANGE | :TQ20012 | 5917 | 525K| | 21M (1)| 00:27:50 | | | Q2,12 | P->P | RANGE |
| 19 | WINDOW SORT | | 5917 | 525K| | 21M (1)| 00:27:50 | | | Q2,12 | PCWP | |
| 20 | PX RECEIVE | | 5917 | 525K| | 21M (1)| 00:27:50 | | | Q2,12 | PCWP | |
| 21 | PX SEND RANGE | :TQ20011 | 5917 | 525K| | 21M (1)| 00:27:50 | | | Q2,11 | P->P | RANGE |
| 22 | VIEW | | 5917 | 525K| | 21M (1)| 00:27:50 | | | Q2,11 | PCWP | |
|* 23 | HASH JOIN OUTER BUFFERED | | 5917 | 566K| | 21M (1)| 00:27:50 | | | Q2,11 | PCWP | |
| 24 | PX RECEIVE | | 217 | 17577 | | 21M (1)| 00:27:50 | | | Q2,11 | PCWP | |
| 25 | PX SEND HASH | :TQ20009 | 217 | 17577 | | 21M (1)| 00:27:50 | | | Q2,09 | P->P | HASH |
| 26 | VIEW | | 217 | 17577 | | 21M (1)| 00:27:50 | | | Q2,09 | PCWP | |
|* 27 | HASH JOIN OUTER BUFFERED | | 217 | 28861 | | 21M (1)| 00:27:50 | | | Q2,09 | PCWP | |
| 28 | PX RECEIVE | | 3 | 204 | | 3 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 29 | PX SEND HASH | :TQ20006 | 3 | 204 | | 3 (0)| 00:00:01 | | | Q2,06 | P->P | HASH |
| 30 | VIEW | | 3 | 204 | | 3 (0)| 00:00:01 | | | Q2,06 | PCWP | |
| 31 | NESTED LOOPS | | 3 | 126 | | 3 (0)| 00:00:01 | | | Q2,06 | PCWP | |
| 32 | NESTED LOOPS | | 6 | 126 | | 3 (0)| 00:00:01 | | | Q2,06 | PCWP | |
|* 33 | VIEW | | 1 | 17 | | 2 (0)| 00:00:01 | | | Q2,06 | PCWP | |
| 34 | PX BLOCK ITERATOR | | 1 | 6 | | 2 (0)| 00:00:01 | | | Q2,06 | PCWC | |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C18_A42D6B6C | 1 | 6 | | 2 (0)| 00:00:01 | | | Q2,06 | PCWP | |
|* 36 | INDEX RANGE SCAN | SYS_C00341088 | 6 | | | 1 (0)| 00:00:01 | | | Q2,06 | PCWP | |
| 37 | TABLE ACCESS BY INDEX ROWID | RZ_REMAIN | 3 | 75 | | 1 (0)| 00:00:01 | | | Q2,06 | PCWP | |
| 38 | PX RECEIVE | | 43431 | 2756K| | 21M (1)| 00:27:50 | | | Q2,09 | PCWP | |
| 39 | PX SEND HASH | :TQ20007 | 43431 | 2756K| | 21M (1)| 00:27:50 | | | Q2,07 | P->P | HASH |
| 40 | VIEW | | 43431 | 2756K| | 21M (1)| 00:27:50 | | | Q2,07 | PCWP | |
| 41 | HASH GROUP BY | | 43431 | 3138K| | 21M (1)| 00:27:50 | | | Q2,07 | PCWP | |
| 42 | PX RECEIVE | | 43431 | 3138K| | 21M (1)| 00:27:50 | | | Q2,07 | PCWP | |
| 43 | PX SEND HASH | :TQ20005 | 43431 | 3138K| | 21M (1)| 00:27:50 | | | Q2,05 | P->P | HASH |
| 44 | HASH GROUP BY | | 43431 | 3138K| | 21M (1)| 00:27:50 | | | Q2,05 | PCWP | |
| 45 | VIEW | | 43431 | 3138K| | 21M (1)| 00:27:50 | | | Q2,05 | PCWP | |
|* 46 | FILTER | | | | | | | | | Q2,05 | PCWC | |
| 47 | HASH GROUP BY | | 43431 | 14M| 323M| 21M (1)| 00:27:50 | | | Q2,05 | PCWP | |
| 48 | PX RECEIVE | | 43431 | 14M| | 21M (1)| 00:27:50 | | | Q2,05 | PCWP | |
| 49 | PX SEND HASH | :TQ20004 | 43431 | 14M| | 21M (1)| 00:27:50 | | | Q2,04 | P->P | HASH |
| 50 | HASH GROUP BY | | 43431 | 14M| 323M| 21M (1)| 00:27:50 | | | Q2,04 | PCWP | |
| 51 | VIEW | | 868K| 296M| | 21M (1)| 00:27:49 | | | Q2,04 | PCWP | |
|* 52 | HASH JOIN RIGHT OUTER | | 868K| 443M| | 21M (1)| 00:27:49 | | | Q2,04 | PCWP | |
| 53 | PX RECEIVE | | 10 | 240 | | 2 (0)| 00:00:01 | | | Q2,04 | PCWP | |
| 54 | PX SEND BROADCAST | :TQ20001 | 10 | 240 | | 2 (0)| 00:00:01 | | | Q2,01 | S->P | BROADCAST |
| 55 | PX SELECTOR | | | | | | | | | Q2,01 | SCWC | |
|* 56 | INDEX RANGE SCAN | SYS_C00411191 | 10 | 240 | | 2 (0)| 00:00:01 | | | Q2,01 | SCWP | |
| 57 | VIEW | | 868K| 423M| | 21M (1)| 00:27:49 | | | Q2,04 | PCWP | |
|* 58 | HASH JOIN RIGHT OUTER | | 868K| 506M| | 21M (1)| 00:27:49 | | | Q2,04 | PCWP | |
| 59 | PX RECEIVE | | 257 | 6168 | | 2 (0)| 00:00:01 | | | Q2,04 | PCWP | |
| 60 | PX SEND BROADCAST | :TQ20002 | 257 | 6168 | | 2 (0)| 00:00:01 | | | Q2,02 | S->P | BROADCAST |
| 61 | PX SELECTOR | | | | | | | | | Q2,02 | SCWC | |
|* 62 | INDEX RANGE SCAN | SYS_C00411191 | 257 | 6168 | | 2 (0)| 00:00:01 | | | Q2,02 | SCWP | |
| 63 | VIEW | | 868K| 487M| | 21M (1)| 00:27:49 | | | Q2,04 | PCWP | |
|* 64 | HASH JOIN OUTER | | 868K| 538M| | 21M (1)| 00:27:49 | | | Q2,04 | PCWP | |
| 65 | BUFFER SORT | | | | | | | | | Q2,04 | PCWC | |
| 66 | PX RECEIVE | | 868K| 379M| | 21M (1)| 00:27:30 | | | Q2,04 | PCWP | |
| 67 | PX SEND HASH | :TQ20000 | 868K| 379M| | 21M (1)| 00:27:30 | | | | S->P | HASH |
| 68 | VIEW | | 868K| 379M| | 21M (1)| 00:27:30 | | | | | |
|* 69 | FILTER | | | | | | | | | | | |
| 70 | PX COORDINATOR | | | | | | | | | | | |
| 71 | M) PX SEND QC (RANDO | :TQ10001 | 4812K| 2143M| | 21M (1)| 00:27:30 | | | Q1,01 | P->S | QC (RAND) |
| 72 | NESTED LOOPS | | 4812K| 2143M| | 21M (1)| 00:27:30 | | | Q1,01 | PCWP | |
| 73 | BUFFER SORT | | | | | | | | | Q1,01 | PCWC | |
| 74 | PX RECEIVE | | | | | | | | | Q1,01 | PCWP | |
| 75 | CAST PX SEND BROAD | :TQ10000 | | | | | | | | Q1,00 | P->P | BROADCAST |
|* 76 | VIEW | | 1 | 9 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 77 | ERATOR PX BLOCK IT | | 1 | 6 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 78 | SS FULL TABLE ACCE | SYS_TEMP_0FD9D6C18_A42D6B6C | 1 | 6 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 79 | VIEW | V_KPS_DA_BY_COL | 4812K| 2101M| | 37M (1)| 00:49:29 | | | Q1,01 | PCWP | |
| 80 | VIEW | V_KPS_DA_BY_COL | 1924M| 831G| | 37M (1)| 00:49:19 | | | Q1,01 | PCWP | |
| 81 | VIEW | V_KPS_DA_BY_COL | 1924M| 831G| | 37M (1)| 00:49:19 | | | Q1,01 | PCWP | |
|* 82 | FILTER | | | | | | | | | Q1,01 | PCWC | |
| 83 | N RANGE ALL PX PARTITIO | | 1924M| 107G| | 37M (1)| 00:49:19 | 1 | 2644 | Q1,01 | PCWC | |
|* 84 | SS FULL TABLE ACCE | T_KPS_DA_BY_COL | 1924M| 107G| | 37M (1)| 00:49:19 | 1 | 2644 | Q1,01 | PCWP | |
|* 85 | INDEX RANGE SCAN | SYS_C00411191 | 1 | 18 | | 2 (0)| 00:00:01 | | | | | |
| 86 | PX RECEIVE | | 17M| 3200M| | 252K (1)| 00:00:20 | | | Q2,04 | PCWP | |
| 87 | PX SEND HASH | :TQ20003 | 17M| 3200M| | 252K (1)| 00:00:20 | | | Q2,03 | P->P | HASH |
| 88 | VIEW | V_CFA_CONTRACT_FULL_ALL | 17M| 3200M| | 252K (1)| 00:00:20 | | | Q2,03 | PCWP | |
| 89 | VIEW | V_CFA_CONTRACT_FULL_ALL | 17M| 3200M| | 252K (1)| 00:00:20 | | | Q2,03 | PCWP | |
| 90 | VIEW | V_CFA_CONTRACT_FULL_ALL | 17M| 3200M| | 252K (1)| 00:00:20 | | | Q2,03 | PCWP | |
|* 91 | FILTER | | | | | | | | | Q2,03 | PCWC | |
| 92 | PX BLOCK ITERATOR | | 17M| 216M| | 252K (1)| 00:00:20 | | | Q2,03 | PCWC | |
| 93 | L TABLE ACCESS FUL | T_CFA_CONTRACT_FULL_ALL | 17M| 216M| | 252K (1)| 00:00:20 | | | Q2,03 | PCWP | |
| 94 | PX RECEIVE | | 16360 | 271K| | 219 (1)| 00:00:01 | | | Q2,11 | PCWP | |
| 95 | PX SEND HASH | :TQ20010 | 16360 | 271K| | 219 (1)| 00:00:01 | | | Q2,10 | P->P | HASH |
| 96 | VIEW | | 16360 | 271K| | 219 (1)| 00:00:01 | | | Q2,10 | PCWP | |
| 97 | HASH GROUP BY | | 16360 | 655K| | 219 (1)| 00:00:01 | | | Q2,10 | PCWP | |
| 98 | PX RECEIVE | | 16360 | 655K| | 219 (1)| 00:00:01 | | | Q2,10 | PCWP | |
| 99 | PX SEND HASH | :TQ20008 | 16360 | 655K| | 219 (1)| 00:00:01 | | | Q2,08 | P->P | HASH |
| 100 | HASH GROUP BY | | 16360 | 655K| | 219 (1)| 00:00:01 | | | Q2,08 | PCWP | |
| 101 | VIEW | | 16360 | 655K| | 218 (0)| 00:00:01 | | | Q2,08 | PCWP | |
| 102 | NESTED LOOPS | | 16360 | 14M| | 218 (0)| 00:00:01 | | | Q2,08 | PCWP | |
| 103 | VIEW | | 1 | 8 | | 2 (0)| 00:00:01 | | | Q2,08 | PCWP | |
| 104 | PX BLOCK ITERATOR | | 1 | 6 | | 2 (0)| 00:00:01 | | | Q2,08 | PCWC | |
| 105 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C18_A42D6B6C | 1 | 6 | | 2 (0)| 00:00:01 | | | Q2,08 | PCWP | |
| 106 | VIEW | | 16360 | 13M| | | | | | Q2,08 | PCWP | |
| 107 | COLLECTION ITERATOR PICKLER FETCH | SHOW | 16360 | | | 38 (0)| 00:00:01 | | | Q2,08 | PCWP | |
| 108 | PX COORDINATOR | | | | | | | | | | | |
| 109 | PX SEND QC (RANDOM) | :TQ30000 | 11834 | 1028K| | 28 (0)| 00:00:01 | | | Q3,00 | P->S | QC (RAND) |
| 110 | LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6C1A_A42D6B6C | | | | | | | | Q3,00 | PCWP | |
| 111 | VIEW | | 11834 | 1028K| | 28 (0)| 00:00:01 | | | Q3,00 | PCWP | |
| 112 | PX BLOCK ITERATOR | | 11834 | 751K| | 28 (0)| 00:00:01 | | | Q3,00 | PCWC | |
| 113 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C19_A42D6B6C | 11834 | 751K| | 28 (0)| 00:00:01 | | | Q3,00 | PCWP | |
|*114 | PX COORDINATOR | | | | | | | | | | | |
| 115 | PX SEND QC (RANDOM) | :TQ50000 | 11834 | 1028K| | 21M (1)| 00:27:50 | | | Q5,00 | P->S | QC (RAND) |
|*116 | FILTER | | | | | | | | | Q5,00 | PCWC | |
| 117 | VIEW | SV_BO_REMAIN2 | 11834 | 1028K| | 21M (1)| 00:27:50 | | | Q5,00 | PCWP | |
| 118 | VIEW | | 11834 | 1028K| | 27 (0)| 00:00:01 | | | Q5,00 | PCWP | |
| 119 | PX BLOCK ITERATOR | | 11834 | 751K| | 27 (0)| 00:00:01 | | | Q5,00 | PCWC | |
| 120 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C1A_A42D6B6C | 11834 | 751K| | 27 (0)| 00:00:01 | | | Q5,00 | PCWP | |
| 121 | PX COORDINATOR | | | | | | | | | | | |
| 122 | PX SEND QC (RANDOM) | :TQ40000 | 1 | | | 2 (0)| 00:00:01 | | | Q4,00 | P->S | QC (RAND) |
| 123 | VIEW | | 1 | | | 2 (0)| 00:00:01 | | | Q4,00 | PCWP | |
| 124 | PX BLOCK ITERATOR | | 1 | 13 | | 2 (0)| 00:00:01 | | | Q4,00 | PCWC | |
| 125 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C17_A42D6B6C | 1 | 13 | | 2 (0)| 00:00:01 | | | Q4,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("PARAMETERS"."REFRESH"('P000',"SDREP"=>TO_DATE(' 2021-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'),"SKIND"=>1) IS NULL)
10 - filter("DAY"=:B1)
23 - access("C"."PROGRAM"(+)="A"."PROGRAM" AND "P"."FULL"=CASE WHEN ("C"."PROGRAM"(+) IS NOT NULL) THEN 0 ELSE 0 END )
27 - access("B"."PROGRAM"(+)="A"."PROGRAM" AND "P"."FULL"=CASE WHEN ("B"."PROGRAM"(+) IS NOT NULL) THEN 0 ELSE 0 END )
33 - filter("P"."FULL"=0)
36 - access("A"."DAY"=INTERNAL_FUNCTION("P"."DEND"))
46 - filter(MAX()<>0)
52 - access("R"."ITEM"(+)="C"."CREDIT_PROGRAM_NAME_DIC")
56 - access("R"."BOOK"(+)='PROGRAM')
58 - access("D"."ITEM"(+)="A"."DEAL_CREDIT_KIND")
62 - access("D"."BOOK"(+)='ENTITY')
64 - access("C"."DEAL_CONTRACT_NUMB"(+)="A"."DEAL_CONTRACT_NUMB")
69 - filter("A"."DEAL_CONTRACT_NUMB" LIKE 'CTX%' OR "A"."DEAL_CONTRACT_NUMB" LIKE '9%' AND (LENGTH("A"."DEAL_CONTRACT_NUMB")=11 OR LENGTH("A"."DEAL_CONTRACT_NUMB")=12) OR EXISTS
(SELECT 0 FROM "PF_REPORT"."CONSTANT" "CONSTANT" WHERE "BOOK"='ENTITY' AND "ITEM"=:B1))
76 - filter("B"."FULL"=0)
79 - filter("A"."DATE_REP">=SYS_OP_CSEE(INTERNAL_FUNCTION("B"."DREP")) AND "A"."DATE_REP"<=SYS_OP_CSEE(INTERNAL_FUNCTION("B"."DREP")))
82 - filter(SYS_AUDIT(1,'ADBUS','V_KPS_DA_BY_COL','S_KPS_DA_BY_COL',3) IS NULL)
84 - filter("DAY_MONTH"='D' AND NVL("A_MAIN_DEPT_BAL_M",'0000')<>'8100')
85 - access("BOOK"='ENTITY' AND "ITEM"=:B1)
filter("ITEM"=:B1)
91 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_CONTRACT_FULL_ALL','S_CFA_CONTRACT_FULL_ALL',3) IS NULL)
114 - filter( EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */ "C0" "EXEC" FROM "SYS"."SYS_TEMP_0FD9D6C17_A42D6B6C" "T1") "PARAMETER"))
116 - filter( EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */ "C0" "EXEC" FROM "SYS"."SYS_TEMP_0FD9D6C17_A42D6B6C" "T1") "PARAMETER"))
Note
-----
- dynamic statistics used: dynamic sampling (level=8)
- Degree of Parallelism is 2 because of hint
Много и страшно !!!
Проблема здесь = HASH JOIN OUTER BUFFERED
Многократное обращение к with MAIN - приемлемо, но стоит только перейти к Cursor-у - имею проблему
Как можно это решить ? Смотрю в сторону pq_distribute(K, none)
Но не совсем понимаю его применение в моем случае
SaveCursor(Cursor(select /*+ pq_distribute(R none) */ * from MAIN R))
Заранее благодарен ! |
