Коллеги добрый день!
Есть хранимая функция. Должная возвращать реализацию или себестоимость. Отлаживал на реализации - все ок, скорость приемлемая.
Но когда стал добавлять себестоимость (сейчас закомментировано), резко просела производительность. (планы обоих вариантов прикладываю)
Вопрос:
1. Почему так сильно поменялся план
2. Что сделать, что бы он не менялся или как этим можно управлять.
Буду благодарен, если посоветуете где почитать.
Заранее благодарю...
Собственно функция:
Код:
create or replace function grost_getValue(dat1 in date, dat2 in date, MX in number, TREE1 in VARCHAR, flag in number)
return ffmaprep.salesum%type
is
r ffmaprep.salesum%type;
s ffmaprep.salesum%type;
begin
select
sum(decode(f.saleq , 0, 0, f.salesum/f.saleq*f.quantity * decode(f.saleop, 1, 1, -1)) ) into r
-- sum(decode(f.incomeq, 0, 0, f.incomesum/f.incomeq*f.quantity*decode(f.saleop, 1, 1, -1) )) into r, s
from
ffMapRep f, smcard c, sacardclass ca
where
(f.article= c.article) and
(c.idclass=ca.id) and
(f.saleop in (1,3)) and
(f.saledate >= dat1) and
(f.saledate <= dat2) and
(ca.tree like tree1 ) and
(f.saletype in ('CS','CR') ) and
(decode(f.salelocationfrom,null, f.salelocationto, f.salelocationfrom) = MX);
return nvl (r,0 );
exception when no_data_found then
dbms_output.put_line('function grost_getValue : ОШИБКА!');
return -1;
end grost_getValue;
/ Первый вариант, где только реализация
Код:
SELECT
SUM(DECODE(F.SALEQ , 0, 0, F.SALESUM/F.SALEQ*F.QUANTITY * DECODE(F.SALEOP, 1, 1, -1)) )
FROM
FFMAPREP F, SMCARD C, SACARDCLASS CA
WHERE
(F.ARTICLE= C.ARTICLE) AND (C.IDCLASS=CA.ID) AND
(F.SALEOP IN (1,3)) AND (F.SALEDATE >= :B4 ) AND (F.SALEDATE <= :B3 ) AND
(CA.TREE LIKE :B2 ) AND (F.SALETYPE IN ('CS','CR') ) AND
(DECODE(F.SALELOCATIONFROM,NULL, F.SALELOCATIONTO, F.SALELOCATIONFROM) = :B1 )
Plan hash value: 3224485307
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 136 (100)| |
| 1 | SORT AGGREGATE | | 1 | 68 | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 1 | 68 | 136 (0)| 00:00:02 |
| 5 | NESTED LOOPS | | 58 | 1740 | 13 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| SACARDCLASS | 1 | 14 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | SACCARDCLASSTREE | 1 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| SMCARD | 58 | 928 | 10 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | SMCARDCLASSIFID | 58 | | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | FFMAPREP_ARTICLE | 59 | | 2 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | FFMAPREP | 1 | 38 | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:B4<=:B3)
7 - access("CA"."TREE" LIKE :B2)
filter("CA"."TREE" LIKE :B2)
9 - access("C"."IDCLASS"="CA"."ID")
10 - access("F"."ARTICLE"="C"."ARTICLE")
11 - filter((INTERNAL_FUNCTION("F"."SALEOP") AND
DECODE(TO_CHAR("F"."SALELOCATIONFROM"),NULL,"F"."SALELOCATIONTO","F"."SALELOCATIONFROM")=:B1
AND INTERNAL_FUNCTION("F"."SALETYPE") AND "F"."SALEDATE">=:B4 AND "F"."SALEDATE"<=:B3))
Добавляем в запрос себестоимость:
Код:
SELECT
SUM(DECODE(F.SALEQ , 0, 0 , F.SALESUM/F.SALEQ*F.QUANTITY * DECODE(F.SALEOP, 1, 1, -1)) ),
SUM(DECODE(F.INCOMEQ, 0, 0 , F.INCOMESUM/F.INCOMEQ*F.QUANTITY*DECODE(F.SALEOP, 1, 1, -1) ))
FROM
FFMAPREP F, SMCARD C, SACARDCLASS CA WHERE
(F.ARTICLE= C.ARTICLE) AND (C.IDCLASS=CA.ID) AND
(F.SALEOP IN (1,3)) AND (F.SALEDATE >= :B4 ) AND
(F.SALEDATE <= :B3 ) AND (CA.TREE LIKE :B2 ) AND
(F.SALETYPE IN ('CS','CR') ) AND
(DECODE(F.SALELOCATIONFROM,NULL, F.SALELOCATIONTO, F.SALELOCATIONFROM) = :B1 )
Plan hash value: 186733415
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5041 (100)| |
| 1 | SORT AGGREGATE | | 1 | 75 | | |
|* 2 | FILTER | | | | | |
|* 3 | HASH JOIN | | 721 | 54075 | 5041 (1)| 00:01:01 |
|* 4 | HASH JOIN | | 721 | 43981 | 5031 (1)| 00:01:01 |
|* 5 | TABLE ACCESS FULL| FFMAPREP | 721 | 32445 | 3763 (1)| 00:00:46 |
| 6 | TABLE ACCESS FULL| SMCARD | 179K| 2805K| 1267 (1)| 00:00:16 |
|* 7 | TABLE ACCESS FULL | SACARDCLASS | 4190 | 58660 | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:B4<=:B3)
3 - access("C"."IDCLASS"="CA"."ID")
4 - access("F"."ARTICLE"="C"."ARTICLE")
5 - filter((INTERNAL_FUNCTION("F"."SALEOP") AND
DECODE(TO_CHAR("F"."SALELOCATIONFROM"),NULL,"F"."SALELOCATIONTO","F"."SALELOC
ATIONFROM")=:B1 AND INTERNAL_FUNCTION("F"."SALETYPE") AND
"F"."SALEDATE">=:B4 AND "F"."SALEDATE"<=:B3))
7 - filter("CA"."TREE" LIKE :B2)