oracle 小結

1 buffer cache 中有兩個佇列，一個是free buffer list,當資料被修改後，狀態
變為dirty,移動到dirty list,dirty list上的都是侯選的可以被DBWR寫出
到資料檔案的buffer

2 buffer cache會存在latch競爭

3 oracle還提供了buffer bucket結構，就象圖書館尋找圖書的索引卡片，用於快速判斷資料塊是否在
buffer cache中，抽屜中的每張卡片就是一個buffer header,實際上是以連結串列的形式存在閂在一起（cache
buffer chain),這就會產生cache buffer chain競爭，而增加bucket一點，可以快速進行搜尋，但也
不是增加得越多越就越好

4 從8I後，每個buffer latch需要管理多個bucket,每個bucket上的buffer數量降低，latch效能提升

5 查詢當前資料庫最繁忙的buffer
    在x$bh中有個欄位tch ,表明一個buffer的訪問次數，越多越表明存在競爭：

SELECT *
  FROM (SELECT   addr, ts#, file#, dbarfil, dbablk, tch
            FROM x$bh
        ORDER BY tch DESC)
WHERE ROWNUM < 11;

再查詢出這些熱點buffer來自哪些物件：

SELECT e.owner, e.segment_name, e.segment_type
  FROM dba_extents e,
       (SELECT *
          FROM (SELECT   addr, ts#, file#, dbarfil, dbablk, tch
                    FROM x$bh
                ORDER BY tch DESC)
         WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
   AND e.block_id <= b.dbablk
   AND e.block_id + e.blocks > b.dbablk;

  找出熱點物件的相關資訊：
SELECT distinct e.owner, e.segment_name, e.segment_type
           FROM dba_extents e,
                (SELECT *
                   FROM (SELECT   addr, ts#, file#, dbarfil, dbablk, tch
                             FROM x$bh
                         ORDER BY tch DESC)
                  WHERE ROWNUM < 11) b
          WHERE e.relative_fno = b.dbarfil
            AND e.block_id <= b.dbablk
            AND e.block_id + e.blocks > b.dbablk;



6 SHARD POOL的基本原理：
     通過shard pool實現SQL共享，減少程式碼硬解析等，從而提高資料庫的效能。包括了庫快取（v$librarychace檢視，
儲存了SQL語句執行計劃，執行的程式碼塊等）
，資料字典快取(v$rowcache)查詢。
   從11G開始多了一個result cache memory,主要是將查詢的結果集快取起來，如果同一SQL多次執行，就把結果直接返回。

服務端的result cache memory由兩部分組成：sql query result cache,儲存SQL查詢的結果集
通過引數result_cache_max_size設定大小，如果=0，則禁止該特性。
  設定有auto(自動），manual(通過查詢hints判斷），設定force(儘可能快取）結果
比如
    select /*+ result_cache */ ...............
   檢視cache 使用情況
    select * from v$result_cache_memory where free='no';
查詢result cache的統計資訊
     select * from v$result_cache_statisitcs;

7 oracle 9i中，增加了子緩衝池的管理，shared pool劃分為多個子緩衝池，9I中每個子緩衝池至少為128MB，SUBPOOL
最多有7個；在10G中，每個子緩衝為256MB，每4個CPU分配一個子緩衝。11G中，每個至少為512MB

8 oracle中，如果SQL語句大小寫不同，也會認為是不同的SQL，不會重用

9 ORA-04031錯誤：當嘗試分配大塊連續記憶體失敗（碎片過多），先清除共享池中當前沒用的所有物件，是空閒塊合拼，如果
依然沒足夠記憶體滿足需要，產生該錯誤

10 查詢硬解析次數
  1）select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and
name lile '%parse%';
  JAVA程式中使用preparestament 繫結變數；或者服務端的引數cursor_sharing,預設為exact,還有force,simliar.

11) 調整日誌檔案大小
   alter database add logfile group 4 'xxxxxx/xxx.dbf' size 1m;
  再強制切換日誌
     alter system switch logfile;
  將當前status為inactive的日誌組刪除
     alter database drop logfile group 1;

12) REDO日誌恢復,丟失非活動日誌組的恢復
       如果丟失非活動(inactive)日誌組，由於已經完成了檢查點，資料庫不會發生資料損失，通過clear重建日誌組即可恢復
      清除該日誌組即可啟動
       alter database clear logfile group 2(假設2是有問題的日誌組）
       alter database open;
     如果為歸檔模式，且該日誌組沒完成歸檔則需要使用如下命令強制清除
      alter database clear unarchived logfile group 2;



13) 在statkspack報告的開頭，有rollback per transcation(事務回滾率）=rollbaks/(commit+rollbacks),如果比例太高，
證明回滾高，效能不大好

14）回滾段中：對於insert操作，回滾段只記錄插入記錄的rowid，如果回退，則只需要將該記錄根據rowid刪除即可；
對於update，回滾段只需要記錄舊值即可；對於delete, 必須記錄整行的資料，因此產生的undo最多。

15）從9I開始自動管理UNO表空間
    show parameter undo;
    其中undo_retention:表示當回滾段變非啟用後，資料在被覆蓋前保留多少秒；
   查詢回滾段資訊
    select * from v$rollname;預設建立10個回滾段；
從10G開始，在share pool中開獨立的區域存放undo資訊，增強效率；
    在11G開始，RMAN UNO的新特性，可以在備份UNDO空間時，已經提交了的UNO資訊將不再備份，增強效率；
 
16） 當更新完資料後，在事務提交後，orcle要清除掉在資料塊上的儲存了的ITL和鎖等資訊，叫塊清除；
如果提交事務的時候，修改過的資料塊已經被寫回到資料檔案上，再讀出修改資源昂貴，選擇延遲清除，等到
下一次訪問該資料塊的時候再來清除。
   更新完後，強制將buffer cache的寫到資料檔案
   alter session set events='immediate trace name flush_cache';
   commit;
17) Oracle ORA-01555快照過舊的錯誤
   假設有一張6000萬行資料的testdb表，預計testdb全表掃描1次需要2個小時，參考過程如下：
1、在1點鐘，使用者A發出了select * from testdb;此時不管將來testdb怎麼變化，正確的結果應該是使用者A會看到在1點鐘這個時刻的

內容。
2、在1點30分，使用者B執行了update命令，更新了testdb表中的第4100萬行的這條記錄，這時，使用者A的全表掃描還沒有到達第4100萬

條。毫無疑問，這個時候，第4100萬行的這條記錄是被寫入了回滾段，假設是回滾段UNDOTS1，如果使用者A的全表掃描到達了第4100萬

行，是應該會正確的從回滾段UNDOTS1中讀取出1點鐘時刻的內容的。
3、這時，使用者B將他剛才做的操作提交了，但是這時，系統仍然可以給使用者A提供正確的資料，因為那第4100萬行記錄的內容仍然還在

回滾段UNDOTS1裡，系統可以根據SCN到回滾段裡找到正確的資料，但要注意到，這時記錄在UNDOTS1裡的第4100萬行記錄已經發生了重

大的改變：就是第4100萬行在回滾段UNDOTS1裡的資料有可能隨時被覆蓋掉，因為這條記錄已經被提交了！
4、由於使用者A的查詢時間漫長，而業務在一直不斷的進行，UNDOTS1回滾段在被多個不同的transaction使用著，這個回滾段裡的

extent迴圈到了第4100萬行資料所在的extent，由於這條記錄已經被標記提交了，所以這個extent是可以被其他transaction覆蓋掉的

！
5、到了1點45分，使用者A的查詢終於到了第4100萬行，而這時已經出現了第4條說的情況，需要到回滾段UNDOTS1去找資料，但是已經被

覆蓋掉了，這時就出現了ORA-01555錯誤


   解決方法：1、擴大回滾段，因為回滾段是迴圈使用的，如果回滾段足夠大，那麼那些被提交的資料就能儲存足夠長的時間，使那

些大事務完成一致性讀取
    2  增加undo_retention時間，因為UNDO回滾段是迴圈使用，裡面的資料可能隨時被迴圈覆蓋掉，如果設定undo_retention時間更

長，那麼在retention規定的時間內，任何其他事務都不能覆蓋這些資料
   3  最重要的一點就是優化程式相關查詢語句，減少查詢語句的一致性讀，降低讀取不到回滾段資料的風險。


18）  oracle 中的塊清除：
    資料庫塊的最前面有一個“開銷”空間(overhead)，這裡會存放該塊的一個事務表，對於鎖定了該塊中某些資料的各個“實際”

事務，在這個事務表中都有一個相應的條目。->http://blog.csdn.net/fw0124/article/details/6899223
1）首先當一個事務開始時，需要在回滾段事務表上分配一個事務槽；
2）在資料塊頭部獲得一個ITL事務槽，該事務槽指向回滾段段頭的事務槽；
3）在修改資料之前，需要在回滾段中記錄前映象資訊，回滾段頭事務槽指向該記錄；
4)  鎖定修改行，修改行鎖定位（lb-lock block）指向ITL事務槽；
5)  資料修改可以進行。
COMMIT時候Oracle需要將回滾段上的事務表資訊標記為非活動，以便空間可以重用；此外所做的一個操作是塊清除（Block cleanout

），如果事務修改的某些塊還在緩衝區快取中，會清除塊首部的ITL事務資訊（包括提交標誌、SCN等）和鎖定資訊。

在與我們的事務相關的提交列表中，Oracle會記錄已修改的塊列表（每個列表可以有20個塊指標），Oracle會根據需要分配多個這樣

的列表，直至達到某個臨界點。如果我們修改的塊加起來超過了塊緩衝區快取大小的10%，Oracle 會停止為我們分配新的列表。例如

，如果緩衝區快取設定為可以快取3,000個塊，Oracle 會為我們維護最多300個塊。
COMMIT時，Oracle會通過這些列表找到塊，如果塊仍在塊緩衝區中，Oracle會執行一個很快的清理，這叫做快速塊清除（FAST BLOCK

CLEANOUT）。
所以，只要我們修改的塊數沒有超過快取中總塊數的10%，而且塊仍在塊快取區中（如果已經被寫回到資料檔案上再次讀出該資料塊進

行修改成本過於昂貴），Oracle就會在COMMIT時清理這些塊。否則，就會延遲塊清除到下次訪問該塊的時候。通過延遲塊清除

（DELAYED BLOCK CLEANOUT）可以提高資料庫的效能，加快提交操作。

所以如果執行一個大的INSERT、UPDATE或DELETE，影響資料庫中的許多塊，就有可能在此之後，第一個“接觸”塊的查詢會需要修改

