CUR Gets

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

execute snap_my_stats.start_snap

insert /* append */ into t1
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        1e6 + rownum            id,
        mod(rownum,1000)        btree_col,
        mod(rownum,1000)        bitmap_col,
        rpad('x',100)           padding
from
        generator
;

execute snap_my_stats.end_snap


SQL> ed funny_deadlock

SQL> ed temp1.txt

SQL> host expand  temp1.txt
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 – though 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 or on delete set null 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:

rem
rem     Script:         pk_fk_gets.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2013
rem

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 autotrace with the implicit explain plan re-optimises anyway, but here’s the plan and stats for the second delete:

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 (which is present as a comment in my original text) 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 too much Oracle output in the posting already so I haven’t included the results.