Oracle 常用的V$ 视图脚本

1. 基本的数据库信息

版本信息：
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 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

数据库信息：
SQL> select name, created, log_mode from v$database;

NAME      CREATED        LOG_MODE
--------- -------------- ------------
TEST      13-9月 -09     ARCHIVELOG

 

2. 自动工作量仓库(AWR) 的基本信息

自动工作量仓库(AWR)在默认情况下，仓库用小时填充，保留期是7天。

AWR使用多少空间

SQL>Select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like '%AWR%';

OCCUPANT_N OCCUPANT_DESC                                        SPACE_USAGE_KBYTES
---------- --------------------------------------------------   ------------------
SM/AWR     Server Manageability - Automatic Workload Repository              51200
 
系统上最原始的AWR信息是什么？
SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
21-8月 -09 09.18.15.359000000 上午 +08:00

什么是AWR信息的保留期？
SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

将AWR信息的保留期更改为15天？
SQL> EXEC dbms_stats.alter_stats_history_retention(15);

PL/SQL 过程已成功完成。

 

3. 基本的许可信息

V$LICENSE视图允许DBA监控系统内任何时候有关数据库数量的所有系统活动的数量。会话警告级别为0表示没有设置init.ora会话警告参数，所以系统不会显示警告信息。会话最大级别为0表示没有设置init.ora会话最大参数，所以系统不会限制会话的数量。查询V$LICENSE视图，以查看所允许的最大会话数。也可以在接近最大数时设置警告。

应该定期执行脚本，以向DBA提供系统一天中实际的会话数量，从而保证正确的许可授权。设置init.ora参数LICENSE_MAX_SESSIONS = 110，将会话数限制为110。设置init.ora参数LICENSE_SESSIONS_WARNING = 100，系统将向每位在第100个会话之后的用户显示警告信息，这样他们就会通知DBA，系统因遇到问题而关闭(希望能如此)。init.ora参数LICENSE_MAX_USERS用于设置数据库中可以创建的已命名的用户数。在以下程序清单中，该值为0，所以没有限制。

SQL> select * from v$license;

SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER  USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
           0                0                9                 18          0

 

4. 数据库中已安装的产品项

查询V$OPTION视图，可以获取您已安装的Oracle产品项。V$VERSION视图将给出已安装的基本产品项的版本。

SQL> select * from v$option;

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----
Partitioning                                                     TRUE
Objects                                                          TRUE
Real Application Clusters                                        FALSE
Advanced replication                                             TRUE
Bit-mapped indexes                                               TRUE
Oracle Data Guard                                                TRUE
Oracle Label Security                                            FALSE
Flashback Database                                               TRUE
Data Mining Scoring Engine                                       FALSE
Transparent Data Encryption                                      TRUE
Backup Encryption                                                TRUE
Unused Block Compression                                         TRUE
... ...

5. 内存分配摘要(V$SGA)

V$SGA视图给出了系统的系统全局区(System Global Area，SGA)内存结构的摘要信息。Data Buffers是在内存中分配给数据的字节数量。它根据init.ora的参数DB_CACHE_SIZE得到。Redo Buffers主要是依据init.ora参数LOG_BUFFER计算得到，每当COMMIT命令提交数据时，它被用于缓存已改变的记录并将它们保存到重做日志中。访问V$SGA视图可以得到系统的物理内存分配的基本概念，包括在Oracle中为数据、共享池、large池、java池以及日志缓冲区分配的内存。

SQL> COLUMN value FORMAT 999,999,999,999
SQL> select * from  v$sga;

NAME                            VALUE
-------------------- ----------------
Fixed Size                  1,248,576
Variable Size             100,664,000
Database Buffers          180,355,072
Redo Buffers                7,139,328

如果使用SGA_TARGET-- 内部动态调整大小：
SQL> select ((select sum(value) from v$sga) -(select current_size from v$sga_dynamic_free_memory)) "
SGA_TARGET" from dual;

SGA_TARGET
----------
 289406976

 

