12c db_block_checking and db_block_checksum measured overhead

      2,426 Comments on 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.
[code language=”sql”]
Begin
Delete full table
Insert into the table from a query
End
[/code]
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:
[code language=”sql”]
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;
/
[/code]
 
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…

[code language=”sql”]
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%
[/code]
 
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
 
 
 
 
 
 

     

2,426 thoughts on “12c db_block_checking and db_block_checksum measured overhead”

  99. Hairstyles

    I know this if off topic but I’m looking into starting my own blog and was curious what all is required to get set up? I’m assuming having a blog like yours would cost a pretty penny? I’m not very web smart so I’m not 100 sure. Any suggestions or advice would be greatly appreciated. Thanks

    Reply ↓
  106. Fashion Styles

    Today, I went to the beach front with my children. I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is entirely off topic but I had to tell someone!

    Reply ↓
  167. หวยฮานอย

    Amazing blog! Is your theme custom made or did you download it from somewhere?
    A theme like yours with a few simple tweeks would really
    make my blog stand out. Please let me know where you
    got your theme. Bless you

    Reply ↓
  205. Hairstyles

    One thing is that often one of the most popular incentives for using your card is a cash-back or perhaps rebate supply. Generally, you’ll have access to 1-5 back upon various purchases. Depending on the credit cards, you may get 1 back again on most acquisitions, and 5 again on expenses made going to convenience stores, filling stations, grocery stores in addition to ‘member merchants’.

    Reply ↓
  230. Fashion Styles

    I have really learned some new things by means of your blog site. One other thing I’d like to say is that newer computer os’s are likely to allow extra memory to be used, but they furthermore demand more ram simply to perform. If people’s computer can not handle additional memory and the newest software requires that memory space increase, it may be the time to shop for a new Personal computer. Thanks

    Reply ↓
  324. Latest Hairstyles

    Interesting blog! Is your theme custom made or did you download it from somewhere? A theme like yours with a few simple tweeks would really make my blog stand out. Please let me know where you got your design. Thanks

    Reply ↓