Oracle 常见的数据库视图

//查看正在运行的事物
SELECT s.sid,
s.serial#,
s.event,
a.sql_text,
a.sql_fulltext,
ername,
s.status,
s.machine,
s.terminal,
s.program,
a.executions,
s.sql_id,
p.spid,
a.direct_writes
FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s
LEFT JOIN v$sqlarea a
ON s.sql_id = a.sql_id
INNER JOIN v$process p
ON s.paddr = p.addr


2009-04-05
oracle 动态性能(V$)视图
C.1 动态性能视图
Oracle 服务器包括一组基础视图，这些视图由服务器维护，系统管理员用户 SYS 可以
访问它们。这些视图被称为动态性能视图，因为它们在数据库打开和使用时不断进行更新，
而且它们的内容主要与性能有关。
虽然这些视图很像普通的数据库表，但它们不允许用户直接进行修改。这些视图提供
内部磁盘结构和内存结构方面的数据。用户可以对这些视图进行查询，以便对系统进行管理
与优化。
文件CATALOG.SQL 包含这些视图的定义以及公用同义词。必须运行CATALOG.SQL 创建这
些视图及同义词。
C.1.1 V$ 视图
动态性能视图由前缀V_$标识。这些视图的公用同义词具有前缀V$。数据库管理员或用
户应该只访问V$对象，而不是访问V_$对象。
动态性能视图由企业管理器和Oracle Trace 使用，Oracle Trace 是访问系统性能信息
的主要界面。
建议： 一旦实例启动，从内存读取数据的V$视图就可以访问了。从磁盘读取数据的视
图要求数据库已经安装好了。
警告：给出动态性能视图的有关信息只是为了系统的完整性和对系统进行管理。公司
并不承诺以后也支持这些视图。
C.1.2 GV$ 视图
在Oracle 中，还有一种补充类型的固定视图。即GV$（Global V$，全局V$）固定视图。
对于本章介绍的每种V$ 视图（除V$CACHE_LOCK、V$LOCK_ACTIVITY、V$LOCKS_WITH_COLLISIONS
和V$ROLLNAME 外），都存在一个GV$视图。在并行服务器环境下，可查询GV$视图从所有限
定实例中检索V$视图的信息。除V$信息外，每个GV$视图拥有一个附加的名为INST_ID 的
整





几个常用视图的说明

? v$lock
? v$sqlarea
? v$session
? v$sesstat
? v$session_wait
? v$process
? v$transaction
? v$sort_usage
? v$sysstat

九个重要视图

1）v$lock
给出了锁的信息，如type字段， user type locks有3种：TM，TX，UL，system type locks有多种，常见的有：MR，RT，XR，TS等。我们只关心TM，TX锁。
当TM锁时，id1字段表示object_id；当TX锁时，trunc(id1/power(2,16))代表了回滚段号。
lmode字段，session持有的锁的模式，有6种：
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
request字段，process请求的锁的模式，取值

范围与lmode相同。
ctime字段，已持有或等待锁的时间。
block字段，是否阻塞其它锁申请，当block=1时说明这个session阻塞了别的session。

2）v$sqlarea
共享池中存储的sql和一些相关的信息，如累计的执行次数（executions），逻辑读（buffer_gets），物理读（disk_reads）等统计信息。
根据address和hash_value可以定位一条sql。sql_text字段存的这个sql的前1000个字符。查找整个的sql还需去v$sqltext或者v$sqltext_with_newlines。

3）v$session
所有当前session的信息，包括一些用户信息如username，terminal，program，logon_time等，command字段表示session执行的什么命令。
通过v$session.paddr=v$process.addr跟process相关，通过v$session.taddr=v$transaction.addr跟trancation相关。
通 过sid可以到相关视图中查询与这个session相关的各种信息，如v$sesstat中有session的统计信息，v$sess_io中有 session的io统计信息，v$session_wait中有session的等待信息，v$session_event中有session在一段时 间内所等待的各种事件的信息。
根据lockwait字段可以查询当前正在等待的锁的相关信息：
select * from v$lock where kaddr in (select lockwait from v$session where sid=&sid);
(sql_address,sql_hash_value)，(prev_sql_addr,prev_hash_value) 根据这两组字段, 可以查询到当前session正在执行的或最近一次执行的sql语句的详细信息：
select * from v$sqltext where address = &sql_address and hash_value = &sql_hash_value;

4）v$sesstat
根据session id可以得到这个session的统计信息：
select ,b.value
from v$statname a,v$sesstat b
where a.STATISTIC#=b.STATISTIC#
and b.sid=&sid
and b.value <>0
order by b.value;

5）v$session_wait
根据session id可以得到这个session的等待情况。
event字段表示等待事件。
p1、 p2、p3告诉我们等待事件的具体含义，如果wait event是db file scattered read，p1=file_id/p2=block_id/p3=blocks，然后通过dba_extents即可确定出热点对象；如果是latch free的话，p2为闩锁号，它指向v$latch。
P1RAW,P2RAW,P3RAW列对应P1,P2,P3的十六进制值；P1TEXT,P2TEXT,P3TEXT列对应P1,P2,P3列的解释。
--求等待事件及其对应的latch
col event format a32
col name format a32
select sid,event,p1 as file_id, p2 as "block_id/latch", p3 as blocks,
from v$session_wait sw,v$latch l
where event not like '%SQL%' and event not like '%rdbms%'
and event not like '%mon%' and sw.p2 = tch#(+);
--求等待事件及其热点对象
col owner format a18
col segment_name format a32
col segment_type format a32
select owner,segment_name,segment_type
from dba_extents
where file_id = &file_id and &block_id between block_id
and block_id + &blocks - 1;
--综合以上两条sql，同时显示latch及热点对象(速度较慢)
select sw.sid,event,,de.segment_name
from v$session_wait sw,

