|
такое бывает ? оптимизация
#32154231
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
Привет всем !
занимаюсь оптимизацией запроса:
изначальный вариант
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
explain plan for
select wc.uwi, wc.facility_id, rsvr.reservoir_id, rsvr.reservoir_name,
mpd.start_time, mpd.productive_days, mpd.mass, mpd.mass_uom,
whdr.well_name,ofo.duration,whdr.field,
ofo.DOWNHOLE_FACILITY_S,mpd.liquid_volume,df.facility_id as downhole_id,mpd.volume_uom
from monthly_production_hdr mph,
monthly_production_data mpd,
well_completion wc,
reservoir rsvr,
oil_field_operation ofo,
downhole_facility df,
well_hdr whdr
where mph.material_type = 'OIL' and mph.flow_dir_type = 'PRODUCTION'
and mpd.preferred_flag = 'Y' and mph.existence_type = 'ACTUAL' and mph.activity_type = 'ALLOCATED'
and mph.monthly_production_hdr_s = mpd.monthly_production_hdr_s
and mph.well_completion_s = wc.well_completion_s
and mph.oil_field_operation_s = ofo.oil_field_operation_s
and wc.reservoir_s = rsvr.reservoir_s and wc.uwi=whdr.uwi
and df.DOWNHOLE_FACILITY_S(+)=ofo.DOWNHOLE_FACILITY_S;
выдал следующую стоимость:
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.
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 157K| 22M| 56911 |
| HASH JOIN | | 157K| 22M| 56911 |
| TABLE ACCESS FULL |RESERVOIR | 30 | 450 | 1 |
| HASH JOIN | | 157K| 20M| 56592 |
| TABLE ACCESS FULL |WELL_HDR | 2K| 46K| 12 |
| HASH JOIN | | 157K| 17M| 53816 |
| TABLE ACCESS FULL |WELL_COMP | 10K| 244K| 31 |
| HASH JOIN OUTER | | 157K| 13M| 48978 |
| MERGE JOIN | | 157K| 12M| 46716 |
Plan Table
----------------------------------------------------------------
| SORT JOIN | | 157K| 8M| 21233 |
| MERGE JOIN | | 157K| 8M| 15057 |
| SORT JOIN | | 157K| 6M| 7088 |
| TABLE ACCESS FULL|MONTHLY_P | 157K| 6M| 2042 |
| SORT JOIN | | 509K| 5M| 7970 |
| TABLE ACCESS FULL|OIL_FIELD | 509K| 5M| 1233 |
| SORT JOIN | | 946K| 24M| 25483 |
| TABLE ACCESS FULL |MONTHLY_P | 946K| 24M| 1528 |
| TABLE ACCESS FULL |DOWNHOLE_ | 6K| 61K| 15 |
select count(*) от этого запроса потратил 36 сек
после его модификации:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
explain plan for
select wc.uwi, wc.facility_id, rsvr.reservoir_id, rsvr.reservoir_name,
mpd.start_time, mpd.productive_days, mpd.mass, mpd.mass_uom,
whdr.well_name,ofo.duration,whdr.field,
ofo.DOWNHOLE_FACILITY_S,mpd.liquid_volume,df.facility_id as downhole_id,mpd.volume_uom
from monthly_production_hdr mph,
monthly_production_data mpd,
oil_field_operation ofo,
well_completion wc,
downhole_facility df,
reservoir rsvr,
well_hdr whdr
where mph.material_type = 'OIL' and mph.flow_dir_type = 'PRODUCTION'
and mpd.preferred_flag = 'Y' and mph.existence_type = 'ACTUAL' and mph.activity_type = 'ALLOCATED'
and mph.monthly_production_hdr_s = mpd.monthly_production_hdr_s
and mph.well_completion_s = wc.well_completion_s
and mph.oil_field_operation_s = ofo.oil_field_operation_s
and wc.reservoir_s = rsvr.reservoir_s and wc.uwi=whdr.uwi
and ofo.uwi=whdr.uwi
and ofo.well_completion_s=wc.well_completion_s
and df.DOWNHOLE_FACILITY_S(+)=ofo.DOWNHOLE_FACILITY_S
стоимость исполнения уменьшилась более чем в 5 раз
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.
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 1 | 164 | 10318 |
| NESTED LOOPS | | 1 | 164 | 10318 |
| NESTED LOOPS OUTER | | 1 | 137 | 10314 |
| NESTED LOOPS | | 1 | 127 | 10313 |
| NESTED LOOPS | | 1 | 112 | 10312 |
| HASH JOIN | | 159 | 10K| 9835 |
| HASH JOIN | | 10K| 438K| 102 |
| TABLE ACCESS FULL |WELL_HDR | 2K| 46K| 12 |
| TABLE ACCESS FULL |WELL_COMP | 10K| 244K| 31 |
Plan Table
----------------------------------------------------------------
| TABLE ACCESS FULL |OIL_FIELD | 509K| 12M| 1233 |
| TABLE ACCESS BY INDEX|MONTHLY_P | 157K| 6M| 3 |
| INDEX RANGE SCAN |MONTHLY_P | 157K| | 2 |
| TABLE ACCESS BY INDEX |RESERVOIR | 30 | 450 | 1 |
| INDEX UNIQUE SCAN |RESERVOIR | 30 | | |
| TABLE ACCESS BY INDEX R|DOWNHOLE_ | 6K| 61K| 1 |
| INDEX UNIQUE SCAN |DOWNHOLE_ | 6K| | |
| TABLE ACCESS BY INDEX RO|MONTHLY_P | 946K| 24M| 4 |
| INDEX RANGE SCAN |MONTHLY_P | 946K| | 3 |
----------------------------------------------------------------
а вот скорость исполнения увеличилась и составила 39 сек.
запросы (оба варианта) исполнял неоднократно, время выполнения практически неизменно.
Перед запуском включал оптимизатор:
alter session set optimizer_mode = choose;
что не так? |
|
|
