甲骨論21課 Oracle日志原理剖析
常見數(shù)據(jù)庫：Oracle感局、SQLserver、MySQL暂衡、DB2询微。Oracle優(yōu)化目標：數(shù)據(jù)一致性、高性能狂巢，日志保障了數(shù)據(jù)一致性撑毛。日志嚴格按時間記錄buffer_cache中buffer的改變過程。
日志記錄內(nèi)容：數(shù)據(jù)塊地址唧领、修改時間藻雌、修改操作（增加數(shù)據(jù)、刪除數(shù)據(jù)）斩个。修改數(shù)據(jù)就是先刪除后增加胯杭。大量的日志會產(chǎn)生性能問題，與等待事件有關(guān)受啥。
實際的日志產(chǎn)生過程：serverprocess修改buffer_cache中的數(shù)據(jù)做个，同時產(chǎn)生日志寫入會話對應(yīng)的PGA。當日志積累到一定時時滚局，從PGA寫入log_buffer居暖。logger_buffer由后臺進程LGwriter 寫入redoLogBuffer。
涉及到數(shù)據(jù)一致性的問題：
l? 如何確保已經(jīng)提交的事務(wù)不會丟失
l? 日志優(yōu)先寫入Write-Ahead-Log
l? LGWR繞過OS緩存直接定稿磁盤藤肢，但是繞不過存儲的寫緩存太闺。
甲骨論22課Oracle日志核心意義快速提交寫緩存
>delete fromt1 where rownum=1; //刪一行數(shù)據(jù)
>commit;
log_buffer約數(shù)十兆大小。
Oracle中的事物：一條或多條增刪改除sql語句谤草，事物與Oracle的一致性關(guān)系密切跟束。Oracle中所有已提交數(shù)據(jù)不應(yīng)該丟失。提交會觸發(fā)LGwriter把log_buffer寫入redoLogBuffer（連續(xù)寫入速度較快）丑孩，而不會觸發(fā)臟數(shù)據(jù)寫回磁盤（不連續(xù)寫入冀宴，較多花費尋道時間）。這種機制使得當Oracle意外終止（未提交）時温学，磁盤數(shù)據(jù)有不一致問題略贮，但日志卻是完好的。可以用日志重做數(shù)據(jù)逃延，保證數(shù)據(jù)一致览妖。這種機制避免了臟數(shù)據(jù)的不連續(xù)寫入，從而花費較多尋道時間的問題揽祥。
日志實現(xiàn)了：快速提交讽膏、提高寫性能（寫入只寫日志，不寫不連續(xù)的數(shù)據(jù)塊）
確保已經(jīng)提交的事務(wù)不會丟失：提交后保證修改的“數(shù)據(jù)塊對應(yīng)的日志”完好寫入磁盤中的redoLog拄丰。重啟時Oracle會根據(jù)redoLog重新構(gòu)建buffer_cache中的臟緩存內(nèi)容府树。
logWriter繞過文件系統(tǒng)緩存直接寫入磁盤，但是繞不過存儲的寫緩存（原因）：
buffer_cache提供寫緩存料按；文件系統(tǒng)只有讀緩存奄侠；存儲上的緩存是寫緩存，由電池供電载矿；存儲中的硬盤緩存是讀緩存垄潮。讀要經(jīng)過4個緩存，log_buffer寫入redoLog會繞過文件系統(tǒng)緩存闷盔，寫入存儲的緩存弯洗。這一過程中如果存儲的緩存數(shù)據(jù)未寫入磁盤，則會引起數(shù)據(jù)丟失（涉及存儲方面的知識）馁筐。
甲骨論23課log_buffer相關(guān)設(shè)置
log_writer觸發(fā)機制：
l? 有1/3重做日志緩存區(qū)未被寫入磁盤
l? 有大于1M的重做日志緩存區(qū)未被寫入磁盤
l? 每隔3秒鐘
l? 用戶提交
l? DBSR需要寫入的數(shù)據(jù)的SCN大于LGWR記錄的SCN,DBWR觸發(fā)LGWR寫入涂召。
臟塊寫回磁盤要先保證log_buffer寫回redo_log，這稱為日志寫入優(yōu)先Write-Ahead-Log敏沉。
（日志機制保證記錄日志而不保證記錄修改后結(jié)果果正，用舊數(shù)據(jù)和日志來恢復(fù)新數(shù)據(jù)，實現(xiàn)高效運行）
redo_log寫操作的少量頻繁連續(xù)寫特性及其優(yōu)先建議：
redo_log寫操作是少量頻繁的盟迟，一般寫大小數(shù)KB秋泳，每秒寫IO數(shù)千次。因此redo_log適合放在IOPS（SSD的IOPS較高）較高的高轉(zhuǎn)速磁盤上攒菠。redo_log文件寫入是串行的迫皱，對redo_log底層文件做底層條帶化處理，對redo_log寫入性能提升十分有限辖众。也就是說條帶化的raid5卓起、raid6寫性能很差，不能用來寫redo_log凹炸。
Oracle10g以后自動調(diào)整log_buffer值戏阅，調(diào)整原則是’Redo Buffers’=’Fixed SGA Size’+整數(shù)倍granule size（粒度）。redo_log是整數(shù)倍啤它，目的是防止磁盤碎片產(chǎn)生奕筐，影響連續(xù)讀寫性能舱痘。
>select * from v$sgainfo where name in (‘fixed SGA Size’,’RedoBuffers’,’granule Size’); //查看redo_log大小
日志相關(guān)操作：
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的區(qū)域，一塊寫滿后會切換到下一塊离赫。根據(jù)實際應(yīng)用產(chǎn)生日志的速度芭逝，控制工塊區(qū)域?qū)憹M日志的時間在10-20分鐘。
>select to_char(FIRST_TIME,’yyyy-mm-dd hh24:mi:ss’)f_time,SEQUENCE# from v$log_history //查看日志切換時間
關(guān)于日志的歸檔模式渊胸，放在數(shù)據(jù)恢復(fù)中講旬盯。
甲骨論24課PGA內(nèi)存作用及構(gòu)成
PGA構(gòu)成：private SQLarea、session Memory翎猛、SQL Work Areas瓢捉。PGA為server process提供工作空間。
WORKAREA_SIZE_POLICY構(gòu)成：
l? sort_area_size排序區(qū)空間
l? sort_area_retained_size最小保留排序區(qū)空間
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
會話要獲取排序的數(shù)據(jù)办成，數(shù)據(jù)先被讀到PGA中，在PGA中進行排序搂漠。使用hash_area_size進行多個表之間的hash連接迂卢。
甲骨論25課PGA管理和調(diào)整
Oracle兩種工作方式：Dedicated Server、Shared Server
會話連接請求調(diào)度進程桐汤，請求serverprocess而克。調(diào)度進程負責(zé)將會話加入隊列，server process不斷搜索這個隊列怔毛，發(fā)現(xiàn)sql語句就要處理员萍。
Oracle9i以后全部為自動PGA管理〖鸲龋“自動”指自動分配PGA中的WORKAREA中的排序區(qū)碎绎、哈希區(qū)、位圖區(qū)的大小抗果。自動PGA管理只需要設(shè)定PGA大小筋帖。手動設(shè)置會出現(xiàn)的問題是如果PGA內(nèi)存區(qū)空間過小，會導(dǎo)致磁盤排序冤馏，效率變低日麸。
自動PGA管理的設(shè)置：
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系統(tǒng)中，典型PGA內(nèi)在設(shè)置應(yīng)該是總內(nèi)存較小部分（20%）逮光，剩下的80%分配給SGA代箭。
OLPT：pga_aggregate_target=總物理內(nèi)存80%20%
在DSS系統(tǒng)中，由于會運行一些很大的查詢涕刚，典型的PGA內(nèi)在最多分配70%的內(nèi)存嗡综。
DSS：pga_aggregate_target=總物理內(nèi)存80%20%
open_cursors指一個會話同時執(zhí)行sql語句的數(shù)量，一般設(shè)置在200以上副女。
_pga_max_size指PGA能夠分配的最大內(nèi)存空間蛤高，查詢方法：
>select ksppinm “Name”,ksppstvl/1024/1024 ||”M’ “Value”,ksppdesc“desc”
from xksppcv ywhere x.indx =y.indx and ksppinm =’_pga_max_size’;
查詢內(nèi)存中的session數(shù)量：
l? select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_memfrom v$process where PROGRAM like ‘%數(shù)據(jù)庫名%’
查詢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
圖表會發(fā)現(xiàn)PGA內(nèi)存越大蚣旱，在內(nèi)存中排序命中率越大，額外物理磁盤讀寫戴陡。緩存溢出導(dǎo)致磁盤讀寫塞绿。先用預(yù)估值運行一段時間數(shù)據(jù)庫，再查此表修正PGA恤批。
查詢系統(tǒng)統(tǒng)計信息：內(nèi)存排序次數(shù)异吻、磁盤排序次數(shù)、全部排序行數(shù)
>select * from V&SYSSTAT where name like ‘%sort%’;
顯示當前會話使用的PGA內(nèi)存
>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 ‘%數(shù)據(jù)庫%’;
