【轉】DBA日常維護SQL整理

database 概況資訊檢查

# 檢查 database 基本資訊

 檢視資料檔案資訊 

# 檢查表空間資料檔案資訊

# 檢查表空間

# 檢查資料檔案狀态  

# 檢查表空間使用情況

# 查詢臨時 segment 使用情況

# 檢視臨時表空間大小 

 # 檢視臨時表空間的使用情況

# 查找消耗較多臨時表空間的sql

# 檢視目前臨時表空間使用大小與正在占用臨時表空間的sql語句

# 檢視資料檔案資訊 , 若檔案較多可以根據需要字段進行排序 輸出 top 10

# 檢視所有資料檔案 i/ o 情況 , 若檔案太多 , 可以改寫 為 top 10 select *( order by xx desc) where

rownum<=10。其中phyrds為實體讀的次數極為Reads，phywrts為實體寫的次數極為Writes，phyblkrd為實體塊讀的次數即為br，phyblkwrt為實體寫的次數即為bw。readtime為耗費在實體讀上的總時間極為RTime，writetim為耗費在實體寫上的總時間即為WTime。這兩個值隻有在參數timed_statistics參數為true時才有效。

# 擷取 top 10 熱 segment

 # 判斷實體讀最多的 object

# 檢視熱點資料檔案 ( 從單塊讀取時間判斷 )

檢視redo 

# 檢查日志切換頻率

# 檢查 lgwr i/o 性能 (time_waited/total_waits:表示平均lgwr寫入完成時間若>1表示寫入過慢 ) 

# 查詢 redo block size

# 檢視 user commit 次數 

# 檢視系統運作時間 

# 計算出每秒使用者送出次數 

 # 計算出每個事務平均處理多少個 redo block 

sga,pga, 命中率 

# sga,pga, 命中率

# 檢查 sga

# 檢視buffer cache設定建議

# 檢視 cache 池 

# 檢視 buffer cache 中 defalut pool 命中率 

# 檢查 shared pool 

# 檢查 shared pool 中 library cache

# 檢查整體命中率 (library cache) 

# 檢查 shared pool free space 

# 每個子shared pool 由單獨的 shared pool latch保護，檢視他們的命中率 shared pool latch，用于shared pool空間回收配置設定使用的latch 

# 使用 v$shared_pool_advice 計算不同 shared pool 大小情況下，響應時間， S 機關 

# 檢視 shared pool 中 各種類型的 chunk 的大小數量 

# 檢視是否有庫緩沖有關的等待事件 

# 查詢 sga 中各個 pool 情況 

 # 檢視使用 shard_pool 保留池情況 

Oracle 專門從共享池内置出一塊區域來來配置設定記憶體保持這些大塊。這個保留共享池的預設大小是共享池的5%(_shared_pool_reserved_pct 5 控制 ) oracle 建設定為 10% 。大小通過參數 SHARED_POOL_RESERVED_SIZE 改。它是從共享池中配置設定，不是直接從 SGA 中配置設定的，它是共享池的保留部分，專門用于存儲大塊段#shared pool 中記憶體大于 _SHARED_POOL_RESERVED_MIN_ALLOC 将放入 shared pool 保留池 , 保留池維護一個單獨的 freelist,lru ，并且不會在 lru 清單存recreatable 類型 chunks ，普通 shared pool 的釋放與 shared pool 保留池無關。 

# 關于設定 SHARED_POOL_RESERVED_SIZE

#1.如果系統出現ora-04031, 發現請求記憶體都是大于 _SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) , 且v$shared_pool_reserved 中有大量 REQUEST_MISSES( 并且可以看下LAST_MISS_SIZE )表示 SHARED_POOL_RESERVED_SIZE 太小了需要大的記憶體的請求失敗 , 那麼需要加大SHARED_POOL_RESERVED_SIZE

#2. 如果 ora-04031 請求記憶體出現在 4100-4400 并造成 shared pool lru 合并 , 老化換出記憶體 , 可以調小 _SHARED_POOL_RESERVED_MIN_ALLOC 讓此部分記憶體進入shared reserved pool, 相應的加大SHARED_POOL_RESERVED_SIZE

#3. 從 v$shared_pool_reserved 來判斷 , 如果 REQUEST_FAILURES>0( 出現過 ora-04031) 且LAST_FAILURE_SIZE( 最後請求記憶體大小 )>_SHARED_POOL_RESERVED_MIN_ALLOC表示 shared reserved pool 缺少連續記憶體 , 可以加大 SHARED_POOL_RESERVED_SIZE, 減少 _SHARED_POOL_RESERVED_MIN_ALLOC 少放對象 , 并相對加大 shared_pool_size

# 要是反過來 REQUEST_FAILURES>0( 出現過 ora-04031) 且 LAST_FAILURE_SIZE( 最後請求記憶體大小)<_SHARED_POOL_RESERVED_MIN_ALLOC, 表示 在 shared pool 中缺少連續記憶體 , 可以加減少_SHARED_POOL_RESERVED_MIN_ALLOC 多放入一些對象 , 減少 sharedpool 壓力 , 适當加大shared_pool_size,SHARED_POOL_RESERVED_SIZE

# 查詢還保留在 library cache 中，解析次數和執行次數最多的 sql( 解析 * 執行 )

 # 檢視 pga 

# 檢視pga建議

# 檢視資料庫 cache 或 keep 了哪些 object

# 取消 cache 或 keep(keep pool) 

檢查undo 

# 檢查 undo rollback segment 使用情況 

 # 計算每秒鐘産生的 undoblk 數量 

# 計算undo表空間大小

 # 查詢 undo 具體資訊 

# 在記憶體中排序比率 ( 最優排序 ) 

# 檢視目前系統undo使用情況

# 檢視目前系統和undo相關的會話

檢視對象

# 檢查資料庫中無效對象

# 檢查是否有禁用限制 

# 檢查是否有禁用 trigger 

# 在某個使用者下找所有的索引

# 檢查與索引相關的字段

檢視目前系統狀态

# 檢查系統中目前等待事件

# 檢視經常被使用而沒有pin在記憶體中的對象

# 形成生成pin住共享池中目前沒有被pin住的對象的sql語句。在執行exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_GZ_LOGS','P');可能會報出未定義的錯誤，需要在sqlplus下執行腳本$ORACLE_HOME/rdbms/admin/dbmspool.sql

# 檢視使用了超過10MB記憶體 而沒有pin的對象

# 檢視大的沒有被pin住的對象.

# 檢視大的沒有被pin住的過程，包和函數

需要被pin入記憶體中的對象主要有：常用的較大的存儲對象，如standard、diutil包；編譯的常用的triggers；sequences。

最好在開機時就将其pin入記憶體中。這樣，既是使用指令alter system flush shared_pool時，也不會講這些object flush掉。具體pin對象到記憶體的方法使用DBMS_SHARED_POOL.keep存儲過程。可以用unkeep方法解除其pin狀态。

db_object_cache和碎片化

碎片化造成在共享池中雖然有許多小的碎片可以使用，但沒有足夠大的連續空間，這在共享池中是普遍的現象。消除共享池錯誤的關鍵就是即将加載對象的大小是否可能會産生問題。一旦知道了這個存在問題的PL/SQL，那麼就可以在資料庫啟動時(這時共享池是完全連續的)就将這個代碼固定。這将確定在調用大型包時，它已經在共享池裡，而不是在共享池中搜尋連續的碎片(在使用系統時，這些碎片可能就不複存在)。可以查詢V$DB_OBJECT_CACHE視圖來判斷PL/SQL是否很大并且還沒有被辨別為"kept"的标記。今後需要加載這些對象時，可能會産生問題(因為它們的大小和需要占用大量連續的記憶體)。通過查詢V$DB_OBJECT_CACHE表，可以發現那些沒有固定，但由于所需空間太大而很有可能導緻潛在問題的對象。

# 查詢一下復原段的使用情況，其中USED_UREC為undo記錄的使用條目數，USED_UBLK為undo塊的使用數目

# 檢視鎖住對象的會話資訊，作業系統程序資訊

 # 根據程序檢視sql

# 檢視被鎖的表的被鎖時間

# 檢視被鎖的對象和引起鎖的sql

# 檢視鎖定的會話資訊

 # 殺死相關會話

# 如果出現ora-00031錯誤，則

# 亦可先查詢該會話相對應的作業系統程序，在作業系統上進行kill

TOP SQL

# 邏輯讀 TOP 10

# 實體讀 TOP 10

# 消耗CPU TOP 10

# 執行時間 TOP 10

查找需要使用綁定變量的sql

再 select sql_text from v$sqlarea where sql_text like 'insert into test %'; 找出具體的sql代碼