6.内存分配的细节(V$SGASTAT)

在V$视图中，可以查询V$SGASTAT视图来提供有关SGA更详细的内存分配信息。这个视图提供了SGA和内存资源的动态信息(访问数据库时会出现相应变化)。这个语句非常详细地描述了SGA的尺寸。在V$SGA和V$SGASTAT视图中均包含记录FIXED_SGA、BUFFER_CACHE和LOG_BUFFER.V$SGASTAT视图可获取Oracle SGA详细的分类列表以及共享池分配中各存储容器的详细信息。

SQL> select * from v$sgastat;

POOL         NAME                            BYTES
------------ -------------------------- ----------
             fixed_sga                     1248576
             buffer_cache                180355072
             log_buffer                    7139328
shared pool  dpslut_kfdsg                      256
shared pool  hot latch diagnostics              80
shared pool  ENQUEUE STATS                    8360
shared pool  transaction                    264528
shared pool  KCB buffer wait statistic        3352
shared pool  invalid low rba queue             320
shared pool  KQF optimizer stats table        2396
... ...

 

7. 在V$PARAMETER显示init.ora信息

程序清单中的脚本显示了系统中的init.ora参数。它还提供了有关参数的信息，确定每一个参数的当前值是否就是默认值(ISDEFAULT=TRUE)。查询V$PARAMETER视图，将得到init.ora参数的当前值。它还显示了哪些init.ora参数已经改动了原始的默认值：ISDEFAULT = FALSE。它还显示了对于一个给定的会话，只能修改哪些参数(当ISSES_MODIFIABLE = TRUE时)。最后，它显示了在不用关闭和重启数据库可以修改哪些参数(当ISSYS_MODIFIABLE = IMMEDIATE时)；而ISSYS_MODIFIABLE = DEFERRED说明该参数对所有新登录的，但当前未登录会话的用户有效。如果参数ISSYS _MODIFIABLE =FALSE，则说明该实例必须关闭并重启，才能使设置生效。

SQL>select name, value, isdefault, isses_modifiable,issys_modifiable from v$parameter order by name;

NAME            VALUE                               ISDEFAULT ISSES ISSYS_MOD
--------------- ----------------------------------- --------- ----- ---------
active_instance                                     TRUE      FALSE FALSE
asm_diskgroups                                      TRUE      FALSE IMMEDIATE
audit_file_dest D:\ORACLE\ADMIN\TEST\ADUMP          FALSE     FALSE DEFERRED
audit_sys_opera FALSE                               TRUE      FALSE FALSE
background_dump D:\ORACLE\ADMIN\TEST\BDUMP          FALSE     FALSE IMMEDIATE
backup_tape_io_ FALSE                               TRUE      FALSE DEFERRED
... ...

8.测定数据的命中率(V$SYSSTAT)

查询V$SYSSTAT视图(如下程序清单所示)可以查看从内存中读取数据的频率。它提供了数据库中设置的数据块缓存区的命中率。这个信息可以帮助您判断系统何时需要更多的数据缓存(DB_CACHE_SIZE)，或者系统的状态何时调整得不佳(二者均将导致较低的命中率)。通常情况下，您应当确保读数据的命中率保持在95%以上。将系统的命中率从98%提高到99%，可能意味着性能提高了100%(取决于引起磁盘读操作的语句)。
SELECT   1
       - (  SUM (DECODE (NAME, 'physical reads', VALUE, 0))
          / (  SUM (DECODE (NAME, 'db block gets', VALUE, 0))
             + (SUM (DECODE (NAME, 'consistent gets', VALUE, 0)))
            )
         ) "Read Hit Ratio"
  FROM v$sysstat;

Read Hit Ratio
--------------
    .993067726

在Oracle 10g中，也可以直接获得V$SYSMETRIC中的 AWR 信息:
SQL> select metric_name,value from v$sysmetric where metric_name='Buffer Cache Hit Ratio';

METRIC_NAME                                                           VALUE
---------------------------------------------------------------- ----------
Buffer Cache Hit Ratio                                                  100
Buffer Cache Hit Ratio                                                  100

 

