12c db_block_checking and db_block_checksum measured overhead

Hi There,

 

I went to a customer today for a performance issue between test and production environment.

Basically, a specific process was 5 times faster in test than in production.

 

Production is running under ODA with 8 cores on each nodes in RAC mode and TEST is running in single instance with NFS storage and caged to one CPU.

The long part of the process was quiet strange but it was something like that.

Begin

Delete full table

Insert into the table from a query

End

The customer table has 600’000 rows and one primary key with few columns on 45’000 blocks of 8K

Here is a small test case I have done to present that:

Create Table tsttmp as select * from sales;
create sequence seqtmp;
alter table tsttmp add pk number default seqtmp.nextval;

Create table mytest as select * from tsttmp;
Create unique index indmytest on mytest (pk);


begin

delete from mytest;
insert into mytest select * from tsttmp;

commit;
end;
/

 

I have run both cases in TEST and in PROD and i got

1 minute in test

6 minutes in prod!! What the …

So I start checking parameters … and guess what? In PROD db_block_checking and db_block_checksum were set to FULL!

I changed to default value (db_block_checking =false and db_block_checksum=typilcal) and then PROD was faster than TEST which is the expected behaviour!

 

I have redo the test at home on my lab and here are the results.

Remember that Oracle documentation says that:

  • db_block_checksum=FULL overhead is between 1 and 5%
  • db_block_checking=FULL overhead is between 1 and 10% and could be worse in some cases…
set timing on

alter system set db_block_checking = false;
alter system set db_block_checksum = false;

begin
delete from mytest;
insert into mytest select * from tabtmp;
commit;
end;
/
Elapsed: 00:00:52.599

alter system set db_block_checking = false;
alter system set db_block_checksum = typical;

begin
delete from mytest;
insert into mytest select * from tabtmp;
commit;
end;
/
Elapsed: 00:00:55.051 -> + 4.66%


alter system set db_block_checking = false;
alter system set db_block_checksum = full;

begin
delete from mytest;
insert into mytest select * from tabtmp;
commit;
end;
/
Elapsed: 00:00:58.546 -> + 6.34%


alter system set db_block_checking = low;
alter system set db_block_checksum = full;

begin
delete from mytest;
insert into mytest select * from tabtmp;
commit;
end;
/
Elapsed: 00:00:59.954 -> + 2.40%


alter system set db_block_checking = medium;
alter system set db_block_checksum = full;

begin
delete from mytest;
insert into mytest select * from tabtmp;
commit;
end;
/
Elapsed: 00:01:35.032 -> + 58.50%


alter system set db_block_checking = full;
alter system set db_block_checksum = full;

begin
delete from mytest;
insert into mytest select * from tabtmp;
commit;
end;
/
Elapsed: 00:03:58.062 -> + 150.50%

 

Degradation between

db_block_checking = false;
db_block_checksum = typical;

And

db_block_checking = full;
db_block_checksum = full;

Is 332%!!

 

And guess what?

db_block_checking = full;
db_block_checksum = full;

Is the default value in ODA and Exadata configurations !

You might have some cool performance improvement by setting those two parameters to the appropriate value:-)

 

Cheers

jko

 

 

 

 

 

 

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

1 thought on “12c db_block_checking and db_block_checksum measured overhead

  1. Cory Costa

    FULL/FULL is NOT the default value in ODA and Exadata configurations? That last statement is not correct, the Exadata with 12c defaults are set to FALSE and TYPICAL, and you should test with combination: db_block_checksum=TYPICAL, and db_block_checking=FULL as another good option, in case the block checksum is most expensive part of the increased time, rather than the block checking parameter.
    SQL> show parameter db_block

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_block_checking string FALSE
    db_block_checksum string TYPICAL

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *