查看当前高耗CPU的SQL语句

1.查找SPID

先在linux系统中通过top命令查看产用资源较多的pid号，及spid

2.查看sql_id

将上一步查出的spid代入where条件

select sid,serial#,machine,username,program,sql_hash_value,sql_id, to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time from v$session where paddr in (select addr from v$process where spid in ('5648612','256523'));

3.查看sql文本

如果上一步sql_id或者 hash_value不为空，则可用v$sqlarea查出当前正在使用的sql：

select sql_text   from v$sqltext_with_newlines   where hash_value = &hash_value order by piece;

此处输入的哈希值即上一步获取到的sql_id
或者

select * from v$sql where sql_id=''

4.脚本

直接通过输入pid获取sql文本：

SELECT 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 = '&pid')) ORDER BY piece ASC

锁占用相关

查看被锁（占用）的表的sid

select SESS.sid, SESS.SERIAL#, LO.ORACLE_USERNAME, LO.OS_USER_NAME,
AO.OBJECT_NAME, LO.LOCKED_MODE, SESS.SQL_id
from V$LOCKED_OBJECT LO, DBA_OBJECTs AO, V$SESSION SESS
where AO.OBJECT_ID = LO.OBJECT_ID
and lo.session_id = sess.sid;

杀掉会话

alter system kill session '173,16112';

查看等待

SELECT sid,
       username,
       event,
       blocking_session,
       seconds_in_wait,
       wait_time,
       SQL_id
  FROM v$session
 WHERE state IN ('WAITING') AND wait_class <> 'Idle';

https://www.cnblogs.com/cure-t-x-y/p/4244098.html

查看等待事件

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

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

查询数据库过去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;

消耗更多的IO的SQL

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

监控高占用cpu的sql(最常用)

SELECT a.logon_time,
       sid,
       serial#,
       a.username,
       a.command,
       a.status,
       a.program,
       a.machine,
       a.client_identifier,
       b.sql_text,
       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;

查看当前占用CPU时间高的SQL语句(常用)

select a.username,a.command,a.status,a.program,a.machine,a.client_identifier,b.sql_text,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 *
 from (select sa.SQL_TEXT,
        sa.SQL_FULLTEXT,
        sa.EXECUTIONS "执行次数",
        round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "用户ID",
        u.username "用户名",
        sa.HASH_VALUE
     from v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum <= 50;

查询正在执行中的select会话(用处是把查询出来的select会话手动杀掉，从而是CPU快速下降-只针对查询)

select v$session.SID,
       v$session.SERIAL#,
       v$session.MACHINE,
       v$sqlarea.SQL_FULLTEXT,
       v$sqlarea.SQL_TEXT,
       'alter system kill session ' || '''' || v$session.SID || ',' || v$session.SERIAL# ||
       ''' immediate;'
  from v$sqlarea, v$session 
 where v$sqlarea.ADDRESS = v$session.SQL_ADDRESS
   and v$sqlarea.HASH_VALUE = v$session.SQL_HASH_VALUE
   and v$session.STATUS = 'ACTIVE'
      --and v$session.PROGRAM = 'JDBC Thin Client' 
   and UPPER(v$sqlarea.SQL_TEXT) like 'SELECT %';

查看连接IP地址

使用DBMS_SESSION 过程包

BEGIN
  DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;

创建触发器

create or replace trigger on_logon_trigger
  after logon on database
begin
  dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
end;

查看连接信息

select username,
       program,
       machine,
       client_info,
       sys_context('userenv', 'ip_address') as ipadd
  from v$session s
 where username is not null
 order by username, program, machine;

自动断开连接

空闲3分钟自动断开

SELECT * FROM DBA_PROFILES;

CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 3;

alter PROFILE DEFAULT LIMIT IDLE_TIME 3; 

连接时间超过多少就断开

alter PROFILE DEFAULT  LIMIT CONNECT_TIME UNLIMITED; 

12c后有新参数max_idle_time：

alter system set max_idle_time=2;

即2分钟空闲时间不操作，会主动被杀掉

杀掉会话

杀掉一个用户所有会话

SELECT s.inst_id,

s.sid,

s.serial#,

p.spid,

s.username,

s.program,

s.paddr,

s.STATUS,

'ALTER SYSTEM KILL SESSION ''' || s.sid || ', ' || s.serial# || '''' || ';' AS KILL

FROM gv$session s

JOIN gv$process p

ON p.addr = s.paddr

AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND'

AND S.USERNAME = '用户名';

阻塞信息查询

使用V$SESSION查看单实例阻塞信息

SELECT LPAD(' ',5*(LEVEL-1))||S."USERNAME" AS user_name ,
LPAD(' ',5*(LEVEL-1))||S."SID" AS session_id,
S."SERIAL#",
S."SQL_ID", S."ROW_WAIT_OBJ#",
S."WAIT_CLASS",
S."EVENT",
S."P1",
S."P2",
S."P3",
S."SECONDS_IN_WAIT"
FROM V$SESSION S
WHERE S."BLOCKING_SESSION" IS NOT NULL
OR S.SID IN(SELECT DISTINCT BLOCKING_SESSION FROM V$SESSION)
START WITH S."BLOCKING_SESSION" IS NULL
CONNECT BY PRIOR S."SID" = S."BLOCKING_SESSION";

使用v$lock查看当前阻塞：

select 
 (select username||':'||sid||':'||serial# from v$session where sid=a.sid) || 
 ' locked ' || 
 (select username ||':'||sid||':'||serial# from v$session where sid=b.sid) 
 from v$lock a, v$lock b 
 where a.block = 1 
 and b.request > 0 
 and a.id1 = b.id1 
 and a.id2 = b.id2; 

找到目前的阻塞源：

 select b.SQL_TEXT,a.SID,a.SERIAL# from v$session a, v$sqlarea b where a.SQL_HASH_VALUE = b.HASH_VALUE(+) and sid=&sid; 

杀掉目前的阻塞源：

 alter system kill session 'SID,SERIAL#'immediate; 

生成kill语句脚本(自行修改)：

select 'alter system kill session ''' || s.SID || ',' || s.SERIAL#  || ''' immediate;' killsql
  from v$lock l, v$session s
where (id1, id2, l.type) in
       (select id1, id2, type from v$lock where request > 0)
   and l.sid = s.sid
   and s.status='INACTIVE'
   and s.type='USER'
   and decode(request, 0, 'Holder:', ' Waiter:') = 'Holder:'
order by id1, ctime desc, request;

查看 Oracle 正在执行的 sql 语句以及发起的用户

SELECT b.sid oracleID,  
       b.username 用户名,  
       b.serial#,  
       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 

oracle 查到锁的源头 blocking_session为源头,

select last_call_et,v.event,
    s.sql_id,
      ---   s.SQL_FULLTEXT,
         s.SQL_TEXT,
        v.inst_id,
         V.SID,
         V.CLIENT_IDENTIFIER,
         v.blocking_session,
         v.blocking_session_status,
         'alter system kill session ''' || v.sid || ',' || v.serial# || ''' immediate;',
          v.USERNAME,
         s.CPU_TIME,
         s.ELAPSED_TIME,
         v.PROGRAM,
         'kill -9 ' || p.spid,
         v.CLIENT_INFO,
         v.SQL_HASH_VALUE,
         v.SQL_ADDRESS,
         v.MACHINE,
         v.TERMINAL, s.DISK_READS,s.BUFFER_GETS,s.SORTS,s.SHARABLE_MEM,s.PERSISTENT_MEM,s.RUNTIME_MEM,s.ROWS_PROCESSED    
from gv$session v, gv$process p, gv$sql s  
   where v.last_call_et > 0  
     and v.status = 'ACTIVE'
     and v.username != 'SYS'
     and p.addr = v.paddr
     and s.ADDRESS = v.SQL_ADDRESS
     and s.HASH_VALUE = v.SQL_HASH_VALUE
   order by last_call_et desc;

杀掉源头blocking_session,sid的值为上一条sql查到的blocking_session

select CLIENT_IDENTIFIER,v.inst_id,v.status,'alter system kill session ''' || v.sid || ',' || v.serial# || ''' immediate;', v.USERNAME, v.CLIENT_INFO,v.SQL_HASH_VALUE,v.SQL_ADDRESS,v.MACHINE,v.TERMINAL  from gv$session v   where sid='XXX';