V$ Performance Views

V$SGASTAT - provides detailed information about SGA. To get a free memory in SGA, run the following command

SELECT       pool, ROUND(bytes/1024/1024,0) free_memory_mb

FROM         v$sgastat

WHERE      name LIKE '%free memory%';

 

V$SYSSTAT – provides system statistics since the instance was started and mostly joined with V$STATNAME view to provide the statistic name as follows. V$SESSTATは使うことが非常に少ない。

SELECT       n.name, s.VALUE

FROM         v$statname n, v$sysstat s

WHERE      n.statistic# = s.statistic#

ORDER BY  n.class, n.name;

 

V$SQLAREA – displays statistics on shared SQL areas. You can join it with along V$SESSION view to get detailed information about the session and the query it executes.

SELECT       ss.sid, ss.status, ss.schemaname, ss.osuser, SUBSTR(sa.sql_text,1,40) "SQL Text", ss.program

FROM         v$session ss, v$sqlarea sa

WHERE      ss.sql_hash_value = sa.hash_value (+)

AND            ss.sql_address = sa.address (+);

 

 

V$SESSION_WAIT – displays current or last wait event for the session. In the following query we join it with V$SESSION view and get the list of database session waits orderd by the most waited session

SELECT       s.username, s.sid, s.serial#, sw.event, sw.wait_class, sw.state, sw.seconds_in_wait

FROM         v$session_wait sw, v$session s

WHERE      s.sid = sw.sid

ORDER BY  sw.seconds_in_wait DESC;