Oracle效能異常查詢及調整指令碼-不斷更新(old versionl)

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 ;
 

知道有問題的SPID情況下檢視正在執行的SQL .

SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text
from v$session a,v$process b,v$sqltext c
WHERE b.spid='14150'
AND b.addr=a.paddr AND a.sql_address=c.address(+)order BY c.piece

 

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及查詢哪個SID最消耗資源

檢視佔I/O較大的正在執行的session

SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE,
       se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM  v$session se,v$session_wait st,v$sess_io si,v$process pr
WHERE  st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0
       AND st.event NOT LIKE '%SQL%'
       ORDER BY physical_reads DESC ;

 

查詢物理讀寫嚴重的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 

 

查詢哪個SID最消耗資源

select s.sid,s.value "CPU Used"
from v$sesstat s,v$statname n
where s.statistic#=n.statistic# and n.name='CPU used by this session'
and s.value>0
order by 2 desc;

查詢前十條效能差的SQL

SELECT * FROM 
  (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
    FROM v$sqlarea order BY disk_reads DESC )
where ROWNUM<10 ;

 

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

select * from  dba_tables 
where wner='DFMS' and table_name='TEST' and LAST_ANALYZED >= sysdate-1
and status='VALID';

select * from  dba_indexes where LAST_ANALYZED
where wner='DFMS' and index_name='IDX_TEST' and LAST_ANALYZED >= sysdate-1; 

select * from  dba_tab_columns where LAST_ANALYZED
where wner='DFMS' and table_name='TEST' and LAST_ANALYZED >= sysdate-1
and  COLUMN_NAME='XXXXX' ; 

 

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

9i, 10g 繫結變數窺視使得執行計劃出現變化 。11g有改善。如果出現此類
問題導致的效能問題， 需要升級到11g, 或者加入hint進行強制改變執行計劃。

 

8. 壞塊導致系統效能 (當然一般alert log中都有error, 查詢壞塊)

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. Oracle9i, 10g buffer cache及Library 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;

監控SGA Library Cache的命中率，應該小於1%

select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;

select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;

監控記憶體和硬碟的排序比率，最好使它小於 .10，增加PGA 
SELECT name, value  FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

 

9. redo log都處於active狀態。

select * from v$log ; 檢視redo log是否都出於active狀態，以及大小及組數
是否需要調大 。

 

10. 碎片程度定期檢視

檢視碎片程度高的表

SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

分析index的碎片程度：
 
SQL> analyze  index   dfms.IDX1_WIP_D_WO_DETAIL_COMID  validate structure ; 

SQL> select name, del_lf_rows, lf_rows, round((del_lf_rows/(lf_rows+0.0000000001))*100)
  2   "Frag Percent"  from index_stats   where   name = 'IDX1_WIP_D_WO_DETAIL_COMID'  ;

 

11. OS及網路檢視命令

top, sar, vmstat, iostat, ping , tnsping, route, traceroute,

 

 

---------------------------

 

效能調整FAQ .

[Q]如果設定自動跟蹤
[A]用system登入
執行$ORACLE_HOME/rdbms/admin/utlplan.sql建立計劃表
執行$ORACLE_HOME/rdbms/admin/plustrce.sql建立plustrace角色
如果想計劃表讓每個使用者都能使用，則
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
如果想讓自動跟蹤的角色讓每個使用者都能使用，則
SQL> grant plustrace to public;
透過如下語句開啟/停止跟蹤
SET AUTOTRACE ON |OFF
| ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN

 

[Q]如果跟蹤自己的會話或者是別人的會話
[A]跟蹤自己的會話很簡單
Alter session set sql_trace true|false
Or
Exec dbms_session.set_sql_trace(TRUE);
如果跟蹤別人的會話，需要呼叫一個包
exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)
跟蹤的資訊在user_dump_dest 目錄下可以找到或透過如下指令碼獲得檔名稱（適用於Win環境，如果是unix需要做一定修改）
SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.ora' filename
FROM
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p.addr = s.paddr
AND s.audsid = USERENV ('SESSIONID')
最後，可以透過Tkprof來解析跟蹤檔案，如
Tkprof 原檔案 目標檔案 sys=n

 

[Q]怎麼設定整個資料庫系統跟蹤
[A]其實文件上的alter system set sql_trace=true是不成功的
但是可以透過設定事件來完成這個工作，作用相等
alter system set events
'10046 trace name context forever,level 1';
如果關閉跟蹤，可以用如下語句
alter system set events
'10046 trace name context off';
其中的level 1與上面的8都是跟蹤級別
level 1：跟蹤SQL語句，等於sql_trace=true
level 4：包括變數的詳細資訊
level 8：包括等待事件
level 12：包括繫結變數與等待事件

 

