【Oracle SQL】session相关

---

一、查询正在执行的SQL

--查询Oracle正在执行的sql语句及执行该语句的用户
SELECT b.sid
       , b.serial#       	
       , a.spid       操作系统PID
       , b.username
       , b.status
       , c.sql_text		
       , b.machine		计算机名
       , b.port            
       , o.object_name
       , b.logon_time	登录数据库时间 
       , a.pga_used_mem
FROM v$process a, v$session b, v$sqlarea c, dba_objects o
WHERE a.addr = b.paddr --process address该session对应的进程地址，关联v$process的addr可以查出对应的操作系统进程PID
AND b.sql_hash_value = c.hash_value(+)
and b.plsql_entry_object_id = o.object_id(+);

--查看正在执行sql的发起者的发放程序
SELECT a.sid
       , a.serial#
       , a.username
       , a.schemaname
       , b.cpu_time
       , a.status
       , a.sql_id
       , b.sql_text
       , a.osuser           电脑登录身份      
       , a.program          发起请求的程序
       , a.machine          计算机名
       , a.logon_time       登录数据库的时间
       , a.sql_exec_start
       , a.event       
       , a.plsql_entry_object_id
       , a.plsql_object_id
       , o.object_name
       , a.blocking_session
       , b.buffer_gets      内存使用量
FROM v$session a
LEFT JOIN v$sql b ON a.sql_address = b.address AND a.sql_hash_value = b.hash_value
LEFT JOIN dba_objects o on a.plsql_entry_object_id = o.object_id
WHERE a.status = 'ACTIVE'
ORDER BY b.cpu_time DESC;

二、查询锁

0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁
3：Row-X 行专用(RX)：用于行的修改
4：Share 共享锁(S)：阻止其他DML操作
5：S/Row-X 共享行专用(SRX)：阻止其他事务操作
6：exclusive 专用(X)：独立访问使用
数字越大锁级别越高, 影响的操作越多

--单实例：查出oracle当前的被锁对象
SELECT l.session_id sid
       , s.serial#
       , l.locked_mode 锁模式
       , l.oracle_username 登录用户
       , l.os_user_name 登录机器用户名
       , s.machine 机器名
       , s.terminal 终端用户名
       , o.object_name 被锁对象名
       , s.logon_time 登录数据库时间
       , s.sql_exec_start
       , s.event
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
ORDER BY sid, s.serial#;

--RAC：查出oracle当前的被锁对象
SELECT s.inst_id,
       l.session_id sid,
       s.serial#,
       l.locked_mode 锁模式,
       l.oracle_username 登录用户,
       s.machine 机器名,
       l.os_user_name 登录机器用户名,
       s.terminal 终端用户名,
       o.object_name 被锁对象名,
       s.logon_time 登录数据库时间
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
ORDER BY sid, s.serial#; 

select vs.inst_id,
       vs.sid,
       vs.serial#,
       vl.locked_mode 锁模式,
       vl.oracle_username 登录用户,       
       vs.machine 机器名,
       vl.os_user_name 登录机器用户名,
       vs.terminal 终端用户名,
       b.owner,
       b.object_name,
       vs.logon_time 登录数据库时间,
       vs.program,      
       vsq.sql_text
  from gv$session vs, gv$sqlarea vsq, gv$locked_object vl, dba_objects b
 where vl.session_id = vs.sid
   and vs.prev_sql_addr = vsq.address
   and vl.object_id = b.object_id
 order by vs.sid;

