Tune log switches (TUNING LOG SWITCHES)
Log Switching: LGWR Process Stop Write Logs to the current log file, turn off log files, open new log files and write data from log caches to new log files.
Log Switch You can command the Alter System Switch logfile or ALTER System Archive log to perform it manually, or it can be automatically performed by setting parameters log_archive_start. The general reason is that a process cannot write the generated redo log from the cache to the current log file because the last data block of the current log file has been used.
The sequence of procedures do when log switching:
1. Perform a control file transaction to select the next log file to use and clear the control file entry. Generally, the log file currently has the least log sequence number is used as the next use. On this step, if necessary, log switch will wait for the checkpoint of log switching of the DBWR process completely a log file, waiting for the ARCN process to complete Archive.
2. Use the Redo Copy Latches and Redo Generation Latches to block the redo log from generating (tag log cache status) in the log cache and write the data in the log cache to the log file (disk). If possible in parallel, the SCN number in the file will be written in the header block at the same time. After these "write" operations are completed, the LGWR process closes this log file.
3. Increase the SCN value to execute the second control file transaction to mark the log file as CURRENT, mark the previous log file as Active, once the DBWR process completes the checkpoint of log switching, this file status (previous log file) Will be marked as inactive. If the database runs in archive mode, the LGWR process adds this log file (previous log file) to the archive connection order through the entry segment of the log file in the file, if the automatic archive is activated, the LGWR will activate the ARCN background process will This log file is archived. If all the ARCN processes currently used are busy, the LGWR activates a new ARCN process, the ARCN process number is used by the parameter log_archive_max_processes.
4, the last step, the LGWR process opens all members of the new log file group and writes the new log serial number and the bottom SCN to the header block. Then change the log cache state to generate a log.
Log switching may take a long time, often in seconds, log switching process has a large tuning potential. Compared to continuous slow performance, users of OLTP systems are generally more difficult to endure the interruption of the database, so that the log switching frequency is reduced, and the switching interval is very important.
In terms of system performance, the main manifestation of log switching conflicts is on the Log File Switch Completion waiting for events. If the log switching performance is poor, another wait for the Log Buffer Space will be given immediately. Switching to logs is to force the log file switch completion waiting event and wait for the log buffer space.
Using big log files: In order to minimize log switching frequency, you can increase the size of the log file (as big as possible, easier archive), but also control the checkpoint (such as setting parameters fast_start_mttr_target) to make the database recovery performance optimal.
Each online log file should be placed on a proprietary disk to prevent the ARCN process simultaneously access the disk. Do not use small log files to save disk space, because the space saved will not be used again (the online log file size is determined, it will not grow again, so these space you have retained) increases online The log file will increase the interval of archiving. ,
On the other hand, when you use a large online log file, you should use the tuning archive to reduce the use of resources.
Keeping the log file is open status: Adjusting the maximum tuning of the log switching is the opening process of the new log file member, open the new log file is the instruction Open () of the operating system. If there are other processes that have already opened a descriptor in the same file, the operating system calls this command to open the new log file is very fast, the main reason is that some information about the file is in the system. Nuclear memory.
Below this script is used to place the information of the log file in the kernel memory when the instance is started, and the speed of the log switch can be improved to a certain extent. This is the shell script. For the UNIX operating system, use cron to regularly implement: HOLD_LOGS_OPEN.SH
######################################################################################################################################################################################################################################################################################################## ###########################################
#
# Synopsis: Hold_logs_open.sh instance
# Purpose: To Hold The Log Files Open to Accelerate Log Switches
#
# Copyright: (c) ixora pty Ltd
# Author: Steve Adams with Acknowledgements to Chris Bunting
#
######################################################################################################################################################################################################################################################################################################## ###########################################
IF [$ # -ne 1]
THEN
Echo "USAGE: HOLD_LOGS_OPEN Instance"> & 2
EXIT 1
Fi
Instance = $ 1
Oracle_sid = $ 1
Oracarv_ask = no
Oraenv
FD = 3
echo '
SET PAGES 0 Feedback Off
SELECT MEMBER from V $ logfile;
PROMPT end-of-files
"
SQLPlus -s INTERNAL |
While Read logfile
DO
IF [$ logfile = end-of-files]
THEN
IF [$ fd "-gt 3]
THEN
Sleep 86460 &
Fi
EXIT 0
Fi
Eval "EXEC $ fd <$ logfile"
FD = `expr $ fd 1`
#
# UNCOMMENT THESE LINES INPUT REDIRECTION
# For single Digit File Descriptors.
#
# IF ["$ fd" -eq 10]
# THEN
# SLEEP 86460 &
# Fd = 3
# Fi
DONE
Adjust the control file transaction: Each log switch contains two control file transactions, the control file transaction is executed under the protection of the CF queue lock, so there is no further necessary to control the "write" operation of the control file, but in order to The instance of the control file transaction or the recoverableity of the system Failure is still necessary. Control file recoverability, first write recovery structures to the second data block of the control file, when the target block in the write control file (the block to be written) is waiting to complete it (recovery structure write operation), Therefore, each control file transaction contains at least two write I / O operations.
If the activated control file used is multiple, the I / O operation is implemented in a serial manner. Therefore, it is necessary to improve the performance of the control file transaction to reduce the number of control files that are activated. In most cases, only one activated control file is used, and then backed up with hardware mirroring and using the command ALTER System Backup ControlFile to TRACE to back up the control file and protection control file.
The following script (backup_controlfile.sql) provides a backup scheme, mainly in UNIX, putting a backup of the control file into the creation directory of the database:
TRACE_FILE_NAME.SQL (get trace file name)
-------------------------------------------------- -----------------------------
-
- Script: trace_file_name.sql
- Purpose: To Get The Name of The Current TRACE FILE
-
- Copyright: (c) ixora pty Ltd
- Author: Steve Adams
-
- synopsis: @trace_file_name
-
- OR
-
- set termout off
- @trace_file_name
- set Termout on
- ... & TRACE_NAME ...
-
- Description: this script gets the name of the trace file for the capital
- session. It can be used interactively, or from other scripts.
- The name is saved in The SQL * Plus define & trace_name.
-
- There Are Three Versions of The Query Below, Because The TRACE
- Files Are Named Differently Depending on The Platform. The Platform. The
- TWO INCORRECT VERSIONS SHOULD BE Comment Out or deled.
-
-------------------------------------------------- -----------------------------
COLUMN TRACE_FILE_NAME NEW_VALUE TRACE_NAME
Column TRACE_FILE_ZIPPED New_VALUE TRACE_ZIPPED NOPRINTSELECT
D.Value || '/ ora_' || p.spid || '.trc' trace_file_name,
D.Value || '/ Ora_' || P.SPID || '.trc.gz' trace_file_zipped
From
(SELECT)
p.spid
From
SYS.V_ $ MyStat M,
Sys.v_ $ session s,
SYS.V_ $ Process P
WHERE
m.statistic # = 1 and
S.SID = M.SID AND
P.Addr = S.paddr
P,
(SELECT)
Value
From
SYS.V_ $ Parameter
WHERE
Name = 'user_dump_dest'
) D
/
SELECT
D.Value || '/' || Lower (instance, chr (0))) || '_ora_' || p.spid || '.trc' trace_file_name,
D.Value || '/' || Lower (ionstance, chr (0)) || '_ora_' || p.spid || '.trc.gz' trace_file_zipped
From
(SELECT)
p.spid
From
SYS.V_ $ MyStat M,
Sys.v_ $ session s,
SYS.V_ $ Process P
WHERE
m.statistic # = 1 and
S.SID = M.SID AND
P.Addr = S.paddr
P,
(SELECT)
T.instance
From
Sys.v_ $ thread t,
SYS.V_ $ Parameter V
WHERE
v.name = 'thread' and
(
v.value = 0 or
T.Thread # = to_number (v.value)
)
i,
(SELECT)
Value
From
SYS.V_ $ Parameter
WHERE
Name = 'user_dump_dest'
) D
/
SELECT
D.Value || '/ Ora' || LPAD (P.SPID, 5, '0') || '.trc' trace_file_name,
D.Value || '/ Ora' || LPAD (P.SPID, 5, '0') || '_trc.gz' trace_file_zipped
From
(SELECT)
p.spid
From
SYS.V_ $ MyStat M,
Sys.v_ $ session s,
SYS.V_ $ Process P
WHERE
m.statistic # = 1 and
S.SID = M.SID AND
P.Addr = S.paddr
P,
(SELECT)
Value
From
SYS.V_ $ Parameter
WHERE
Name = 'user_dump_dest'
) D
/
Clear columns
Backup_controlfile.sql
-------------------------------------------------- -----------------------------
- Script: backup_controlfile.sql - purpose: To save a create controlfile statement
-
- Copyright: (c) ixora pty Ltd
- Author: Steve Adams
-
- Description: This Script Uses The Backup ControlFile to TRACE Command To
- Save a create ControlFile Statement, and The Moves the TRACE
- File Into The Apt Create Directory.
-
- The SQL * Plus Connection IS Closed, Because ITS TRACE HAS
- Been Moved.
-------------------------------------------------- -----------------------------
Alter Database Backup ControlFile to TRACE
/
Set Termout Off
@Trace_file_name
Set Termout on
Disconnect
Host MV & TRACE_NAME $ CREATE / CREATE_CONTROLFILE.SQL
exit
from
www.ixor.com.au
