Daily sql collection -Updated on Mar 27th,2013-优快云博客

本文链接：https://blog.youkuaiyun.com/duqiangatom/article/details/8725914

// Undo 空间管理

select status,sum(bytes/1024/1024) from dba_undo_extents/* where tablespace_name='UNDOTBS1'*/ group by status; --active,expired,
select tablespace_name,sum(bytes/1024/1024) from dba_free_space where tablespace_name='UNDOTBS1' group by tablespace_name;
select bytes/1024/1024 from dba_data_files where tablespace_name like '%UNDO%';

//session 使用的undo

select b.sid ,value "undo change vector size"
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and a.name ='undo change vector size' ;-- 'redo size';

//具体undo block有4种状态
//1.active表示使用这个undo block的 transaction还未提交
//2.inactive:undo block上没有活动事务，可以被覆盖（但是未过期expired）
//3.expired:undo block状态为inactive的时间超过了undo_retention
//4.free:空的

ADDM instance 级别问题；AWR+SQL ADVISOR+SQL ACCESS ADVISOR +10046+ sql_trace 查APP问题；ASH活动会话级别问题。

ADDMRPT.SQL,AWRRPT.SQL,ASHRPT.SQ( ashrpti.sql Rac instance)

//bdump

alter session set events 'immediate trace name systemstate level 10';

alter session set events 'immediate trace name HANGANALYZE level 3';

//激活10046事件诊断sql问题

//通过设置10046事件来进行SQL跟踪，并且可以设置不同的跟踪级别，比使用SQL_TRACE获得更多的信息。

Level 0   停用SQL跟踪，相当于SQL_TRACE=FALSE
Level 1   标准SQL跟踪，相当于SQL_TRACE=TRUE
Level 4   在level 1的基础上增加绑定变量的信息
Level 8   在level 1的基础上增加等待事件的信息
Level 12 在level 1的基础上增加绑定变量和等待事件的信息

10046事件不但可以跟踪用户会话(trace文件位于USER_DUMP_DEST )，也可以跟踪background进程(trace文件位于BACKGROUND_DUMP_DEST )。
trace文件的大小决定于4个因素：跟踪级别，跟踪时长，会话的活动级别和MAX_DUMP_FILE_SIZE参数。

//启用跟踪事件10046

0.在全局设置/修改初始化参数

EVENT = "10046 trace name context forever, level 8"

1.在当前session设置

alter session set tracefile_identifier = 'MyTrace'; 或

select tracefile,p.spid
from v$process p, v$session s
where s.paddr = p.addr
and s.sid = (select sid from v$mystat where rownum = 1);

alter session set events '10046 trace name context forever, level 8';
alter session set events '10046 trace name context off';

2.对其他用户session设置

--首先通过V$PROCESS获得该session的os process id。

select s.username, p.spid os_process_id, p.pid Oracle_process_id
from v$session s, v$process p
where s.paddr = p.addr and s.username = upper('TRACE_USERNAME');

oradebug setospid 4336;
oradebug unlimit;
oradebug event 10046 trace name context forever, level 8;
oradebug event 10046 trace name context off;

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/opt/oracle/product/9.2.0/rdbms/log/uxdb_ora_9183.trc --获取跟踪文件

--首先获得要跟踪的session的session id和serial number

select sid,serial#,username from v$session where username='TRACE_USERNAME';
alter session set tracefile_identifier = 'MyTrace';

