一、主机

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执行总次数