Сравнение планов выполнения запросов

Зорин Е.В.
Дата: 19.11.2008 11:31:31
Всем доброго времени суток.
Существует запрос, в обобщённом виде его можно представить так:
with one as
(....),
two as
(...)
select * from one
MINUS
select * from two

Дело в том, что по отдельности каждый запрос (select * from one, select * from two) выполняются весьма быстро: one = 0.3 сек (955 кортежей), two = 0.1 cек(388 кортежей). Однако при выполнении вышеприведённого запроса(с использованием MINUS) время выполнения возрастает до 5-8 секунд. Уповать на различность результатов выборок из one и two не стоит, потому что была произведена такая проверка: запрос из one был положен в pipelined-функцию, а затем полученная pipelined-функция была соединена с фрагментом two:
with two as
(...)
select * from table(cast(func_name as type))
MINUS
select * from two

В результате, время выполнения сократилось с 5-8 секунд до 0.5 cек. Смог бы кто нибудь объяснить, почему?
Чтобы не быть голословным, представляю вашему вниманию полный текст запроса:

with resel as
(
select
well.well_s,
well.well_id,
reservoir_part.reservoir_part_s reservoir_element_s,
reservoir_part.reservoir_part_name reservoir_element_name,
reservoir_part.reservoir_part_code reservoir_element_code,
reservoir_group.reservoir_part_s reservoir_group_s,
reservoir_group.reservoir_part_name reservoir_group_name,
reservoir_group.reservoir_part_code reservoir_group_code
from
well,
wellbore,
wellbore_intv,
reservoir_part,
-- reservoir group tables
(select * from reservoir_part where entity_type_nm = 'RESERVOIR_ZONE'
)reservoir_group,
(select distinct prim_toplg_obj_s, sec_toplg_obj_s from topological_rel
where prim_toplg_obj_t = 'EARTH_POS_RGN'),
earth_pos_rgn
where
wellbore.well_s = well.well_s and
wellbore_intv.wellbore_s = wellbore.wellbore_s and
wellbore_intv.geologic_ftr_t = 'RESERVOIR_PART' and
wellbore_intv.geologic_ftr_s = reservoir_part.reservoir_part_s and
-- reservoir group
sec_toplg_obj_s = wellbore_intv_s and prim_toplg_obj_s = earth_pos_rgn_s and
reservoir_group.RESERVOIR_PART_S = earth_pos_rgn.geologic_ftr_s
),
--all data work in month for well-reservoirs
wellresdate as
(
select
column_value,
resel.well_id,
resel.reservoir_group_code,
well_s,
resel.reservoir_element_s,
bsasc_source
from
resel
cross join
(
select * from table(ProdDataProcessingPckg.GetDaysInMonth(extract(month from &pEndDate),extract(year from &pEndDate)))
cross join
(select 'liquid rate' bsasc_source from dual union all
select 'water cut' from dual union all
select 'oil density' from dual union all
select 'gas-oil ratio' from dual union all
select 'natural gas' from dual union all
select 'injected gas' from dual union all
select 'condensate' from dual union all
select 'crude oil' from dual union all
select 'injected water' from dual union all
select 'produced water' from dual union all
select 'production' from dual union all
select 'gas injection' from dual union all
select 'water injection' from dual
)
)

inner join
(select * from table(proddataprocessingPckg.GetPeriodsForWell('current well role', 'oil producing',&pEndDate))) wellroledates
on resel.well_s = wellroledates.key_s
and column_value between wellroledates.start_time and wellroledates.end_time

inner join
(select * from table(proddataprocessingPckg.GetPeriodsForWell('well status', 'active stock',&pEndDate))) workingwells
on resel.well_s = workingwells.key_s
and column_value between workingwells.start_time and workingwells.end_time

left outer join
--inner join --inner join greatly slows speed
(select * from table(proddataprocessingPckg.GetPeriodsForWellRes(&pEndDate))) workwellres
on resel.reservoir_element_s = workwellres.key_s
and column_value between workwellres.start_time and workwellres.end_time

where workwellres.key_s is not null
),
one as
(
select
column_value,
reservoir_element_s,
bsasc_source
from
(
select column_value,
reservoir_element_s,
bsasc_source,
lag(bsasc_source) over (partition by reservoir_element_s, bsasc_source order by column_value) chk
from wellresdate
)
where chk is null
),
two as
(
select
min(prod_start_time) prod_start_time,
reservoir_element_s,
bsasc_source
select * from table(proddataprocessingPckg.GetPrevDailyData(&pEndDate))
where prod_start_time between &pStartDate and &pEndDate
group by reservoir_element_s, bsasc_source
)
select * from one
MINUS
select * from two

------------------------------------------------------------------------------------------------------
Далее представлены планы выполнения запросов(я в них пока не совсем силён, надеюсь, они помогут опытным специалистам):
Plan(one MINUS two).xls - план выполнения общего запроса
Plan(one).xls - план выполнения select * from one
Plan(two).xls - план выполнения select * from two
Зорин Е.В.
Дата: 19.11.2008 11:38:55
Извиняюсь за отсутствие форматирование в первом посте.
Первый пост пропускать :), читать сразу второй.

Всем доброго времени суток.
Существует запрос, в обобщённом виде его можно представить так:
with one as
(....),
two as
(...)
select * from one
MINUS
select * from two

