Tuning log switching (tuning log switches)

Tuning log switching (tuning log switches)



Log switching: The LGWR process stops writing logs to the current log file, closes the log file, opens a new log file, and writes the data from the log cache to the new log file.



Log switching can either command alter system SWITCH logfile or alter system ARCHIVE log for manual execution, 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 it has been used to the last block of data in the current log file.





The sequence of steps to do when the log is switched:



1. Perform a control file transaction to select the next log file to use and clear the control file entry. Typically, a log file with the minimum log sequence number is selected as the next use, and in this step, if necessary, the log switch waits for the DBWR process to checkpoint the log switch of the log file before the ARCN process completes the archive.

2. Use the redo copy latches and redo generation latches two latch to prevent the redo log from being generated in the log cache (marking the log cache state) and writing the data in the log cache to the log file (disk). If possible in a parallel manner, the SCN number of the last record in the file is also written to the header block. The LGWR process closes this log file after these write operations are completed.

3. Enhance the SCN value perform a second control file transaction to mark the log file as current, mark the previous log file as active, and once the DBWR process completes the checkpoint of the log switch, The status of this file (the previous log file) is marked as inactive. If the database is running in archive mode, the LGWR process adds the log file (the previous log file) to the archive connection form by controlling the entry section of the log file in the file, and if the automatic archive is started, LGWR activates the ARCN daemon to archive the log file. If all of the ARCN processes currently in use are busy, LGWR activates a new ARCN process, ARCN the number of processes by parameter log_archive_max_processes.

4, the final step, the LGWR process opens all members of the new log file group and writes the new log sequence number and the base SCN to the header block. The log cache status is then changed to generate the log.



Log switching may take a long time, often in seconds, and the log switching process has a greater potential for tuning. Compared to continuous slow performance, the users of OLTP systems are generally more difficult to endure the poor performance of the database, so it is very important to reduce the log switching frequency and reduce the switching interval time.



In terms of system performance, the main performance of the log switch conflict is that log file switch completion wait for the event. If log switching performance is poor, another wait event log buffer Space Wait will also be immediately generated. For log switching tuning is to first tune the log file switch completion wait for the event then log buffer space wait.



Use large log files: In order to minimize log switching frequency, you can increase the size of the log file (as large as possible, the most easily archived), but also control 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 from accessing the disk at the same time. Do not use small log files to save disk space, because the saved space will no longer be used (the online log file size is determined will not grow again, so will not use the space you have left)

Adding an online log file can increase the interval between archives. 、

On the other hand, when using large online log files, you can reduce resource usage by tuning the archive.



Keep the log file turned on: Adjusting the speed of the log switch the most likely place lies in the opening of new log file members, which are opened with an operating system directive open (). If there are other processes in the same file that already have an open file descriptor then the operating system calls this command to open the new log file quickly, mainly because some information about the file is placed in the kernel memory of the system.

The following script is used to put the log file information into kernel memory when the instance is started, which can increase the speed of log switching to some extent. This is a shell script, used in Unix operating systems, that is implemented on a daily basis with Cron: 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

Oraenv_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

Todo

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 if your shells only supports input redirection

# for single digit file descriptors.

#

# if ["$FD"-eq 10]

# Then

# Sleep 86460 &

# fd=3

# fi

Done





To adjust control file transactions: Each log switch contains two control file transactions that control file transactions are executed under the protection of the CF queue lock, so there is no further need to control the "write" operation of the control file, but in order to control the recoverability of instances or systems failure during file transactions, It is still necessary to have some control.



Control the recoverability of the file, the recovery structure is first written to the second block of control files, and the target block in the Write control file (which controls the blocks to be written by the file office) waits for its operation (the recovery structure write), so each control file transaction contains at least two write I/O operations waiting.



If the active control file used is multiple, I/O operations are implemented in a sequential fashion. Therefore, to improve the performance of control file transactions is to reduce the number of active control files. In most cases, you can use only one active control file, and then use the hardware mirroring and the command alter SYSTEM backup Controlfile to trace to back up the control files and protect the control files.



The following script (BACKUP_CONTROLFILE.SQL) provides a backup scenario that is implemented primarily under UNIX and puts the backup of the control files into the database creation directory:

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 current

--session. It can be used interactively, or from the 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

--Two incorrect versions should is commented out or deleted.

--

-------------------------------------------------------------------------------



Column Trace_file_name New_value trace_name

Column trace_file_zipped new_value trace_zipped noprint



Select

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 (RTrim (i.instance, Chr (0)) | | _ora_ ' | | p.spid| | '. TRC ' Trace_file_name,

d.value| | ' /'|| Lower (RTrim (i.instance, 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 then moves the trace

--File into the APT create directory.

--

--The Sql*plus connection is closed, because its trace file 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