ITPub博客

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

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

原创 Linux操作系统 作者:tolywang 时间:2011-06-23 17:57:22 0 删除 编辑


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. 10g或以上版本通过视图SYS.wrh$_active_session_history查询历史等待事件及SQL 

查詢等待事件及TX锁表 

 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;


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'
       -- AND  event =  '' ; 


SELECT * FROM  v$event_name

 

 

 

 

1.获取最大的磁盘读的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 < 20;

 

 


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或OEM参看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 ;  

 


软硬解析数 :

select  a.value as total_Parse, b.value as hard_parse,
a.value - b.value as soft_parse
from v$mystat a , v$mystat b,v$statname aa , v$statname bb
where aa.name = 'parse count (total)'
and bb.name = 'parse count (hard)'
and aa.statistic# = a.statistic#
and bb.statistic# = b.statistic# ;

 

 


查询坏块:

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)

 


查询当前运行的程序:

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 () ;  


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

Alter system kill session 'SID,SERIAL#'  ; 

Kill -9  SPID   (unix,linux)

orakill  sid(oracle_instance_name)  spid  (windows)

 

 

 


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,


 

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

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

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13505634