I have an Oracle procedure that normally takes around 5 minutes to run. There are two areas in the procedure where I need to query a list of id numbers that may have been modified. The query in these two segments is identical. The first is at the beginning of a cursor definition, and the second is in a delete statement in the main body of the procedure. The key part that is repeated a lot involves getting the most recent date a certain table was refreshed. The entire procedure is fairly lengthy, so I'll include only the relevant bits below.
Here is the beginning of the cursor:
cursor gvg_profile_c is
with last_refreshed as
(select trunc(max(t.last_refreshed)) as max_date
from ad.pbi_gvg_profile t),
with modified_ids as
(
select distinct id_number
from ad.hr_giving cg
join ad.pbi_dates d
on d.DATE_FULL = trunc(cg.processed_date)
where d.RELATIVE_DATE >= (select max_date from last_refreshed)
and d.RELATIVE_DATE <= trunc(CURRENT_DATE)
and cg.fiscal_year >= ad.current_fiscal_year - 6
union
select distinct gi.gift_donor_id as id_number
from ad.gift gi
where gi.date_added >= (select max_date from last_refreshed)
or gi.date_modified >= (select max_date from last_refreshed)
union
select distinct p.pledge_donor_id as id_number
from ad.pledge_rev p
where p.date_added >= (select max_date from last_refreshed)
or p.date_modified >= (select max_date from last_refreshed)
union
select distinct a.id_number
from ad.affiliation a
where a.date_added >= (select max_date from last_refreshed)
or a.date_modified >= (select max_date from last_refreshed)
),
Here is the delete statment:
delete from ad.pbi_gvg_profile p
where p.id_number in
(with last_refreshed as (select trunc(max(t.last_refreshed)) as max_date
from ad.pbi_gvg_profile t)
select distinct id_number
from ad.hr_giving cg
join ad.pbi_dates d
on d.DATE_FULL = trunc(cg.processed_date)
where d.RELATIVE_DATE >= (select max_date from last_refreshed)
and d.RELATIVE_DATE <= trunc(CURRENT_DATE)
and cg.fiscal_year >= ad.current_fiscal_year - 6
union
select distinct gi.gift_donor_id as id_number
from ad.gift gi
where gi.date_added >= (select max_date from last_refreshed)
or gi.date_modified >= (select max_date from last_refreshed)
union
select distinct p.pledge_donor_id as id_number
from ad.pledge_rev p
where p.date_added >= (select max_date from last_refreshed)
or p.date_modified >= (select max_date from last_refreshed)
union
select distinct a.id_number
from ad.affiliation a
where a.date_added >= (select max_date from last_refreshed)
or a.date_modified >= (select max_date from last_refreshed)
);
I thought it would make more sense and be more efficient to a have a function that calculates the last_refreshed date and puts it in a variable that can be reused. So I did that. Here's the function:
NOTE: You can see here that I sometimes need to get the date from a different table. That is accounted for for the above code via an if/else, I just left it out so you don't have to read identical code 4 times.
function get_last_refreshed_date(scope in smallint) return date is
last_refreshed date;
table_name varchar2(30);
select_sql varchar2(255);
begin
if scope = c_scope_all then
table_name := 'ad.pbi_gvg_profile';
else
table_name := 'ad.pbi_gvg_profile_ag';
end if;
select_sql := 'select trunc(max(last_refreshed))
from '||table_name;
execute immediate select_sql
into last_refreshed;
return(last_refreshed);
end get_last_refreshed_date;
Then I change the beginning of my cursor to use this function and a variable instead, so now it looks like this:
last_refreshed date := get_last_refreshed_date(scope);
cursor gvg_profile_c is
with modified_ids as
(
select distinct id_number
from ad.hr_giving cg
join ad.pbi_dates d
on d.DATE_FULL = trunc(cg.processed_date)
where d.RELATIVE_DATE >= last_refreshed
and d.RELATIVE_DATE <= trunc(CURRENT_DATE)
and cg.fiscal_year >= ad.current_fiscal_year - 6
union
select distinct gi.gift_donor_id as id_number
from ad.gift gi
where gi.date_added >= last_refreshed
or gi.date_modified >= last_refreshed
union
select distinct p.pledge_donor_id as id_number
from ad.pledge_rev p
where p.date_added >= last_refreshed
or p.date_modified >= last_refreshed
union
select distinct a.id_number
from ad.affiliation a
where a.date_added >= last_refreshed
or a.date_modified >= last_refreshed
),
This works fine. My procedure still only takes 5 minutes to run.
However, when I make the same identical change to the Delete statement, suddenly the procedure takes about 65 minutes to run. And I can tell from logging that the execution of the delete statement accounts for over 60 of those minutes.
Why would the variable work fine in the cursor, but cause a major slowdown in a delete statement in the body of the procedure?
Any help is greatly appreciated!
EDIT: Here are the execution plans. I hope this is legible; I'm not sure about the right way to post these.
Initial execution plan (for the original version that runs faster):
Plan Hash Value :
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 5198617 | 93575106 | 246606 | 00:00:10 |
| 1 | DELETE | PBI_GVG_PROFILE | | | | |
| * 2 | HASH JOIN | | 5198617 | 93575106 | 246606 | 00:00:10 |
| 3 | VIEW | VW_NSO_1 | 385797 | 2700579 | 113764 | 00:00:05 |
| 4 | TEMP TABLE TRANSFORMATION | | | | | |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9DBD7E_75CD48C7 | | | | |
| 6 | SORT AGGREGATE | | 1 | 8 | | |
| 7 | TABLE ACCESS FULL | PBI_GVG_PROFILE | 9804360 | 78434880 | 122284 | 00:00:05 |
| 8 | SORT UNIQUE | | 385797 | 10441299 | 113764 | 00:00:05 |
| 9 | UNION-ALL | | | | | |
| * 10 | HASH JOIN | | 2065 | 80535 | 83893 | 00:00:04 |
| 11 | VIEW | PBI_DATES | 1 | 15 | 14 | 00:00:01 |
| 12 | SORT ORDER BY | | 1 | 2235 | 14 | 00:00:01 |
| * 13 | FILTER | | | | | |
| * 14 | HASH JOIN OUTER | | 1 | 2235 | 9 | 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 1 | 2235 | 9 | 00:00:01 |
| 16 | STATISTICS COLLECTOR | | | | | |
| * 17 | VIEW | | 1 | 2210 | 8 | 00:00:01 |
| 18 | WINDOW SORT | | 1 | 74 | 8 | 00:00:01 |
| * 19 | HASH JOIN OUTER | | 1 | 74 | 7 | 00:00:01 |
| * 20 | HASH JOIN OUTER | | 1 | 48 | 4 | 00:00:01 |
| 21 | VIEW | | 1 | 13 | 2 | 00:00:01 |
| * 22 | CONNECT BY WITHOUT FILTERING | | | | | |
| 23 | FAST DUAL | | 1 | | 2 | 00:00:01 |
| 24 | VIEW | | 1 | 35 | 2 | 00:00:01 |
| 25 | VIEW | | 1 | 13 | 2 | 00:00:01 |
| * 26 | CONNECT BY WITHOUT FILTERING | | | | | |
| 27 | FAST DUAL | | 1 | | 2 | 00:00:01 |
| 28 | VIEW | | 1 | 26 | 3 | 00:00:01 |
| 29 | WINDOW SORT | | 1 | 13 | 3 | 00:00:01 |
| 30 | VIEW | | 1 | 13 | 2 | 00:00:01 |
| * 31 | CONNECT BY WITHOUT FILTERING | | | | | |
| 32 | FAST DUAL | | 1 | | 2 | 00:00:01 |
| 33 | VIEW | | 1 | 6 | 2 | 00:00:01 |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9DBD7E_75CD48C7 | 1 | 8 | 2 | 00:00:01 |
| * 35 | INDEX RANGE SCAN | ZZ_ADV_TABLE_KEY0 | 1 | 25 | 1 | 00:00:01 |
| * 36 | INDEX RANGE SCAN | ZZ_ADV_TABLE_KEY0 | 1 | 25 | 1 | 00:00:01 |
| * 37 | TABLE ACCESS FULL | HR_GIVING | 206493 | 4955832 | 83878 | 00:00:04 |
| * 38 | TABLE ACCESS FULL | GIFT | 345555 | 9329985 | 22655 | 00:00:01 |
| 39 | VIEW | | 1 | 6 | 2 | 00:00:01 |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9DBD7E_75CD48C7 | 1 | 8 | 2 | 00:00:01 |
| 41 | VIEW | | 1 | 6 | 2 | 00:00:01 |
| 42 | TABLE ACCESS FULL | SYS_TEMP_0FD9DBD7E_75CD48C7 | 1 | 8 | 2 | 00:00:01 |
| * 43 | TABLE ACCESS FULL | PLEDGE_REV | 313 | 8451 | 22 | 00:00:01 |
| 44 | VIEW | | 1 | 6 | 2 | 00:00:01 |
| 45 | TABLE ACCESS FULL | SYS_TEMP_0FD9DBD7E_75CD48C7 | 1 | 8 | 2 | 00:00:01 |
| 46 | VIEW | | 1 | 6 | 2 | 00:00:01 |
| 47 | TABLE ACCESS FULL | SYS_TEMP_0FD9DBD7E_75CD48C7 | 1 | 8 | 2 | 00:00:01 |
| * 48 | TABLE ACCESS FULL | AFFILIATION | 117827 | 3181329 | 4067 | 00:00:01 |
| 49 | VIEW | | 1 | 6 | 2 | 00:00:01 |
| 50 | TABLE ACCESS FULL | SYS_TEMP_0FD9DBD7E_75CD48C7 | 1 | 8 | 2 | 00:00:01 |
| 51 | VIEW | | 1 | 6 | 2 | 00:00:01 |
| 52 | TABLE ACCESS FULL | SYS_TEMP_0FD9DBD7E_75CD48C7 | 1 | 8 | 2 | 00:00:01 |
| 53 | TABLE ACCESS FULL | PBI_GVG_PROFILE | 9804360 | 107847960 | 121780 | 00:00:05 |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."ID_NUMBER"="ID_NUMBER")
* 10 - access("D"."DATE_FULL"=TRUNC(INTERNAL_FUNCTION("CG"."PROCESSED_DATE")))
* 13 - filter( (SELECT "MAX_DATE" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ INTERNAL_FUNCTION("C0") "MAX_DATE" FROM "SYS"."SYS_TEMP_0FD9DBD7E_75CD48C7" "T1") "LAST_REFRESHED")<=TRUNC(CURRENT_DATE))
* 14 - access(TO_NUMBER("ADV_TABLE_CODE"(+))=TO_NUMBER("DTS"."DATE_FY"))
* 17 - filter(INTERNAL_FUNCTION("DTS"."RELATIVE_DATE")<=TRUNC(CURRENT_DATE) AND INTERNAL_FUNCTION("DTS"."RELATIVE_DATE")>= (SELECT "MAX_DATE" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */
INTERNAL_FUNCTION("C0") "MAX_DATE" FROM "SYS"."SYS_TEMP_0FD9DBD7E_75CD48C7" "T1") "LAST_REFRESHED"))
* 19 - access("FY_NTH_DAY_TBL"."N"(+)="DATE_TBL"."N")
* 20 - access("FY_TBL"."N"(+)="DATE_TBL"."N")
* 22 - filter(LEVEL<=CEIL(TO_DATE('30/06'||TO_CHAR(EXTRACT(YEAR FROM SYSDATE@!)+5),'DD/MM/YYYY')-TO_DATE(' 1966-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
* 26 - filter(LEVEL<=CEIL(TO_DATE('30/06'||TO_CHAR(EXTRACT(YEAR FROM SYSDATE@!)+5),'DD/MM/YYYY')-TO_DATE(' 1966-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
* 31 - filter(LEVEL<=CEIL(TO_DATE('30/06'||TO_CHAR(EXTRACT(YEAR FROM SYSDATE@!)+5),'DD/MM/YYYY')-TO_DATE(' 1966-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
* 35 - access("ADV_TABLE_TYPE"(+)='@FY1' AND "ADV_TABLE_CODE"(+)>='2013' AND "ADV_TABLE_CODE"(+)<="CURRENT_FISCAL_YEAR"())
* 35 - filter(TO_NUMBER("ADV_TABLE_CODE"(+))=TO_NUMBER("DTS"."DATE_FY"))
* 36 - access("ADV_TABLE_TYPE"(+)='@FY1' AND "ADV_TABLE_CODE"(+)>='2013' AND "ADV_TABLE_CODE"(+)<="CURRENT_FISCAL_YEAR"())
* 37 - filter(TO_NUMBER("CG"."FISCAL_YEAR")>=TO_NUMBER("ADVRPT"."CURRENT_FISCAL_YEAR"())-6)
* 38 - filter("GI"."DATE_ADDED">= (SELECT "MAX_DATE" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ INTERNAL_FUNCTION("C0") "MAX_DATE" FROM "SYS"."SYS_TEMP_0FD9DBD7E_75CD48C7" "T1") "LAST_REFRESHED") OR
"GI"."DATE_MODIFIED">= (SELECT "MAX_DATE" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ INTERNAL_FUNCTION("C0") "MAX_DATE" FROM "SYS"."SYS_TEMP_0FD9DBD7E_75CD48C7" "T1") "LAST_REFRESHED"))
* 43 - filter("P"."DATE_ADDED">= (SELECT "MAX_DATE" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ INTERNAL_FUNCTION("C0") "MAX_DATE" FROM "SYS"."SYS_TEMP_0FD9DBD7E_75CD48C7" "T1") "LAST_REFRESHED") OR
"P"."DATE_MODIFIED">= (SELECT "MAX_DATE" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ INTERNAL_FUNCTION("C0") "MAX_DATE" FROM "SYS"."SYS_TEMP_0FD9DBD7E_75CD48C7" "T1") "LAST_REFRESHED"))
* 48 - filter("A"."DATE_ADDED">= (SELECT "MAX_DATE" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ INTERNAL_FUNCTION("C0") "MAX_DATE" FROM "SYS"."SYS_TEMP_0FD9DBD7E_75CD48C7" "T1") "LAST_REFRESHED") OR
"A"."DATE_MODIFIED">= (SELECT "MAX_DATE" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ INTERNAL_FUNCTION("C0") "MAX_DATE" FROM "SYS"."SYS_TEMP_0FD9DBD7E_75CD48C7" "T1") "LAST_REFRESHED"))
Execution plan after deleting the CTE clause and replacing (select max_date from last_refreshed) with the :last_refreshed parameter. I don't really understand how this takes into account the fact that in reality I'm using a variable containing a function result, but maybe someone can explain that to me.
Plan Hash Value :
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 5198617 | 93575106 | 246602 | 00:00:10 |
| 1 | DELETE | PBI_GVG_PROFILE | | | | |
| * 2 | HASH JOIN | | 5198617 | 93575106 | 246602 | 00:00:10 |
| 3 | VIEW | VW_NSO_1 | 385797 | 2700579 | 113760 | 00:00:05 |
| 4 | SORT UNIQUE | | 385797 | 10441299 | 113760 | 00:00:05 |
| 5 | UNION-ALL | | | | | |
| * 6 | HASH JOIN | | 2065 | 80535 | 83889 | 00:00:04 |
| 7 | VIEW | PBI_DATES | 1 | 15 | 10 | 00:00:01 |
| 8 | SORT ORDER BY | | 1 | 2235 | 10 | 00:00:01 |
| * 9 | HASH JOIN OUTER | | 1 | 2235 | 9 | 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 2235 | 9 | 00:00:01 |
| 11 | STATISTICS COLLECTOR | | | | | |
| * 12 | VIEW | | 1 | 2210 | 8 | 00:00:01 |
| 13 | WINDOW SORT | | 1 | 74 | 8 | 00:00:01 |
| * 14 | FILTER | | | | | |
| * 15 | HASH JOIN OUTER | | 1 | 74 | 7 | 00:00:01 |
| * 16 | HASH JOIN OUTER | | 1 | 48 | 4 | 00:00:01 |
| 17 | VIEW | | 1 | 13 | 2 | 00:00:01 |
| * 18 | CONNECT BY WITHOUT FILTERING | | | | | |
| 19 | FAST DUAL | | 1 | | 2 | 00:00:01 |
| 20 | VIEW | | 1 | 35 | 2 | 00:00:01 |
| 21 | VIEW | | 1 | 13 | 2 | 00:00:01 |
| * 22 | CONNECT BY WITHOUT FILTERING | | | | | |
| 23 | FAST DUAL | | 1 | | 2 | 00:00:01 |
| 24 | VIEW | | 1 | 26 | 3 | 00:00:01 |
| 25 | WINDOW SORT | | 1 | 13 | 3 | 00:00:01 |
| 26 | VIEW | | 1 | 13 | 2 | 00:00:01 |
| * 27 | CONNECT BY WITHOUT FILTERING | | | | | |
| 28 | FAST DUAL | | 1 | | 2 | 00:00:01 |
| * 29 | INDEX RANGE SCAN | ZZ_ADV_TABLE_KEY0 | 1 | 25 | 1 | 00:00:01 |
| * 30 | INDEX RANGE SCAN | ZZ_ADV_TABLE_KEY0 | 1 | 25 | 1 | 00:00:01 |
| * 31 | TABLE ACCESS FULL | HR_GIVING | 206493 | 4955832 | 83878 | 00:00:04 |
| * 32 | TABLE ACCESS FULL | GIFT | 345555 | 9329985 | 22655 | 00:00:01 |
| * 33 | TABLE ACCESS FULL | PLEDGE_REV | 313 | 8451 | 22 | 00:00:01 |
| * 34 | TABLE ACCESS FULL | AFFILIATION | 117827 | 3181329 | 4067 | 00:00:01 |
| 35 | TABLE ACCESS FULL | PBI_GVG_PROFILE | 9804360 | 107847960 | 121780 | 00:00:05 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."ID_NUMBER"="ID_NUMBER")
* 6 - access("D"."DATE_FULL"=TRUNC(INTERNAL_FUNCTION("CG"."PROCESSED_DATE")))
* 9 - access(TO_NUMBER("ADV_TABLE_CODE"(+))=TO_NUMBER("DTS"."DATE_FY"))
* 12 - filter(INTERNAL_FUNCTION("DTS"."RELATIVE_DATE")>=:LAST_REFRESHED AND INTERNAL_FUNCTION("DTS"."RELATIVE_DATE")<=TRUNC(CURRENT_DATE))
* 14 - filter(TRUNC(CURRENT_DATE)>=:LAST_REFRESHED)
* 15 - access("FY_NTH_DAY_TBL"."N"(+)="DATE_TBL"."N")
* 16 - access("FY_TBL"."N"(+)="DATE_TBL"."N")
* 18 - filter(LEVEL<=CEIL(TO_DATE('30/06'||TO_CHAR(EXTRACT(YEAR FROM SYSDATE@!)+5),'DD/MM/YYYY')-TO_DATE(' 1966-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
* 22 - filter(LEVEL<=CEIL(TO_DATE('30/06'||TO_CHAR(EXTRACT(YEAR FROM SYSDATE@!)+5),'DD/MM/YYYY')-TO_DATE(' 1966-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
* 27 - filter(LEVEL<=CEIL(TO_DATE('30/06'||TO_CHAR(EXTRACT(YEAR FROM SYSDATE@!)+5),'DD/MM/YYYY')-TO_DATE(' 1966-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
* 29 - access("ADV_TABLE_TYPE"(+)='@FY1' AND "ADV_TABLE_CODE"(+)>='2013' AND "ADV_TABLE_CODE"(+)<="CURRENT_FISCAL_YEAR"())
* 29 - filter(TO_NUMBER("ADV_TABLE_CODE"(+))=TO_NUMBER("DTS"."DATE_FY"))
* 30 - access("ADV_TABLE_TYPE"(+)='@FY1' AND "ADV_TABLE_CODE"(+)>='2013' AND "ADV_TABLE_CODE"(+)<="CURRENT_FISCAL_YEAR"())
* 31 - filter(TO_NUMBER("CG"."FISCAL_YEAR")>=TO_NUMBER("ADVRPT"."CURRENT_FISCAL_YEAR"())-6)
* 32 - filter("GI"."DATE_ADDED">=:LAST_REFRESHED OR "GI"."DATE_MODIFIED">=:LAST_REFRESHED)
* 33 - filter("P"."DATE_ADDED">=:LAST_REFRESHED OR "P"."DATE_MODIFIED">=:LAST_REFRESHED)
* 34 - filter("A"."DATE_ADDED">=:LAST_REFRESHED OR "A"."DATE_MODIFIED">=:LAST_REFRESHED)