管理Oracle时常用的数据字典和动态性能视图

管理Oracle时常用的数据字典和动态性能视图

1.v$version

查询数据库版本信息

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

2.v$sgainfo

查询sga中的相关信息

select name, bytes/1024/1024"Size(M)"from v$sgainfo;

NAME                                Size(M)
-------------------------------- ----------
Fixed SGA Size                   2.12744903
Redo Buffers                     2.19921875
Buffer Cache Size                       300
Shared Pool Size                        216
Large Pool Size                           4
Java Pool Size                            4
Streams Pool Size                         4
Shared IO Pool Size                       0
Granule Size                              4
Maximum SGA Size                 816.328125
Startup overhead in Shared Pool  68.5404663
Free SGA Memory Available               284

3.v$parameter

查询初始化参数相关信息

select name,valuefrom v$parameterwhere name like'%pool%';

NAME                                                                             VALUE
-------------------------------------------------------------------------------- ----------------------------------------------------
shared_pool_size                                                                 0
large_pool_size                                                                  0
java_pool_size                                                                   0
streams_pool_size                                                                0
shared_pool_reserved_size                                                        10905190
buffer_pool_keep                                                                 
buffer_pool_recycle                                                              
global_context_pool_size                                                         
olap_page_pool_size                                                              0
 
9 rows selected

4.v$sysstat和v$statname

v$sysstat是统计信息，v$statname用于显示从v$sesstat和v$sysstat解析过的统计名。比如，确定系统总的事务量，可以：

select a.value as "TransactionCount" from v$sysstat a, v$statname b
where a.statistic# = b.statistic# and b.name = 'user commits';

TransactionCount
----------------
            6954

5.v$instance

这个v$视图显示当前实例的状态。比如，要确定系统总的运行时间：

select (sysdate - startup_time)*24*60*60 as seconds from v$instance;

   SECONDS
----------
     34183

6.v$session

查询会话信息。比如：

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

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
SYS                                     1          7
SYSMAN                                 32        165
SYSMAN                                 33         25
SYSMAN                                 35         55
SYS                                    37       1065
SYS                                    38       3205
SYSMAN                                 39          3
SYS                                    42       5039
SYSMAN                                 43         49
SYSMAN                                 44         97
SYSMAN                                 45         29
DBSNMP                                 46         95
SYSMAN                                 47        359
DBSNMP                                 48        893
SYSMAN                                 51         13
SYS                                    57       2345

7.v$sql

查询sql相关信息。如下：

select sql_text, parse_calls, loads, executions from v$sql
where upper(sql_text)like'。。。';

8.v$process和v$bgprocess

进程和后台进程相关信息，比如：

select p.pid, p.spid, b.name, b.description from v$process p, v$bgprocess b
where p.addr = b.paddr and b.paddr <>'00';

       PID SPID                     NAME  DESCRIPTION
---------- ------------------------ ----- ----------------------------------------------------------------
         2 808                      PMON  process cleanup
         3 810                      PSP0  process spawner 0
         4 812                      VKTM  Virtual Keeper of TiMe process
         5 816                      GEN0  generic0
         6 818                      DIAG  diagnosibility process
         7 820                      DBRM  DataBase Resource Manager
         8 822                      DIA0  diagnosibility process 0
         9 824                      MMAN  Memory Manager
        10 826                      DBW0  db writer process 0
        11 828                      LGWR  Redo etc.
        12 830                      CKPT  checkpoint
        13 832                      SMON  System Monitor Process
        14 834                      RECO  distributed recovery
        15 836                      MMON  Manageability Monitor Process
        16 838                      MMNL  Manageability Monitor Process 2
        20 852                      ARC0  Archival Process 0
        21 854                      ARC1  Archival Process 1
        22 856                      ARC2  Archival Process 2
        23 858                      ARC3  Archival Process 3
        24 860                      QMNC  AQ Coordinator
 
       PID SPID                     NAME  DESCRIPTION
---------- ------------------------ ----- ----------------------------------------------------------------
        25 874                      CJQ0  Job Queue Coordinator
        28 908                      SMCO  Space Manager Process
 
22 rows selected

9.dba_tablespaces

select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT
STU                            ONLINE    PERMANENT
 
7 rows selected

10.user_segments

查看段信息。比如：

select segment_name, segment_type, tablespace_name, bytes/1024"Size(KB)", extents, blocks
from user_segments where segment_name ='DEPT'

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                  Size(KB)    EXTENTS     BLOCKS
------------------------------ ------------------ ------------------------------ ---------- ---------- ----------
DEPT                           TABLE              USERS                                  64          1          8

11.user_extents

查看区段信息。比如：

select segment_name, tablespace_name, extent_id, bytes/1024"Size(KB)", blocks 
from user_extents where segment_name ='DEPT';

SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID   Size(KB)     BLOCKS
------------------------------ ------------------------------ ---------- ---------- ----------
DEPT                           USERS                                   0         64          8

12.dba_data_files

查看数据文件相关信息。比如：

select file_id, file_name, tablespace_name from dba_data_files;

   FILE_ID FILE_NAME                                                                        TABLESPACE_NAME
---------- -------------------------------------------------------------------------------- ------------------------------
         4 /u01/oradata/mike/users01.dbf                                                    USERS
         3 /u01/oradata/mike/undotbs01.dbf                                                  UNDOTBS1
         2 /u01/oradata/mike/sysaux01.dbf                                                   SYSAUX
         1 /u01/oradata/mike/system01.dbf                                                   SYSTEM
         5 /u01/oradata/mike/example01.dbf                                                  EXAMPLE
         6 /u01/oradata/mike/stu01.dbf                                                      STU
         7 /u01/oradata/mike/undotbs02.dbf                                                  UNDOTBS1
 
7 rows selected

13.dba_temp_files

查看临时表数据文件信息。比如：

select file_id, file_name, tablespace_name from dba_temp_files;

FILE_ID FILE_NAME                                                                        TABLESPACE_NAME
---------- -------------------------------------------------------------------------------- ------------------------------
         1 /u01/oradata/mike/temp01.dbf                                                     TEMP