oracle中的V$视图-CSDN博客

1.基本数据库信息

版本信息

SQL> 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 Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

数据库信息

SQL> select name,created,log_mode from v$database;

NAME CREATED LOG_MODE
--------- --------- ------------
WILSON 04-DEC-12 ARCHIVELOG

2.AWR基本信息

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

OCCUPANT_NAME        OCCUPANT_DESC        SPACE_USAGE_KBYTES
-------------------- -------------------- ------------------
SM/AWR               Server Manageability              42624
                      - Automatic Workloa
                     d Repository

3.系统最原始AWR信息

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
03-SEP-13 10.41.38.000000000 PM +08:00

AWR的保留时间：

SQL> select dbms_stats.get_stats_history_retention from dual;

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

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

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

PARAMETER                                VALUE
---------------------------------------- --------------------
Parallel execution                       TRUE
Parallel load                            TRUE
Point-in-time tablespace recovery        TRUE
Fine-grained access control              TRUE
Proxy authentication/authorization       TRUE
Change Data Capture                      TRUE
Plan Stability                           TRUE
Online Index Build                       TRUE
Coalesce Index                           TRUE
Managed Standby                          TRUE
Materialized view rewrite                TRUE

PARAMETER                                VALUE
---------------------------------------- --------------------
Materialized view warehouse refresh      TRUE
Database resource manager                TRUE
Spatial                                  TRUE
Automatic Storage Management             FALSE
Export transportable tablespaces         TRUE
Transparent Application Failover         TRUE
Fast-Start Fault Recovery                TRUE
Sample Scan                              TRUE
Duplexed backups                         TRUE
Java                                     TRUE
OLAP Window Functions                    TRUE

PARAMETER                                VALUE
---------------------------------------- --------------------
Block Media Recovery                     TRUE
Fine-grained Auditing                    TRUE
Application Role                         TRUE
Enterprise User Security                 TRUE
Oracle Data Guard                        TRUE
Oracle Label Security                    FALSE
OLAP                                     TRUE
Basic Compression                        TRUE
Join index                               TRUE
Trial Recovery                           TRUE
Data Mining                              TRUE

PARAMETER                                VALUE
---------------------------------------- --------------------
Online Redefinition                      TRUE
Streams Capture                          TRUE
File Mapping                             TRUE
Block Change Tracking                    TRUE
Flashback Table                          TRUE
Flashback Database                       TRUE
Transparent Data Encryption              TRUE
Backup Encryption                        TRUE
Unused Block Compression                 TRUE
Oracle Database Vault                    FALSE
Result Cache                             TRUE

PARAMETER                                VALUE
---------------------------------------- --------------------
SQL Plan Management                      TRUE
SecureFiles Encryption                   TRUE
Real Application Testing                 TRUE
Flashback Data Archive                   TRUE
DICOM                                    TRUE
Active Data Guard                        TRUE
Server Flash Cache                       TRUE
Advanced Compression                     TRUE
XStream                                  TRUE
Deferred Segment Creation                TRUE

65 rows selected.

5.内存分配

V$SGA系统全局区内存结构的摘要信息。databuffers是在内存中分配给数据的字节数据量。它根据spfile的参数DB_CACHE_SIZE得到。redo buffers主要是依据spfile参数LOG_BUFFER计算得到。当commit命令提交数据时，它被用于缓存已改变的记录并将它们保存到重做日志中。

SQL> column value format 999,999,9999,999
SQL> select * from v$sga;

NAME                             VALUE
-------------------- -----------------
Fixed Size                    1336,960
Variable Size              31,8769,536
Database Buffers            9,6468,992
Redo Buffers                  6094,848

6.内存分配细节

SQL>select * from v$sgastat

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool PX server deq stats              1692
shared pool kks stbkt                     1048576
shared pool JSX SGA                          3732
shared pool Background process state           44
shared pool KKSSP                          162528
shared pool quiesce system context            300
shared pool SGA structure for KPON kp        2824
shared pool SGA struct - SWRF Metrics        2160
shared pool ASM kfk state object             2660
shared pool client/application info l         100
shared pool change notification regis        8200
......

7.在v$parameter中显示spfile信息

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