exec dbms_support.start_trace_in_session(sid => 13,serial# => 15,waits => true,binds => true);
exec dbms_support.stop_trace_in_session(sid => 1234,serial# => 56789);

或者

exec dbms_system.set_ev( 13, 15, 10046, 8, '');
exec dbms_system.set_ev( 13, 15, 10046, 0, '');

或者

exec dbms_monitor.session_trace_enable(session_id => 13,serial_num => 15,waits => true,binds => true);
exec dbms_monitor.session_trace_disable(session_id => 13,serial_num => 15);

3.查看当前session的跟踪级别

SQL>Set serveroutput on
SQL> declare
     event_level number;
     begin
     dbms_system.read_ev(10046,event_level);
     dbms_output.put_line(to_char(event_level));
     end;
     /

--1.实例用于工作和等待的时间比率(最近1，2分钟或半分钟)：
select begin_time,
       end_time,
       round((end_time - begin_time) * 60 * 60 * 24, 2) || 's' as Duration,
       metric_name,
       round(value, 3) value,
       metric_unit
from v$sysmetric
where metric_name in ('Database CPU Time Ratio',
                       'Database Wait Time Ratio',
                       'Disk Sort Per Sec',
                       'Memory Sorts Ratio',
                       'Buffer Cache Hit Ratio',
                       'Physical Reads Per Sec',
                       'Redo Generated Per Sec',
                       'Library Cache Hit Ratio',
                       'Row Cache Hit Ratio',
                       'Soft Parse Ratio')
   and INTSIZE_CSEC = (select max(intsize_csec) from v$sysmetric)
order by metric_id desc;

--1.1 有等待的活动会话or当前有等待的session
--1) v$session as datasource
SELECT inst_id, event,state,sid,machine,program,username,p1text,p1,p2text,p2,p3text,p3,blocking_session,seconds_in_wait,wait_time
FROM gv$session
WHERE state IN ('WAITING')
AND wait_class != 'Idle'
and event not like 'PX%';

--Parameter meaning
select t.name,t.event_id,t.wait_class,t.parameter1, t.parameter2,t.parameter3 from v$event_name t where t.name='&event_name';

--2)v$session_wait as datasource
select wait_class,event, sid, state, wait_time, seconds_in_wait,t.p1text,t.p1, t.p2text, t.p2, t.p3text,t.p3
from v$session_wait t
where wait_class <> 'Idle'
order by wait_class, event, sid;

--1.1.1 锁等待信息（以上查询结果是一个树状结构，如果有子节点，则表示有等待发生)

SELECT /*+ rule */
(SELECT '''' || TT.SID || ',' || SS.SERIAL# || ''' (' || SS.MACHINE || ')'
    FROM V$LOCK TT, V$SESSION SS
   WHERE TT.BLOCK = 1
     AND TT.SID = SS.SID
     AND TT.ID1 = (SELECT T.ID1 FROM V$LOCK T WHERE S.LOCKWAIT = T.KADDR)) AS BLOCKED_BY_SID_SERIAL#,
decode(l.LOCKED_MODE,
        0,
        'None',
        1,
        'Null',
        2,
        'Row-S',
        3,
        'Row-X',
        4,
        'Share',
        5,
        'S/Row-X',
        6,
        'Exclusive',
        'Unknown') LockMode,
LPAD(' ', DECODE(L.XIDUSN, 0, 3, 0)) || L.ORACLE_USERNAME USER_NAME,
O.OWNER,
O.OBJECT_NAME,
S.MODULE,
S.MACHINE,
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;

--1.1.2 当前锁(XIDUSN,XIDSLOT,XIDSQN是活得锁后的事务槽信息)

SELECT a.inst_id,
       a.oracle_username,
       c.sid,
       c.machine,
       c.osuser,
       decode(a.locked_mode,
              0,
              'None',
              1,
              'Null',
              2,
              'Row share',
              3,
              'Row Exclusive',
              4,
              'Share',
              5,
              'Share Row Exclusive',
              6,
              'Exclusive') lock_type,
       b.owner,
       b.object_name,
       a.xidusn,
       a.xidslot,
       a.xidsqn
FROM gv$locked_object a, all_objects b, gv$session c
WHERE a.object_id = b.object_id
   AND a.inst_id = c.inst_id
   and a.session_id = c.sid;

--1.2 15min内最重要的等待事件

select a.event, sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a
where a.sample_time between sysdate - 30 / 2880 and sysdate
group by a.event
order by total_wait_time desc;

--1.2.1 15min 内等待最多的用户

select s.sid,
         s.username,
         sum(a.wait_time + a.time_waited) total_wait_time
    from v$active_session_history a, v$session s
   where a.sample_time between sysdate - 15/60*24 and sysdate
     and a.session_id = s.sid
   group by s.sid, s.username
   order by total_wait_time desc;

--1.2.2 实例中等待最多的sql

select a.user_id,
         d.username,
         s.sql_text,
         sum(a.wait_time + a.time_waited) total_wait_time
    from v$active_session_history a, v$sqlarea s, dba_users d
   where a.sample_time between sysdate - 15/60*24 and sysdate
     and a.sql_id = s.sql_id
     and a.user_id = d.user_id
   group by a.user_id, d.username, s.sql_text
   order by total_wait_time desc;

--1.3 最多的等待对象及这些对象在近15分钟内等待事件类型

select o.object_name,
       o.object_type,
       a.event,
       sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a, dba_objects o
where a.sample_time between sysdate -15/60*24 and sysdate
   and a.current_obj# = o.object_id
group by o.object_name, o.object_type, a.event
order by total_wait_time;

--2.1实例总等待按等待类的分布百分比（等待次数+等待时间(百分之一秒))

select wait_class,
       total_waits,
       --sum_waits,
       round(100 * (total_waits / sum_waits), 2) pct_tot_waits,
       round((time_waited / 100), 2) time_waited_sec,
       --round((sum_time / 100), 2) as sum_time_sec,
       round(100 * (time_waited / sum_time), 2) pct_time
from (select wait_class, total_waits, time_waited
          from v$system_wait_class
         where wait_class != 'Idle'),
       (select sum(total_waits) sum_waits, sum(time_waited) sum_time
          from v$system_wait_class
         where wait_class != 'Idle')
order by pct_time desc;

--2.2 各等待事件排行榜
select event,
       total_waits,
       round(time_waited / 100, 2) as time_wait_sec,
       round(average_wait / 100, 2) average_wait_sec
from v$system_event
where event not in ('pmon timer',
                     'smon timer',
                     'rdbms ipc reply',
                     'rdbms ipc message',
                     'parallel deque wait',
                     'virtual circuit',
                     '%SQL*Net%',
                     'client message',
                     'NULL event',
                     '%idle%')
order by time_waited desc;

-- 正在运行的大session执行百分比和预计完成时间
col sid format 999999 col opname format a30 col target format a20 col pct format a10 set lines 500

    select sid,
         serial#, SOFAR, TOTALWORK,
         round((sofar / totalwork) * 100, 2) || '%' pct,
         opname,
         target,
         round(time_remaining / 60, 2) minutes
    from v$session_longops
   where round((sofar / totalwork) * 100, 2) != '100'
     and totalwork <> sofar
      AND TOTALWORK != 0;

--当前所有用户级会话列表(除去系统级)

select inst_id,
       saddr,
       paddr,
       sid,
       username,
       status,
       server,
       machine,
       program,
       logon_time,
       event,
       p1,
       p2
from gv$session
WHERE nvl(username, 'SYS_BG') <> 'SYS_BG'
   and status = 'ACTIVE';

SELECT *
FROM v$session
WHERE nvl(username, 'SYS_BG') <> 'SYS_BG'
and program = 'dllhost.exe' --osuser <> 'SYSTEM'
ORDER BY status, terminal;

--当前有活动dblink的session

SELECT b.NAME, a.*
FROM v$sesstat a, v$statname b
WHERE a.sid = 271
AND a.statistic# = b.statistic#
AND b.NAME LIKE '%dblink%';

--长时间session集中的对象：

SELECT target, COUNT(*) FROM V$SESSION_LONGOPS t GROUP BY t.target;

--未执行完的正在执行的操作:
SELECT * FROM V$SESSION_LONGOPS t WHERE sofar <> totalwork;

--OSSTAT
select * from v$osstat order by value desc;

--会话的高负载事件：
SELECT b.NAME, a.VALUE
FROM v$sesstat a, v$statname b
WHERE a.sid = &sid --sys_context('userenv','sid')
AND a.statistic# = b.statistic#
AND a.VALUE > 10
ORDER BY VALUE DESC;

--全表（索引）扫描：
SELECT obj.owner,
       obj.object_name,
       COUNT(*),
       (SELECT round(SUM(seg.bytes) / 1024 / 1024, 2)
          FROM dba_segments seg
         WHERE seg.owner = obj.owner
           AND seg.segment_name = obj.object_name) objsize_m
FROM dba_objects obj, sys.x$bh xbh
WHERE obj.object_type IN ('TABLE', 'INDEX')
   AND xbh.obj = obj.data_object_id
   AND bitand(xbh.flag, 524288) > 0
   AND obj.owner IN ('UNAME')
GROUP BY obj.owner, obj.object_name
ORDER BY 3 DESC;

--热点对象(9i after)：
SELECT *
FROM v$segment_statistics
WHERE owner = upper('helios')
ORDER BY VALUE DESC;

--查询一个存储过程是否在运行
select *
from v$db_object_cache
where locks > 0
and pins > 0
and type = 'PROCEDURE'

--==某个session情况（Linux top 诊断高负载session进程得出spid后）==--

