Oracle常用动态视图和字典表

动态性能视图用于记录当前例程的活动。启动例程时，oracle会自动建立动态性能视图；停止时，oracle会自动停止。需要注意，数据字典信息是从数据文件中获得的，而动态性能视图信息是从SGA和控制文件中取得。通过查询动态性能视图，一方面可以获得性能数据，另一方面还可以取得磁盘和内存结构相关的其他信息。所有动态视图都是以V_$开始的，oracle为每个动态性能视图提供了相应的同义词。例如：V_$datafile的同义词为v$datafile; V_$SGA的同义词是v$sga。通过查询V$FIXED_TABLE,可以显示所有动态性能视图。当数据库处于不同状态时，可以访问的动态性能视图有所不同。

1.nomount 

启动例程时，oracle会打开参数文件，分配SGA并启动后台进程。因此，当例程处于NOMOUNT状态时，不仅可以访问从SGA中获取信息的动态性能视图，还可以访问从控制文件中获取信息的动态性能视图

2. Mount 

装载数据库时，Oracle根据初始化参数controlfile打开所有控制文件。当例程处于mount状态时，不仅可以访问从sga中获取信息的动态性能视图，还可以访问从控制文件中获取信息的动态性能视图。

3.Open

打开数据库时，Oracle按照控制文件所记载的信息打开所有的数据文件和重做日志，除了可以访问从SGA和控制文件中获取信息的动态性能视图外，还可以访问与Oracle性能相关的动态性能视图（如V$FILESTAT, V$SESSION_WAIT,V$WAITSTAT）,需要注意：只有处于OPEN状态时，才能访问数据字典视图。

V$FIXED_TABLE中1393条记录，视图name，object_id,type,table_num四个字段。

下面列出常用的数据字典和动态性能视图：

一、DBA最常用的数据字典

dba_data_files:通常用来查询关于数据库文件的信息   

dba_db_links:包括数据库中的所有数据库链路，也就是databaselinks。

dba_extents:数据库中所有分区的信息                     

dba_free_space:所有表空间中的自由分区

dba_indexs:关于数据库中所有索引的描述                

dba_ind_columns:在所有表及聚集上压缩索引的列

dba_objects:数据库中所有的对象                            

dba_rollback_segs:回滚段的描述

dba_segments:所有数据库段分段的存储空间            

dba_synonyms:关于同义词的信息查询

dba_tables:数据库中所有数据表的描述                     

dba_tabespaces:关于表空间的信息

dba_tab_columns:所有表描述、视图以及聚集的列     

dba_tab_grants/privs:对象所授予的权限

dba_ts_quotas:所有用户表空间限额                        

dba_users:关于数据的所有用户的信息

dba_views:数据库中所有视图的文本

二、DBA最常用的动态性能视图

v$datafile：数据库使用的数据文件信息                   

v$librarycache：共享池中SQL语句的管理信息

v$lock：通过访问数据库会话，设置对象锁的所有信息

v$log：从控制文件中提取有关重做日志组的信息

v$logfile有关实例重置日志组文件名及其位置的信息    

v$parameter：初始化参数文件中所有项的值

v$process：当前进程的信息                                 

v$rollname：回滚段信息

v$rollstat：联机回滚段统计信息                             

v$rowcache：内存中数据字典活动/性能信息

v$session:有关会话的信息                                    

v$sesstat：在v$session中报告当前会话的统计信息

v$sqlarea：共享池中使用当前光标的统计信息，光标是一块内存区域，有Oracle处理SQL语句时打开。

v$statname：在v$sesstat中报告各个统计的含义   

v$sysstat：基于当前操作会话进行的系统统计

v$session + v$session_wait (在10g里功能被整合,凑合算1个吧.)

v$process

 v$sql

v$sqltext

v$bh (更宁愿是x$bh)

v$lock

v$latch_children

v$sysstat

v$system_event

按组分的几组重要的性能视图

 

1。System 的 over viewv$sysstat , v$system_event , v$parameter

 

2。某个session 的当前情况v$process , v$session , v$session_wait ,v$session_event , v$sesstat

 

3。SQL 的情况 v$sql ,v$sqlarea , v$SQL_PLAN , V$SQL_PLAN_STATISTICS, v$sqltext_with_newlines

 

4. Latch / lock /ENQUEUE v$latch ,v$latch_children , v$latch_holder , v$lock ,V$ENQUEUE_STAT ,V$ENQUEUE_LOCK

 

5. IO 方面的 v$segstat ,v$filestat , v$tempstat ,v$datafile , v$tempfile

 

6.shared pool / Library cachev$Librarycache , v$rowcache , x$ksmsp

 

7.几个advice也不错v$db_cache_advice , v$PGA_TARGET_ADVICE, v$SHARED_POOL_ADVICE

 

--------

 

本视图列出Oracle 服务器当前拥有的锁以及未完成的锁或栓锁请求。如果你觉着session在等待等待事件队列那你应该检查本视图。如果你发现session在等待一个锁。那么按如下先后顺序：

 

1.使用V$LOCK找出session持有的锁。

 

2.使用V$SESSION找出持有锁或等待锁的session执行的sql语句。

 

3.使用V$SESSION_WAIT找出什么原因导致session持有锁堵塞。

 

4.使用V$SESSION获取关于持有锁的程序和用户的更多信息。

 

V$LOCK中的常用列

 

l  SID：表示持有锁的会话信息。

 

l  TYPE：表示锁的类型。值包括TM和TX等。

 

l  LMODE：表示会话等待的锁模式的信息。用数字0－6表示，和表1相对应。

 

l  REQUEST：表示session请求的锁模式的信息。

 

l  ID1,ID2：表示锁的对象标识。

 

公共锁类型

　　在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

　　当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁，如下表1。

TX：行级锁，事务锁

 

l  在改变数据时必须是排它模式(mode6)。

 

l  每一个活动事务都拥有一个锁。它将在事务结束(commit/rollback)时释放。

 

l  如果一个块包括的列被改变而没有ITL(interested transaction list)槽位(entries)，那么session将锁置于共享模式(mode4)。当session获得块的ITL槽位时释放。

 

l  当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

l  指出回滚段和事务表项

按下列项以避免竞争：

l   避免TX－6类型竞争，需要根据您的应用而定。

 

l   避免TX－4类型竞争，可以考虑增加对象INITRANS参数值。

TM：表级锁

n   数据库执行任何DDL语句时必须是排它模式；例如，alter table,drop table。

n   执行像insert,update,delete这类DML语句时处于共享模式。它防止其它session对同一个对象同时执行ddl语句。

n   任何对象拥有正被改变的数据，TM锁都将必须存在。

n   锁指向对象。

在TM队列避免竞争，可以考虑屏蔽对象表级锁，屏蔽表级锁防止对象执行任何ddl语句。

ST：空间事务锁

 

l   每个数据库(非实例)拥有一个ST锁。

l   除了本地管理表空间，在space管理操作(新建或删除extents)时必须是排它模式。

l   对象creation,dropping, extension, 以及truncation都处于这种锁

l   多数公共原因的争夺，是在磁盘排序(并非使用真正的临时表空间)或回滚段扩展或收缩。

按如下项以避免竞争：

l   使用真正的临时表空间(truetemporary tablespaces)，利用临时文件。临时段在磁盘排序之后并不创建或删除。

l   使用本地管理表空间。

l   指定回滚段避免动态扩展和收缩，或使用自动undo management。

l   避免应用执行创建或删除数据库对象。

UL：用户定义锁

用户可以自定义锁。内容较多并与此节关系不大，略过。

V$LOCK中的连接列

Column                                     View                                   JoinedColumn(s)

SID                                          V$SESSION                       SID

ID1, ID2, TYPE                          V$LOCK                             ID1, ID2, TYPE

ID1                                           DBA_OBJECTS                  OBJECT_ID

TRUNCID1/65536)                     V$ROLLNAME                    USN

1.        如果session在等待锁，这可被用于找出session持有的锁，。

2.        可被用于找出DML锁类型的被锁对象(type='TM')

3.        可被用于找出行级事务锁(TYPE='TX')使用中的回滚段，不过，需要通过V$TRANSACTION连接查询得到。

 

表1 Oracle的TM锁类型

 锁模式

 锁描述

 解释

 SQL操作

 

0

 none

 

