查询正在执行的SQL

--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;

--SQL_FULLTEXT 包含完整的SQL
SELECT A.USERNAME, A.SID, B.SQL_TEXT, B.SQL_FULLTEXT
FROM V\$SESSION A, V\$SQLAREA B
WHERE A.SQL_ADDRESS = B.ADDRESS;
---执行过的
SELECT B.SQL_TEXT, B.FIRST_LOAD_TIME, B.SQL_FULLTEXT
FROM V\$SQLAREA B
WHERE B.FIRST_LOAD_TIME BETWEEN '2016-10-1/09:24:47' AND
'2016-10-1/09:24:47'
ORDER BY B.FIRST_LOAD_TIME;

--正在执行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;

--查出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 登录数据库时间
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#;

--kill掉当前的锁对象可以为

alter system kill session 'sid， s.serial#‘;

--查找前十条性能差的sql
SELECT *
FROM (SELECT PARSING_USER_ID,
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
SQL_TEXT
FROM V\$SQLAREA
ORDER BY DISK_READS DESC)
WHERE ROWNUM < 10;

--查看占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;