--kill掉当前的锁对象可以为(sid,serial#)
alter system kill session '686,30161';

--杀进程集群 
alter system kill session 'sid,serial#, @inst_id';

三、查询会话阻塞

--会话阻塞
select distinct 
--'alter system kill session '''||a.sid||','||a.serial#||''';', 
a.sid waiting_session,
                a.serial#,
                d.sql_text waiting_query,
                a.blocking_session blocking_session,
                a.process,
                a.machine,
                a.program,
                a.inst_id,
                ROUND(a.last_call_et / 60) blocktime,
                to_char(a.logon_time, 'DD-MON-YYYY HH24:MI:SS'),
                a.event
  from gv$session a
  join gv$sql d
    on a.sql_id = d.sql_id
  --left join gv$active_session_history b
    --on a.blocking_session = b.session_id
  --left join gv$sql c
    --on c.sql_id = b.sql_id
 where a.blocking_session != 0
 order by blocktime desc

四、ASH会话历史查询

--ASH会话历史查询
SELECT instance_number
       , ash.p1
       , ash.p2
       , ash.p3
       , to_char(sample_time, 'yyyy/mm/dd hh24:mi:ss.ff') TIME
       , session_id sid
       , session_serial#
       , ash.sql_id
       , (select a.sql_text from v$sql a where a.sql_id = ash.sql_id and rownum = 1) sql_text
       , ash.program
       , en.event_name
       , ash.blocking_session
       , ash.temp_space_allocated
       , ash.machine
       , u.username
  FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en, dba_users u
 WHERE ash.event_id = en.event_id(+)
   and ash.user_id = u.user_id(+)
   AND sample_time >= to_timestamp('20240307 1300', 'yyyymmdd hh24mi')
   AND sample_time <= to_timestamp('20240307 1430', 'yyyymmdd hh24mi')
   --and session_id = 2619
 ORDER BY sample_time;


--定位不知源头的sql
SELECT sql_id, plsql_entry_object_id FROM dba_hist_active_sess_history;


SELECT * FROM dba_objects where object_id = 8390;

四、查询sql的物理读和IO等待

--查询sql的物理读和IO等待
SELECT s.instance_number,
       s.snap_id,
       to_char(ss.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') begin_interval_time,
       to_char(ss.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') end_interval_time,
       SUM(DISK_READS_TOTAL) DISK_READS_TOTAL,
       SUM(DISK_READS_DELTA) DISK_READS_DELTA,
       SUM(IOWAIT_TOTAL) IOWAIT_TOTAL,
       SUM(IOWAIT_DELTA) IOWAIT_DELTA
  FROM dba_hist_sqlstat s, dba_hist_snapshot ss
 WHERE s.snap_id = ss.snap_id
   AND s.dbid = ss.dbid
   AND s.instance_number = ss.instance_number
   AND to_char(ss.begin_interval_time, 'yyyy-mm-dd hh24:mi') >= '2024-03-08 00:00'
   AND s.instance_number = 2
 GROUP BY s.instance_number,s.snap_id, ss.begin_interval_time, ss.end_interval_time;

五、查询UNDO占用SQL

--查询UNDO占用SQL
SELECT DISTINCT s.machine,
                s.program,
                s.sid,
                s.SERIAL#,
                ROUND(t.used_ublk * 8 / 1024, 2) undo_MB,
                t.used_urec undo_records,
                s.status,
                l.sql_text
  FROM gv$transaction t, gv$session s, gv$sqlstats l
 WHERE t.ses_addr = s.saddr
   AND s.sql_id = l.sql_id(+)
 ORDER BY undo_MB;
 
--查询undo回退情况
select * from gv$fast_start_transactions;

六、查询事务占用情况

--事务占用情况查询
SELECT b.tablespace
       , b.segfile#
       , b.segblk#
       , b.blocks
       , a.sid
       , a.serial#
       , a.username
       , a.osuser
       , a.status
       , c.sql_text
  FROM v$session a, v$sort_usage b, v$sql c
 WHERE a.saddr = b.session_addr
   AND a.sql_address = c.address(+)
 ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks; 

--查询某天的事务总数
select instance_number
      , metric_unit
      , trunc(begin_time) time
      , avg(average) * 60 * 60 * 24 "Transactions Per Day"
from DBA_HIST_SYSMETRIC_SUMMARY
where metric_unit = 'Transactions Per Second'
 and begin_time >= to_date('2021-12-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
 and begin_time < to_date('2021-12-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by instance_number, metric_unit, trunc(begin_time)

--查询当前正在执行的事务:
SELECT s.sid,
       s.serial#,
       s.event,
       a.sql_text,
       a.sql_fulltext,
       s.username,
       s.status,
       s.machine,
       s.terminal,
       s.program,
       a.executions,
       s.sql_id,
       p.spid,
       a.direct_writes
  FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s
  LEFT JOIN v$sqlarea a
    ON s.sql_id = a.sql_id
 INNER JOIN v$process p
    ON s.paddr = p.addr;

--查看Oracle数据库中的长事务:
with transaction_details as
 (select inst_id, ses_addr, sysdate - start_date as diff from gv$transaction)
select s.username,
       to_char(trunc(t.diff)) || ' days, ' ||
       to_char(trunc(mod(t.diff * 24, 24))) || ' hours, ' ||
       to_char(trunc(mod(t.diff * 24 * 60, 24))) || ' minutes, ' ||
       to_char(trunc(mod(t.diff * 24 * 60 * 60, 60))) || ' seconds' as transaction_duration,
       s.program,
       s.terminal,
       s.status,
       s.sid,
       s.serial#
  from gv$session s, transaction_details t
 where s.inst_id = t.inst_id
   and s.saddr = t.ses_addr
 order by t.diff desc;

--查询长事务SQL:
with ltr as
 (select to_char(sysdate, 'YYYYMMDDHH24MISS') TM,
         s.sid,
         s.sql_id,
         s.sql_child_number,
         s.prev_sql_id,
         xid,
         to_char(t.start_date, 'YYYYMMDDHH24MISS') start_time,
         e.TYPE,
         e.block,
         e.ctime,
         decode(e.CTIME, 0, (sysdate - t.start_date) * 3600 * 24, e.ctime) el_second
  --  q.sql_text 
    from v$transaction t, v$session s, v$transaction_enqueue e
   where t.start_date <= sysdate - interval '100' second /*查询开始多少秒的事务*/
     and t.addr = s.taddr
        --and s.sql_child_number = q.CHILD_NUMBER(+) 
        --and s.sql_id = q.sql_id(+) and s.prev_sql_id = q.sql_id(+) 
     and t.addr = e.addr(+))
select ltr.*,
       (select q1.sql_text
          from v$sql q1
         where ltr.prev_sql_id = q1.sql_id(+)
           and rownum = 1) prev_sql_text,
       (select q1.sql_text
          from v$sql q1
         where ltr.sql_id = q1.sql_id(+)
           and ltr.sql_child_number = q1.CHILD_NUMBER(+)) sql_text
  from ltr ltr;

七、查询会话占用内存

SELECT s.server "连接类型",
       s.username,
       s.status,
       s.logon_time,
       s.machine,
       s.port,
       osuser,
       name,
       st.value/1024/1024 "占用内存MB",
       s.sid,
       s.serial#,
       spid "操作系统PID",
       p.pga_used_mem,
       p.pga_alloc_mem,
       p.pga_freeable_mem,
       p.pga_max_mem
  FROM v$session s, v$sesstat st, v$statname sn, v$process p
 WHERE st.sid = s.sid
   AND st.statistic# = sn.statistic#
   AND sn.name = 'session pga memory'
   AND p.addr = s.paddr
 ORDER BY VALUE DESC;