1

 NULL

 空

 Select

 

2

 SS(Row-S)

 行级共享锁，其他对象只能查询这些数据行

 Select for update、Lock forupdate、Lock row share

 

3

 SX(Row-X)

 行级排它锁，在提交前不允许做DML操作

 Insert、Update、Delete、Lock rowshare

 

4

 S(Share)

 共享锁

 Create index、Lock share

 

5

 SSX(S/Row-X)

 共享行级排它锁

 Lockshare row exclusive

 

6

 X(Exclusive)

 排它锁

 Alter table、Drop able、Drop index、Truncatetable 、Lock exclusive

 

　　数字越大锁级别越高, 影响的操作越多。一般的查询语句如select ... from ... ;是小于2的锁, 有时会在v$locked_object出现。select ... from ... for update; 是2的锁。

　　当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select...for update操作。insert / update / delete ... ;    是3的锁。  

　　没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。

　　创建索引的时候也会产生3,4级别的锁。locked_mode为2,3,4不影响DML(insert,delete,update,select)操作,        但DDL(alter,drop等)操作会提示ora-00054错误。有主外键约束时update / delete ... ; 可能会产生4,5的锁。DDL语句时是6的锁。

　　如果出现了锁的问题, 某个DML操作可能等待很久没有反应。当你采用的是直接连接数据库的方式，也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接，因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。记得在数据库级别用alter system kill session 'sid,serial#';杀掉不正常的锁。

示例：

 

我按照自己的理解演示的TX,TM锁如下：

 

1.create table TMP1(col1  VARCHAR2(50));--创建临时表

 

2.select * from v$lock;--关掉当前锁信息

 

3.select * from tmp1 for update; --加锁

 

4.select * from v$lock;   ---看看现在的锁列表，是不是多了两条记录。Type分别为tx,tm，对照表1。

 

5.新开一个连接，然后

 

select * from tmp1 for update;  --呵呵，等待状态了吧

 

6.select * from v$lock;  --又新增了两条记录，其它一条type=tx,lmode=0

 

7.查看当前被锁的session正在执行的sql语句

 

select a.username, a.machine, a.sid,a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text"SQL"

 

from v$session a, v$lock b, v$sqltext c

 

where a.username is not null and a.lockwait= b.kaddr and c.hash_value =a.sql_hash_value

 

8.将之前的for update语句commit或者rollback，然后新开连接的session拥有锁。有兴趣的朋友还可以试试两条for update的时候，关闭先执行的那个窗口，看看oracle会给出什么样的响应。

　　这一节是我在自整理v$系列视图以来花费时间和精力最多的一个，我反复看了document，又从网上搜索了各种资料实际使用案例等，就是不开窍。这一节至今我也仍未有把握说尽在掌握，所以在上述文字中除了例子，我如实贴出了收集来的内容，未加任何自我理解，就是担心万一我的理解有误，会对其它浏览本文的人造成困扰。同时我把在收集过程中自我感觉对理解v$lock可能有帮助的资料地址列出，供有心人参考：

Oracle数据库中的锁机制研究

 DB2和 Oracle的并发控制（锁）比较

 

Itpub论坛的oracle专题深入讨论区也有一篇非常精彩的讨论，地址如下：

 我对ORACLE数据锁的一点体会

 

本视图列出系统上的每个事务处理所获得的所有锁。

V$LOCKED_OBJECT中的列说明：

l        XIDUSN：回滚段号

 

l        XIDSLOT：槽号

 

l        XIDSQN：序列号

 

l        OBJECT_ID：被锁对象ID

 

l        SESSION_ID：持有锁的sessionID

 

l        ORACLE_USERNAME：持有锁的Oracle 用户名

 

l        OS_USER_NAME：持有锁的操作系统用户名

 

l        PROCESS：操作系统进程号

 

l        LOCKED_MODE：锁模式，值同上表1

示例：

 

1.以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：

 

select object_id,session_id,locked_modefrom v$locked_object;

 

select t2.username, t2.sid, t2.serial#,t2.logon_time

 

 from v$locked_object t1, v$session t2

 

 where t1.session_id = t2.sid order byt2.logon_time;

 

如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：

 

alter system kill session 'sid,serial#';