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
