甲骨论21课 Oracle日志原理剖析

常见数据库：Oracle、SQLserver、MySQL、DB2。Oracle优化目标：数据一致性、高性能，日志保障了数据一致性。日志严格按时间记录buffer_cache中buffer的改变过程。

日志记录内容：数据块地址、修改时间、修改操作（增加数据、删除数据）。修改数据就是先删除后增加。大量的日志会产生性能问题，与等待事件有关。

实际的日志产生过程：serverprocess修改buffer_cache中的数据，同时产生日志写入会话对应的PGA。当日志积累到一定时时，从PGA写入log_buffer。logger_buffer由后台进程LGwriter 写入redoLogBuffer。

涉及到数据一致性的问题：

l  如何确保已经提交的事务不会丢失

l  日志优先写入Write-Ahead-Log

l  LGWR绕过OS缓存直接定稿磁盘，但是绕不过存储的写缓存。

甲骨论22课Oracle日志核心意义快速提交写缓存

>delete fromt1 where rownum=1; //删一行数据

>commit;

log_buffer约数十兆大小。

Oracle中的事物：一条或多条增删改除sql语句，事物与Oracle的一致性关系密切。Oracle中所有已提交数据不应该丢失。提交会触发LGwriter把log_buffer写入redoLogBuffer（连续写入速度较快），而不会触发脏数据写回磁盘（不连续写入，较多花费寻道时间）。这种机制使得当Oracle意外终止（未提交）时，磁盘数据有不一致问题，但日志却是完好的。可以用日志重做数据，保证数据一致。这种机制避免了脏数据的不连续写入，从而花费较多寻道时间的问题。

日志实现了：快速提交、提高写性能（写入只写日志，不写不连续的数据块）

确保已经提交的事务不会丢失：提交后保证修改的“数据块对应的日志”完好写入磁盘中的redoLog。重启时Oracle会根据redoLog重新构建buffer_cache中的脏缓存内容。

logWriter绕过文件系统缓存直接写入磁盘，但是绕不过存储的写缓存（原因）：

buffer_cache提供写缓存；文件系统只有读缓存；存储上的缓存是写缓存，由电池供电；存储中的硬盘缓存是读缓存。读要经过4个缓存，log_buffer写入redoLog会绕过文件系统缓存，写入存储的缓存。这一过程中如果存储的缓存数据未写入磁盘，则会引起数据丢失（涉及存储方面的知识）。

甲骨论23课log_buffer相关设置

log_writer触发机制：

l  有1/3重做日志缓存区未被写入磁盘

l  有大于1M的重做日志缓存区未被写入磁盘

l  每隔3秒钟

l  用户提交

l  DBSR需要写入的数据的SCN大于LGWR记录的SCN,DBWR触发LGWR写入。

脏块写回磁盘要先保证log_buffer写回redo_log，这称为日志写入优先Write-Ahead-Log。

（日志机制保证记录日志而不保证记录修改后结果，用旧数据和日志来恢复新数据，实现高效运行）

redo_log写操作的少量频繁连续写特性及其优先建议：

redo_log写操作是少量频繁的，一般写大小数KB，每秒写IO数千次。因此redo_log适合放在IOPS（SSD的IOPS较高）较高的高转速磁盘上。redo_log文件写入是串行的，对redo_log底层文件做底层条带化处理，对redo_log写入性能提升十分有限。也就是说条带化的raid5、raid6写性能很差，不能用来写redo_log。

Oracle10g以后自动调整log_buffer值，调整原则是’Redo Buffers’=’Fixed SGA Size’+整数倍granule size（粒度）。redo_log是整数倍，目的是防止磁盘碎片产生，影响连续读写性能。

>select * from v$sgainfo where name in (‘fixed SGA Size’,’RedoBuffers’,’granule Size’); //查看redo_log大小

日志相关操作：

l  >alter database add logfile group 5‘/opt/oracle/oradata/dbtest/redo05_1.log’ SIZE 10M //添加日志组成员

