Saturday, November 6, 2010

What happens if db_recovery_file_dest_size too smaller to cater for db_flashback_retention_target?

Assumation here is the archivelog is not stored in the flash_recovery_area.

if the db_recovery_file_dest_size is too smaller, Oracle does not hang, it will purge old flashback logs to ensure the transaction can continue to run.
But the performance will be degraded due to oracle keeping to housekeep old flashback logs.

Test Envrionment: Oracle 10.2.0.4 on Linux x86.
Test script: (to insert 4029000 rows)
[10gr2@rh5 adhoc]$ cat perform_flashback_test.sh
sqlplus donghua/donghua <select sysdate from dual;
declare
cursor c is select object_id,owner,object_name,object_type from dba_objects;
begin
for i in 1..100 loop
for c1 in c loop
insert into fbtable values(c1.object_id,c1.owner,c1.object_name,c1.object_type);
commit;
end loop;
end loop;
end;
/
select sysdate from dual;
exit
EOD



Test 1:
DB_RECOVERY_FILE_DEST_SIZE=10GB.
The script toke 00:17:34 to complete.
3,949 transactions per seconds.

Test 2:
DB_RECOVERY_FILE_DEST_SIZE=100MB.
The script toke 00:24:29 to complete.
2,712 transactions per seconds.

Test 3:
FLASHBACK DATABSE OFF
The script toke 00:11:44 to complete.