概述

分享下平时工作用来监控session的执行语句和查看session的资源占用，平时做监控的时候都要查的。

通过动态性能视图查找活动session的执行语句

select a.SID,a.USERNAME,a.machine,a.TERMINAL,b.PIECE,b.SQL_TEXT
from v$session a,
 v$sqltext b
where b.ADDRESS = decode(a.SQL_HASH_VALUE,0,a.PREV_SQL_ADDR,a.SQL_ADDRESS)
 and a.status = 'ACTIVE'
 and user# >0
order by a.SQL_ADDRESS,b.PIECE;

通过动态性能视图查找所有session的执行语句

select a.SID,b.ADDRESS,b.HASH_VALUE,a.USERNAME,a.machine,b.SADDR,c.PIECE,c.SQL_TEXT
from v$session a,
 v$open_cursor b,
 v$sqltext c
where a.SID = b.SID
 and b.ADDRESS = c.ADDRESS
 and b.HASH_VALUE = c.HASH_VALUE
 and a.status = 'ACTIVE'
 and user# >0
order by a.SID,b.ADDRESS,b.HASH_VALUE,a.USERNAME,a.machine,b.SADDR,c.PIECE;

通过操作系统查找相关session信息

1、 找出最消耗cpu的操作系统进程

# ps aux| grep -v grep | grep ora| head -10
oracle 876648 1.9 1.0 57832 82156 - A 16:22:35 7:59 oracleSISDB2 (LO
oracle 594138 1.9 1.0 58808 83132 - A 15:22:46 16:48 oracleSISDB2 (LO
oracle 495712 0.9 1.0 56628 80952 - A 17:04:47 0:43 oracleSISDB2 (LO
oracle 712946 0.5 1.0 55716 80040 - A 17:11:33 0:07 oracleSISDB2 (LO
oracle 966862 0.1 1.0 55144 79468 - A Jul 08 153:01 oracleSISDB2 (LO
oracle 442494 0.1 1.0 58984 83308 - A Feb 16 1751:47 ora_lms1_SISDB2
oracle 581808 0.1 1.0 59140 83464 - A Feb 16 1747:01 ora_lms0_SISDB2
oracle 811254 0.1 1.0 55228 79552 - A 15:51:29 0:31 oracleSISDB2 (LO
oracle 573582 0.0 1.0 57680 82004 - A Feb 16 149:17 ora_lmon_SISDB2
oracle 651300 0.0 1.0 57204 81528 - A Feb 16 125:13 ora_diag_SISDB2

2、找出给定操作系统pid的session的执行sql

V$open_cursor视图列出session打开的所有cursor, 很多时候都将被用到, 比如: 你可以通过这个视图查看各个session打开的cursor数.

当诊断系统资源占用时, v$open_cursor视图常被用来连接v$sqlarea和v$sql查询出特定SQL(高逻辑或物理IO). 然后, 下一步就是找出源头.

V$sqlarea中的统计项在语句完全执行后被更新(并且从v$session.sql_hash_value中消失). 因此, 我们无法通过v$sqlarea跟v$session直接关联找到session, 除非语句被再次执行. 不过如果session的cursor仍然打开着, 用户就可以通过v$open_cursor来找出执行这个语句的session.

SELECT /*+ ORDERED */
 address,piece,sql_text
 FROM v$sqltext a
 WHERE (a.hash_value, a.address) IN (
 SELECT d.HASH_VALUE,d.ADDRESS
 FROM v$session b,v$open_cursor d 
 where b.SID = d.SID
 and b.paddr = (SELECT addr
 FROM v$process c
 WHERE c.spid = '&pid'))
ORDER BY address,piece;

session的资源占用

通过动态性能视图查找相关session信息

利用V_$SQLAREA视图提供了执行的细节。（执行、读取磁盘和读取缓冲区的次数）

• 数据列

EXECUTIONS：执行次数

DISK_READS：读盘次数

COMMAND_TYPE：命令类型（3:select,2:insert;6:update;7delete;47:pl/sql程序单元）

OPTIMIZER_MODE：优化方式

SQL_TEXT：Sql语句

SHARABLE_MEM：占用shared pool的内存多少

BUFFER_GETS：读取缓冲区的次数

• 用途

1、帮忙找出性能较差的SQL语句

2、帮忙找出最高频率的SQL

3、帮忙分析是否需要索引或改善联接

求DISK READ较多的SQL

select st.ADDRESS,st.PIECE,st.sql_text
 from v$sql s, v$sqltext st
 where s.address = st.address
 and s.hash_value = st.hash_value
 and s.disk_reads > 300
 order by st.address, st.piece ;

求DISK SORT严重的SQL

select sess.username, sql.sql_text, sort1.blocks
 from v$session sess, v$sqlarea sql, v$sort_usage sort1
 where sess.serial# = sort1.session_num
 and sort1.sqladdr = sql.address
 and sort1.sqlhash = sql.hash_value
 and sort1.blocks > 200;

查看语句占用的内存情况

select username, sum(sharable_mem), sum(persistent_mem), sum(runtime_mem)
 from sys.v_$sqlarea a, dba_users b
 where a.parsing_user_id = b.user_id
 group by username;

上面就是平时小编在监控数据库活动session时的脚本了，觉得有用的帮忙转发点赞哦！！

后面会分享更多关于devops和DBA内容，感兴趣的朋友可以关注下。