调优5（SGA其他缓存区调整）

首页 > 代码库 > 调优5（SGA其他缓存区调整）

调优5（SGA其他缓存区调整）

2024-07-04 07:40:05 224人阅读

第五章 SGA其他缓存区调整

一、redo log buffer

1、redo log 的功能
1）Sever 进程在buffer cache修改数据块后，Oracle提倡‘先记后写’，对修改的数据块的改变生成log entries（日志条目),将日志条目按顺序写入log buffer；而对于脏块
先链接到检查点队列，等待dbwr 进程写入到datafile
2）进程会及时的将其按顺序写入到redo log files。（日志条目的数据块大小一般和操作系统数据块大小一致，在Unix 和linux 下一般为512Bytes）
3）lgwr 在以下条件会写log entries：
A)、commit
B)、log buffer 空间1/3
C)、日志到1M时
D)、每隔3S
E)、在dbwr 写脏块前
--redo 日志及时的写入到redo log files，可以在instance 或 media recover 时，通过redo log 来对数据库进行recovery

2、redo log buffer 大小

09:59:26 SYS@ test1>select name ,bytes/1024/1024 from v$sgainfo;

NAME BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size 1.16233444
Redo Buffers 2.8359375
----由于redo log buffer的空间循环使用，可以不用设太大的尺寸，一般默认值即可。

3、监控log buffer

10:07:36 SYS@ test1>select sid,event,seconds_in_wait,state
10:07:59 2 from v$session_wait
10:08:05 3 where event=‘log buffer space‘;

no rows selected
------‘log buffer space’ 事件，指的是当server 进程写日志条目到log buffer 时，没有可用的空间，而导致等待

10:10:58 SCOTT@ test1>begin
10:11:28 2 for i in 1..1000000
10:11:38 3 loop
10:11:41 4 execute immediate ‘insert into t1 values (:num)‘ using i;
10:11:46 5 end loop;
10:11:50 6 end;
10:11:51 7 /

PL/SQL procedure successfully completed.

10:13:10 SYS@ test1>r
1 select sid,event,seconds_in_wait,state
2 from v$session_wait
3* where event=‘log buffer space‘;
10:12:04 SYS@ test1>/

SID EVENT SECONDS_IN_WAIT STATE
---------- ---------------------------------------------------------------- --------------- -------------------
20 log buffer space 9 WAITED KNOWN TIME

Elapsed: 00:00:00.01
10:12:24 SYS@ test1>/

SID EVENT SECONDS_IN_WAIT STATE
---------- ---------------------------------------------------------------- --------------- -------------------
20 log buffer space 22 WAITED KNOWN TIME

Elapsed: 00:00:00.00
-----事务量增加，导致了“log buffer space” 的等待事件

10:17:55 SYS@ test1>select username,sid from v$session where username is not null;

USERNAME SID
------------------------------ ----------
SYS 39
SCOTT 28

Elapsed: 00:00:00.02
10:18:07 SYS@ test1>select SID,EVENT,TOTAL_WAITS WAIT_CLASS from v$session_event
10:18:29 2 where sid in(39,28) and event in (‘log file sync‘,‘log file parallel write‘);

SID EVENT WAIT_CLASS
---------- ---------------------------------------------------------------- ----------
39 log file sync 5

Elapsed: 00:00:00.03
10:18:30 SYS@ test1>

log file sync等待事件
当用户发出提交或回滚语句时会触发LGWR将重做记录写入联机日志文件，这种触发LGWR的方式叫做同步写（sync writes）触发，
而其他剩下的触发LGWR的方式叫做后台写（background writes）。log file sync等待事件只与sync writes有关，
而log file parallel write等待事件只与background writes有关。

-----调整（增加日志组或日志组的大小，或者增加log_buffer 的大小）

10:35:42 SYS@ test1>alter system set log_buffer =10240000 scope=spfile;

System altered.

10:38:53 SCOTT@ test1>
begin
10:39:03 SCOTT@ test1>for i in 1..1000000
loop
10:39:03 3 execute immediate ‘insert into t1 values (:num)‘ using i;
10:39:03 5 end loop;
10:39:03 6 end;
10:39:03 7 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:39.52
10:40:44 SCOTT@ test1>

10:38:17 SYS@ test1>select sid,event,seconds_in_wait,state
10:39:14 2 from v$session_wait
10:39:14 3 where event=‘log buffer space‘;

no rows selected

Elapsed: 00:00:00.11
10:39:15 SYS@ test1>/

no rows selected

Elapsed: 00:00:00.04
10:39:42 SYS@ test1>/

no rows selected

Elapsed: 00:00:00.05
10:39:51 SYS@ test1>/

no rows selected

Elapsed: 00:00:00.09

10:43:18 SYS@ test1>select SID,EVENT,TOTAL_WAITS WAIT_CLASS from v$session_event
10:43:19 2 where sid in(39,22) and event in (‘log file sync‘,‘log file parallel write‘);

no rows selected

-------调整了redo buffer 空间后，log buffer space 等待事件被消除

-------查看日志条目的信息

10:20:30 SYS@ test1>select name,value from v$sysstat
10:20:51 2 where name in (‘redo buffer allocation retries‘,‘redo entries‘);

NAME VALUE
---------------------------------------------------------------- ----------
redo entries 1125660
redo buffer allocation retries 9

