CTAS方式（create table as）建立資料表時，使用nologging子句的情況
1、查詢當前會話的sid：
SQL> select sid from v$mystat where rownum = 1;
SID
----------
929
2、查詢當前會話redo的資訊：
select * from v$sesstat b,v$statname c where b.STATISTIC# = c.STATISTIC# and b.SID=929 and c.NAME like '%redo%';
現在，redo的數量很少
3、CTAS方式建一張表：
SQL> create table t2 as select * from dba_objects;
Table created.
4、查詢會話的redo資訊：
select * from v$sesstat b,v$statname c where b.STATISTIC# = c.STATISTIC# and b.SID=929 and c.NAME like '%redo%';
漲了10M的redo
5、CTAS方式建另外一張表（加上nologging）：
SQL> create table t3 as select * from dba_objects nologging;
Table created.
6、再次查詢會話的redo資訊：
select * from v$sesstat b,v$statname c where b.STATISTIC# = c.STATISTIC# and b.SID=929 and c.NAME like '%redo%';
redo又漲了10M；理論上：加上nologging之後，redo應該是不漲的，但是它同樣漲了10M
所以：CTAS方式同樣會產生等量的日誌
注意：CTAS方式，只有在非歸檔模式下，加nologging的時候，才不會產生redo日誌
模擬一個實驗，看看oracle的redo是如何產生的？
1、檢視當前的redo是第幾組：
select * from v$log;
當前使用第三組
切換日誌：
SQL> alter system switch logfile;
System altered.
切換到第一組了
2、刪除表的資料：
刪除一行資料：
SQL> delete from t3 where object_id = 25;
1 row deleted.
再刪除一行資料：
SQL> delete from t3 where object_id = 41;
1 row deleted.
SQL> commit;
Commit complete.
3、切換日誌，然後dump出第一組日誌來（或者挖掘出第一組日誌）：
切換日誌：
SQL> alter system switch logfile;
System altered.
dump日誌：
select * from v$logfile;
SQL> connect / as sysdba
Connected.
SQL> ALTER SYSTEM DUMP LOGFILE '/oradata/orcl/redo01.log';
System altered.
檢視dump出來的日誌：
模擬一個例子，檢視日誌資訊（重做記錄、改變向量）
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,SEQUENCE#,STATUS,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 82 INACTIVE 1044519 1045380
2 83 ACTIVE 1045380 1045541
3 84 CURRENT 1045541 2.8147E+14
1、首先建立一張表t21
SQL> create table t21(id number,name varchar2(20));
Table created.
2、插入一行資料
SQL> insert into t21 values(1,'zyr');
1 row created.
3、刪除表t21，並提交事務
SQL> delete from t21;
1 row deleted.
SQL> commit;
Commit complete.
4、切換日誌
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,SEQUENCE#,STATUS,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 85 CURRENT 1045594 2.8147E+14
2 83 ACTIVE 1045380 1045541
3 84 ACTIVE 1045541 1045594
5、dump出序列號為84的日誌
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
/oradata/orcl/redo03.log
NO
2 ONLINE
/oradata/orcl/redo02.log
NO
1 ONLINE
/oradata/orcl/redo01.log
NO
SQL> ALTER SYSTEM DUMP LOGFILE '/oradata/orcl/redo03.log';
System altered.
檢視日誌內容：
redo日誌檔案
1、redo record：
一個 redo record 由多個 chang# 組成（可以認為：一個chang#就是修改了一個資料塊）
重做記錄：通常由一組改變向量組成，是一個改變向量的集合
重做記錄：代表一個資料庫的變更（INSERT、UPDATE、DELETE等操作），構成資料庫變更的最小恢復單位
例如：一個Update的重做記錄包括相應的回滾段的改變向量和相應的資料塊的改變向量等
2、chang#：改變向量
改變向量：表示對資料庫內某一個資料塊所做的一次變更（一個改變向量對應一個資料塊）
改變向量中：包含了變更的資料塊的版本號、事務操作程式碼、變更從屬資料塊的地址（DBA）以及更新後的資料
例如：一個update事務包含一系列的改變向量，對於資料塊的修改是一個向量，對於回滾段的修改又是一個向量
3、ALTER SYSTEM DUMP LOGFILE '/oradata/orcl/redo01.log';
dump出日誌的內容
/oradata/orcl/redo01.log：日誌成員地址
buffer cache
增量檢查點
將控制檔案dump出來，因為控制檔案中記錄著增量檢查點的資訊
SQL> alter session set events 'immediate trace name controlf level 2';
Session altered.
檢視控制檔案內容：
檢查點資訊：
LRBA：
1、髒資料塊裡面有LRBA
2、控制檔案裡有LRBA：
1、LRBA
2、on disk rba
3、heartbeat
dump一個資料塊出來：
SQL> alter system dump datafile 4 block 173;
System altered.
檢視資料塊資訊：
關於檢查點的一個引數
log_checkpoints_to_alert，這個引數我們一般不要設定
buffer cache裡的等待事件
1、free buffer waits
有三種情況會發生這個等待事件：
1、比如想要把一個資料檔案（只讀的）改為讀寫的狀態，這時候oracle會將buffer cache裡面所有的資料塊全部凍結，凍結以後改為invalid（無效的），這時候想要把資料從磁碟讀到buffer裡去，就找不到空閒的buffer，因為都已經凍結了（這種情況很少出現）
2、一個會話修改了一些資料塊，髒塊的鏈上有太多的髒塊，因為修改之後的資料塊變成了髒塊，就要往髒塊的鏈上掛，但是鏈上髒塊太多，就掛不上去，這時候就會產生等待事件free buffer waits
3、把資料從磁碟讀到buffer的時候，在buffer cache裡面找空閒的buffer，找半天沒找到，這時候會產生free buffer waits
大量的髒塊產生，跑批處理的時候，載入資料的時候，會產生free buffer waits（解決辦法：採用直接路徑載入，不要等buffer cache）
buffer cache太小，會產生free buffer waits（解決辦法：增大buffer cache的大小，增加dbwr的數量）
2、read by other session
就是說：一個會話要讀一個資料塊，從磁碟往buffer cache裡面讀，還沒讀完，這時候，正好另外一個會話也要讀這個資料塊，這時候會認為這個資料塊已經在buffer cache裡了，但是這個塊正在往buffer cache裡面讀，但是還沒有讀完，這時候就會產生read by other session等待事件
buffer cache太小，會產生read by other session等待事件
全表掃描，也會產生read by other session等待事件
3、buffer busy waits
兩種情況：
1、現在buffer cache裡面有一個buffer，一個會話在修改這個buffer，在修改的那瞬間，另外一個會話上來要讀它，這時候就會產生buffer busy waits
2、一個會話正在往buffer cache裡讀一個資料塊，這時候，另外一個會話也想往buffer cache裡讀同一個資料塊，這時候也會產生buffer busy waits
這種時候，往往意味著有熱塊
4、log file sync
一個會話要提交一個事務的時候，lgwr會把日誌往redo log裡寫，就是說：一個事務提交的那一刻就開始等，等到lgwr把日誌往redo log裡寫完的時候，給會話一個響應，提交到返回響應的這段時間就是：log file sync等待事件
redo io效能差，會產生log file sync等待事件
提交過於頻繁，也會產生log file sync等待事件
5、log file parallel write
就是：lgwr把日誌往redo log裡寫的總體時間
redo io效能差，會產生log file parallel write
6、log buffer space
就是說：很多會話，修改資料之後，產生很多的日誌，但是log buffer滿了，日誌寫不進去了，這時候就會產生log buffer space
redo 的 io 效能差，會產生log buffer space
7、log file switch (archiving needed)（經常出現的）
比如：現在有4個redo log日誌，現在正在用1號，然後2、3、4號redo log都沒有歸檔成功（可能歸檔日誌空間滿了），這時候1號redo log寫滿以後，要覆蓋2號，但是還沒有歸檔，就覆蓋不了，就會產生log file switch (archiving needed)，這時候就不能做任何的DML操作了，不然會導致資料庫hang住了
8、log file switch (checkpoint incomplete)
比如：現在有4個redo log日誌，現在正在用1號（current），然後2、3、4號redo log都是active，這時候1號redo log寫滿以後，要覆蓋2號，就覆蓋不了，因為active的redo log不能被覆蓋，這時候就要等dbwr把髒塊寫完之後，redo log變為inactive才能被覆蓋
DBWR效能差，會產生log file switch (checkpoint incomplete)
IO效能差，會產生log file switch (checkpoint incomplete)
解決辦法：
調整IO：
增加redo log的大小
增加redo log的組數
buffer cache裡的CBC latch
查詢buffer cache裡面latch的數量：
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppinm like '_db_block_hash_latches';
查詢buffer cache裡面鏈的數量：
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppinm like '_db_block_hash_buckets';
鏈和latch的關係：3倍的關係，就是三個鏈用一個latch
bucket數量的預設值是大於2倍的buffer數量的最小的2的冪的值的公式
模擬cbc latch爭用
一個會話，建一個表，然後反覆的訪問這個表：
SQL> create table t21(id number,name varchar2(20));
Table created.
SQL> insert into t21 values(1,'zyr');
1 row created.
SQL> commit;
Commit complete.
SQL> select rowid,t21.* from t21;
ROWID ID NAME
------------------ ---------- --------------------
AAAVVWAAEAAAAYuAAA 1 zyr
SQL> declare
aa varchar2(100);
begin
for i in 1..1000000 loop
select name into aa from t21 where rowid='AAAVVWAAEAAAAYuAAA';
end loop;
end;
/
另外一個會話，反覆的更新這個表：
SQL> declare
begin
for i in 1..20000 loop
update t21 set id=15 where rowid='AAAVVWAAEAAAAYuAAA';
commit;
end loop;
end;
/
查詢會話：
select * from v$session a where a.USERNAME='U1';
查詢會話相關的等待事件：
select sid ,event,total_waits,total_timeouts,time_waited_micro
from v$session_event where sid in(912,931) and event like '%buffer%';
查詢資料庫裡目前有多少CBC latch：
select * from v$latch_children where name like '%cache buffers chains%';
......
select * from v$latch_children where name like '%cache buffers chains%'order by sleeps desc;
查詢latch下面掛著的資料塊：
select a.dbarfil,dbablk,class,state,tch from x$bh a where a.hladdr='00000000B9141B90' order by tch desc;
class:表示buffer header對應block的型別:
1=data block, 9=2nd level bmb,
2=sort block, 10=3rd level bmb,
3=save undo block, 11=bitmap block,
4=segment header, 12=bitmap index block,
5=save undo header, 13=unused,
6=free list, 14=undo header,
7=extent map, 15=undo block
state:
0, FREE, no valid block image
1, XCUR, a current mode block, exclusive to this instance 正在被當前的instance獨佔
2, SCUR, a current mode block, shared with other instances正在被當前的instance共享
3, CR, a consistent read (stale) block image 一致讀
4, READ, buffer is reserved for a block being read from disk 正在從磁碟上讀取塊
5, MREC, a block in media recovery mode 處於介質恢復模式
6, IREC, a block in instance (crash) recovery mode處於例項恢復模式
查詢資料塊對應著哪個物件（生產裡儘量不要做這個操作）：
select * from dba_extents x where 1582 between x.block_id and x.block_id+x.blocks;
查詢t21這個表被訪問對應的SQL：
select * from v$sql b where b.SQL_TEXT like '%t21%';
