Shulou(Shulou.com)06/01 Report--

Tuning it through case study-- the main Latch related to LOG BUFFER

4.1. major Latch related to LOG BUFFER

There are: Latch:Redo Copy

Latch:Redo Allocation Latch

4.2 when a process modifies data, it will generate a Redo, and this Redo is first saved in PGA.

Then the process needs to get the Redo Copy Latch (the number of this Latch is determined by the implicit parameter _ log_simultaneous_copies). When it gets the Redo Copy Latch, the process then obtains the Redo Allocation Latch to allocate the space in the Redo Log Buffer, and releases the Redo Allocation Latch when the space allocation is complete. Then the process copies the Redo information temporarily stored in the PGA to the Redo Log Buffer, and when the copy is complete, release the Redo Copy Latch.

4.3 the logical structure is as follows:

Case study:

Test Latch competition in redo

1. Set up the test environment 15:08:51 SYS@ prod > select name, bytes/1024/1024 from v$sgastat where rownum select group#,sequence#,status,bytes/1024/1024 from v$log GROUP# SEQUENCE# STATUS BYTES/1024/1024- 4 108 CURRENT 4 5 106 INACTIVE 4 2 、 Set up three test tables 15:11:59 SCOTT@ prod > create table tb1 as select * from user_objects Table created.15:13:48 SCOTT@ prod > select count (*) from tb1 COUNT (*)-376832 15:19:16 SCOTT@ prod > create table tb2 as select * from tb1 where rownum create table tb3 as select * from tb1 where rownum / NAME MISSES SLEEPS SPIN_GETS WAIT_TIME-- -redo copy 111126 0 300388redo allocation 50 52 20 56124Elapsed: 00 NAME MISSES SLEEPS SPIN_GETS WAIT_TIME- 00 00 12 SYS@ prod > / NAME MISSES SLEEPS SPIN_GETS WAIT_TIME- -redo copy 202 234 0 594703redo allocation 75 79 0 83114Elapsed: 00 SYS@ prod 00.0015 7 7 7 0 SYS@ prod > / NAME MISSES SLEEPS SPIN _ GETS WAIT_TIME-----redo copy 220 258 0 661577redo allocation 81 85 0 10369715 Jiang 28 SYS@ prod > / NAME MISSES SLEEPS SPIN_GETS WAIT_TIME -- redo copy 346400 1 1174583redo allocation 146150 0 189359

As you can see, there is a lot of redo latch contention in the system.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.