Дело в том, что по отдельности каждый запрос (select * from one, select * from two) выполняются весьма быстро: one = 0.3 сек (955 кортежей), two = 0.1 cек(388 кортежей). Однако при выполнении вышеприведённого запроса(с использованием MINUS) время выполнения возрастает до 5-8 секунд. Уповать на различность результатов выборок из one и two не стоит, потому что была произведена такая проверка: запрос из one был положен в pipelined-функцию, а затем полученная pipelined-функция была соединена с фрагментом two:
with two as
(...)
select * from table(cast(func_name as type))
MINUS
select * from two

В результате, время выполнения сократилось с 5-8 секунд до 0.5 cек. Смог бы кто нибудь объяснить, почему?
Чтобы не быть голословным, представляю вашему вниманию полный текст запроса:

with resel as
(
select
well.well_s,
well.well_id,
reservoir_part.reservoir_part_s reservoir_element_s,
reservoir_part.reservoir_part_name reservoir_element_name,
reservoir_part.reservoir_part_code reservoir_element_code,
reservoir_group.reservoir_part_s reservoir_group_s,
reservoir_group.reservoir_part_name reservoir_group_name,
reservoir_group.reservoir_part_code reservoir_group_code
from
well,
wellbore,
wellbore_intv,
reservoir_part,
-- reservoir group tables
(select * from reservoir_part where entity_type_nm = 'RESERVOIR_ZONE'
)reservoir_group,
(select distinct prim_toplg_obj_s, sec_toplg_obj_s from topological_rel
where prim_toplg_obj_t = 'EARTH_POS_RGN'),
earth_pos_rgn
where
wellbore.well_s = well.well_s and
wellbore_intv.wellbore_s = wellbore.wellbore_s and
wellbore_intv.geologic_ftr_t = 'RESERVOIR_PART' and
wellbore_intv.geologic_ftr_s = reservoir_part.reservoir_part_s and
-- reservoir group
sec_toplg_obj_s = wellbore_intv_s and prim_toplg_obj_s = earth_pos_rgn_s and
reservoir_group.RESERVOIR_PART_S = earth_pos_rgn.geologic_ftr_s
),
--all data work in month for well-reservoirs
wellresdate as
(
select
column_value,
resel.well_id,
resel.reservoir_group_code,
well_s,
resel.reservoir_element_s,
bsasc_source
from
resel
cross join
(
select * from table(ProdDataProcessingPckg.GetDaysInMonth(extract(month from &pEndDate),extract(year from &pEndDate)))
cross join
(select 'liquid rate' bsasc_source from dual union all
select 'water cut' from dual union all
select 'oil density' from dual union all
select 'gas-oil ratio' from dual union all
select 'natural gas' from dual union all
select 'injected gas' from dual union all
select 'condensate' from dual union all
select 'crude oil' from dual union all
select 'injected water' from dual union all
select 'produced water' from dual union all
select 'production' from dual union all
select 'gas injection' from dual union all
select 'water injection' from dual
)
)

inner join
(select * from table(proddataprocessingPckg.GetPeriodsForWell('current well role', 'oil producing',&pEndDate))) wellroledates
on resel.well_s = wellroledates.key_s
and column_value between wellroledates.start_time and wellroledates.end_time

inner join
(select * from table(proddataprocessingPckg.GetPeriodsForWell('well status', 'active stock',&pEndDate))) workingwells
on resel.well_s = workingwells.key_s
and column_value between workingwells.start_time and workingwells.end_time

left outer join
--inner join --inner join greatly slows speed
(select * from table(proddataprocessingPckg.GetPeriodsForWellRes(&pEndDate))) workwellres
on resel.reservoir_element_s = workwellres.key_s
and column_value between workwellres.start_time and workwellres.end_time

where workwellres.key_s is not null
),
one as
(
select
column_value,
reservoir_element_s,
bsasc_source
from
(
select column_value,
reservoir_element_s,
bsasc_source,
lag(bsasc_source) over (partition by reservoir_element_s, bsasc_source order by column_value) chk
from wellresdate
)
where chk is null
),
two as
(
select
min(prod_start_time) prod_start_time,
reservoir_element_s,
bsasc_source
select * from table(proddataprocessingPckg.GetPrevDailyData(&pEndDate))
where prod_start_time between &pStartDate and &pEndDate
group by reservoir_element_s, bsasc_source
)
select * from one
MINUS
select * from two

------------------------------------------------------------------------------------------------------
Далее представлены планы выполнения запросов(я в них пока не совсем силён, надеюсь, они помогут опытным специалистам):
Plan(one MINUS two).xls - план выполнения общего запроса
Plan(one).xls - план выполнения select * from one
Plan(two).xls - план выполнения select * from two
Зорин Е.В.
Дата: 19.11.2008 11:39:57
Plan(one MINUS two):
Зорин Е.В.
Дата: 19.11.2008 11:40:38
Зорин Е.В.
Дата: 19.11.2008 11:41:00
реплика
Дата: 19.11.2008 11:59:38
Зорин Е.В.,

пора бы снять трассировку.
ну или хотя бы посмотреть статистику исполнения.
Зорин Е.В.
Дата: 19.11.2008 13:19:17
реплика,

подскажите, а как посмотреть статистику исполнения?
реплика
Дата: 19.11.2008 14:45:56
Зорин Е.В.,

а поискать лень?

как один из вариантов для получения статистики исполнения - в SQL*Plus перед запуском своих запросов скажи:
SET AUTOTRACE ON
в итоге после выполнения запроса будет выводиться план и статистика его выполнения

Для детальной трассировки - FAQ про трассировку