1.查找你的session信息
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V$SESSION WHERE audsid = userenv('SESSIONID');
2.当machine已知的情况下查找session
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
FROM V$SESSION
WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';
3.查找当前被某个指定session正在运行的sql语句。假设sessionID为100
select b.sql_text
from v$session a,v$sqlarea b
where a.sql_hash_value=b.hash_value and a.sid=100
寻找被指定session执行的SQL语句是一个公共需求,如果session是瓶颈的主要原因,那根据其当前在执行的语句可以查看session在做些什么。
1.查看消耗资源最多的SQL:
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
FROM V$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC;
2.查看某条SQL语句的资源消耗:
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');
1.找出执行某语句的session:
SELECT hash_value, buffer_gets, disk_reads
FROM V$SQLAREA
WHERE disk_reads > 1000000
ORDER BY buffer_gets DESC;
HASH_VALUE BUFFER_GETS DISK_READS
---------- ----------- ----------
1514306888 177649108 3897402
478652562 63168944 2532721
360282550 14158750 2482065
3 rows selected.
SQL> SELECT sid FROM V$SESSION WHERE sql_hash_value = 1514306888 ;
no rows selected
--直接通过hash_value查找v$session,没有记录
SQL> SELECT sid FROM V$OPEN_CURSOR WHERE hash_Value = 1514306888 ;
SID
-----
1125
233
935
1693
531
5 rows selected.
--通过hash_value在v$open_cursor中查找sid(只有在session的cursor仍然打开的情况下才有可能找到)
2.列出拥有超过400个cursor的sessionID
SQL> SELECT sid, count(0) ct FROM v$open_cursor
GROUP BY sid HAVING COUNT(0) > 400 ORDER BY ct desc;
事实上,v$open_cursor是一个相当常用的视图,特别是web开发应用的时候。仅通过它一个视图你就能分析出当前的连接情况,主要执行语句等。
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9650775/viewspace-923246/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9650775/viewspace-923246/