Oracle性能问题一般排查方法

一、主机 1、cpu cpu高 一般为逻辑读高，不排除有些异常的逻辑读sql     工具         描述     uptime       平均负载     vmstat       包括系统范围的cpu平均负载     mpstat       查看所有cpu核信息     top          监控每个进程cpu用量     sar -u       查看cpu信息     pidstat      每个进程cpu用量分解     perf         cpu剖析和跟踪，性能计数分析   2、I/O I/O高 一般为物理(磁盘)读高，可能有全表扫等     工具       描述     iostat     磁盘详细统计信息     iotop      按进程查看磁盘IO的使用情况     pidstat    按进程查看磁盘IO的使用情况     perf       动态跟踪工具   3、内存 内存高 sga(buffer cache、share pool等) ，pga分配内存问题等     工具        描述     free        缓存容量统计信息     vmstat      虚拟内存统计信息     top         监视每个进程的内存使用情况     pidstat     显示活动进程的内存使用统计     pmap        查看进程的内存映像信息     sar -r      查看内存     dtrace      动态跟踪     valgrind    分析程序性能及程序中的内存泄露错误   二、数据库 1、等待事件(当前)     1）查看当前等待事件及数量，如果是库问题 优化参数或调整业务逻辑等，如果是sql问题 继续     col event for a58     select inst_id,event,count(*) from gv$session_wait     where wait_class not like 'Idle'     group by inst_id, event order by 3 desc;           --查询当前执行sql     SELECT b.inst_id,b.sid oracleID,            b.username 登录Oracle用户名,            b.serial#,            spid 操作系统ID,            paddr,            sql_text 正在执行的SQL,            sql_fulltext,            b.machine 计算机名,            b.EVENT,            'alter system kill session '''||b.sid||','||b.serial#||''';'     FROM gv$process a, gv$session b, gv$sql c     WHERE a.addr = b.paddr        AND b.sql_hash_value = c.hash_value        and a.inst_id=1        and b.inst_id=1        and c.inst_id=1        and b.status='ACTIVE'           2）带入等待事件，查到当前等待事件最多的sql     SELECT b.inst_id,b.sid oracleID,            b.username 登录Oracle用户名,            b.serial#,            spid 操作系统ID,            paddr,            sql_text 正在执行的SQL,            sql_fulltext,            b.machine 计算机名,            b.EVENT,            c.SQL_ID,            c.CHILD_NUMBER     FROM gv$process a, gv$session b, gv$sql c     WHERE a.addr = b.paddr        AND b.sql_hash_value = c.hash_value     and event like '%gc current request%'        and a.inst_id=1        and b.inst_id=1        and c.inst_id=1                 3）根据具体情况 优化此sql     （1）统计信息是否最新并且准确     --查看表统计时间     select * from dba_tables;           --收集table信息   并行8     SQL> execute dbms_stats.gather_table_stats(ownname=>'&owner',tabname=>'&table_name',cascade=>true,DEGREE=>8,no_invalidate=>false,granularity=>'ALL',method_opt=>'for all columns size 1',estimate_percent =>dbms_stats.auto_sample_size);           --分析收集schema信息     execute dbms_stats.gather_schema_stats(ownname=>'&owner',no_invalidate=>false,granularity=>'ALL',method_opt=>'for all columns size 1',degree=>10,cascade=>TRUE,estimate_percent=>dbms_stats.auto_sample_size);                 （2）无索引     查看sql中的表是否有索引，并确认是否可以用到索引     选择性是否好                 （3）隐式转换，查看历史sql的执行计划来判断是否隐式转换     执行计划           select sql_id,child_number,LAST_ACTIVE_TIME from v$sql where sql_id='&sql_id';           alter session set statistics_level=ALL;           col plan_table_output format A160     set linesize 300\r     col plan_table_output format A160\r     select * from table(dbms_xplan.DISPLAY_CURSOR('&sql_id', &child_number, 'ALL  LAST'));                 （4）硬解析等情况     select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE,            count(1) as counts       from v$sql     where FORCE_MATCHING_SIGNATURE > 0        and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE     group by FORCE_MATCHING_SIGNATURE     having count(1) > 50     order by 2 desc;                 select substr(sql_text,0,50),count(1)     FROM v$sqlarea     group by substr(sql_text,0,50)     order by 2 desc;           （5）其他情况等   2、等待事件(历史) 提取awr或者ash观察，或者直接用dba_hist_*视图查看如下：     --查看dba_hist_snapshot视图查看snap_id和时间的对应关系     select * from dba_hist_snapshot;           --创建快照     exec dbms_workload_repository.create_snapshot();           --查看等待事件的sql_id排行     col event for a50     select * from (     select event,sql_id,count(1) from dba_hist_active_sess_history t     where snap_id between &snap_id_start   and &snap_id_end        and event is not null        and sql_id is not null     group by event,sql_id     order by count(1) desc     )     where  rownum<20;           ----历史等待事件及sql     with tmp as     (     select t.instance_number,t.event,t.sql_id,count(1) cnt       from dba_hist_active_sess_history t     where t.snap_id between 7104 and  7105        and t.event like '%ITL%'        and t.sql_id is not null     group by instance_number,event,t.sql_id     )     select t1.*,t2.sql_text        from tmp t1,dba_hist_sqltext t2       where t1.sql_id = t2.sql_id     order by cnt desc           --查看sql绑定变量     set linesize 400     col NAME for a10     col value_string for a70     select distinct instance_number,            sql_id,            name,            datatype_string,            last_captured,            value_string       from dba_hist_sqlbind t     where sql_id = '&slq_id'        and t.SNAP_ID between &snap_id_start and &snap_id_end        and instance_number = &inst_id     order by LAST_CAPTURED;           -----快照时间的sql执行时间及逻辑读物理读的统计     SELECT T.SQL_ID,              T.EXECUTIONS_DELTA EXEC_CNT,  --快照时间内执行总次数              ROUND(ELAPSED_TIME_DELTA / 1000000, 2) EXEC_TIME,  --快照时间内执行总时间  秒              ROUND(ELAPSED_TIME_DELTA /                    DECODE(T.EXECUTIONS_DELTA, 0, 1, T.EXECUTIONS_DELTA) /                    1000000,                    2) EXEC_PER,   --快照时间内执行平均时间  秒              PARSING_SCHEMA_NAME SCHEMA,              ROUND(DISK_READS_DELTA ,2) DISK_READS,  ----快照时间内 磁盘读 io高              ROUND(BUFFER_GETS_DELTA , 2) BUFFER_GETS,  ----快照时间内 逻辑读 cpu高              T1.SQL_TEXT,              to_char(substr(T1.SQL_TEXT,0,80))         FROM DBA_HIST_SQLSTAT T         JOIN DBA_HIST_SQLTEXT T1           ON T.SQL_ID = T1.SQL_ID        WHERE T.SNAP_ID = 6825  --快照时间          AND T.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM')          AND T.INSTANCE_NUMBER = 1  --节点          AND T.EXECUTIONS_DELTA >= 0 --快照时间内 sql执行总次数