数据字典和动态性能视图——常用动态性能视图

当维护数据库，调整数据库性能时，需要经常访问动态性能视图，已取得例程的动态信息。

注意：大多数动态性能视图只能有特权用户和dba用户访问。下面介绍几种常见的动态性能视图。

1.v$fixed_table

该动态性能视图用于列出所有的动态性能视图和动态性能表。

select name from v$fixed_table where name like 'V$%';

2.v$instance

该动态性能视图用于取得当前例程的详细信息。

select instance_name,host_name,status from v$instance;

INSTANCE_NAME HOST_NAME STATUS
---------------- ---------------------------------------------------------------- ------------
orcl ZHAOJIEDI1992 OPEN

3.v$sga

该动态性能视图用于显示sga主要组成部分（共享池，数据高速缓存和重做日志缓冲区）的尺寸。

select * from v$sga;

NAME VALUE
-------------------- ----------
Fixed Size 1374808
Variable Size 360711592
Database Buffers 411041792
Redo Buffers 5259264

3.v#sgainfo

该动态性能视图用于取得sga更详细的信息。

select *from v$sgainfo;

NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1374808 No
Redo Buffers 5259264 No
Buffer Cache Size 411041792 Yes
Shared Pool Size 335544320 Yes
Large Pool Size 8388608 Yes
Java Pool Size 8388608 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 8388608 No
Maximum SGA Size 778387456 No
Startup overhead in Shared Pool 75497472 No
Free SGA Memory Available 8388608

已选择12行。

5.v$parameter

该动态性能视图用于取得初始化参数的详细信息。

select name,value,description from v$parameter 
where name='db_name';

NAME
-------------------------------------------------
VALUE
-------------------------------------------------
DESCRIPTION
-------------------------------------------------
db_name
orcl
database name specified in CREATE DATABASE

6.v$version

该动态视图用于取得oracle版本的详细信息。

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

7.v$option

该动态性能视图用于显示已经安装的oracle选项，其中true表示该选项已经安装，而false则表示该选项没有安装。

select * from v$option where value='FALSE';

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Real Application Clusters FALSE
Automatic Storage Management FALSE
Oracle Label Security FALSE
Oracle Database Vault FALSE

8.v$session

该动态性能视图用于显示会话的详细信息。

select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME
---- ---------- ------------------------------
169 64 SYS
170 103 SCOTT

9.v$process

该动态性能视图用于显示与oracle相关的所有进程信息。（包括后台和服务器进程）

select a.terminal,a.spid,a.pga_alloc_mem
from v$process a,v$session b
where a.addr=b.paddr and b.username='SCOTT';

TERMINAL SPID PGA_ALLOC_MEM
---------------- ------------------------ -------------
ZHAOJIEDI1992 3560 1271730

10.v$bgprocess

该动态性能视图用于显示后台进程的详细信息。

select name,description,paddr from v$bgprocess
where paddr<>'00';

NAME DESCRIPTION PADDR
----- ---------------------------------------------------------------- --------
PMON process cleanup 3E545444
VKTM Virtual Keeper of TiMe process 3E545F4C
GEN0 generic0 3E546A54
DIAG diagnosibility process 3E54755C
DBRM DataBase Resource Manager 3E548064
PSP0 process spawner 0 3E548B6C
DIA0 diagnosibility process 0 3E549674
MMAN Memory Manager 3E54A17C
DBW0 db writer process 0 3E54AC84
LGWR Redo etc. 3E54B78C
CKPT checkpoint 3E54C294
SMON System Monitor Process 3E54CD9C
SMCO Space Manager Process 3E551AD4
RECO distributed recovery 3E54D8A4
CJQ0 Job Queue Coordinator 3E557314
QMNC AQ Coordinator 3E553BEC
MMON Manageability Monitor Process 3E54E3AC
MMNL Manageability Monitor Process 2 3E54EEB4

11.v$database

该动态性能视图用于取得当前数据库的详细信息（如数据库名，日志操作模式以及建立时间）

