Oracle效能 - 常用查詢指令碼(final)

1. 查詢長時間操作的SQL(或者透過OEM圖形介面檢視)

V$SESSION_LONGOPS

查詢執行超過6秒鐘的操作，這些操作包括很多備份恢復功能，統計資訊收集，查詢操作，
不同版本可能有更多的操作加入。 Oracle OEM中的長時間操作就是讀取這個檢視。

要監控query execution progress，必須滿足以下前提條件:

1) Oracle最佳化器使用 CBO;
2) 資料物件已經收集了統計資訊;
3) 初始化引數 TIMED_STATISTICS = true 或是 SQL_TRACE = true ;

如果是Oracle RAC, 可以使用以下指令碼：

SELECT     a.opname,
           a.SID,
           a.serial#,
           a.sql_id,
           a.start_time,
           a.time_remaining,
           a.elapsed_seconds,
           b.sql_fulltext,
           a.MESSAGE
    FROM   gv$session_longops a, gv$sql b
   WHERE       a.start_time > SYSDATE - 0.1
           AND a.time_remaining > 0
           AND a.sql_id = b.sql_id
ORDER BY   a.start_time DESC;

如果是單機, 可以使用以下指令碼：

SELECT     a.opname,
           a.SID,
           a.serial#,
           a.sql_id,
           a.start_time,
           a.time_remaining,
           a.elapsed_seconds,
           b.sql_fulltext,
           a.MESSAGE,
           b.module,
           b.executions
   FROM   v$session_longops a, v$sql b
   WHERE a.start_time > SYSDATE - 0.1
           AND a.time_remaining > 0
           AND a.sql_id = b.sql_id
ORDER BY   a.start_time DESC;

2. 透過Linux PID及SID查詢相關SQL及程式：

select a.sid,a.serial#, a.program,b.spid from v$session a,v$process b
where a.paddr=b.addr and b.spid in ('1245','2985','5884');

spid為top檢視到的os段的process id .

緊急處理時候可以透過kill session方式或直接kill os process來結束程式。
Alter system kill session 'SID,SERIAL#' ;
Kill -9 SPID (unix,linux)
orakill sid(oracle_instance_name) spid (windows)

select a.username,a.machine,a.program,b.spid,c.sql_text
from v$session a,v$process b,v$sqlarea c
where a.paddr=b.addr and c.hash_value=a.sql_hash_value
and c.address=a.sql_address and b.spid=12984 ;

3. 查詢Oracle庫中的Lock (或透過Toad中session browser參看lock情況)

檢視資料庫中的鎖(LOCK),找出程式及SQL

SELECT   se.inst_id, se.SID, se.serial#,lk.SID,
         se.username,se.OSUser,se.Machine,se.program,
         DECODE (lk.TYPE,
                 'TX', 'Transaction',
                 'TM', 'DML',
                 'UL', 'PL/SQL User Lock',
                 lk.TYPE)
            lock_type,
         DECODE (lk.lmode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive',
                 TO_CHAR (lk.lmode))
            mode_held,
         DECODE (lk.request,
                 0, 'None',
                 1, 'Null',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive',
                 TO_CHAR (lk.request))
            mode_requested,
         TO_CHAR (lk.id1) lock_id1,
         TO_CHAR (lk.id2) lock_id2,
         ob.owner,
         ob.object_type,
         ob.object_name,
         DECODE (lk.Block,
                 0,
                 'No',
                 1,
                 'Yes',
                 2,
                 'Global')
            block,
         se.lockwait,
         sq.sql_fulltext
FROM   GV$lock lk, dba_objects ob, GV$session se, GV$sql sq
WHERE       lk.TYPE IN ('TM', 'UL')
         AND lk.SID = se.SID
         AND lk.id1 = ob.object_id(+)
         AND (lk.inst_id = se.inst_id)
         AND sq.address=se.sql_address ;

4. 透過AWR查詢效能問題(預設有1小時延遲，不能查詢當前時間的session)

SQL> conn / AS SYSDBA
SQL> @/u01/product/oracle/rdbms/admin/awrrpt.sql
輸入 report_type 的值:
輸入 num_days 的值: 2      --- 現在到過去兩天時間內的snap id (可以檢視到).
輸入 begin_snap 的值: 2147    --- 輸入的開始及結束的snap id 對應您要查詢的出現問題的時間段。
輸入 end_snap 的值: 2182
輸入 report_name 的值:
Report written to awrrpt_1_2177_2182.html
SQL> exit

下載awrrpt_1_2177_2182.html並開啟檢視。

ASH儲存了系統最新的處於等待的會話記錄，可以用來診斷資料庫的當前狀態；
而AWR中的資訊最長可能有1小時的延遲，所以其取樣資訊並不能用於診斷資料
庫的當前狀態，但可以用來作為一段時期內資料庫效能調整的參考。

5. 查詢物理讀寫嚴重的SQL

SELECT *
FROM (SELECT   sql_text, module,
                 disk_reads / DECODE (executions, 0, 1, executions) AS tt
            FROM v$sqlarea a
        ORDER BY tt DESC)
WHERE ROWNUM <= 20

6. 查詢物件統計資訊是否不是最新的。

7. 壞塊導致系統效能 (查詢壞塊)

SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where file_id = 10
AND 51896 between block_id and (block_id + blocks - 1)

8. 繫結變數窺視(Peeking)問題

繫結變數窺視使得執行計劃發生改變。

8. Oracle9i, 10g buffer cache的命中率

9i 命中率 :
// oracle9i data buffer hit ratio .

select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 40 and b.statistic# = 41 and c.statistic# = 42;

10g 命中率 :
// oracle10g data buffer hit ratio .

select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 47 and b.statistic# = 50 and c.statistic# = 54;

8. Oracle

Oracle效能問題檢查 - 常用查詢指令碼(final)

相關文章