9.测定数据字典的命中率(V$ROWCACHE)

可以使用V$ROWCACHE视图(如程序清单所示)来发现对数据字典的调用是否有效地利用了通过init.ora参数SHARED_POOL_SIZE分配的内存缓存.如果字典的命中率不高，系统的综合性能将大受影响。推荐的命中率是95%或者更高。如果命中率低于这个百分比，说明可能需要增加init.ora参数SHARED_POOL_SIZE。但要记住，在V$SGASTAT视图中看到的共享池包括多个部分，而这里仅仅就是其中之一。注意：在大幅度使用公共同名的环境中，字典命中率可能难以超过75%，即使共享池的尺寸很大。这是因为Oracle必须经常检查不存在的对象是否依旧存在。

SQL>select sum(gets),sum(getmisses),(1 - (sum(getmisses) / (sum(gets)+ sum(getmisses)))) * 100 HitRate from v$rowcache;

 SUM(GETS) SUM(GETMISSES)    HITRATE
---------- -------------- ----------
    370854          11068 97.1020261

在Oracle 10g中，也可以直接获得V$SYSMETRIC中的AWR信息:
select metric_name, value from v$sysmetric where metric_name ='Library Cache Hit Ratio';
METRIC_NAME                                                           VALUE
---------------------------------------------------------------- ----------------
Library Cache Hit Ratio                                          98.0281690140845
Library Cache Hit Ratio                                          98.0281690140845

 

10.测定共享SQL和PL/SQL的命中率(V$LIBRARYCACHE)
访问V$LIBRARYCACHE视图可以显示实际使用的语句(SQL和PL/SQL)访问内存的情况。如果init.ora的参数SHARED_POOL_SIZE设置得太小，内存中就没有足够的空间来存储所有的语句。固定命中率通常应该是95%或更高，而重载的次数不应该超过1%。查询V$SQL_BIND_CAPTURE视图，看看每个SQL绑定是否太高，是否需要CURSOR_SHARING。

select sum(pins) "Executions", sum(pinhits) "Hits",((sum(pinhits) / sum(pins)) * 100) "PinHitRatio",sum(reloads) "Misses", ((sum(pins) / (sum(pins)+ sum(reloads))) * 100) "RelHitRatio" from v$librarycache;

Executions       Hits PinHitRatio     Misses RelHitRatio
---------- ---------- ----------- ---------- -----------
    417954     403489  96.5390928       4092  99.0304374

查询 v$sql_bind_capture，看看 average binds 是否大于15 (issue):
select  sql_id, count(*) bind_count from v$sql_bind_capture where child_number = 0 group by sql_id having  count(*) > 20order by count(*);

SQL_ID        BIND_COUNT
------------- ----------
9qgtwh66xg6nz         21

 

11.确定需要固定的PL/SQL对象
碎片化现象造成共享池中的可用空间均成为许多零散的片段，而没有足够大的连续空间，这是共享池中的普遍现象。消除共享池错误(参阅第4章和第13章以了解更多信息)的关键是理解哪些对象会引起问题。一旦知道了会引起潜在问题的PL/SQL对象，就可以在数据库启动时固定这个代码(这时共享池是完全连续的)。

SQL>select name, sharable_mem from v$db_object_cache where sharable_mem > 100000 and type in ('PACKAGE', 'PACKAGE BODY','FUNCTION', 'PROCEDURE') and kept = 'NO'; 
NAME              SHARABLE_MEM
----------------- ------------
DBMS_BACKUP_RESTO       258495
DBMS_STATS              131422

 

12.通过V$SQLAREA查找有问题的查询
V$SQLAREA视图提供了一种识别有潜在问题或者需要优化的SQL语句的方法，从而可通过减少磁盘的访问来优化数据库的综合性能。
select b.username username, a.disk_reads reads,a.executions exec, a.disk_reads /decode(a.executions, 0, 1,a.executions) rds_exec_ratio,a.command_type, a.sql_text Statement from        v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and a.disk_reads > 100000 order by a.disk_reads desc;

 

