jump to navigation

Consistent Gets, db block gets and DML June 11, 2009

Posted by mwidlake in performance.
Tags: , ,
add a comment

A few posts back I talked about consistent gets, db block gets and how I had realised I had got by for years without fully undertanding the terms. I then described how I went about learning more.

Well, I am about to learn yet more on the subject.

Jonathan Lewis posted a question about just this recently and I’m pretty sure I am the blog he was looking at. I won’t link to it as wordpress can automatically convert a link to a comment on other wordpress blogs and I don’t want to do that yet. You can find Jonathan’s post easily enough from my blogroll.

When I looked at consistent gets and db block gets I did so from a purely select point of view – and that is my downfall. Oracle does a lot more than select. DB block gets appear when data is being changed…

You can get a copy ofmy test script here and this is the full output.

Below is a printout of my test script. I simply create an empty copy of the EMP table, populate it, select from it and then delete from it. I do it twice so that the second time all code is parsed. All I am looking at right now is how much db_block_get activity there is.

set pause off timi on echo on
drop table emp2 purge;
spool dml_gets_test1.lst
set autotrace on
-- create an empty table from emp
create table emp2 as select 
* from emp where rownum < 1;
-- round one of tests, where parsing overhead exists
select empno,ename from emp2;
insert into emp2 select * from emp;
select empno,ename from emp2;
delete from emp2;
-- round two, just the statements
insert into emp2 select * from emp;
select empno,ename from emp2;
delete from emp2;
commit;
select empno,ename from emp2;
spool off
set autotrace off
drop table emp2 purge;

So, what do we see? {i’ve trimmed the output to show only what I think are the relevant bits}

Oh, this is on version 11.1.0.6 of oracle on windows vista, 8k block size, db_file_multiblock_read_count 128 {wow, more than I thought}, straight as created from the download and instal.

select empno,ename from emp2;
no rows selected

Execution Plan
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP2 | 1 | 20 | 2 (0)| 00:00:01 |
————————————————————————–

Statistics
———————————————————-
44 recursive calls
0 db block gets
8 consistent gets
0 physical reads

Just 8 consistent gets. No db_block gets, even though the statment is being parsed.

insert into emp2 select * from emp;
14 rows created.

Execution Plan
————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
————————————————————–
| 0 | INSERT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01
| 1 | LOAD TABLE CONVENTIONAL | EMP2 | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 (0)| 00:00:01
————————————————————-

Statistics
———————————————————-
129 recursive calls
19 db block gets
107 consistent gets
0 physical reads
1672 redo size

As you can see, 129 recursive calls, 19 db block gets and 107 consistent gets. I believe a lot of that is due to parsing and maybe other things to do with the first time data is inserted into the table.

The second itteration gave the same plan but the statistics are very different:-

Statistics
———————————————————-
0 recursive calls
3 db block gets
8 consistent gets
0 physical reads

This helps show the overhead of parsing. however, we still see 3 db block gets

select empno,ename from emp2;

EMPNO ENAME
———- ———-
7369 SMITH
7499 ALLEN
{snip}
7934 MILLER

14 rows selected.

Execution Plan
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 14 | 280 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP2 | 14 | 280 | 3 (0)| 00:00:01 |
————————————————————————–

Statistics (run two)
———————————————————-
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads

No db block gets, 14 consistent gets as the data is read. Remember, this statement was parsed at the start of this script.

delete from emp2;
14 rows deleted.

Execution Plan
———————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | DELETE STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | DELETE | EMP2 | | | |
| 2 | TABLE ACCESS FULL| EMP2 | 1 | 3 (0)| 00:00:01 |
——————————————————————-

First run:

Statistics
———————————————————-
4 recursive calls
15 db block gets
15 consistent gets
0 physical reads
4036 redo size

second run:
Statistics
———————————————————-
0 recursive calls
15 db block gets
7 consistent gets
0 physical reads
4036 redo size

So, as you can see, with delete there appears to be plenty of db block gets activity that is nothing to do with parsing.

I’m not going to analyse or investigate any further, I’ll wait to see what Jonathan says or I have more free time.

What I do know for sure is that I need to know more about the details of db block gets and my advice that it is internal processing that you can do little about may be very questionable advice indeed…

Follow

Get every new post delivered to your Inbox.

Join 156 other followers