某些塊首部並把塊弄髒，生成REDO日誌，會導致DBWR把這些塊寫入磁碟。
如果Oracle不對塊完成這種延遲清除，那麼COMMIT的處理可能很長，COMMIT必須重新訪問每一個塊，可能還要從磁碟將塊再次讀入（

它們可能已經重新整理輸出）。

在一個OLTP系統中，可能從來不會看到這種情況發生，因為OLTP系統的特點是事務都很短小，只會影響為數不多的一些塊。
如果你有如下的處理，就會受到塊清除的影響：
· 將大量新資料批量載入到資料倉儲中；
· 在剛剛載入的所有資料上執行UPDATE（產生需要清理的塊）；
· 讓別人查詢這些資料
比較好的做法是：在批量載入了資料後，通過執行DBMS_STATS實用程式來收集統計資訊，就能自然的完成塊清除工作。


19） oracle 11g中，提供了對UNDO資料的歸檔，閃回資料歸檔，新增的後臺程式FBDA用於對閃回資料進行歸檔
ps -ef|grep fbda|grep -v grep


20) 等待事件
   1 v$session:記錄資料庫當前連線的session資訊；v$session_wait檢視記錄的是當前資料庫連線的活動session正在等待的
資源或事件資訊，但10G起，很多東西都合拼到v$session中了。
       v$system_event記錄的是整體情況，記錄資料庫自啟動以來的所有等待資訊
 
   2  oracle 10g中，引入了v$event_histogram,可以看到等待事件的分佈，比如
        select event,wait_time_milli,wait_count from v$event_histogram where event='latch: shard pool';
   3 oracle 11g中實時SQL監控：超過5秒的SQL記錄在v$sql_monitor檢視中。但要強制開statistics_level為TYPICAL和ALL
   4 強制對某個SQL實時監控
      select /*+ monitor */ from xxxxxxx
      select /*+no monitor */ from xxxxxxx
   5 select * from v$session_wait ;//獲得各程式的等待事件
   6 捕捉慢的SQL：
     SELECT   sql_text
    FROM v$sqltext a
   WHERE a.hash_value = (SELECT sql_hash_value
                           FROM v$session b
                          WHERE b.SID = '&sid')
ORDER BY piece ASC
    輸入已知的sesssion的ID
（select sid from v$mystat where rownum=1)
獲得最慢的SQL
   

