溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
通過案例學調優之--和 LOG BUFFER 相關的主要 Latch
4.1、和 LOG BUFFER 相關的主要 Latch
有: Latch:Redo Copy
Latch:Redo Allocation Latch
4.2 當一個進程在修改數據時候將會產生 Redo,這個 Redo 首先在 PGA 中保存。
然后進程需要 獲取Redo Copy Latch(這個Latch的個數由隱含參數_log_simultaneous_copies決定),當獲 得 Redo Copy Latch 后,進程接著獲取 Redo Allocation Latch 來分配 Redo Log Buffer 中的空間, 空間分配完成后,釋放 Redo Allocation Latch。然后進程把 PGA 里臨時存放的 Redo 信息復制 到 Redo Log Buffer,復制完成后,釋放 Redo Copy Latch。
4.3 邏輯架構如下:
案例分析：
測試redo中Latch的競爭
1、建立測試環境
15:08:51 SYS@ prod >select name ,bytes/1024/1024 from v$sgastat where rownum <6;
NAME BYTES/1024/1024
-------------------------- ---------------
fixed_sga 1.27443695
buffer_cache 60
log_buffer 6.0078125
kkj jobq wor .003913879
dpslut_kfdsg .000244141
建立一個最小的日志組
15:09:33 SYS@ prod >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、建立三張測試表
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 <100000;
Table created.
15:20:30 SCOTT@ prod >create table tb3 as select * from tb1 where rownum <100000;
Table created.
4、建立測試腳本
[oracle@RH6 ~]$ cat 22.sh
#!/bin/bash
export ORACLE_SID=prod
count=0
while [ $count -lt 1000 ]
do
sqlplus 'scott/tiger'<<EOF
update tb1 set object_id=1000 ;
rollback;
EOF
count=`expr $count + 1`
done
[oracle@RH6 ~]$ cat 33.sh
#!/bin/bash
export ORACLE_SID=prod
count=0
while [ $count -lt 1000 ]
do
sqlplus 'scott/tiger'<<EOF
update tb2 set object_id=1000 ;
rollback;
EOF
count=`expr $count + 1`
done
[oracle@RH6 ~]$ cat 44.sh
#!/bin/bash
export ORACLE_SID=prod
count=0
while [ $count -lt 1000 ]
do
sqlplus 'scott/tiger'<<EOF
update tb3 set object_id=1000 ;
rollback;
EOF
count=`expr $count + 1`
done
5、通過3個session，運行腳本
6、查看session event
15:22:08 SYS@ prod >select sid,username ,event from v$session where username='SCOTT';
SID USERNAME EVENT
---------- ------------------------------ ----------------------------------------------------------------
31 SCOTT log file switch (checkpoint incomplete)
45 SCOTT enq: TX - row lock contention
Elapsed: 00:00:00.00
15:22:14 SYS@ prod >/
SID USERNAME EVENT
---------- ------------------------------ ----------------------------------------------------------------
31 SCOTT log file switch completion
41 SCOTT enq: TX - row lock contention
44 SCOTT log file switch completion
45 SCOTT enq: TX - row lock contention
47 SCOTT log file switch completion
15:23:42 SYS@ prod >/
SID USERNAME EVENT
---------- ------------------------------ ----------------------------------------------------------------
31 SCOTT db file sequential read
41 SCOTT enq: TX - row lock contention
44 SCOTT latch: redo copy
45 SCOTT enq: TX - row lock contention
47 SCOTT latch: redo allocation
15:26:54 SYS@ prod >r
1* select sid,username ,event from v$session where username='SCOTT'
SID USERNAME EVENT
---------- ------------------------------ ----------------------------------------------------------------
31 SCOTT log file switch completion
41 SCOTT enq: TX - row lock contention
44 SCOTT log file switch completion
45 SCOTT enq: TX - row lock contention
47 SCOTT log file switch completion
7、查看redo latch競爭
15:25:11 SYS@ prod >select name,misses,sleeps,spin_gets,wait_time from v$latch
15:25:34 2 where name in ('redo copy','redo allocation');
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy 101 116 0 279828
redo allocation 48 50 0 54560
Elapsed: 00:00:00.02
15:25:53 SYS@ prod >/
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy 111 126 0 300388
redo allocation 50 52 0 56124
Elapsed: 00:00:00.01
15:26:08 SYS@ prod >/
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy 111 126 0 300388
redo allocation 50 52 0 56124
Elapsed: 00:00:00.00
15:26:12 SYS@ prod >/
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy 202 234 0 594703
redo allocation 75 79 0 83114
Elapsed: 00:00:00.00
15:27:58 SYS@ prod >/
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy 220 258 0 661577
redo allocation 81 85 0 103697
15:28:29 SYS@ prod >/
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy 346 400 1 1174583
redo allocation 146 150 0 189359可以看到，在系統中產生了大量的redo latch的爭用。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