Elapsed: 00:00:00.01

------redo entries 生成的redo 日志条目的个数
------redo buffer allocation retries server 进程在写入日志条目时，没有足够的空间，重试的次数

--一般要求，重试次数应该为0，两者相比的比率小于1%

4、减少redo 的生成

1)、在做sql load 时采用direct 导入和nologging。
2)、在执行sql 语句时，使用nologging（但dml 操作无效）

------dml

0:32:36 SCOTT@ test1>update emp1 nologging set sal=sal+2000 where deptno=10;

12 rows updated.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2879231406

-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | EMP1 |
|* 2 | TABLE ACCESS FULL| EMP1 |
-----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("DEPTNO"=10)

Note
-----
- rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
1 recursive calls
6 db block gets
23 consistent gets
0 physical reads
3036 redo size
681 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed

10:32:44 SCOTT@ test1>

------在nologging 下仍有日志生成

------通过视图监控

10:36:58 SCOTT@ test1>select a.name,b.value from v$statname a,v$mystat b
10:37:05 2 where a.STATISTIC#=b.STATISTIC# and a.name like ‘%redo size%‘;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 269785556

10:37:11 SCOTT@ test1>update emp1 nologging set sal=sal+2000 where deptno=10;

12 rows updated.

10:37:31 SCOTT@ test1>select a.name,b.value from v$statname a,v$mystat b
10:37:45 2 where a.STATISTIC#=b.STATISTIC# and a.name like ‘%redo size%‘;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 269790652

------DDL

-------使用nologging 创建索引

10:40:32 SCOTT@ test1>create index t1_id_ind on t1(id) nologging;

Index created.

Elapsed: 00:00:04.53
10:41:12 SCOTT@ test1>select a.name,b.value from v$statname a,v$mystat b
10:41:25 2 where a.STATISTIC#=b.STATISTIC# and a.name like ‘%redo size%‘;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 269910748

Elapsed: 00:00:00.00
10:41:33 SCOTT@ test1>

------正常创建索引

10:20:30 SYS@ test1>select name,value from v$sysstat
10:20:51 2 where name in (‘redo buffer allocation retries‘,‘redo entries‘);

NAME VALUE
---------------------------------------------------------------- ----------
redo entries 1125660
redo buffer allocation retries 9

Elapsed: 00:00:00.01
10:20:56 SYS@ test1>create table t1 as select * from scott.t1;

Table created.

Elapsed: 00:00:02.48
10:43:04 SYS@ test1>select a.name,b.value from v$statname a,v$mystat b
10:43:18 2 where a.STATISTIC#=b.STATISTIC# and a.name like ‘%redo size%‘;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 13852204

Elapsed: 00:00:00.02
10:43:29 SYS@ test1>create index t1_id_index on t1(id);

Index created.

Elapsed: 00:00:17.44
10:44:08 SYS@ test1>select a.name,b.value from v$statname a,v$mystat b
10:44:20 2 where a.STATISTIC#=b.STATISTIC# and a.name like ‘%redo size%‘;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 34085060

Elapsed: 00:00:00.01
10:44:24 SYS@ test1>

15:09:25 SYS@ test1 > select sid,event,seconds_in_wait,state
15:09:58 2 from v$session_wait
15:09:58 3 where event like ‘log%‘;

no rows selected

Elapsed: 00:00:00.00
15:09:59 SYS@ test1 >/

SID EVENT SECONDS_IN_WAIT STATE
---------- ---------------------------------------------------------------- --------------- -------------------
11 log file parallel write 0 WAITING

Elapsed: 00:00:00.00
15:10:02 SYS@ test1 >/

no rows selected

Elapsed: 00:00:00.00
15:10:03 SYS@ test1 >/

SID EVENT SECONDS_IN_WAIT STATE
---------- ---------------------------------------------------------------- --------------- -------------------
11 log file parallel write 0 WAITING
36 log buffer space 1 WAITING
37 log buffer space 1 WAITING

Elapsed: 00:00:00.00
15:10:04 SYS@ test1 >

解决方法：

可以调整log buffer 空间大小（不推荐），主要是调整lgwr的写出的速度

1、对于大的事务量，尽量采用分批提交
2、将redo 日志存放到磁盘I/O 最快的磁盘上（比如采用RAID10方式）
3、增加日志组，增加日志组的大小
4、如果是归档模式，可再启用多个归档进程

二、java pool

在oracle 10g 采用内存自动管理，可动态分配java pool的大小

三、启动异步读写

异步：当dbwr 写时，后台进程可以继续运行
同步：当dbwr 写时，后台进程必须处于等待状态（串行方式）
10:44:24 SYS@ test1>show parameter syn

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE
tape_asynch_io boolean TRUE
10:56:49 SYS@ test1>

------可以启用多个dbwr 进程（dbw0-dbw9）
10:57:37 SYS@ test1>show parameter writer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes integer 1
10:57:56 SYS@ test1>

----但和cpu个数有关，如果是多个cpu ，可以启动多个dbwr

db_writer_processes integer 1
10:57:56 SYS@ test1>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 1

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 调优5（SGA其他缓存区调整）

调优5（SGA其他缓存区调整）

看完仍有疑问？有类似问题直接问程序猿