Oracle Database performance degradation caused by redo log size

Source: Internet
Author: User

Today, we are doing an experiment with records/s inserted into the Oracle database. Both servers locally copy a million record table to another table with the same structure. One server took 12 s and the other one took nearly 2 minutes. Generate an AWR report during insertion in Em. With the help of eygle, it is found that the database performance is degraded due to the size of redo logs.
# The following statement can be used to find that the database is slow because log_sync
SQL> select * from V $ session_wait;
# View log status
SQL> select * from V $ log;
SQL> select Member from V $ logfile;
# Create a new log Group
SQL> alter database add logfile group 4'/oradata/ora108/redo04.log '2048 m;
SQL> alter database add logfile group 5'/oradata/ora108/redo05.log' 2048 m;
SQL> alter database add logfile group 6'/oradata/ora108/redo06.log '2048 m;
# Switch logs so that 1, 2, and 3 are inactive.
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
# Delete group 1, 2, and 3
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

TIPS: About UNDO log

when you modify data in the buffer cache, Oracle does not immediately write the modified data to the data file, because the efficiency will be very low, so far, the busiest part of the computer system is the disk I/O operations. Oracle aims to reduce the number of I/O operations. after a certain amount of modified data is reached, you can efficiently write data in batches.
most traditional databases (including Oracle) follow the no-force-at-commit policy when processing data changes. That is to say, it is not mandatory to write when submitting. Oracle introduces the redo mechanism to ensure that data can be restored in case of database faults (such as power outages, write random and scattered data blocks by sequential log entries. This delay improves the performance of writing data in batches.
similar to redo log buffer, redo log file is also used cyclically. Oracle allows at least two log groups. By default, three log groups are created during database creation.
SQL> select group #, members, status from V $ log;
group # members status
---- ------
1 inactive
2 1 Current
3 1 inactive
when a log file is full, switch to another log file, which is called Log switch. Log switch triggers a checkpoint, prompting the dbwr process to write the change data protected by full log files back to the database. Before the checkpoint is completed, log files cannot be reused.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.