ORACLE 常用的查询操作

等待事件

查询数据库等待时间和实际执行时间的相对百分比

select *
  from v$sysmetric a
 where a.METRIC_NAME in
       ('Database CPU Time Ratio', 'Database Wait Time Ratio')
   and a.INTSIZE_CSEC = (select max(intsize_csec) from v$sysmetric);

查询数据库中过去30分钟引起最多等待的sql语句

select ash.USER_ID,
       u.username,
       sum(ash.WAIT_TIME) ttl_wait_time,
       s.SQL_TEXT
  from v$active_session_history ash, v$sqlarea s, dba_users u
 where ash.SAMPLE_TIME between sysdate - 60 / 2880 and sysdate
   and ash.SQL_ID = s.SQL_ID
   and ash.USER_ID = u.user_id
 group by ash.USER_ID, s.SQL_TEXT, u.username
 order by ttl_wait_time desc

查询数据库中的等待事件

select event, count(*)
  from v$session_wait
 group by event
 order by count(*) desc

利用等待事件查询导致等待的sql

select sql_text
  from V$sqlarea
 where (address, hash_value) in
       (select sql_address, sql_hash_value
          from v$session
         where event like '%sequential%');
         

select sql_address, sql_hash_value
  from v$session
 where event like '%sequential%'

查询数据库过去15分钟最重要的等待事件

select ash.EVENT, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time
  from v$active_session_history ash
 where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
 group by event
 order by total_wait_time desc

在过去15分钟哪些用户经历了等待

select s.SID,
       s.USERNAME,
       sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time
  from v$active_session_history ash, v$session s
 where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
   and ash.SESSION_ID = s.SID
 group by s.SID, s.USERNAME
 order by total_wait_time desc;

查询等待时间最长的对象

select a.CURRENT_OBJ#,
       d.object_name,
       d.object_type,
       a.EVENT,
       sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time
  from v$active_session_history a, dba_objects d
 where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
   and a.CURRENT_OBJ# = d.object_id
 group by a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT
 order by total_wait_time desc;

查询过去15分钟等待时间最长的sql语句

select a.USER_ID,
       u.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 u
 where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
   and a.SQL_ID = s.SQL_ID
   and a.USER_ID = u.user_id
 group by a.USER_ID, s.SQL_TEXT, u.username
 order by total_wait_time desc;

那些SQL消耗更多的IO

select *
  from (select s.PARSING_SCHEMA_NAME,
               s.DIRECT_WRITES,
               substr(s.SQL_TEXT, 1, 500),
               s.DISK_READS
          from v$sql s
         order by s.DISK_READS desc)
 where rownum < 20

查看哪些会话正在等待IO资源

SELECT username, program, machine, sql_id
  FROM V$SESSION
 WHERE EVENT LIKE 'db file%read';

查看正在等待IO资源的对象

SELECT d.object_name, d.object_type, d.owner
  FROM V$SESSION s, dba_objects d
 WHERE EVENT LIKE 'db file%read'
   and s.ROW_WAIT_OBJ# = d.object_id

查看redo日志切换频率

Select round(FIRST_TIME, 'DD'), THREAD#, Count(SEQUENCE#)
  From v$log_history
 Group By round(FIRST_TIME, 'DD'), THREAD#
 Order By 1, 2