NAME                 VALUE                ISDEFAULT ISSES ISSYS_MOD
-------------------- -------------------- --------- ----- ---------
listener_networks                         TRUE      FALSE IMMEDIATE
local_listener                            TRUE      FALSE IMMEDIATE
lock_name_space                           TRUE      FALSE FALSE
lock_sga             FALSE                TRUE      FALSE FALSE
log_archive_config                        TRUE      FALSE IMMEDIATE
log_archive_dest                          TRUE      FALSE IMMEDIATE
log_archive_dest_1                        TRUE      TRUE IMMEDIATE
log_archive_dest_10                       TRUE      TRUE IMMEDIATE
log_archive_dest_11                       TRUE      TRUE IMMEDIATE
log_archive_dest_12                       TRUE      TRUE IMMEDIATE
log_archive_dest_13                       TRUE      TRUE IMMEDIATE

......

8.测定数据的命中率

SQL> select 1
2 -(SUM(DECODE(NAME,'physical reads',VALUE,0))
3 /(SUM(DECODE(NAME,'db block gets',VALUE,0))
4 +(SUM(DECODE(NAME,'consistent gets',VALUE,0)))
5 )
6 )"Read Hit Ratio"
7 from v$sysstat;

Read Hit Ratio
--------------
.953775746

10g中使用V$SYSMETRIC来获得AWR信息

9.测定数据字典命中率

V$ROWCACHE显示对数据字典的调用是否有效的利用了通过spfile参数SHARED_POOL_SIZE分配的内存缓存。如果字典的命中率不高，系统的综合性能将大受影响。推荐是95%。如果低于这个就要修改spfile中参数SHARED_POOL_SIZE。

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

SUM(GETS) SUM(GETMISSES) HITRATE
---------- -------------- ----------
168364 9505 94.6561795

10g中使用V$AYAMETRIC来获得AWR信息

SQL> select metric_name,value from v$sysmetric where metric_name='Library Cache Hit Ratio';

METRIC_NAME                               VALUE
------------------------------ ----------------
Library Cache Hit Ratio                     100
Library Cache Hit Ratio                     100

10测定共享SQL和PL/SQL的命中率（V$LIBRARYCACHE）

V$LIBRARYCACHE视图显示实际使用的语句(SQL和PL/SQL)访问内存情况。查询V$SQL_BIND_CAPTURE视图查看SQL绑定情况，是否需要CURSOR_SHARING。

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

Executions Hits PinHitRatio Misses RelHitRatio
---------- ---------- ----------- ---------- -----------
100803 92007 91.2740692 159 99.842515

查询V$SQL_BIND_CAPTURE查看average binds是否大于issue

SQL> select sql_id,count(*) bind_count from v$sql_bind_capture where child_number=0 group by sql_id having count(*) >20 order by count(*);

SQL_ID        BIND_COUNT
------------- ----------
4jrfrtx4u6zcx         21
0kkhhb2w93cx0         22
c0agatqzq2jzr         25
9ds1amk0gttnh         25
19rkm1wsf9axx         29
3qsmy8ybvwt3n         32
b5cr4hhndmbuf         41

7 rows selected.

11.确定需要固定的PL/SQL对象

共享池中由于碎片化造成许多可用的空间成为许多零散的片段，形成没有足够的连续空间。使用v$db_object_cache查询哪些对象引起碎片化严重

SQL>select name,sharable_mem from v$db_object_cache where sharable_mem >10000 and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') and kept='NO';

NAME                 SHARABLE_MEM
-------------------- ------------
DBMS_FEATURE_OBJECT         20880
DBMS_FEATURE_RULESMA        16784
NAGER

DBMS_OBFUSCATION_TOO 12688
LKIT

EM_TASK                     64088
STANDARD                   565652
MGMT_ADMIN_DATA             82512
DBMS_STATS_INTERNAL         66056

NAME                 SHARABLE_MEM
-------------------- ------------
DBMS_OUTPUT                 12672
EM_POLICY                   37276
DBMS_LOCK                   16768
MGMT_JOB_ENGINE            544684
EM_METRIC_EVAL              16784
EM_POLICY_EVAL              80676
DBMS_FEATURE_SEGADV_        20880
USER

DBMS_FEATURE_SFDEDUP 16784
_SYS

NAME SHARABLE_MEM
-------------------- ------------