13.检查用户的当前操作及其使用的资源
将V$SESSION和V$SQLTEXT连接就可以显示目前每一个会话正在执行的SQL语句，如下面的程序清单所示。这在有些时候是极为有用的，例如DBA希望查看某一个给定的时间点上系统究竟执行了哪些操作。
select a.sid, a.username, s.sql_text from v$session a, v$sqltext s where a.sql_address = s.address and a.sql_hash_value = s.hash_value order by a.username, a.sid, s.piece;

select a.username, b.block_gets, b.consistent_gets,b.physical_reads, b.block_changes, b.consistent_changes from v$session a, v$sess_io b where a.sid = b.sid order by a.username;

USERNAME                       BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS
------------------------------ ---------- --------------- --------------
DBSNMP                                 27          118917            246
DBSNMP                               4383          111119            268
SYSMAN                              45617          123434            660
SYSMAN                               9416           94902            157
SYSMAN                                380           37019             47
SYSTEM                                 11          256588            147

 

14.查找用户正在访问的对象
通过查询V$ACCESS视图可查看在给定的时间点上用户所访问的所有对象。这有助于查明有问题的对象，在想修改一个特定的对象时也很有用(查找谁在访问它)。然而，当系统有一个很大的共享池和数百个用户时，这个操作的开销将很大。
select a.sid, a.username, b.owner, b.object, b.type from v$session a, v$access b where a.sid = b.sid;

 

15.使用索引
Oracle 9i提供了监控索引使用的功能。这个新的视图表示索引是否被引用，但不能反映索引使用的频率。要监控的索引需要单独打开和关闭。可以使用alter index命令来初始化监控工作，然后通过对视图V$OBJECT_USAGE的查询来实现索引的跟踪。

select * from v$object_usage;
开始监控索引： 
alter index HRDT_INDEX1 monitoring usage; 
select index_name, table_name, monitoring, used,start_monitoring, end_monitoring from v$object_usage;

INDEX_NAME  TABLE_NAME MON  USE START_MONITORING    END_MONITORING
----------- ---------- --- --- ------------------- ------------------
HRDT_INDEX1 HRS_DETAIL YES NO   10/13/2002 03:11:34

 

16.确定锁定问题
确定锁定问题将有助于定位正在等待其他某些用户或者某些东西的用户。可以使用这个策略来确定当前被锁定在系统中的用户。这也使DBA们可以确认一个相关的Oracle进程是否真地被锁定了，还是仅仅运行得比较慢。您还能够识别当前的语句是否正在执行锁定用户的操作。
select  /*+ ordered */ b.username, b.serial#, d.id1, a.sql_text from v$lock d, v$session b, v$sqltext a where b.lockwait = d.kaddr and a.address = b.sql_address and      a.hash_value  = b.sql_hash_value;

查看系统中是哪个用户造成了前一个用户被锁定的问题
select  /*+ ordered */ a.serial#, a.sid, a.username, b.id1, c.sql_text from v$lock b, v$session a, v$sqltext c where b.id1 in(select /*+ ordered */ distinct e.id1 from v$lock e, v$session d where d.lockwait= e.kaddr) and a.sid = b.sid and c.hash_value  = a.sql_hash_value and b.request = 0;

 

17. 关闭有问题的会话
select username, sid, serial#, program, terminal from  v$session;
alter system kill session '11,18';

You can't kill your own session though:
alter system kill session '10,4';
*ERROR at line 1:ORA-00027: cannot kill current session

 

18.查找使用多会话的用户
有些时候，用户喜欢使用多会话来一次完成多个任务，但这会引起问题。开发人员也会有同样的问题，如果他开发了一个创建了会派生大量进程的糟糕的应用程序。所有这些都可能降低系统的综合性能。用户名NULL是后台进程。
SQL> select username, count(*) from v$session group by  username;

USERNAME                         COUNT(*)
------------------------------ ----------
                                       17