v$latch l,dba_extents de
where event not like '%SQL%' and event not like '%rdbms%'
and event not like '%mon%' and sw.p2 = tch#(+) and sw.p1 = de.file_id(+) and p2 between de.block_id and de.block_id + de.blocks - 1;
--如果是非空闲等待事件，通过等待会话的sid可以求出该会话在执行的sql
select sql_text
from v$sqltext_with_newlines st,v$session se
where st.address=se.sql_address and st.hashvalue=se.sql_hash_value
and se.sid =&wait_sid order by piece;
其中 STATE 列 的描述如下(参考 Oracle Database Reference 10g Release 1 Part Number B10755-01):
? 0 - WAITING (当前等待的 Session)
? -2 - WAITED UNKNOWN TIME (最后等待持续时间未知)
? -1 - WAITED SHORT TIME (最后的等待 <1/100 秒)
? >0 - WAITED KNOWN TIME (WAIT_TIME = 最后等待持续时间)

6）v$process
根据session id可以查到操作系统进程的信息：
select * from v$process where addr in (select paddr from v$session where sid=&sid );
spid字段是操作系统进程号，可以用来进行 kill -9 spid 这么一个操作。
可以找到 unix命令 top 出来的最耗cpu资源的pid，根据pid最耗资源的sql：
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC
/
7）v$transaction
根据session id可以查到当前session正在执行的事务信息：
select * from v$transaction where addr in (select taddr from v$session where sid=&sid );
看下面这两个字段可以看到事务进行到什么程度了：
USED_UBLK
NUMBER
Number of undo blocks used

USED_UREC
NUMBER
Number of undo records used
重复查询这两个值，可以看到变化，可以估计事务的进度，尤其是长时间的回滚操作，当这两个值为0，回滚也就完成了。

8）v$sort_usage
temp表空间的使用情况，当temp表空间变得巨大的时候，根据session_addr可以得到session id，根据sqladdr和sqlhash可以得到正在执行的sql：
select ername,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where ='db_block_size'
and su.session_addr=se.saddr
and s.hashvalue=su.sqlhash
and s.address=su.sqladdr
order by ername,se.sid;

9）v$sysstat
所有instance的统计信息。



dba_开头.....
dba_users 数据库用户信息
dba_segments 表段信息
dba_extents 数据区信息
dba_objects 数据库对象信息
dba_tablespaces 数据库表空间信息
dba_data_files 数据文件设置信息
dba_temp_files 临时数据文件信息
dba_rollback_segs 回滚段信息
dba_ts_quotas 用户表空间配额

信息
dba_free_space数据库空闲空间信息
dba_profiles 数据库用户资源限制信息
dba_sys_privs 用户的系统权限信息
dba_tab_privs用户具有的对象权限信息
dba_col_privs用户具有的列对象权限信息
dba_role_privs用户具有的角色信息
dba_audit_trail审计跟踪记录信息
dba_stmt_audit_opts审计设置信息
dba_audit_object 对象审计结果信息
dba_audit_session会话审计结果信息
dba_indexes用户模式的索引信息
user_开头
user_objects 用户对象信息
user_source 数据库用户的所有资源对象信息
user_segments 用户的表段信息
user_tables 用户的表对象信息
user_tab_columns 用户的表列信息
user_constraints 用户的对象约束信息
user_sys_privs 当前用户的系统权限信息
user_tab_privs 当前用户的对象权限信息
user_col_privs 当前用户的表列权限信息
user_role_privs 当前用户的角色权限信息
user_indexes 用户的索引信息
user_ind_columns用户的索引对应的表列信息
user_cons_columns 用户的约束对应的表列信息
user_clusters 用户的所有簇信息
user_clu_columns 用户的簇所包含的内容信息
user_cluster_hash_expressions 散列簇的信息
v$开头
v$database 数据库信息
v$datafile 数据文件信息
v$controlfile控制文件信息
v$logfile 重做日志信息
v$instance 数据库实例信息
v$log 日志组信息
v$loghist 日志历史信息
v$sga 数据库SGA信息
v$parameter 初始化参数信息
v$process 数据库服务器进程信息
v$bgprocess 数据库后台进程信息
v$controlfile_record_section 控制文件记载的各部分信息
v$thread 线程信息
v$datafile_header 数据文件头所记载的信息
v$archived_log归档日志信息
v$archive_dest 归档日志的设置信息
v$logmnr_contents 归档日志分析的DML DDL结果信息
v$logmnr_dictionary 日志分析的字典文件信息
v$logmnr_logs 日志分析的日志列表信息
v$tablespace 表空间信息
v$tempfile 临时文件信息
v$filestat 数据文件的I/O统计信息
v$undostat Undo数据信息
v$rollname 在线回滚段信息
v$session 会话信息
v$transaction 事务信息
v$rollstat 回滚段统计信息
v$pwfile_users 特权用户信息
v$sqlarea 当前查询过的sql语句访问过的资源及相关的信息
v$sql 与v$sqlarea基本相同的相关信息
v$sysstat 数据库系统状态信息
all_开头
all_users 数据库所有用户的信息
all_objects 数据库所有的对象的信息
all_def_audit_opts 所有默认的审计设置信息
all_tables 所有的表对象信息
all_indexes所有的数据库对象索引的信息
session_开头
session_roles 会话的角色信息
sessi

on_privs 会话的权限信息
index_开头
index_stats 索引的设置和存储信息
伪表
dual 系统伪列表信息