MGMT_COLLECTION             20892
DBMS_ISCHED                273812
DBMS_SCHEDULER             131472
EM_PING                     16768
DBMS_BACKUP_RESTORE        115696
DBMS_FEATURE_XDB            20880
DBMS_UTILITY                45440
EMD_SCHEMA                  20864
DBMS_ISCHED                 33196
DBMS_AQADM_SYSCALLS         33188

NAME                 SHARABLE_MEM
-------------------- ------------
MGMT_SQLTRACE               12688
MGMT_JOB_ENGINE             49616
DBMS_FEATURE_SFCOMPR        16784
ESS_SYS

DBMS_FEATURE_AWR 20880
DBMS_FEATURE_SERVICE 20880
S

DBMS_SQL 57920
MGMT_ADMIN 49632

NAME SHARABLE_MEM
-------------------- ------------

.........

12.查询有问题的查询

V$SQLAREA视图提供了一种识别潜在问题或者需要优化的SQL语句的方法，从而减少磁盘的访问来优化数据库的性能

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语句。

SQL> 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;

       SID USERNAME                       SQL_TEXT
---------- ------------------------------ ------------------------------
         1 SYS                            select a.sid,a.username,s.sql_
                                          text from v$session a,v$sqltex
                                          t s

         1 SYS                            where a.sql_address=s.address
                                          and a.sql_hash_value=s.hash_va
                                          lue

1 SYS order by a.username,a.sid,s.pi
ece

SQL>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;

SERNAME                       BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS
------------------------------ ---------- --------------- --------------
BLOCK_CHANGES CONSISTENT_CHANGES
------------- ------------------
SYS                                   164           97297           1209
          134                  0

0 0 0
0 0

.......

14.查找用户正在访问的对象

SQL>select a.sid,a.username,b.owner,b.object,b.type from v$session a,v$access b where a.sid=b.sid;

SID USERNAME   OWNER      OBJECT               TYPE
---------- ---------- ---------- -------------------- --------------------
        30            SYS        DBMS_LOGSTDBY_LIB    LIBRARY
        31            SYS        DBMS_LOGSTDBY        PACKAGE
        31            SYS        DBMS_AQ_LDAP_LIB     LIBRARY
        31            SYS        PLITBLM              PACKAGE
        31            SYS        AQ$_REPLAY_INFO      TABLE
        31            SYS        DBMS_AQADM_SYSCALLS PACKAGE
        36            SYS        AQ$_ALERT_QT_H       TABLE
        36            SYS        OBJ$                 TABLE
        36            SYS        ICOL$                TABLE

.......

354 rows selected.

15.使用索引

新的视图表示索引是否被引用，但不能反映索引使用的频率。要监控的索引需要单独打开和关闭。使用alter index命令来初始化监控工作，然后通过视图v$object_usage的查询来实现索引的跟踪。

SQL> select * from v$object_usage;

开始监控索引：

SQL>alter index INDEX_NAME monitoring usae;

SQL> select index_name,table_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

16.确定锁问题

识别当前的语句是否正在执行锁定用户的操作

SQL> select /*+ orader */ 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;
查看哪个用户造成前一个用户被锁定

SQL> 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 /*+ order */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.查找磁盘I/O问题

SQL> 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 /u01/oradata/wilson/ SYSTEM   734003200       9433        268
           system01.dbf

2 /u01/oradata/wilson/ ONLINE 566231040 2176 2193
sysaux01.dbf

3 /u01/oradata/wilson/ ONLINE 104857600 29 706
undotbs01.dbf

4 /u01/oradata/wilson/ ONLINE 15728640 2 0
users01.dbf

FILE# NAME STATUS BYTES PHYRDS PHYWRTS
---------- -------------------- ------- ---------- ---------- ----------

5 /u01/oradata/wilson/ ONLINE 104857600 7 0
example01.dbf

18.查找回滚段内容

SQL> 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;

19.查看表空间是否充足

SQL> 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');

PCT
----------
0

19.等待事件

常用查看等待事件的视图：

V$SESSION

V$SESSION_WAIT

V$ACTIVE_SESSION_HISTORY(ASH)

V$EVENT_NAME

V$SESSION_WAIT_CLASS

V$SYSTEM_EVENT