v$session之小測試（二）_與v$sql

   先羅列一點官方手冊：

    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