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.