7 從10G開始，新增加了v$session_wait_history檢視，記錄了最近10次的等待事件，
    select * from v$session_wait_history where sid=120;
  8 ASH報告，每秒採集1次活動會話，通過SGA中分配記憶體
  9 ASH呼叫方式：/rdbms/admin/ashrpt.sql 指令碼即可生成，OEM方式也可以看到ASH報告

20） 重要的等待事件
   1 db file sequential read(資料檔案順序讀取）
       讀取一個索引塊或通過索引讀取時，都會有這個等待。P1代表oracle要讀取的檔案的絕對檔案號；
P2代表ORACLE從這個檔案中開始讀取的起始資料塊號，P3讀取BLOCK數量。
     如果這個等待事件比較顯著，可能表示多表連線中，連線順序存在問題，或者索引存在問題；

   2）db file scattered read
       表明使用者程式正在讀資料到buffer cache中，其實表示FULL SCAN時，讀資料到buffer cache時，連續的
資料在記憶體中的儲存位置不連續，所以叫scattered read.一般來說，大量這樣的事件可能是索引缺少。
可以結合v$session_longops(記錄超過6秒的事務）來檢視。

     獲得全表掃描的語句：
    SELECT   sql_text
    FROM v$sqltext t, v$sql_plan p
   WHERE t.hash_value = p.hash_value
     AND p.operation = 'TABLE ACCESS'
     AND p.options = 'FULL'
ORDER BY p.hash_value, t.piece;

   3） direct path read write(直接路徑讀寫）
       多發生在磁碟排序IO中，都是直接從PGA寫或讀資料到資料檔案的。

  4）日誌檔案相關的等待
       log file switch (日誌檔案切換），包括log file switch(archiving needed)和log file switch(checkpoint imcomplete)
造成的日誌組迴圈寫滿後，覆蓋前的等待；
     log file sync(日誌檔案同步）：LGWR寫效率低，將緩衝區的REDO寫到日誌遇到等待，不要把
redo log file放RAID 5ZHONG  ，LGWR被過度頻繁啟用。

  5）log buffer space-日誌緩衝空間
      當資料庫產生日誌的速度比LGWR寫出速度快時，或者日誌切換太小時，會出現該等待，表明redo log buffer過小，
可以考慮增大日誌檔案大小，或者增加日誌緩衝區的大小，儘量使用RAID10放日誌檔案。

21 TX鎖和TM鎖
    1）TX鎖叫事務鎖，當執行DDL，DML時，先獲得之，在行級獲得的，每個資料行上都有一個鎖定位，用來
判斷資料是否被鎖定，有一個ITL的資料結構，用來記錄事務資訊，只有排他鎖而沒有共享模式。
       TM叫表鎖，通過LOCK獲得，或者DML操作以及SELECT FOR UPDATE獲得，可以防止其他程式對錶加X排他鎖。
可以通過v$lock來看這些資訊，TYPE欄位表示鎖定的型別。

   LMODE含義  2 ROW-S（SS）
              3  ROX-X（SX）
              4  SHARE--S
              5  S/ROW-X （SSX）
              6  EXCLUSIVE （X）
     
   檢視方法： select * from v$lock where sid=xxxxx;(select sid,username from v$session where username='scott');


22 Latch free(閂鎖釋放）
    latch是一低階的排隊（序列）機制，保護SGA中的共享記憶體結構，是一快速被獲取和釋放的的記憶體鎖，用來防止共
享記憶體結構被多個使用者同時訪問。
   willing to wait:如果請求的latch不能立即得到，請求程式等待很短時間後再次發出請求，一直重複直到得到LATCH
   IMMEDIATE:如果所請求的latch不能立即得到，請求程式不再等待，繼續執行下去

  
23 10G起檢視執行計劃的步驟
   1）explain plan set statement_id='test'(隨便給一個名) for sql 語句
  
   2）select plan_table_output from table(DBMS_XPLAN_DISPLAY('PLAN_TABLE','TEST','ALL'));
    3)還可以通過AWR報告中的SQLID去查
      select * from table(dbms_xplan.display_awr('sqlid'));


24  找SQL語句慢的一般步驟
    1）select * from v$session_wait;
    找出SID

   2） SELECT   sql_text
    FROM v$sqltext a
   WHERE a.hash_value = (SELECT sql_hash_value
                           FROM v$session b
                          WHERE b.SID = '&sid')
   輸入上一步的SID