首先看看什么时候使用动态性能视图：
（1）NOMOUNT
启动例程时，Oracle会打开参数文件，分配SGA并启动后台进程。因此，当例程处于NOMOUNT状态时，只能访问访问从SGA 中获取信息的动态性能视图。
（2）MOUNT
装在数据库时，Oracle根据初始化参数control_files打开所有的控制文件。当例程处于mount状态时，不仅可以访问从SGA中获取信息的动态性能视图，还可以访问从控制文件中获取信息的动态性能视图。
（3）OPEN
只有处于OPEN状态时，才能访问数据字典视图。
下面是常用的动态性能视图：
1、V$FIXED_TABLE
用于列出所有可用的动态性能视图和动态性能表
05:06:34 SQL> select name from v$fixed_table where name like 'V$%';
NAME
------------------------------
V$WAITSTAT
V$BH
V$GC_ELEMENT
V$CR_BLOCK_SERVER
V$CURRENT_BLOCK_SERVER
V$GC_ELEMENTS_WITH_COLLISIONS
V$FILE_CACHE_TRANSFER
V$TEMP_CACHE_TRANSFER
V$CLASS_CACHE_TRANSFER
V$INSTANCE_CACHE_TRANSFER
V$LOCK_ELEMENT
V$BSP
V$LOCKS_WITH_COLLISIONS
V$FILE_PING
V$TEMP_PING
V$CLASS_PING
2、v$instance
用于取得当前例程的详细信息
05:14:56 SQL> col host_name for a20
05:15:08 SQL> r
1* select instance_name ,host_name,status from v$instance
INSTANCE_NAME HOST_NAME STATUS
---------------- -------------------- ------------
orcl oraserv OPEN
3、v$sga
用于显示SGA主要组成部分（共享池、数据库高速缓存和重做日志缓冲区的尺寸）。
5:17:36 SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 1218992
Variable Size 79693392
Database Buffers 201326592
Redo Buffers 2973696
4、v$sgainfo
用于取得SGA更详细的信息
5:19:55 SQL> select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1218992 No
Redo Buffers 2973696 No
Buffer Cache Size 201326592 Yes
Shared Pool Size 71303168 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 285212672 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 0
11 rows selected.
05:19:56 SQL> select name ,bytes/1024/1024 MB from v$sgainfo;
NAME MB
-------------------------------- ----------
Fixed SGA Size 1.16252136
Redo Buffers 2.8359375
Buffer Cache Size 192
Shared Pool Size 68
Large Pool Size 4
Java Pool Size 4
Streams Pool Size 0
Granule Size 4
Maximum SGA Size 272
Startup overhead in Shared Pool 36
Free SGA Memory Available 0
11 rows selected.
5、v$parameter
用于取得初始化参数的详细信息
05:22:57 SQL> col name for a10
05:22:59 SQL> col description for a30
05:23:08 SQL> col value for a10
05:23:13 SQL> select name,value,description from v$parameter
05:23:36 2 where name='db_name';
NAME VALUE DESCRIPTION
---------- ---------- ------------------------------
db_name orcl database name specified in CRE
ATE DATABASE
6、v$version
用于取得Oracle版本的详细信息
05:24:58 SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
05:25:00 SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
05:25:13 SQL>
7、v$option
用于显示已经安装Oracle选项
05:26:54 SQL> col parameter for a40
05:26:56 SQL> col value for a10
05:27:03 SQL> select * from v$option;
PARAMETER VALUE
---------------------------------------- ----------
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
。。。。。。
8、v$session
用于显示会话的详细信息
05:28:48 SQL> select sid,serial#,username from v$session
05:28:49 2 where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
153 54 SYS
159 5 SCOTT
9、v$process
显示与Oracle 相关的所有进程信息（包括后台进程和服务器进程）
05:39:32 SQL> select a.terminal,a.spid,a.pga_alloc_mem
05:39:48 2 from v$process a,v$session b
05:39:51 3 where a.addr=b.paddr and b.usernAMe='SCOTT';
no rows selected
05:39:57 SQL> /
TERMINAL SPID PGA_ALLOC_MEM
------------------------------ ------------ -------------
pts/2 5212 973097
10、v$bgprocess
显示后台进程的详细信息
05:41:54 SQL> col description for a40
05:41:56 SQL> select name,description from v$bgprocess
05:42:11 2 where paddr<>'00';
NAME DESCRIPTION
---------- ----------------------------------------
PMON process cleanup
PSP0 process spawner 0
MMAN Memory Manager
DBW0 db writer process 0
ARC0 Archival Process 0
ARC1 Archival Process 1
ARC2 Archival Process 2
LGWR Redo etc.
CKPT checkpoint
SMON System Monitor Process
RECO distributed recovery
CJQ0 Job Queue Coordinator
QMNC AQ Coordinator
MMON Manageability Monitor Process
MMNL Manageability Monitor Process 2
15 rows selected.