l  >alter database add logfile member ‘/opt/oracle/oradata/dbtest/redo04_3.log’to group 4 //添加日志组成员

l  >alter database drop logfile group 5 //删除日志组成员

l  >alter database drop logfile(‘/opt/oracle/oradata/dbtest/redo05_1.log’,’/opt/oracle/oradata/dbtest/redo05_2.log)//删除日志组成员

redo_log切换时间尽可能不低于10-20分钟：写入log_buffer的区域，一块写满后会切换到下一块。根据实际应用产生日志的速度，控制工块区域写满日志的时间在10-20分钟。

>select to_char(FIRST_TIME,’yyyy-mm-dd hh24:mi:ss’)f_time,SEQUENCE# from v$log_history //查看日志切换时间

关于日志的归档模式，放在数据恢复中讲。

甲骨论24课PGA内存作用及构成

PGA构成：private SQLarea、session Memory、SQL Work Areas。PGA为server process提供工作空间。

WORKAREA_SIZE_POLICY构成：

l  sort_area_size排序区空间

l  sort_area_retained_size最小保留排序区空间

l  hash_area_size

l  hash_join_enable

l  bitmap_merge_area_size

l  create_bitmap_area_size

l  open_cursors

l  pga_max_size

会话要获取排序的数据，数据先被读到PGA中，在PGA中进行排序。使用hash_area_size进行多个表之间的hash连接。

甲骨论25课PGA管理和调整

Oracle两种工作方式：Dedicated Server、Shared Server

会话连接请求调度进程，请求serverprocess。调度进程负责将会话加入队列，server process不断搜索这个队列，发现sql语句就要处理。

Oracle9i以后全部为自动PGA管理。“自动”指自动分配PGA中的WORKAREA中的排序区、哈希区、位图区的大小。自动PGA管理只需要设定PGA大小。手动设置会出现的问题是如果PGA内存区空间过小，会导致磁盘排序，效率变低。

自动PGA管理的设置：

l  >alter system set workarea_size_policy=auto scope=both;

l  >alter system set pga_aggregate_target=512 scope=both;

l  >show parameter pga;

在OLPT系统中，典型PGA内在设置应该是总内存较小部分（20%），剩下的80%分配给SGA。

OLPT：pga_aggregate_target=总物理内存80%20%

在DSS系统中，由于会运行一些很大的查询，典型的PGA内在最多分配70%的内存。

DSS：pga_aggregate_target=总物理内存80%20%

open_cursors指一个会话同时执行sql语句的数量，一般设置在200以上。

_pga_max_size指PGA能够分配的最大内存空间，查询方法：

>select ksppinm “Name”,ksppstvl/1024/1024 ||”M’ “Value”,ksppdesc“desc”

from xksppcv ywhere x.indx =y.indx and ksppinm =’_pga_max_size’;

查询内存中的session数量：

l  select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_memfrom v$process where PROGRAM like ‘%数据库名%’

查询PGA建议值视图：

>select pga_target_for_estimate/1024/1024 ||’M’ “Estimate_PGATarget”

,estd_pga_cache_hit_percentage “Cache Hit(%)”

,estd_extra_bytes_rw/1024/1024 ||’M’ “Extraread/Write”

,estd_overalloc_count“Over_alloc_count”

from v$pga_target_advice

图表会发现PGA内存越大，在内存中排序命中率越大，额外物理磁盘读写。缓存溢出导致磁盘读写。先用预估值运行一段时间数据库，再查此表修正PGA。

查询系统统计信息：内存排序次数、磁盘排序次数、全部排序行数

>select * from V&SYSSTAT where name like ‘%sort%’;

显示当前会话使用的PGA内存

>select name,value from vmystat b wherea.statistic#=b.statistic# and a.name like ‘%pga%’;

查询所有进程的PGA使用情况：

>selectspid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem fromv$process where PROGRAM like ‘%数据库%’;