SYSTEM                                  4
SYSMAN                                  3
DBSNMP                                  2

 

19.查找磁盘I/O问题
视图V$DATAFILE、V$FILESTAT和V$DBA_DATA_FILES提供了数据库中所有数据文件和磁盘的文件I/O活动信息。理想情况下，物理的读和写应当平均分布。如果没有合理的配置系统，其综合性能就会受到影响。
select a.file#, a.name, a.status, a.bytes,b.phyrds, b.phywrts from  v$datafile a, v$filestat b where a.file# = b.file#;

     FILE# NAME              STATUS       BYTES     PHYRDS    PHYWRTS
---------- ----------------- ------- ---------- ---------- ----------
         1 D:\ORACLE\ORADATA SYSTEM   534773760       7732       1506
         2 D:\ORACLE\ORADATA ONLINE    31457280         54       2778
         3 D:\ORACLE\ORADATA ONLINE   356515840       2680       7905
         4 D:\ORACLE\ORADATA ONLINE     5242880         25          3

 

20.查找回滚段的内容
这个有帮助的查询显示了一个回滚段的实际等待数。可以显示回滚信息(包括自动撤消)。还可以从程序清单显示的视图中查询Shrink和 wrap信息。查询V$ROLLNAME、V$ROLLSTAT和V$TRANSACTION视图可以提供用户如何使用回滚段和撤消表空间的信息。通常情况下，在一个时间点上不应让多个用户访问同一个回滚段(尽管这是被允许的)。

select a.name, b.extents, b.rssize, b.xacts,b.waits, b.gets, optsize, status from v$rollname a, v$rollstat b where a.usn = b.usn;
NAME                 EXTENTS     RSSIZE      XACTS      WAITS       GETS STATUS
----------------- ---------- ---------- ---------- ---------- ---------- -------
SYSTEM                     6     385024          0          0        215 ONLINE
_SYSSMU1$                  3    1171456          0          0       3191 ONLINE

 

21.检查空闲列表是否充足

如果使用多进程完成大量的插入操作，空闲列表(空闲的数据库数据块的列表)的默认值1可能是不够的。如果没有使用自动空间段管理(Automatic Space Segment Management，简称ASSM)，您可能需要增加空闲列表，或者空闲列表组。在使用多进程完成大量的插入操作时，应确保有足够的空闲列表和空闲列表组。空闲列表的默认存储值是1。如果您使用了ASSM，Oracle将为您管理这些参数，但是一个有大量数据交换的事务环境中，在应用ASSM前应经过仔细的测试。虽然如此，但通常最好使用ASSM。

select ((A.Count/(B.Value + C.Value))*100)Pct from V$WaitStat A, V$SysStat B, V$SysStat C where  A.Class = 'free list' and B.Statistic# = ( select Statistic# from V$StatName where Name = 'db block gets') and C.Statistic# =  (select Statistic# from V$StatName where Name = 'consistent gets')；

 

22  检查角色和权限设置

根据用户名进行授权的对象级特权
select b.owner || '.' || b.table_name obj,b.privilege what_granted, b.grantable,a.username from sys.dba_users a, sys.dba_tab_privs b where a.username = b.grantee order by 1,2,3;

 

根据被授权人进行授权的对象级特权
Select owner || '.' || table_name obj,privilege what_granted, grantable, grantee from sys.dba_tab_privs where not exists(select 'x'from sys.dba_users where username = grantee)order by 1,2,3;

 

根据用户名进行授予的系统级特权
select b.privilege what_granted,b.admin_option, a.username from sys.dba_users a, sys.dba_sys_privs b where a.username = b.grantee order by 1,2;

 

根据被授权人进行授予的系统级特权
select privilege what_granted,admin_option, grantee from sys.dba_sys_privs where not exists ( select 'x' from sys.dba_users where username = grantee ) order by 1,2;

 

根据用户名授予的角色
select b.granted_role ||decode(admin_option, 'YES',' (With Admin Option)',null) what_granted, a.username from sys.dba_users a, sys.dba_role_privs b where a.username = b.grantee order by 1;

 

