Dynamic Performance Views

• These views are owned by SYS.
• Different views are available at different times:
• The instance has been started.
• The database is mounted.
• The database is open.
• You can query V$FIXED_TABLE to see all the view names.
• These views are often referred to as “v-dollar views.”
• Read consistency is not guaranteed on these views because the data is dynamic. No locking mechanism on these views
• SELECT_CATALOG_ROLE grant is required to allow a user to select the V$ views

Dynamic Performance Views Usage Examples

a.  What are the SQL statements and their associated number of executions where the CPU time consumed is greater than 200,000 microseconds?

    SELECT sql_text, executions

    FROM v$sqlstats

    WHERE cpu_time > 200000;

b.  What sessions logged in from the EDRSR9P1 computer within the last day?

    SQL> SELECT * FROM v$session

    WHERE machine = 'EDRSR9P1' and

    logon_time > SYSDATE - 1;

c.  What are the session IDs of any sessions that are currently holding a lock that is blocking another user, and how long has that lock been held? (block may be 1 or 0; 1 indicates that this session is the blocker.)

    SELECT sid, ctime

    FROM v$lock WHERE block > 0;