常用的会话相关v$视图

 ==============SQL语句的=========================================================  
 ================================================================================

v$sqltext    ---------  存储的是完整的SQL,SQL被分割
v$sqlarea   ---------  存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息 
v$sql          ---------  存储的是具体的SQL 和执行计划相关信息，实际上，v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息

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$SQLAREA: lists statistics on shared SQL area and contains one row per SQL string.
 It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

V$SQLTEXT:This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.

==========================================================================
---------------------------通过v$sql_text查看当前物理读和逻辑读高的SQL---------------------------------------------
物理读大于1000的：
select sql_text from v$sql where disk_reads > 1000 

逻辑读大于30000
select sql_text from v$sql where executions > 0 and buffer_gets >30000

最近执行的sql文本：
select sql_text,last_active_time from v$sql order by last_active_time desc



==========================================================================
---------------------------通过v$sqlarea当前消耗资源的sql文本---------------------------------------------
---------------------------------------------------------------
代价最高的SQL
select OPTIMIZER_COST,EXECUTIONS,sql_text from v$sqlarea
where OPTIMIZER_COST>
(select max(OPTIMIZER_COST)/5 from v$sqlarea);
---------------------------------------------------------------
IO最高的SQL
select round(DISK_READS/EXECUTIONS) ,DISK_READS,EXECUTIONS,sql_text
from v$sqlarea 
where round(DISK_READS/EXECUTIONS)>
(select max(round((DISK_READS/EXECUTIONS)/5)) from v$sqlarea
where EXECUTIONS>0)
and EXECUTIONS>0 and DISK_READS>100
order by 1;
---------------------------------------------------------------
处理行最高的SQL
select round(ROWS_PROCESSED/EXECUTIONS) ,ROWS_PROCESSED,EXECUTIONS,sql_text
from v$sqlarea 
where round(ROWS_PROCESSED/EXECUTIONS)>
(select max(round((ROWS_PROCESSED/EXECUTIONS)/5)) from v$sqlarea
where EXECUTIONS>0)
and EXECUTIONS>0 and ROWS_PROCESSED>1000
order by 1;
 




 =================================锁============================================ 
 =============================================================================== 

--含运行了还没commit或rollback的SQL的会话（就是阻塞的源头）
--dba_waiters：DBA_WAITERS shows all the sessions that are waiting for a lock.
select * from dba_waiters Where MODE_HELD='Exclusive'  

--当前正在等待拿锁才能执行的语句（就是阻塞的SQL）
Select Dba_Objects.Owner, Dba_Objects.Object_Name,locked_mode,SQL_TEXT,Sid
  From Dba_Objects, V$locked_Object, V$session, V$sql
 Where Dba_Objects.Object_Id = V$locked_Object.Object_Id
   And V$session.Sid = V$locked_Object.Session_Id
   And V$session.Sql_Id = V$sql.Sql_Id
   And Dba_Objects.Owner = 'SCOTT'
   
--查出阻塞会话的sid等系统信息，源头的和阻塞的都能查出来
Select Object_Name As 对象名称, s.Sid, s.Serial#, p.Spid As 系统进程号
  From V$locked_Object l, Dba_Objects o, V$session s, V$process p
 Where l.Object_Id = o.Object_Id
   And l.Session_Id = s.Sid
   And s.Paddr = p.Addr;
   
 ===============================会话============================================== 
 ================================================================================= 
SPID一一system process id,
表示該server process在OS層面的Porcess ID ，即操作系统进程ID
在操作系统上面用 prstat -a查出来的其实就是pid,这里和下面描述的oracle进程id
是完全一致对应的

PID一一Oracle process id 
可以理解為Oracle自己用的，Oracle进程ID


SID一一SESSION标识，常用于连接其它列

查看当前会话的sid
select userenv('SID') from dual;

查看运行了很久的SQL并且能查看它运行了%多少 
select username,sid,opname, 
      round(sofar*100 / totalwork,0) || '%' as progress, 
      time_remaining,sql_text 
from v$session_longops , v$sql 
where time_remaining <> 0 
and sql_address = address 
and sql_hash_value = hash_value 


查看占了大量内存的进程
SELECT   server, s.username, osuser, NAME, VALUE / 1024 / 1024, s.SID, s.serial#, spid
    FROM v$session s, v$sesstat st, v$statname sn, v$process p
   WHERE st.SID = s.SID
     AND st.statistic# = sn.statistic#
     AND sn.NAME LIKE 'session pga memory'
     AND p.addr = s.paddr
     AND VALUE > 10 * 1024 * 1024                                              --only show pga > 10M
ORDER BY VALUE DESC


查看当前的争用情况
Select V$waitstat.Class,
       V$waitstat.Count Count,
       Sum(V$sysstat.Value) Sum_Value
  From V$waitstat, V$sysstat
 Where V$sysstat.Name In ('db block gets', 'consistent gets')
 Group By V$waitstat.Class, V$waitstat.Count
 Order By Count Desc
 
查看当前正在等待的对象
Select * From v$session_wait Where wait_class='APPLICATION'  
 
查看程序占用的会话数
select count(*),program from v$session group by program;



根据系统的spid找到SQL和pid,SERIAL#
Select b.Spid, a.Sid, a.Serial#, a.Program, c.Sql_Text, c.Sql_Fulltext
  From V$session a, V$process b, V$sql c
 Where a.Paddr = b.Addr
   And a.Sql_Id = c.Sql_Id
   And b.Spid In ('304', '10615', '26462'); --在solaris上面prsatat查出来的pid就是这里要填的

 =============================等待事件================================================ 
 ===================================================================================== 
查看累积的等待事件
 select * from 
(select event,time_waited from v$system_event order by time_waited desc)
where rownum <11;

查看当前正在等待的事件
V$SESSION_WAIT displays the resources or events for which active sessions are waiting.
select * from v$session_wait 

看各类主要等待事件（这个时间是累积的，并且是按照大体分类来的）
--V$SYSTEM_WAIT_CLASS displays the instance-wide time totals for each registered wait class.
select * from v$system_wait_class