先罗列一点官方手册：

V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

简译如下：v$sql除了 group by 不显示，其它皆会显示（共享sql,说白了就是shared pool 中的library cache)；另外：一些长查询，它是每5秒更新一条统计信息，其它的查询sql则是每次查询执行完就会更新统计信息了

--以下就是根据v$session的sql_adress及sql_hash_value二者匹配v$sql查看正在及刚刚执行过的会话sql

select sql_text,address,hash_value --查看正在执行会话对应的sql
from v$sql
where (address,hash_value)
in (select sql_address,sql_hash_value from v$session where sid=976 and username='PD')

SQL_TEXT ADDRESS HASH_VALUE
---------------------------------------------------------------------------------------------------- -------- ----------
update t1 set a=2 --这是等待锁的会话对应的sql 32D4D31C 3205622493 --sql地址及hash_value

--查找上次刚刚执行过的会话所对应的sql
SQL> select sql_text,address,hash_value from v$sql where (address,hash_value) in (select prev_sql_addr,prev_hash_value from v$session where username='PD');

SQL_TEXT                                                                                             ADDRESS HASH_VALUE
---------------------------------------------------------------------------------------------------- -------- ----------
select distinct sid from v$mystat                                                                    32D4D020 2171660009
select count(*) from c_chapter_info                                                                  35589288 962805696
update t1 set a=1    --这不把持锁会话（刚执行过的会话的sql)                                          32D39BF0 3163124029

ITPub博客

v$session之小测试（二）_与v$sql

最新文章