CUR Gets

CUR Gets:
Here’s an interesting anomaly from the OTN database forum – PL/SQL track. It’s a single row delete from a table that does a huge number of db block gets and (in the first example shown) physical reads. Here’s the supplied output with autotrace enabled:

SQL> delete from messages2 where id = 11004240718;

1 row deleted.

Elapsed: 00:00:03.79

Execution Plan
----------------------------------------------------------
Plan hash value: 140582024

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  DELETE            | MESSAGES2    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0016189 |     1 |    17 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=11004240718)

Statistics
----------------------------------------------------------
         61  recursive calls
       7381  db block gets
         92  consistent gets
       5802  physical reads
     303368  redo size
        839  bytes sent via SQL*Net to client
        801  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

After a little chit-chat, the obvious guess turns out to be relevant – thought the OP doesn’t quite tell us what we need to know – there is a constraint on another table which, when enabled, results in a large amount of work, and when disabled reduces the workload to the expected level. The excess work is (probably) Oracle checking a referential integrity (foreign key) constraint to ensure that there are no child rows blocking the delete (or, possibly, operating an ON DELETE CASCADE operation – although the OP tells us that there is no such constraint).
Here’s a quick and dirty, approximate, demo on 11.2.0.3 with an 8KB block size:

create table parent (
 id  number(8),
 description varchar2(10),
 constraint par_pk primary key (id)
)
;

create table child(
 id_p number(8)
  constraint chi_fk_par
  references parent,
--  on delete cascade
 id number(8),
 description varchar2(10),
 constraint chi_pk primary key (id_p, id)
)
;

insert into parent values (10000001,'one');
insert into child values(10000001, 1,'one')

insert into parent values (10000002,'two');
begin
 for i in reverse 1..20000 loop
  insert into child values(10000002,i,'two');
 end loop;
end;
/

insert into parent values (10000003,'three');
insert into child values(10000003, 1,'three')

commit;

begin
 dbms_stats.gather_table_stats(user,'child',cascade=>true);
 dbms_stats.gather_table_stats(user,'parent');
end;
/

delete from child;
commit;

select index_name, leaf_blocks
from user_indexes
where table_name = 'CHILD'
;

The output from the query on the child index reported 103 leaf blocks for the index.

So here’s the code to prime the test:

variable b1 number
exec :b1 := 10000001
delete from parent where id = :b1;

set autotrace on

exec :b1 := 10000002
delete from parent where id = :b1;

set autotrace off

Note how I set up my data so that I could use a bind variable from SQL*Plus to do a quick and cheap delete to get the optimisation sorted out on the first run. This doesn’t really help in the version of the run that I’m going to show because the autotrace with the implicit explain re-optimises anyway, but here’s the plan and stats:

Execution Plan
----------------------------------------------------------
Plan hash value: 3366423708

-------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | DELETE STATEMENT   |        |     1 |     6 |     1 |
|   1 |  DELETE            | PARENT |       |       |       |
|*  2 |   INDEX UNIQUE SCAN| PAR_PK |     1 |     6 |       |
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=TO_NUMBER(:B1))

Statistics
----------------------------------------------------------
          0  recursive calls
        107  db block gets
          1  consistent gets
          0  physical reads
       7416  redo size
        918  bytes sent via SQL*Net to client
       1038  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Note the db block gets statistics – 107, compared to the 103 leaf blocks in the index – that’s a pretty good indication by itself that the workload that the OP is seeing is related to the prior massive delete from the child table with a large scan (possibly including a high volume of delayed block cleanout in his case) of the now empty section of the foreign key index.
If one quick test isn’t enough to convince you that the explanation is probably relevant then you can run the test a few times with different numbers of rows in the child table, and with the referential integrity constraint changed to on delete cascade to see what happens if you don’t do the child delete but leave it up to Oracle to handle the cascade. You might also want to check the complete set of session stats, and generate a trace file – as I did, but there’s already to much Oracle output in the posting already, so I haven’t included the results.



DIGITAL JUICE