Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Delete statement becomes slow when using variable

user-6v0t1Feb 18 2023 — edited Feb 19 2023

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)
Comments
Post Details
Added on Feb 18 2023
17 comments
234 views