Two , Analysis process

about gc A brief description of , It's the case 1
When the request is initiated, it is found that the requested block is already in another instance , This is the time to pass GCS(LMS) The corresponding block is transferred to the instance through the private network 1, This process is clustered cache
fusion. The requested block is further divided into the current block and the CR.

The most common waiting events include gc buffer busy acquire,gc cr block busy. among gc buffer busy
acquire When session#1 Attempt to request access to the remote instance (remote instance)
buffer, But in the session#1 There has been another one on the same instance before session#2 The request accessed the same buffer, And it's not done , that session#1 wait for gc
buffer busy acquire.gc cr block busy Is an instance 1 And examples 2 Of buffer
cache All contain a certain block, A moment instance 1 This has been modified block And not submitted ; Example of this moment 2 Conversation on 1 Read this block Need to pass undo record
To construct CR block . And the change must be recorded in the instance during the construction 1 Of redo, What happens at this time is waiting gc cr block busy wait for .

 

Here we use two nodes for simulation

1, example 1 Open one on the top session Check the table QXY launch 300W Queries

2, example 1 The other one on the top opens another session Check the table QXY Also initiated 300W Queries

3, example 2 Open one on the top session Check the table QXY launch update operation

4, Statistical examples 1 Wait events for the above session .

 

----- The script to create the table is as follows

create table qxy (name varchar2(20), id number, birth date) tablespace USERS;

 

----- Amount of data inserted

insert into qxy select dbms_random.string('l',20), level lv, sysdate from dual
connect by level < 100;

 

------ Loop statement

declare

ct number;

begin

for i in 1..3000000 loop

select count(*) into ct from qxy;

end loop;

end;

/

------ Create a new table to record the waiting events before the session starts start.sql

define v_inst1=&1

define v_inst2=&3

define v_sid1=&2

define v_sid2=&4

drop table begin_table;

create table begin_table as select inst_id,sid,event,total_waits

from gv$session_event

where (inst_id=&v_inst1 and sid in (&v_sid1))

or (inst_id=&v_inst2 and sid in (&v_sid2))

order by inst_id,sid,event;

 

------ The waiting events generated between queries are counted by subtracting them before and after end.sql

define v_inst1=&1

define v_inst2=&3

define v_sid1=&2

define v_sid2=&4

drop table end_table;

create table end_table as select inst_id,sid,event,total_waits from
gv$session_event

where (inst_id=&v_inst1 and sid in (&v_sid1)) or (inst_id=&v_inst2

and sid in (&v_sid2)) order by inst_id,sid,event;

col event format a30

set linesize 120 pagesize 60

select ed.sid,ed.event,ed.total_waits - nvl(st.total_waits,0) diff_waits

from begin_table st,end_table ed

where st.inst_id(+)=ed.inst_id and st.sid(+)=ed.sid and

st.event(+)=ed.event order by event,sid;

 

---- Simple test 1

example 1 session 1

example 1 session 2

example 1 session 3 Query session statistics first

example 1 Of session1 and session 2 Execute the query statement at the same time , example 2 Of session implement update sentence

example 1 session1

example 1 session 2

example 2 session 1

 

example 1 Of session 3 Count the waiting events after the end of the query

Through the above test, we found that , If the block is quickly committed after another node updates the block ,gc cr block
busy Waiting events are not very high , It's all in the digits . Pay attention to the above update sentence , Although it is 30s Update once , But it was submitted immediately after the update , It's just sleep after that 30s.

 

----- Simple test 2 ( If after the update , wait for 30s What is the effect of submission )

example 1 Of session1 and session 2 Also execute the query statement at the same time , example 2
Of session implement update sentence ( But here it is update It's a stay 30s Then submit )

example 1 session 1

example 1 session 2

example 2 session 1

example 1 session3

You can see from the second test , If the block is accessed across instances , And the block has not been committed since it was modified , This is when another node is accessing the block ,gc The related waiting will rise in multiples . This is mainly because of examples 1 And examples 2 Of buffer
cache All contain a certain block,T1 Time instance 2 This has been modified block;T2 Time instance 1 Conversation on 1 Read this block Before modification CR copy, because CR
copy It needs to be applied undo record Can be constructed , also undo Access time is a single block read , So it will greatly affect the speed , This process produces a lot of gc wait for .

therefore gc cr block busy The process is as follows :

1) example 1 Of LMS To instance 1 Of LMS launch block Read request ;

2) example 2 Of buffer cache This already exists in it block The latest copy after modification C1', example 2 Of LMS Local buffer
cache In accordance with C1' Make a new copy C1'', here C1' and C1'' Is exactly the same ;

3) example 2 Of LMS from undo segment Found in undo record be used for applied reach C1'' upper , hold C1'' Roll back to the state before modification , Recorded as C1;

4) This is an example 2 Of buffer cache It contains C1',C1, among C1 yes C1' Content before modification .

5) utilize undo record take C1'' Roll back to C1 This process will produce redo Of , example 2 Of LMS Process notification Lgwr Progress redo write in online
redolog, Only after successful writing Go to the next step

6) example 2 On the Lgwr Notification instance 2 On the LMS redo Write complete , example 2 On the LMS take C1 Transfer to instance 1 Of LMS

7) example 1 Of LMS Returns the result to the instance 1 Conversation on 1 Of server process

------ Simple test 3 ( structure CR The block needs to be generated REDO)

example 2 session1

Execute a update, But not submitted

example 2 session 2

Statistics redo size, CR block

 

example 1 session 1

query qxy surface , Query instance 2 Modified this line of record , In this way, we can construct CR block .

example 2 session 2

Statistics again redo size, CR block

You can see CR blocks created,data blocks consistent reads - undo records applied,redo
entries,redo size Has changed .

 

------ test 4( Local node select Remote node cache How much is the overhead of uncommitted data blocks in )

1, Submit after modification of remote node , Two local nodes session Concurrent select Same table

example 2 session 1

example 1 session 3 Statistics of waiting events before query

example 1 session 1

example 1 session 2

example 1 session 3 Statistics of waiting events after query

 

2, The remote node will not submit after modification , Two local nodes session Concurrent select Same table

example 2 session 1 Do not submit

example 1 session 3 Statistics of waiting events before query

example 1 session 1

example 1 session 2

 

example 1 session 3 Count the waiting events generated between queries

You can see , From instance 2 Submit to the instance as soon as it is modified 2 No submission after modification , Two queries from 16s Up to 26 minute . The gap is huge .

It also shows that , Although only in the instance 2 A block was modified , But examples 1 Of session 1 and session
2 loop 100W second , Construct every time you loop CR, in other words CR Block sharing .

 

example 2 Of TOP situation

 

You can see , example 2 CPU Usage of , LMS Used 23%, lgwr Used 21%.LGWR The structure is also verified from the reverse side CR You need to write when you block redo.

 

summary :

gc Related waiting is very common in our work , You can see through the above test , When modifying and querying the same block , If it is not used properly, there will be a lot of abnormal waiting , It has a serious impact on database performance

Technology
©2019-2020 Toolsou All rights reserved,
SQL Server Database Glossary CSS Animation effect dedecms Website is hacked How to solve hijacking to other websites Count the number of letters (java Language implementation )Java Basics ( Three ) String In depth analysis The difference between static method and non static method And storage location Django Personal blog building tutorial --- Time classified archiving Keras Save and load model (JSON+HDF5)hive Summary of processing methods for a large number of small files Website mobile phone number capture method