Dynamic Performance Views

What are dynamic performance views?

Dynamic performance views are a set of underlying views that are maintained by the server and accessible to the user SYS. It appears as if these views are derived from regular database tables,but they are not. Instead, these views provide data on internal disk structures and memory structures. Only select on these views are allowd , but no update or alter statements are allowed.
Below are some of the frequently used dynamic performance views

V$PROCESS

The V$PROCESS view can be used to identify
Bottleneck on the database server:
If the bottleneck on the database server is related to an operating system resource such as CPU or memory, the processes typically using most of the resources are the server processes. In such a case, the V$PROCESS view can be used to find the resource intensive processes and relate the processes to their sessions.
Locating trace files for a session:
The SQL*Trace file names are based on the operating system process ID of the server process. To locate the trace file for a session, you can relate the session to the server process using the V$PROCESS view.

V$SYSSTAT

V$SYSSTAT stores cumulative instance-wide statistics on resource usage since the start of the instance. The data in this view is used for monitoring system performance.

V$WAITSTAT

V$WAITSTAT gives the summary of all buffer waits since instance startup. we can break the waits by class if you see a large number of buffer busy waits on the system. The same has been extented in oracle 10g for all waits where each of them falls into certain wait class like application,Hardware etc so that the trouble shooting time and resolution time is less.
The following are possible reasons for waits:
Undo segment header: not enough rollback segments Data segment header/freelist: freelist contention Data block Large number of Consistent Read images for the buffer Range scans on indexes with large number of deletions Full table scans on tables with large number of deleted rows Blocks with high concurrency
The common buffer classes that have buffer busy waits include:
Data block
If the contention is on tables or indexes, and not on the segment header, then
Check for SQL statements using unselective indexes Check for 'right-hand-indexes.' These are indexes that receive insertions at the same point by many processes; for example, those that use sequence number generators for the key values. Consider using automatic segment-space management, or increasing freelists to avoid multiple processes attempting to insert into the same block
Segment header
If the contention is on the segment header, then it is most likely freelist contention.
Set freelists, or increase of number of freelists. If adding more freelists does not alleviate the problem, use freelist groups, which can make a difference even in a single instance. If using Oracle Real Application Clusters, make sure that each instance has its own freelist group and enable Auotamtic Segment Space Management. Undo header
If the contention is on rollback segment header, then add more rollback segments if not using automatic undo management.
Undo block
If the contention is on rollback segment block then consider making rollback segment sizes larger if he is not using automatic undo management.

V$TRANSACTION

This view lists the active transactions in the system.
Lets say you have issued the READ ONLY clause in the ALTER TABLESPACE statement to change a tablespace to read-only. You find that it is taking a long time for the tablespace to quiesce and want to identify the transactions that are preventing the read-only state from taking effect.
You need to identify the transaction entry for the ALTER TABLESPACE ... READ ONLY statement and note the session address (saddr) by querying the V$SQLAREA and V$SESSION views.
Now select the start SCN of each active transaction by querying the V$TRANSACTION view.
SELECT SES_ADDR, START_SCNB FROM V$TRANSACTIONORDER BY START_SCNB;
You know the transaction entry for the ALTER TABLESPACE ... READ ONLY statement, you can now identify the transactions that are preventing the read-only state from taking effect. All transactions with lesser start SCN potentially hold up the quiesce and subsequent read-only state of the tablespace.

A Quick Demo

SESSION 1
SQL>conn test/test connected
SQL> truncate table test;
Table truncated.

SQL> BEGIN FOR i IN 1..100 LOOP INSERT INTO test VALUES (i); end loop; END; /
PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM TEST;
COUNT(*)----------100
1 row selected.

SQL> select tablespace_name from user_tables where table_name='TEST' ;
TABLESPACE_NAME----------------USERS
1 row selected.

SESSION 2

SQL>CONN SYS/SYS AS SYSDBAconnected
SQL> alter tablespace users read only;

This will hang unless and until SESSION2 EITHER COMMITS OR ROLLBACKS

SESSION 3

SQL>CONN SYS/SYS AS SYSDBAconnected
Now we will find out the session address for user test SQL> select saddr from v$session where username='TEST';
SADDR--------65A3FE4C
Lets check for the start SCN as well

SQL> SELECT SES_ADDR, START_SCNB FROM V$TRANSACTION ORDER BY START_SCNB;
SES_ADDR START_SCNB-------- ----------65A3FE4C 71524103 --->This is the test user inserting values65A3D88C 71524182 ---->This is the sys user altering tablespace
Now we do a commit in SESSION 2 and query

SQL> SELECT SES_ADDR, START_SCNB FROM V$TRANSACTION ORDER BY START_SCNB;
no rows selected