select name,log_mode,created from v$database;

NAME LOG_MODE CREATED
--------- ------------ --------------
ORCL NOARCHIVELOG 08-4月 -13

12.v$controlfile

该动态性能视图用于取得当前数据库所有控制文件的信息。

select name  from v$controlfile;

---------------------------------------------------------------------------
E:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL

13.v$datafile

该动态性能视图用于取得当前数据库所有数据文件的详细信息。

select file#,name from v$datafile;

FILE# NAME
---------------------- -------------------------------------------------------------------------------------------------------------
1 E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
2 E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
3 E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
4 E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
5 E:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF

14.v$dbfile

该动态性能视图用于取得数据文件编号及名称。

select * from v$dbfile;

FILE# NAME
---------------------- -------------------------------------------------------------------------------------------------------------
4 E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
3 E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
2 E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
1 E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
5 E:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF

15.v$logfile

该动态性能视图用于显示重做日志的信息。

select * from v$dbfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------------------- ------- ------- ---------------------------------------------------------------------------------------------
3 ONLINE E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG NO
2 ONLINE E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG NO
1 ONLINE E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG NO

16.v$log

该动态性能视图用于显示日志组的详细信息。

select group#,thread#,sequence#,bytes,members,status from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS STATUS
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------
1 1 145 52428800 1 INACTIVE
2 1 146 52428800 1 CURRENT
3 1 144 52428800 1 INACTIVE

17.v$thread

该动态性能视图用于取得重做线程的详细信息。当使用rac结构时，每个例程都对应一个重做线程，并且每个重做线程会包含独立的重做日志组。

select thread#,status,groups,instance,sequence# from v$thread;

THREAD# STATUS GROUPS INSTANCE SEQUENCE#
---------------------- ------ ---------------------- -------------------------------------------------------------------------------
1 OPEN 3 orcl 146

18.v$lock

该动态性能视图用于显示锁信息。

select a.username,a.machine,b.lmode,b.request
from v$session a,v$lock b
where a.sid=b.sid and a.type='USER';

USERNAME MACHINE LMODE REQUEST
------------------------------ ---------------------------------------------------------------- ---------------------- ----------------------
SYS ZHAOJIEDI_HOME\ZHAOJIEDI1992 4 0
SCOTT ZHAOJIEDI1992 4 0
SYS ZHAOJIEDI1992 4

19.v$locked_object

该动态性能视图用于显示倍加锁的数据库对象。通过与dba_object进行连接查询。可以显示具体的对象名几执行加锁操作的oracle用户名。

select a.oracle_username,b.owner||'.'||b.object_name object
from v$locked_object a,dba_objects b
where a.object_id=b.object_id;

20.v$rollname和v$rollstat

该动态性能视图用于显示处于online状态的undo段，而v$rollstat则用于显示undo段统计信息。通过在二者之间的连接查询，可以显示undo段的详细统计信息。

select a.name,b.xacts from v$rollname a,v$rollstat b
where a.usn=b.usn;

ORACLE_USERNAME OBJECT
------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------

0 rows selected

Connected
NAME XACTS
------------------------------ ----------------------
SYSTEM 0
_SYSSMU1_1518548437$ 0
_SYSSMU2_2082490410$ 0
_SYSSMU3_991555123$ 0
_SYSSMU4_2369290268$ 0
_SYSSMU5_1018230376$ 0
_SYSSMU6_1834113595$ 0
_SYSSMU7_137577888$ 0
_SYSSMU8_1557854099$ 0
_SYSSMU9_1126410412$ 0
_SYSSMU10_3176102001$ 0

21.v$tablespace

该动态性能视图用于显示表空间信息。

select * from v$tablespace;

TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------------------- ------------------------------ --------------------------- ------- ------------ -----------------
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES

22.v$tempfile

该动态性能视图用于显示当前数据库所包含的临时文件。

select name from v$tempfile;

NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF