What is the performance degradation during online backups in Oracle 8i and SQL Server 2000 in an OLTP environment?
I can only answer about Oracle 8i since I do not have too much experience with SQL Server. Hopefully, your question can be posed to a SQL Server expert.
When you do an online, or hot, backup in an Oracle database, then you tell the database which tablespaces to place in backup mode. This is done with the ALTER TABLESPACE BEGIN BACKUP command. As soon as you issue this command, Oracle handles updates to that tablespace a little differently. Instead of logging just change vectors in the online redo logs, Oracle logs the entire changed data block in the online redo log. So instead of just writing a few bytes for every change one entire block is written. The size of this block is equal to your DB_BLOCK_SIZE parameter.
If there are lots of changes, then more and more information is written to the online redo logs. This also means that more and more information needs to be archived when that redo log gets full and switches to the next available redo log.
It is the writing to the online redo logs and archiving the full redo logs which can take extra time when hot backups are being performed. This is why your hot backups should be scheduled for periods of lesser activity.
If your system is experiencing a high volume of updates during your hot backups, then you may get the "checkpoint not complete" message in your Alert log. This can be your performance killer. If you are not seeing these messages during your hot backups, then there is negligable performance degradation.
So schedule your backups during a period of lesser activity to minimize your impact!
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.