ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle性能问题 - 常用查询诊断及调整脚本[不断更新] (final)

Oracle性能问题 - 常用查询诊断及调整脚本[不断更新] (final)

原创 Linux操作系统 作者:tolywang 时间:2011-06-27 14:23:55 0 删除 编辑

 

一, 现象:系统运行缓慢,负载较高 


0.  SID, PID及SQL相关视图

-- 通过OS PID得到SID及通过SID得到OS PID :

SELECT pro.spid, sess.SID, sess.*  from v$session sess, v$process pro
where  sess.PADDR = pro.ADDR  and pro.spid=26457  ; 

SELECT pro.spid, sess.SID, sess.*  from v$session sess, v$process pro
where  sess.PADDR = pro.ADDR  and sess.sid=1077  ; 


-- 根据OS PID查出sid,serial# (kill session用得到)

select  s.sid, s.serial#  from v$session s, v$process p
where s.paddr=p.addr and p.spid='138482'
比如sid,serial#为482,56767


-- 结束session及OS中的process .
Alter system kill session 'SID,SERIAL#'  ; 
Kill -9  SPID    (用于unix,linux)
orakill  SID(oracle实例名称)  spid (windows)

orakill参数取得方式:
select instance_name from v$instance;

select s.sid, spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr;

 

 


-- 通过Oracle SID得到问题程序及不完整SQL

SELECT sid, serial#, osuser, username, status,
machine, terminal, program, type, sql_text
from v$session a, v$sql b 
where a.sql_address =b.address and  SID=&SID ;   

 

-- 通过SID, v$sqltext得到完整的SQL:

select /*+ ORDERED */ sql_text
from v$sqltext a
where (a.hash_value,a.ADDRESS) in
( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),
decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS)
from v$session b
where b.sid=&SID)
order by piece asc

或者

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

 


-- 通过OS PID找到完整SQL :

select /*+ ORDERED */ sql_text
from v$sqltext a
where (a.hash_value,a.ADDRESS) in
( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),
decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS)
from v$session b
where b.paddr = (select addr
from v$process c
where c.spid = '&spid')
)  order by piece asc 


或者

SELECT  sess.*,  sqlarea.SQL_TEXT
FROM V$session sess,V$process pro,V$sqlarea sqlarea
WHERE pro.SPID=&pid
AND sess.PADDR = pro.ADDR
AND sess.SQL_ADDRESS = sqlarea.ADDRESS

 

 

1. 实时查询长时间操作的SQL(或者通过OEM图形界面查看)

使用视图 V$SESSION_LONGOPS,查询运行超过6秒或某些操作block数达到一定数量的操作,
这些操作也包括很多备份恢复功能,统计信息收集,查询操作,不同版本可能有更多的操
作加入 。

必须满足以下前提条件:
1) Oracle优化器使用 CBO;
2) 数据对象已经收集了统计信息;
3) 初始化参数 TIMED_STATISTICS = true 或是 SQL_TRACE = true ;


如果是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;

 

 


查看ASH视图进行诊断:


select * from v$active_session_history
where  sample_time>=to_date('2011-08-16 03:08:16','yyyy-mm-dd hh24:mi:ss')
and session_type='FOREGROUND'; 


SELECT * FROM V$SESSION_WAIT_CLASS WHERE SID=1986 ;


select to_char(sample_time,'yyyymmdd hh24:mi:ss.ffff') sample_time,session_id,sql_id,
session_state,event,wait_class,wait_time,time_waited
from   v$active_session_history
where  session_id=1986 and  sample_time>=to_date('2011-08-16 03:08:16','yyyy-mm-dd hh24:mi:ss')  
      order by sample_time;  
 

select session_state,event,wait_class,sum(wait_time),sum(time_waited)
from v$active_session_history
where  session_id=1986  and sample_time>=to_date('2011-08-16 03:08:16','yyyy-mm-dd hh24:mi:ss')   
group by session_state,event,wait_class;


select session_state,event,sum(wait_time),sum(time_waited)
from v$active_session_history
where session_id=1986 
group by session_state,event;

 


2. 10g或以上版本通过视图SYS.wrh$_active_session_history查询历史等待事件
及SQL。 查詢等待事件及TX锁表 :

select count(sid), event from v$session_wait group by event ; 


SELECT sid, serial#, osuser, username, status, sql_text
from v$session a, v$sql b 
where a.sql_address =b.address AND a.schemaname<>'SYS'
and a.status='ACTIVE'  order by username

 


注意: 因為awr表是默认每1小时执行一遍, 所以查询的不是实时信息. 


SELECT session_id, sql_id, program, machine,blocking_session,
       blocking_session_serial#
FROM   SYS.wrh$_active_session_history a, v$event_name b
WHERE  sample_time BETWEEN
          TO_DATE ('2011/06/24 07:20', 'yyyy/mm/dd hh24:mi')
      AND TO_DATE ('2011/06/24 07:40', 'yyyy/mm/dd hh24:mi')
      AND a.event_id = b.event_id 
ORDER BY SQL_ID ;


SELECT session_id, sql_id, program, blocking_session,
       blocking_session_serial#
FROM SYS.wrh$_active_session_history a, v$event_name b
WHERE sample_time BETWEEN TO_DATE ('2011/01/24 21:50', 'yyyy/mm/dd hh24:mi')
                       AND TO_DATE ('2011/01/24 22:35', 'yyyy/mm/dd hh24:mi')
   AND a.event_id = b.event_id
   AND b.NAME = 'enq: TX - row lock contention';


SELECT session_id, sql_id, program, blocking_session,blocking_session_serial#
FROM   SYS.dba_hist_active_sess_history  
WHERE sample_time BETWEEN TO_DATE ('2011/04/14 06:00', 'yyyy/mm/dd hh24:mi')
       AND TO_DATE ('2011/04/14 08:30', 'yyyy/mm/dd hh24:mi')
       AND session_type = 'FOREGROUND' ;

 


3.  如果发现I/O WAIT严重,查询最大的磁盘读的SQL语句

查询物理读写严重的SQL(每次执行)
SELECT *
  FROM  (SELECT  sql_text, module, address , 
         disk_reads / DECODE (executions, 0, 1, executions) AS tt
        FROM v$sqlarea a
        ORDER BY tt DESC)
 WHERE ROWNUM <= 20 
select * from v$sqltext  where  address = '0000000692664EC8'   order by piece ;

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

 SELECT   PARSING_USER_ID EXECUTIONS,
                     SORTS,
                     COMMAND_TYPE,
                     DISK_READS,
                     ROUND(DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS),1)
                         DISKREADS_PER_EXEC , 
                     ADDRESS,
                     MODULE,
                     sql_text
     FROM   v$sqlarea
 ORDER BY  DISKREADS_PER_EXEC  DESC  ;
 
 
select * from v$sqltext  where  address = '0000000692664EC8'   order by piece ;


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

  SELECT   c.*
    FROM   (SELECT   UPPER (b.username) username,
                     a.disk_reads disk_reads,
                     a.executions executions,
                     a.disk_reads / DECODE (a.executions, 0, 1, a.executions)
                        reads_per_exec,
                     a.address,
                     a.sql_id,
                     a.sql_text || CHR (10) || CHR (10) sql,
                     A.MODULE,
                     a.last_load_time last_time
             FROM   sys.gv_$sql a, dba_users b
             WHERE       a.parsing_user_id = b.user_id
                     AND a.disk_reads > 1000
                     AND b.username NOT IN ('SYS', 'SYSTEM')) c
   WHERE   c.reads_per_exec >= 1000  
ORDER BY   c.reads_per_exec DESC;

select * from v$sqltext  where  address = '0000000692664EC8'   order by piece ;


4. 通过top看到明显异常的PID, 通过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情况下查看正在运行的SQL .

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 ;

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

spid为top查看到的os段的process id .

紧急处理时候可以通过kill session方式或直接kill os process来结束进程。
查询当前运行的程序:

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

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

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

结束session及OS中的process .

Alter system kill session 'SID,SERIAL#'  ; 
Kill -9  SPID    (用于unix,linux)
orakill  sid(oracle_instance_name)  spid  (windows)

 


5. 查询Oracle库中的Lock (或通过Toad中session browser或OEM查看lock情况)
查看数据库中的锁(LOCK),找出程序及SQL

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 */
       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, q.sql_text
FROM v$session s, v$lock l, dba_objects o, v$open_cursor c, v$sql q
WHERE l.SID = s.SID
    AND l.id1 = o.object_id(+)
    AND s.username IS NOT NULL
    AND s.sql_address = c.address
    AND s.sql_hash_value = c.hash_value
    AND q.address = s.sql_address
    AND q.hash_value = s.sql_hash_value; 


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


谁被鎖住?
Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name
from v$session a, audit_actions b
where a.command=b.action
AND LOCKWAIT IS NOT NULL;

谁在鎖表?
Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name
from v$session a, audit_actions b
where a.command=b.action
AND STATUS='ACTIVE';

 

 

6.  查询物理读写严重的SQL及查询哪个SID最消耗资源(前提:timed_statistics=true )


查看占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 ;

 


7. 通过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小时的延迟,所以其采样信息并不能用于诊断数据
库的当前状态,但可以用来作为一段时期内数据库性能调整的参考。

 


8.  查询物件统计信息是否不是最新的。

select * from  dba_tables 
where wner='CHANNEL' and table_name='AC_BA_INVOICE' 
      and LAST_ANALYZED >= sysdate-40  
and status='VALID';

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

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

如果统计信息有误或者没有最新的统计信息,我们可以使用如下
语句手动统计。

exec dbms_stats.gather_schema_stats(ownname => 'DFMS',
   estimate_percent => dbms_stats.auto_sample_size,  
   method_opt => 'for all columns size auto',       
   cascade=>TRUE, degree => 8 ) ;

 


9. redo log都处于active状态。

select * from v$log ;
查看redo log是否都处于active状态,以及大小及组数是否需要调大 。

 


10. 绑定变量窥视(Peeking)问题

9i, 10g 绑定变量窥视使得执行计划出现变化 。11g有改善。如果出现此类
问题导致的性能问题, 需要升级到11g, 或者加入hint进行强制改变执行计划。

 


11. 坏块导致系统性能 (当然一般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)

 


12.  9i,10g buffer cache及Library Cache的命中率及其他 

9i buffer cache 命中率 : 
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 buffer cache 命中率 :   
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)');

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-700801/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 性能调整FAQ .
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13504671