根据被授权人授予的角色
select  granted_role ||decode(admin_option, 'YES',' (With Admin Option)', null) what_granted,grantee from sys.dba_role_privs where not exists(select 'x'from sys.dba_users where username = grantee ) order by 1;

 

用户名及已被授予的相应权限
select a.username,b.granted_role || decode(admin_option,'YES',' (With Admin Option)',null) what_granted from 
sys.dba_users a,sys.dba_role_privs b where   a.username = b.grantee 
UNION
select a.username,b.privilege || decode(admin_option,'YES',' (With Admin Option)', null) what_granted from 
sys.dba_users a,sys.dba_sys_privs b where a.username = b.grantee 
UNION
select a.username,b.table_name ||'-' || b.privilege|| decode(grantable,'YES',' (With Grant Option)',null) 
what_granted from sys.dba_users a, sys.dba_tab_privs b where a.username = b.granteeorder by 1;

 

查询用户名及相应的配置文件、默认的表空间和临时表空间
Select username, profile, default_tablespace,temporary_tablespace, created from sys.dba_users order by username;

 

23.等待事件V$视图
 在Oracle 10g中V$SESSION_WAIT中的所有等待事件列现在都在V$SESSION中。因此，确保查询等待信息的 V$SESSION，因为它是一个更快的视图。V$ACTIVE_SESSION_HISTORY (ASH)将许多重要统计数据合并为一个视图或一个报表(ASH报表)。

 

马上该谁等待--查询V$SESSION_WAIT / V$SESSION
select event, sum(decode(wait_time,0,1,0)) "Waiting Now",sum(decode(wait_time,0,0,1)) "Previous Waits",count(*) "Total" from v$session_wait group by event order by count(*);

 

马上该谁等待；SPECIFIC Waits--查询V$SESSION_WAIT 
SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type,p1,p2,p3 FROM v$session_wait sw, dba_extents de WHERE de.file_id = sw.p1 AND sw.p2 between de.block_id and de.block_id+de.blocks - 1 AND (event = 'buffer busy waits' OR event = 'write complete waits') AND p1 IS NOT null ORDER BY event,sid;

 

谁在等待 - 最后10 个等待数--查询V$SESSION_WAIT_HISTORY 
SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type,p1,p2,p3 FROM v$session_wait sw, dba_extents de WHERE de.file_id = sw.p1 AND sw.p2 between de.block_id and de.block_id+de.blocks - 1 AND (event = 'buffer busy waits' OR event = 'write complete waits') AND p1 IS NOT null ORDER BY event,sid;

 

查找P1, P2, P3代表什么--查询 V$EVENT_NAME 
select event#,name,parameter1 p1,parameter2 p2,parameter3 p3 from v$event_name where name in ('buffer busy waits', 'write complete waits');

 

会话开始后的所有等待数--查询 V$SESSION_EVENT 
select sid, event, total_waits, time_waited, event_id from  v$session_event where  time_waited > 0 order by time_waited;

 

类的所有会话等待数--查询V$SESSION_WAIT_CLASS 
select sid, wait_class, total_waits from  v$session_wait_class;

 

系统启动后的所有等待数--查询V$SYSTEM_EVENT 
select  event, total_waits, time_waited, event_id from v$system_event where time_waited > 0 order  by time_waited;

 

类的系统等待数--查询V$SYSTEM_WAIT_CLASS
select wait_class, total_waits from  v$system_wait_class order by total_waits desc;

 

类的系统等待数--查询V$ACTIVE_SESSION_HISTORY
--In the query below, the highest count session is leader in non-idle wait events.
select session_id,count(1) from v$active_session_history group by session_id order by 2;
--In the query below, find the SQL for the leader in non-idle wait events.
select c.sql_id, a.sql_text from v$sql a, (select sql_id,count(1) from v$active_session_history b where sql_id is not null group by sql_idorder by 2 desc ) c where rownum <= 5 order by rownum;