SELECT trunc(first_time) "Date",
       to_char(first_time, 'Dy') "Day",
       count(1) "Total",
       SUM(decode(to_char(first_time, 'hh24'), '00', 1, 0)) "h0",
       SUM(decode(to_char(first_time, 'hh24'), '01', 1, 0)) "h1",
       SUM(decode(to_char(first_time, 'hh24'), '02', 1, 0)) "h2",
       SUM(decode(to_char(first_time, 'hh24'), '03', 1, 0)) "h3",
       SUM(decode(to_char(first_time, 'hh24'), '04', 1, 0)) "h4",
       SUM(decode(to_char(first_time, 'hh24'), '05', 1, 0)) "h5",
       SUM(decode(to_char(first_time, 'hh24'), '06', 1, 0)) "h6",
       SUM(decode(to_char(first_time, 'hh24'), '07', 1, 0)) "h7",
       SUM(decode(to_char(first_time, 'hh24'), '08', 1, 0)) "h8",
       SUM(decode(to_char(first_time, 'hh24'), '09', 1, 0)) "h9",
       SUM(decode(to_char(first_time, 'hh24'), '10', 1, 0)) "h10",
       SUM(decode(to_char(first_time, 'hh24'), '11', 1, 0)) "h11",
       SUM(decode(to_char(first_time, 'hh24'), '12', 1, 0)) "h12",
       SUM(decode(to_char(first_time, 'hh24'), '13', 1, 0)) "h13",
       SUM(decode(to_char(first_time, 'hh24'), '14', 1, 0)) "h14",
       SUM(decode(to_char(first_time, 'hh24'), '15', 1, 0)) "h15",
       SUM(decode(to_char(first_time, 'hh24'), '16', 1, 0)) "h16",
       SUM(decode(to_char(first_time, 'hh24'), '17', 1, 0)) "h17",
       SUM(decode(to_char(first_time, 'hh24'), '18', 1, 0)) "h18",
       SUM(decode(to_char(first_time, 'hh24'), '19', 1, 0)) "h19",
       SUM(decode(to_char(first_time, 'hh24'), '20', 1, 0)) "h20",
       SUM(decode(to_char(first_time, 'hh24'), '21', 1, 0)) "h21",
       SUM(decode(to_char(first_time, 'hh24'), '22', 1, 0)) "h22",
       SUM(decode(to_char(first_time, 'hh24'), '23', 1, 0)) "h23"
  FROM V$log_history
 group by trunc(first_time), to_char(first_time, 'Dy')
 Order by 1

事物与锁

查找堵塞的sql语句

select *
  from (select d.BLOCKING_SESSION,
               b.*,
               c.object_id,
               c.object_type,
               c.object_name,
               c.owner,
               d.client_info,
               d.MACHINE,
               d.status,
               d.TERMINAL,
               d.PROCESS,
               d.EVENT,
               d.WAIT_TIME,
               -- d.BLOCKING_SESSION, 
               d.sql_id,
               d.prev_sql_id,
               e.SQL_TEXT
          from v$locked_object a,
               v$lock          b,
               dba_objects     c,
               v$session       d,
               v$sql           e
         where a.session_id = b.sid
           and a.session_id = d.sid
           and a.object_id = c.object_id
           and d.sql_id = e.sql_id)
 where blocking_session <> 0
    or request <> 0
    or block <> 0

根据堵塞ID找到堵塞sql，（sql_id：堵塞sql）

 select BLOCKING_SESSION,prev_sql_id,sql_id,MACHINE ,a.* from v$session a  where a.sid in ('783'）

查看堵塞的sql(sql_id)

select dbms_lob.substr(sql_fulltext) ,sql_id from v$sql where sql_id in ('4u7v9vsq5wzrt')

根据第一个查找堵塞sql找到需杀进程（SID和SERIAL#字段复制，SID在前面，SERIAL#在后面）

  alter system kill session '2010,11599' immediate;

select * from t_automaticmatching_thread t where t.autosyn is not null

查看数据库产生锁的SID,SERIAL#,表名，用户名和锁的级别。

select s.sid,
       s.serial#,
       s. username,
       l.locked_mode,
       o.object_name,
       o.owner,
       s.machine
  from v$locked_object l, v$session s, dba_objects o
 where l.session_id = s.sid
   and l.object_id = o.object_id

查被锁的对象

select l.xidusn, l.object_id, o.owner, o.object_name, 
l.session_id, l.oracle_username, l.os_user_name, l.process, 
decode(l.locked_mode, 0, '',
        1, 'NULL',
        2, '(SS)',
        3, '(SX)',
        4, '(S)',
        5, '(SSX)',
        6, '(X)',
        '???') locked_mode
from v$locked_object l, dba_objects o
where l.object_id = o.object_id;

解锁

alter system kill session '301,60720'  

根据sid 查sql_text

select sql_text
  from v$sqlarea a, v$session b
 where a.SQL_ID = b.PREV_SQL_ID
   and b.SID =2199;

产生锁的sql

select sql_text
  from v$sql
 where hash_value in
       (select sql_hash_value
          from v$session
         where sid in (select session_id from v$locked_object))

select s.sid, s.serial#, s.machine, s.username, sq.sql_text
  from v$session s, v$locked_object l, v$sql sq
 where s.sid = l.session_id
   and sq.hash_value = s.sql_hash_value;

alter system kill session '11,33216' immediate;

查看会话阻塞–谁阻塞了谁（单机）

SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||
       ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
       s2.sid || ' ) ' AS blocking_status
  FROM v$lock l1, v$session s1, v$lock l2, v$session s2
 WHERE s1.sid = l1.sid
   AND s2.sid = l2.sid
   AND l1.BLOCK = 1
   AND l2.request > 0
   AND l1.id1 = l2.id1
   AND l2.id2 = l2.id2;

数据库资源消耗

SELECT b.SQL_FULLTEXT,
       B.SQL_TEXT,
       'alter system kill session ''' || A.SID || ',' || A.SERIAL# || '''' ||
       ' immediate;' || '',
       A.USERNAME,
       A.COMMAND,
       A.STATUS,
       A.PROGRAM,
       A.MACHINE,
       A.CLIENT_IDENTIFIER,
       B.CPU_TIME,
       B.SHARABLE_MEM,
       ROUND((B.DISK_READS + B.BUFFER_GETS) / B.EXECUTIONS) AS RESOURCE_COST
  FROM V$SESSION A, V$SQLSTATS B
 WHERE A.SQL_ID = B.SQL_ID
   AND B.EXECUTIONS > 0
   AND A.WAIT_CLASS <> 'Idle'
 ORDER BY RESOURCE_COST DESC;

SELECT s.username, 
       l.OBJECT_ID,
       l.SESSION_ID,
       s.SERIAL#，
       l.ORACLE_USERNAME,
       l.OS_USER_NAME,
       l.PROCESS
  FROM V$LOCKED_OBJECT l, V$SESSION S
 WHERE l.SESSION_ID = S.SID;

解锁

alter system kill session '1339,428'  

批量解锁

declare
  cursor mycur is
    select b.sid,
           b.serial# 　　from v$locked_object a,
           v$session b 　　where a.session_id = b.sid group by b.sid,
           b.serial#;
begin
  for cur in mycur loop
    execute immediate ('alter system  kill session  ''' || cur.sid || ',' ||
                      cur.SERIAL# || ''' ') || 'immediate';
  end loop;
end;

查看会话连接用户名 --应用程序名–机器名–操作系统用户–登录时间

select sid, serial#, username, program, machine, osuser, logon_time
  from v$session;

oracle中正在执行的sql

select a.program, b.spid, c.sql_text, c.SQL_ID
  from v$session a, v$process b, v$sqlarea c
 where a.paddr = b.addr
   and a.sql_hash_value = c.hash_value
   and a.username is not null;

查询Oracle正在执行的sql语句及执行该语句的用户

SELECT b.sid, ---- oracleID,  
       b.username, ---- Oracle用户,  
       b.serial#,
       spid, ------ 操作系统ID,  
       paddr,
       sql_text, ----- 正在执行的SQL,  
       b.machine ----- 计算机名  
  FROM v$process a, v$session b, v$sqlarea c
 WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value;

SELECT b.sid, b.username, b.serial#, spid, paddr, sql_text, b.machine
  FROM v$process a, v$session b, v$sqlarea c
 WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value;

看正在执行sql的发起者的发放程序

SELECT A.serial#,
       OSUSER, ----电脑登录身份,
       PROGRAM, ----发起请求的程序,  
       USERNAME, ----登录系统的用户名,  
       SCHEMANAME,
       B.Cpu_Time, ---花费cpu的时间,  
       STATUS,
       B.SQL_TEXT ------执行的sql  
  FROM V$SESSION A
  LEFT JOIN V$SQL B
    ON A.SQL_ADDRESS = B.ADDRESS
   AND A.SQL_HASH_VALUE = B.HASH_VALUE
 ORDER BY b.cpu_time DESC

查看正在执行的sql的进度

SELECT SE.SID,
       OPNAME,
       TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
       ELAPSED_SECONDS ELAPSED,
       ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
       SQL_TEXT
  FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
 WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
   AND SL.SID = SE.SID
   AND SOFAR != TOTALWORK
 ORDER BY START_TIME;

select t2.username, t2.sid, t2.serial#, t2.logon_time
  from v$locked_object t1, v$session t2
 where t1.session_id = t2.sid
 order by t2.logon_time;

查看最耗时的sql

select *
  from (select v.sql_id,
               v.child_number,
               v.sql_text,
               v.elapsed_time,
               v.cpu_time,
               v.disk_reads,
               rank() over(order by v.cpu_time desc) elapsed_rank
          from v$sql v) a
 where elapsed_rank <= 20;

查看大事务

select start_date,addr,used_urec,used_ublk from v$transaction;

select sql_child_number,
       sid,
       sql_id,
       last_call_et,
       blocking_session,
       blocking_instance,
       state,
       event,
       p1,
       p2,
       seconds_in_wait
  from v$session
 where taddr = '0000000A678163E0';

根据sql_id 查看sql的执行计划

select * from table(dbms_xplan.display_cursor('f2d30pd3wm699'));
select sql_text from v$sql where sql_id = 'f2d30pd3wm699';
select sql_id, sql_text from v$sql where sql_id = 'f2d30pd3wm699'

select a.program, b.spid, c.sql_text, c.SQL_ID
  from v$session a, v$process b, v$sqlarea c
 where a.paddr = b.addr
   and a.sql_hash_value = c.hash_value
   and a.username is not null;

查看当前等待事件

select event, sql_id, count(*)
  from v$session
 where wait_class <> 'Idle'
 group by event, sql_id
 order by 1, 2, 3;

历史等待事件视图
dba_hist_active_sess_history

–查看会话阻塞–谁阻塞了谁（单机）

SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||
       ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
       s2.sid || ' ) ' AS blocking_status
  FROM v$lock l1, v$session s1, v$lock l2, v$session s2
 WHERE s1.sid = l1.sid
   AND s2.sid = l2.sid
   AND l1.BLOCK = 1
   AND l2.request > 0
   AND l1.id1 = l2.id1
   AND l2.id2 = l2.id2;

根据sid 查sql_text

select sql_text
  from v$sqlarea a, v$session b
 where a.SQL_ID = b.PREV_SQL_ID
   and b.SID = 1813;

通过sid找到阻塞文本

SELECT sql_id, sql_text FROM v$sqltext WHERE sql_id = '485' ORDER BY piece;

查看阻塞对象信息

SELECT owner, object_name, object_type
  FROM dba_objects
 WHERE object_id in (SELECT object_id FROM v$locked_object);

SELECT sid, total_waits, time_waited
  FROM v$session_event
 WHERE event = 'db file sequential read'
   and total_waits > 0
 ORDER BY 3, 2;

select ash.USER_ID,
       u.username,
       sum(ash.WAIT_TIME) ttl_wait_time,
       s.SQL_TEXT
  from v$active_session_history ash, v$sqlarea s, dba_users u
 where ash.SAMPLE_TIME between sysdate - 60 / 2880 and sysdate
   and ash.SQL_ID = s.SQL_ID
   and ash.USER_ID = u.user_id
 group by ash.USER_ID, s.SQL_TEXT, u.username
 order by ttl_wait_time desc