--查询session当前、前一个执行的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; --os thread id as input

--某会话正在执行的SQL：
SELECT sid, sql_text
FROM v$session s, v$sql q
WHERE sid IN (&sid)
AND (q.sql_id = s.sql_id OR q.sql_id = s.prev_sql_id);

--OS thread ID当前活动会话执行的SQL
SELECT s.sid, q.sql_text, s.machine, s.program, q.OUTLINE_CATEGORY
FROM v$session s, v$sql q
WHERE nvl(s.username, 'SYS') <> 'SYS'
and s.status = 'ACTIVE'
AND (q.sql_id = s.sql_id OR q.sql_id = s.prev_sql_id);

SELECT A.SID, A.SERIAL#
FROM V$SESSION A, V$PROCESS B
WHERE A.PADDR = B.ADDR
AND B.SPID = &SPID; --OS thread id as input

--session当前正在等待事件
SELECT T.EVENT,
       SUM(DECODE(T.WAIT_TIME, 0, 1, 0)) CURR,
       SUM(DECODE(T.WAIT_TIME, 0, 0, 1)) PREV,
       COUNT(*) TOTAL
FROM V$SESSION_WAIT T
WHERE T.SID = &SID
   AND T.EVENT NOT IN ('rdbms ipc message',
                       'SQL*Net message from client',
                       'smon timer',
                       'pmon timer',
                       'wakeup time manager')
GROUP BY T.EVENT
HAVING SUM(DECODE(T.WAIT_TIME, 0, 1, 0)) > 0
ORDER BY 2 DESC;

--===Session级别top统计===---

--session自启动以来等待事件统计信息(时间单位1/100s)

SELECT S.USERNAME,
       S.PROGRAM,
       S.STATUS,
       /*SE.EVENT,
       SE.TOTAL_WAITS,
       SE.TOTAL_TIMEOUTS,
       SE.TIME_WAITED,
       SE.AVERAGE_WAIT*/
       SE.*
FROM V$SESSION S, V$SESSION_EVENT SE
WHERE S.SID = SE.SID
   AND SE.EVENT NOT LIKE 'SQL*Net%'
   AND S.STATUS = 'ACTIVE'
   AND S.USERNAME IS NOT NULL
ORDER BY 2 ASC, 6 DESC;

--查看占用系统io较大的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;

--找出耗cpu较多的session

SELECT A.SID,
       SPID,
       STATUS,
       SUBSTR(A.PROGRAM, 1, 40) PROG,
       A.TERMINAL,
       OSUSER,
       ROUND(VALUE / 60 / 100, 4) VALUE_MINS
FROM V$SESSION A, V$PROCESS B, V$SESSTAT C
WHERE C.STATISTIC# = 12
   AND C.SID = A.SID
   AND A.PADDR = B.ADDR
ORDER BY VALUE DESC;

--Top 10 by Buffer Gets的SQL(使用hash算法从内存读取，耗cpu)

SELECT *
FROM (SELECT substr(sql_text, 1, 40) sql,
               buffer_gets,
               executions,
               buffer_gets / executions "Gets/Exec",
               hash_value,
               address
          FROM V$SQLAREA
         WHERE buffer_gets > 10000
         ORDER BY buffer_gets DESC)
WHERE rownum <= 10;

--Top 10 by Physical Reads SQL（磁盘io多)

SELECT *
FROM (SELECT substr(sql_text, 1, 40) sql,
               disk_reads,
               executions,
               disk_reads / executions "Reads/Exec",
               hash_value,
               address
          FROM V$SQLAREA
         WHERE disk_reads > 1000
         ORDER BY disk_reads DESC)
WHERE rownum <= 10;

--Top 10 by Parse Calls SQL:

SELECT *
FROM (SELECT substr(sql_text, 1, 40) sql,
               parse_calls,
               executions,
               hash_value,
               address
          FROM V$SQLAREA
         WHERE parse_calls > 1000
         ORDER BY parse_calls DESC)
WHERE rownum <= 10;

--Top 10 by Executions SQL:

SELECT *
FROM (SELECT substr(sql_text, 1, 40) sql,
               executions,
               rows_processed,
               rows_processed / executions "Rows/Exec",
               hash_value,
               address
          FROM V$SQLAREA
         WHERE executions > 100
         ORDER BY executions DESC)
WHERE rownum <= 10;