[Q]怎麼樣分析表或索引
[A]命令列方式可以採用analyze命令
如Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE
[CASCADE]|[INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]
等等。
如果想分析整個使用者或資料庫，還可以採用工具包，可以並行分析
Dbms_utility(8i以前的工具包)
Dbms_stats(8i以後提供的工具包)
如
dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
這是對命令與工具包的一些總結
1、對於分割槽表，建議使用DBMS_STATS，而不是使用Analyze語句。
a) 可以並行進行，對多個使用者，多個Table
b) 可以得到整個分割槽表的資料和單個分割槽的資料。
c) 可以在不同級別上Compute Statistics：單個分割槽，子分割槽，全表，所有分割槽
d) 可以倒出統計資訊
e) 可以使用者自動收集統計資訊
2、DBMS_STATS的缺點
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的資訊，這兩個仍舊需要使用Analyze語句。
c) DBMS_STATS 預設不對索引進行Analyze，因為預設Cascade是False，需要手工指定為True
3、對於oracle 9裡面的External Table，Analyze不能使用，只能使用DBMS_STATS來收集資訊。

 

[Q]怎麼樣快速重整索引
[A]透過rebuild語句，可以快速重整或移動索引到別的表空間
rebuild有重建整個索引數的功能，可以在不刪除原始索引的情況下改變索引的儲存引數
語法為
alter index index_name rebuild tablespace ts_name
storage(……);
如果要快速重建整個使用者下的索引，可以用如下指令碼，當然，需要根據你自己的情況做相應修改
SQL> set heading off
SQL> set feedback off
SQL> spool d:\index.sql
SQL> SELECT 'alter index ' || index_name || ' rebuild '
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND wner = USER
SQL>spool off
另外一個合併索引的語句是
alter index index_name coalesce，這個語句僅僅是合併索引中同一級的leaf block
消耗不大，對於有些索引中存在大量空間浪費的情況下，有一些作用。

[Q]如何使用Hint提示
[A] 在select/delete/update後寫/*+ hint */
如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
注意/*和+之間不能有空格
如用hint指定使用某個索引

select /*+ index(cbotab) */ col1 from cbotab;
select /*+ index(cbotab cbotab1) */ col1 from cbotab;
select /*+ index(a cbotab1) */ col1 from cbotab a;
其中
TABLE_NAME是必須要寫的，且如果在查詢中使用了表的別名，在hint也要用表的別名來代替表名;
INDEX_NAME可以不必寫，Oracle會根據統計值選一個索引;
如果索引名或表名寫錯了，那這個hint就會被忽略;

 

[Q]怎麼樣能固定我的執行計劃
[A]可以使用OUTLINE來固定SQL語句的執行計劃
用如下語句可以建立一個OUTLINE
Create oe replace outline OutLn_Name on
Select Col1,Col2 from Table
where ……
如果要刪除Outline，可以採用
Drop Outline OutLn_Name;
對於已經建立了的OutLine，存放在OUTLN使用者的OL$HINTS表下面
對於有些語句，你可以使用update outln.ol$hints來更新outline
如update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
where ol_name in ('TEST1','TEST2');
這樣，你就把Test1 OUTLINE與Test2 OUTLINE互換了
如果想利用已經存在的OUTLINE，需要設定以下引數
Alter system/session set Query_rewrite_enabled = true
Alter system/session set use_stored_outlines = true

[Q]v$sysstat中的class分別代表什麼
[A]統計類別
1 代表事例活動
2 代表Redo buffer活動
4 代表鎖
8 代表資料緩衝活動
16 代表OS活動
32 代表並行活動
64 代表表訪問
128 代表除錯資訊

 

[Q]怎麼快速查詢鎖與鎖等待
[A]資料庫的鎖是比較耗費資源的，特別是發生鎖等待的時候，我們必須找到
發生等待的鎖，有可能的話，殺掉該程式。
這個語句將查詢到資料庫中所有的DML語句產生的鎖，還可以發現，任何DML
語句其實產生了兩個鎖，一個是表鎖，一個是行鎖。
可以透過alter system kill session ‘sid,serial#’來殺掉會話
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
如果發生了鎖等待，我們可能更想知道是誰鎖了表而引起誰的等待
以下的語句可以查詢到誰鎖了表，而誰在等待。
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
以上查詢結果是一個樹狀結構，如果有子節點，則表示有等待發生。如果想知道
鎖用了哪個回滾段，還可以關聯到V$rollname，其中xidusn就是回滾段的USN

 

[Q]什麼是STATSPACK，我怎麼使用它？
[A]Statspack是Oracle 8i以上提供的一個非常好的效能監控與診斷工具，基本上全部包含了BSTAT/ESTAT的功能，更多的資訊
可以參考附帶文件$ORACLE_HOME/rdbms/admin/spdoc.txt。
安裝Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- 解除安裝，第一次可以不需要
sqlplus "/ as sysdba" @spcreate.sql -- 需要根據提示輸入表空間名
使用Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- 進行資訊收集統計，每次執行都將產生一個快照號
-- 獲得快照號，必須要有兩個以上的快照，才能生成報表
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql -- 輸入需要檢視的開始快照號與結束快照號
其他相關指令碼s:
spauto.sql - 利用dbms_job提交一個作業，自動的進行STATPACK的資訊收集統計
sppurge.sql - 清除一段範圍內的統計資訊，需要提供開始快照與結束快照號
sptrunc.sql - 清除(truncate)所有統計資訊