月份：2014年3月

什么是执行计划
执行计划显示了执行一个sql语句所需步骤的详细信息.这些步骤代表了一组数据库操作它们会消费和生产行数据.这些操作的顺序以及它们的实现取决于查询优化器对查询转换和物理优化技术的联合使用.执行计划通常是以表格形式来显示,这个执行计划实际上是一个树形结构.例如下面是一个基于sh方案的查询:
SELECT prod_category, AVG(amount_sold)
FROM sales s, products p
WHERE p.prod_id = s.prod_id
GROUP BY prod_category;
下面的表格是上面语句的执行计划:

树形结构形式的执行计划如下:

上面的表格代表了一个自上而下,从左到右遍历的执行树.当读取一个执行计划树时你应该从底部的左边开始然后自下而上.在上面的这个例子中从查看树的叶子块开始.在这种情况下树的叶子块是对products和sales表进行全表扫描来实现的.由这些表扫描产生的行数据将被连接操作来消费使用.这里连接操作是一个哈希连接.最后group-by操作使用哈希来实现的它将消费由连接操作产生的行数据并将最终结果返回给终端用户.

显示执行计划
有两种常用的方法来显示一个sql语句的执行计划:
Explain plan命令—它能不用实际执行sql语句就能显示出其执行计划

V$sql_plan—在oracle9i中引入的一个动态性能视图它显示一个sql语句已经被编译成游标并存储在游标缓存中的执行计划.在特定条件下使用explain plan显示的执行计划可能不同于使用v$sql_plan所显示的执行计划.例如,当sql语句包含绑定变量时使用explain plan在显示执行计划时会忽略掉绑定变量的值,当使用v$sql_plan显示执行计划时会在生成执行计划的过程中考虑绑定变量的值.
在oracle9i中引入了dbms_xplan包使得显示执行计划变得更加容易,而且这个dbmx_xplan包在后续的版本中功能更加增强了.这个dbms_xplan包提供了一些pl/sql接口来显示不得来源的执行计划:
Explain plan命令
V$sql_plan
Automatic workload repostitory(Awr)
Sql tuning set(STS)
Sql plan baseline(spm)

使用explain plan命令与dbms_xplan.display函数
下面的例子将说明使用dbms_xplan包所提供的不同函数来怎样生成和显示执行计划

Dbms_xplan.display函数的参数如下:
Plan table name(缺省值是’plan_table’)
Statement_id(缺省值是null意味着最后一个被插入plan table的语句)
Format 控制着显示信息的总量(缺省值是’typical’)

为了利用explain plan的功能你需要有合适的权限来运行你要试图explain plan的语句.一个缺省的plan_table对于每一个用户都是存在的不需要提前创建.

使用dbms_xplan.display_cursor函数
一种替代的方法是真实执行sql语句来生产执行计划并使用dbms_xplan.display_cursor函数来显示执行计划.

Dbms_xplan.display_cursor函数的参数值如下:
Sql_id(缺省值是null,意味着在这个会话中最后一个执行的sql语句)
Child number(default 0),
Format 控制着显示信息的数量(缺省值是’typical’)
除了要有实际运行这个sql语句的权限之外还要有对v$sql_plan,v$sql_plan_detail和select_catalog_role的select权限.

格式化执行计划
Dbms_xplan包中函数的格式化参数是高度可定制的在执行计划输出中可以根据需要来显示少量或大量的详细信息.这里有三个预先定义的格式变量:
Basic 在执行计划中只会显示ID,operation和name列
Typical 在执行计划中显示了在basic级别的信息之外还包括了额外优化相关的内部信息比如,cost,cardinality,estimates等等.在执行计划中这些信息显示了每一个操作优化器所认为的操作成本,生成的行记录数等等.也显示了每一个操作的谓词评估.有两种类型的谓词:access和filter.access谓词对于索引来说将通过对合适的列应用搜索条件来检索相关的数据块.filter谓词在检索数据块后来进行评估.

All 在执行计划中显示了在typical级别的信息之外还包括了每一个操作产生的表达式(列)列表.提示别名和查询块名字属于outline信息.最后两个片段的信息可以作为参数对语句添加提示.

低级别的选项可以包括或者排除详细信息比如谓词和cost信息.下面的信息显示了基本的执行计划和谓词信息和优化器成本列cost

也可以使用低级别的参数来排除其它信息.下面的例子显示了排除优化器成本cost和bytes列信息:

Note部分
除了执行计划和谓词信息之外,dbms_xplan包在note部分显示了其它的信息.比如在查询优化或星型转换时应用于查询的动态抽样.例如下面的例子中表sales没有统计信息,所以优化器在查询优化时使用动态抽样,在显示执行计划时加上note信息:

Note部分的信息当格式选项被设置为typical或all时是自动显示的.

什么是成本
Oracle优化器是一个基于成本的优化器.对一个sql语句选择执行计划它其实只是优化器考虑的许多替代执行计划中的一个.优化器会选择成本值最低的执行计划.这里的成本代表了对执行计划的资源使用的评估.成本值越低的执行计划其执行效率会越高.优化器成本模型会对查询计算IO,CPU和网络资源使用情况.

在执行计划中整个执行计划的成本(在第0行显示),每一个单独的操作也显示了执行成本.然而它并不能可以调整.这个成本是一个内部单元用于执行计划的比较.

理解执行计划
为了判断你是否正在查找一个好的执行计划,需要理解优化器判断执行计划首先要考虑的是什么.如果优化器在其评估或者计算中有任何问题导致了选择了次优的执行计划,你应该查看执行计划和其评估.评估的组件有:
Cardinality—-评估每一个操作将产生的行记录数
Access method —-数据被访问的方法可以是表扫描或索引扫描
Join method –-用于表连接的方法(哈希,排序合并,嵌套循环)
Join order –表连接的先后顺序
Partition pruning –-对于查询来说只有必须要被访问的分区才会被访问
Parallel execution –在并行执行情况中,执行计划中的每一个操作是否正在被并行执行,是否使用了正确的数据分布方法

下面将详细说明在执行计划中的这些组件.
Cardinality
基数是评估每一个操作将返回的行记录数.优化器判断每一个操作的基数是基于输入的表和列级统计信息(或者通过动态抽样所得到的统计信息)并使用复杂的一组公式来评估的.在一个单表查询(没有直方图信息)中只有一个等值谓词时将会使用一个简化的公式.在这种情况下优化器会假设列的数据是均匀分布且计算这个查询的基数是通过将表的总行数除以谓词列中不重复值的个数.
下面的查询hr方案中的employees表中107行记录

在表employees中job_id有19个不相同的值所以优化器预测这个查询语句的基数为107/19=5.6因此用dbms_xplan显示为6行

评估基数是很重要的因此尽可能的准确因为他们会影响执行计划的访问路么,连接顺序.然而有多个因素可能导致错误的基数评估即使当基表和列统计统计信息及时更新的情况下.这些因素包括:
数据倾斜
对单个表使用多个单列谓词
在where子句谓词中使用函数加密的列
复杂的表达式

在前面的例子中在employees表中的job_id的数据是倾斜的.不是每一个job_id都有相同的雇员数.实际上在employees表中job_id为’AD_VP’的只有两个雇员,优化器评估的的大小是他的三倍.为了精确的反映数据倾斜,需要对job_id列生成直方图.直方图的出现将会改变优化器基数评估的公式.

缺省情况下oracle会基于列使用的统计数据和数据倾斜的出现来自动判断列需不需要生成直方图.如果要手动创建直方图可以使用下面的命令:
SQL > Exec DBMS_STATS.GATHER_TABLE_STATS(‘HR’,’EMPLOYEES’,
method_opt=>’FOR COLUMNS SIZE 254 JOB_ID’);
当有了直方图后优化器能评估出将要返回的正确行数:

尽管对于这个查询有了更准确的基数评估后执行计划没有发生变化但还是要准确的评估其基数.

判断正确的基数
为了手动判断优化器是否已经正确的评估了正确的基数,可以对于查询中的每一个表使用一个简单的select count(*) 查询并应用属于这个表的where子句来检查.对于这个例子可以先使用:

另外也可以在sql语句中使用gather_plan_statistics提示来自动收集全面的运行时统计信息.这个提示会记录在每一步操作中真实的基数(返回的行数).执行时(运行)基数使用格式化参数’allstats last’的dbms_xplan.display_cursor可以在执行计划中显示出来.额外的列叫做A-Rows它是实际返回的行数

注意使用gather_plan_statistics提示对sql语句的执行时间有影响,所以你应该只在分析的情况下使用它.当初始化参数statistics_level=all的情况下不使用gather_plan_statistics提示也是可以显A-Row列. SQL*Monitoring功能—oracle enterprise manager或者pl/sql接口对于sql语句总是会显示A-Rows列而不会有任何开销.

Access method
访问方法或访问路径—显示了每一个表(或索引)中的数据将怎样被访问.访问方法是在执行计划中的operation列显示的

Oracle支持九种常见的访问方法:
Full table scan –从一个表中读取所有的行记录并过滤掉不满足where子句中谓词条件的记录.一个全表扫描将使用多块IO(通常是1MB IOs).当要从一个表中返回大部分行记录,或者表中没有索引或者存在的索引不能被使用或者它的成本值最低时就会选择全表扫描.决定使用全全扫描也会受以下因素影响:
初始化参数db_multi_block_read_count
Parallel degree
Hints
缺少可用的索引
使用索引的成本更高

Table access by rowed—行的rowid指定的数据文件,数据块,以及行在数据块中的位置.oracle首先从where子句的谓词或者从表中的一个索引或多个索引中获得rowid.oracle然后会基于获得的rowid来回表定位所选的每一行记录的位置再一行一行访问.

Index unique scan—扫描唯一索引只会返回一行记录.在等值谓词用于一个唯一索引或一个主键列上会使用唯一索引扫描

Index range scan—oracle访问相邻的索引条目然后使用索引中的rowid值来从表中检索相关的行记录.索引范围扫描可以是有边界也可以是无边界.当对一个非唯一索引键使用等值谓词或者对一个唯一键使用非等值或范围谓词时将会使用索引范围扫描(=,< ,>,like),数据会以索引列的升序返回.

Index range scan descending 索引范围降序扫描—与索引范围扫描的概念是相同的,当order by … descending子句中的列是某个索引列表的子集时就会使用.

Index skip scan –正常情况下为了使用一个索引,索引键的前缀(索引的前导列)将要在查询中被引用.然而,除了索引中的第一列外其它的列在语句中被引用,oracle可以进行索引跳跃扫描来跳过索引的第一列而使用剩下的列.如果在一个复合索引中前导列只有几个不同的值而在非前导列中有大量不同值时是有用的.

Full index scan—完全索引扫描不会读取索引结构中的每一个索引块.索引完全扫描会处理索引的所有叶子块,但只在足够的分支块中找到第一个叶子块.当查询语句中所引用的列都在索引列中存在这时使用完全索引扫描比扫描表成本更低.在以下情况下可能使用单块IO:
一个orader by子句有索引中所有的列且顺序和索引相同(也可以是索引列中的子集)
查询要求执行一个排序合并连接且查询中所引用的所有列都出现在索引列中
查询中引用列的顺序与索引前导列的顺序相同
一个group by 子句出现在查询中,group by子句中的列出现在索引列中.

Fast full index scan—这是一种替代的完全表扫描当索引包含查询所需要的所有列时且在索引键中至少有一个列有not null约束.它不能用来消除一个排序操作,因为数据访问不遵循索引键.它将使用多块读来读取索引中的所有索引块,与完全索引扫描不一样.

Index join—连接相同表中的 多个索引这个集合包含了查询中所引用的所有列.如果索引连接被使用那么就不需要进行表访问.因为所有相关的列都能从索引中得到,索引连接操作不能消除排序操作.

Bitmap index—位图索引使用每一个索引键的一组位图值和一个映射函数将每一个位图转换成rowid.当where子句中的几个谓词使用boolean操作来解决and和or条件时oracle能够有效的合并位图索引

如果看到的执行计划中访问方法不是你所期望的,可以检查对这些对象的基数评估是不是正确的,连接顺序所允许的访问方法是不是你期待的

Join method
连接方法描述了来自两个数据生产者的数据怎样连接在一起.可以通过查看执行计划中的operations列来识别sql语句中的连接方法

Oracle数据库提供了几种连接方法和连接类型

Join methods

Hash joins—哈希连接用于大数据集的连接.oracle优化器在内存中基于连接键使用两个表或者两个数据源中的小者来构建一个哈希表,然后扫描大表基于连接键执行相同的哈希运算.对于每一个值都会探测之前构建的哈希表如果匹配就返回这一行.

Nested loops joins—嵌套循环连接当第一要访问的表有较小的数据集且对于第二个表存在一种有效的访问方式(例如索引扫描)时是很有用的.对于第一个表(外部表)中的每一行,oracle将要访问第二个表(内部表)中的所有行.可认将它看作是两个嵌入的for循环.在oracle11g中为了减少物理I/O的整体延迟嵌套循环连接的内部实现发生了改变,所以在执行计划中的operations列中会看到两个nested loops.在之前的oracle版本中只会看到一个.

Sort merge joins—当两个表的连接条件是等值条件时排序合并连接是非常有用的,比如< ,<=,>,>=.对于大数据集来说排序合并连接比嵌套循环连接性能更好. 排序合并连接是由两个步骤组成:
排序连接操作:所有输入会基于连接键进行排序
合并连接操作:排序列表将被合并

如果一个表中存在一个索引能够消除排序操作那么排序合并连接就更会被优化器所选择.在下面的例子中只有来自sales表的记录需要进行排序(ID 5),而products表中的记录基于连接键使用主键索引已经被排序了(ID 4).

Cartesian join—优化器用一个数据源中的每一行与另一个数据源中的所有行进行连接.通常情况下如果被调用的表较小或都一个或多个表与语句中的任何其它表没有连接条件的情况下会被优化器选择.笛卡尔连接不常见,所以当它出现时可能是基数评估出现了问题.

Join types
Oracle提供了几种连接类型:inner join,(left) outer join,full outerjoin,anti join,semi join,grouped outer join等等.其中inner join是最常见的连接类型,因此执行计划中没有出现关键字”inner”.

Outer join—外连接将返回满足条件的所有行和没有使用(+)标识的其它表中不满足连接条件的行记录.例如t1.x=t2.x(+),这里t1是左表它的不满足连接条件的行记录将会被返回.

Join order
连接顺序是在一个多表sql语句中每一个表被加入连接的一个顺序.为了判断一个执行计划中表的连接顺序可以查看operation列中表的缩进,在下面的图表中sales和products表的缩进是相同的且它们都比customers表更缩进.因此sales和products表首先使用一个哈希连接进行连接然后它们的连接结果再与customers表进行哈希连接.

在一些复杂的sql语句中通过查看operations列中表的缩进来判断表的连接顺序是不容易的.在这种情况下使用带有format参数的dbms_xplan过程来显示执行计划的outline信息,它包括了连接顺序.例如,下面的图表是使用带有format选项的dbms_xplan.display_cursor来生成outline信息.
DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>’Typical + outline’));

在outline信息中查看以leading开头的行.这一行显示了查询的连接顺序.在这个例子中可以看到”P”,”S”,”C”三个字母,这三个字母是查询中所引用的三个表的别名.P(products)表与S(sales)表进行连接然后再与C(customers)表连接.

连接顺序是基于成本来决定的所以基数评估和访问路径对连接顺序是有强烈影响的.优化器也总是遵守一些基本规则:
大多数产生一行结果集的连接总是会先被执行.优化器基于表的unique和primary key约束来进行判断.

当使用外连接时没有外连接操作符(+)的表必须在谓词中其它有外连接操作符的表之后这样才能确保不满足连接条件的行记录才能被加入到返回结果集中.

当一个子查询已经被转换成反连接或半连接时那么子查询中的表将在外部查询块中的表之后进行连接.然而,哈希反连接和哈希半连接在特定情况会覆盖这种连接顺序.

如果不能进行视图合并那么视图中的所有表会在与视图外部表连接之前进行连接

如果连接顺序不是你所期待的那么检查每一个对象的基数评估和访问方法是否正确.

Partitioning
分区允许一个表，索引或索引组织表被分成一些较小的片段.每一个数据库对象的片段叫作分区.分区修剪或分区消区是使用分区来提高性能的最简单的方法.例如一个程序有一个orders表包含了最近2年所有的订单记录.而且这个orders表通过day来进行分区.一个查询只查询一周的订单记录它只会访问orders表的7个分区而不是730个分区.
分区修剪在执行计划中的pstart和pstop列中是可见的.pstart列包含了将被访问的第一个分区,pstop列包含了将被访问的最后一个分区.在下面的图表中sales表有4个分区被访问,分区名是9,10,11,12

当对一个按day进行分区并且按cust_id列进行子分区的表进行一个简单查询那么在pstart和pstop列中会出现更多的数字,这些额外的数字意味着什么例如:

当使用组合分区时,oracle每一个分区的数字从1到n(绝对分区数字).对于一个只有一层分区的表,这些绝对数代表了单层分区表在磁盘上的真实物理段.

在组合分区表中,然而一个分区是一个逻辑条目不代表磁盘上的真实物理段.每一个分区又是被细分为多个子分区.每一个子分区的分区数字从1到m(单个分区的相对子分区数字).最终组合分区表中的所有子分区都被指定一个全局数字从1到(n*m)(绝对子分区数字).这些绝对数字代表了组合分区表在磁盘上的真实物理段.

所以在之前的执行计划中line 4这一行的pstart和pstop列中的数字为10,代表了全局分区数代表了磁盘上的物理段.执行计划中的line 2这一行中的pstart和pstop列的数字为5代表了分区号,执行计划中的line 3这一行中的pstart和pstop列的数字为2代表分区的了相对子分区号

在有些情况下在pstart和pstop列会出现字母或单词而不是数字.例如在这些列中可以看到单词KEY.这说明在解析时不能识别,查询将访问哪个分区但优化器相信在执行时(动态修剪)分区修剪将会发生.当查询中的基于分区键列等值谓词中包含一个函数时就会出现这种情况.例如time_id=sysdate,另一种情况动态修剪会发生当查询中在分区键列上有一个连接条件且这个表不是与分区表的所有分区进行连接时,例如,因为一个filter谓词,分区修剪将在执行时发生.在下面的例子中,time表与sales表基于分区键time_id进行连接,在where子句谓词被应用到time表且合适的time_ids被选择后那么分区修剪在执行时会发生.

如果分区修剪不象预期那样发生那么检查分区键列上的谓词.确保谓词使用了与分区键列相同的数据类型.也可以检查执行计划中的谓词信息部分的内容.如果表是哈希分区,如果基于分区键列的谓词是一个等值或in-list谓词那么分区修剪将会发生.所以如果一个表是多列哈希分区,那么只有哈希分区中的所有列都在谓词中出现才会发生分区修剪.

Parallel execution
Oracle中的并行执行是基于一组协调原则(通常叫作查询协调器或QC)和并行服务器进程的.QC在并行执行中对单个会话中初始化并行sql语句和并行服务器进程来执行工作.QC将工作分配给并行服务器进程且并可能要执行较小的不能被并行执行的工作.例如一个使用sum()操作的的并行查询要求将每个并行服务器进程的计算结果求和

QC在并行执行计划中是很容易识别的因为会出现它的名字.在下面的图表中可以查看ID 1这一行就会看到在operation列中的PX COORDINATOR.在执行计划中在这一行之上的所有操作都是由QC来完成的.因为这些操作是由单进程串行执行完成的.通常要最小化由QC所有完成的操作.在PX COORDINATOR之下的所有操作通常是由并行服务器进程所完成的.

Granules
粒度是指派给并行服务器进程工作的最小单元.为了在并行服务器进程之间得到均匀的工作分布粒度数量通常比请求的DOP高.每一个并行服务器进程将只会完成分配给它的工作粒度当它完成指定的工作粒度后会得到另外的工作粒度直到所有的工作粒度都被执行完成为止.oracle数据库对于并行执行分配工作使用的基本机制就是基于数据块范围或基于数据块粒度.在执行计划中可以看到粒度是怎样分配给并行服务器进程的.在下面图表中的执行计划中的line 7这一行的operation列中可以看到’PX BLOCK ITERATOR’,这意味着并行服务器进程将遍历完所生成的数据块范围粒度来完成表扫描.

虽然基于数据块粒度是最常见的方法,这里有些操作可能会受益于分区表的底层数据结构.这些情况下分区表将成为一个工作粒度.使用基于分区粒度一个并行服务器进程将以单个分区的所有数据为一个工作粒度.如果在操作中被访问的分区数至少等于DOP时oracle优化器会考虑基于分区的工作粒度,在下面的图表中在执行计划中的line 6这一行的operation列可以看到’PX PARTITION RANGE ALL’意味着每一个并行服务器进程将只会完成分区表中的一定范围的分区.

基于sql语句的并行度,oracle数据库将决定是使用基于数据块还是基于分区的工作粒度来完成更优的执行.
Producers and consumers
为了有效的并行执行一个sql语句,并行服务器进程实际上是以工作集来一起完成这项工作的.一组是生产行(生产者)，一组是消费行记录(消费者).例如在下面的图表中,在sales和customers表之间的并行连接使用两组并行服务器进程.生产者将扫描两个表并应用所有谓词条件然后将结果发送给消费乾(lines 9-11和lines 5-7).可以很容易的识别生产者因为它们是在PX SEND操作(line 9 & 5)之下执行操作.消费者完成实际的哈希连接并将结果发送给QC(line 8和lines 2-4).消费者可以通过PX RECEIVE来识别因为在它们执行工作之先必须先执行PX RECEIVE操作(line 8 & 4).而它们总是通过PX SEND QC(line 2)

在执行计划中在TQ列显示了类似的信息,它显示了哪一组并行服务器进程被执行.在执行计划中Q100组并行服务器进程(生产者)将首先扫描customers表.它然后将结果发送给消费者(line 5)Q102,Q100组并行服务器进程然后变成Q101组并行服务器进程(另一组生产者),Q101扫描sales表然后发送给消费者(line 9),Q102组并行服务器进程(消费者)接收来自生产者(line 8 & 4),完成连接(line 3)然后将结果发送给查询协调器

Data redistribution
在这个例子中有两个大表customers和sales在连接中被调用.为了以并行来处理这个连接,在生产者和消费者之间重新分配行源是必要的.生产者将基于数据块范围来扫描表并应用谓词条件然后将结果发送给消费者.在执行计划中的IN-OUT和PQ Distrib两列中有关于数据在生产者和消费者之间数据是怎样重新分配的信息.PQ Distrib列最有用的列且有些情况下已经替代了IN-OUT列.

下面有五种最常用的数据重新分配方法
Hash:为了在并行服务器进程之间完成一个相等的重新分配哈希数据重新分配是非常常见.一个哈希函数被应用于连接列且结果指示了哪一个消费者并行服务器进程将接收这些行源.

Broadcast:广播重新分配发生在当连接操作中两个结果集中的一个比另一个小得多的时候.代替对两个结果集重新分配行记录.数据库为了保证单个并行服务器进程能够完成它的连接操作将小结果集发送给所有消费者并行服务器进程.

Range:范围重新分配通常用于并行排序操作.单个并行服务器进程以数据范围进行工作所以QC没有进行任何排序但只存在单个并行服务器进程的结果的顺序是正确的.

KEY:键值重新分配确保结果集中单个键值被集簇在一起.这是一种优化主要用于partial partition-wise joins来确保在连接中只有一边被重新分配.

Round robin:循环数据重新分配是在发送数据到请求进程之前最后的重新分配操作.当没有重新分配约束被请求时它也可以用于查询的早期阶段.

在RAC数据库中可以在重新分配方法中看到local后缀.在RAC中本地重新分配是为了对节点间的并行查询进行最小化互连通信.在这种情况下行只会被分配给与RAC相同节点的消费者.

在下面的图表中生产者使用一个hash重新分配方法将数据发送给消费者

你会注意到在执行计划中数据重新分配的位置IN-OUT列中的值有P->P(lines 5 & 9)或P->S(line 2).P->P意味着数据由一个并行操作发送给另一个并行操作.P->S意味着数据由一个并行操作发送给一个串行操作.在line 2这一行数据被发送给QC,它是一个单进程,因此是P->S.然而如果你看到一个P->S操作

结论
Oracle优化器的目的是用来判断查询的最有效执行计划的.它是基于查询数据的统计信息和oracle数据库功特性比如hash joins parallel query和partitioning来进行决策的.通过分析执行计划和评估四个关键因素:基数评估,访问路径,连接方法和连接顺序,能够判断一个执行计划是不是最有效的执行计划.

为了诊断oracle运行缓慢的问题首先要决定收集哪些论断信息,可以采取下面的诊断方法:
1.数据库运行缓慢这个问题是常见还是在特定时间出现
如果数据库运行缓慢是一个常见的问题那么可以在问题出现的时候收集这个时期的awr或者statspack报告(通常收集时间间隔是一个小时).生成awr报告的方法如下:
awr是通过sys用户来收集持久系统性能统计信息并且这些信息保存在sysaux表空间.缺省情况下快照是一个小时生成一次并且保留7天.awr报告输出了基于指定快照之间的一系列的统计信息用于性能分析和调查其它问题.
运行基本的报告
可以执行下面的脚本来生成一个awr报告:
$ORACLE_HOME/rdbms/admin/awrrpt.sql

可以根据自己收集awr报告的原因来决定生成一个快照的时间间隔也可以指定生成awr报告的格式(text或html).

生成各种类型的awr报告
可以根据各种要求来运行各种sql脚本来生成各种类型的awr报告.每一种报告都有两种格式(txt或html):
awrrpt.sql
显示指定快照范围内的各种统计信息

awrrpti.sql
显示一个特定数据库和实例中指定快照范围内的各种统计信息

awrsqrpt.sql
显示一个指定快照范围内的一个特定的sql语句的统计信息.运行这个报告是为了检查或调查一个特定sql语句的性能

awrsqrpi.sql
显示一个特定sql在指定快照范围内的的统计信息.

awrddrpt.sql
比较在两个选择的时间间隔期间内详细的性能数据和配置情况

awrddrpi.sql
在一个特定的数据库和平共处实例中比较在两个选择的时间间隔期间内详细的性能数和配置情况

各种awr相关的操作
怎样修改awr快照的设置:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (43200 = 30 Days).
                               -- Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

创建一个awr基线:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 10,
    end_snap_id   => 100,
    baseline_name => 'AWR First baseline');
END;
/

在oracle11G中引入了一个新的dbms_workload_repository.create_baseline_template过程来创建一个awr基线模板

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name => 'MORNING',
template_name => 'MORNING',
expiration => NULL ) ;
END;
/

“expiration=>NULL”这意味着这个基线将永远保持有效.

删除一个awr基线

BEGIN
    DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
    baseline_name => 'AWR First baseline');
END;
/

也能从一个老的数据库中删除一个awr基线:

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
cascade => FALSE, dbid => 3310949047);
END;
/

删除awr快照:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(low_snap_id=>40,
High_snap_id=>80);
END;
/

也可能基于报告时间期间对创建和删除的awr基线指定一个模板:

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'MONDAY',
hour_in_day => 9,
duration => 3,
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name_prefix => 'MONDAY_MORNING'
template_name => 'MONDAY_MORNING',
expiration => 30 );
END;
/

将会在’&start_date_time’到’&end_date_time’期间的每一个星期一都会生成基线

手动生成的一个awr快照:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/

工作负载资料档案库视图:
V$ACTIVE_SESSION_HISTORY – 显示历史活动会话信息每秒抽样一样
V$METRIC – 显示度量标准信息
V$METRICNAME – 显示与每个度量标准组相关的度量标准
V$METRIC_HISTORY – 显示历史度量标准
V$METRICGROUP – 显示所有的度量标准组
DBA_HIST_ACTIVE_SESS_HISTORY – 显示历史活动会话的详细信息
DBA_HIST_BASELINE – 显示基线信息
DBA_HIST_DATABASE_INSTANCE – 显示数据库环境信息
DBA_HIST_SNAPSHOT – 显示快照信息
DBA_HIST_SQL_PLAN – 显示sql执行计划
DBA_HIST_WR_CONTROL – 显示awr设置情况

如果数据库运行缓慢在特定时间出现那么可以当问题存在时生成一个awr或statspack报告,报告的时间间隔包含了问题出现的时间.另外为了比较可以收集没有出现问题而时间间隔相同的数据库正常运行的报告这样可以对报告进行比较.

2.数据库缓慢它影响的是一个会话,几个会话还是所有会话
如果数据库缓慢它影响的是一个会话或几个会话可以对这个会话或几个会话进行10046跟踪
如果数据库缓慢它影响的是所有会话可以收集awr或statspack报告

执行10046跟踪的方法如下:
收集10046跟踪文件
10046事件是一种标准的方法用来对oracle会话收集扩展的sql_trace信息
对于查询性能问题来说通常要求记录查询的等待和绑定变量信息.这可以使用级别为12的10046跟踪来完成.下面的例子说明了在各种情况下设置10046事件.

跟踪文件的位置
在oracle11g及以上版本中引入了新的诊断架构,跟踪和核心文件存储的位置由diagnostic_dest初始化参数来控制.可以使用下面的命令来显示:

sys@JINGYONG> show parameter diagnostic_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle

注意:在有些例子中可能设置了’tracefile_identifier’来帮助找到输出的跟踪文件

会话跟踪
可以在用户会话执行sql语句之前对会话启用跟踪,在会话级别收集10046跟踪

sys@JINGYONG> alter session set timed_statistics=true;

会话已更改。

sys@JINGYONG> alter session set statistics_level=all;

会话已更改。

sys@JINGYONG> alter session set max_dump_file_size=unlimited;

会话已更改。

sys@JINGYONG> alter session set events '10046 trace name context forever,level 1
2';

会话已更改。

sys@JINGYONG> select * from dual;

D
-
X

sys@JINGYONG>exit

如果会话没有退出可以执行以下语句来禁用10046跟踪

sys@JINGYONG> alter session set events '10046 trace name context off';

会话已更改。


sys@JINGYONG> select * from v$diag_info ;

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
         1 Diag Enabled
TRUE

         1 ADR Base
/u01/app/oracle

         1 ADR Home
/u01/app/oracle/diag/rdbms/jingyong/jingyong

         1 Diag Trace
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace

         1 Diag Alert
/u01/app/oracle/diag/rdbms/jingyong/jingyong/alert

         1 Diag Incident
/u01/app/oracle/diag/rdbms/jingyong/jingyong/incident

         1 Diag Cdump
/u01/app/oracle/diag/rdbms/jingyong/jingyong/cdump

         1 Health Monitor
/u01/app/oracle/diag/rdbms/jingyong/jingyong/hm

         1 Default Trace File
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2572_10046.trc

         1 Active Problem Count
0

         1 Active Incident Count
0


已选择11行。

sys@JINGYONG> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
         1 Default Trace File
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2572_10046.trc

注意:如果会话不是彻底的关闭和禁用跟踪那么重要的跟踪信息可能会从跟踪文件中丢失.

注意:这里statistics_level=all因此它会在这种情况下收集一定程度的统计信息.这个参数有三个参数值.all,typical,basic.为了诊断性能问题会要求获得一定程度的统计信息.设置为all可能是不必要的但可以使用typical以此来获得全面的诊断信息.

跟踪一个已经启动的进程
如果要跟踪一个已经存在的会话可以使用oradebug来连接到会话初始化10046跟踪
1.通过某种方法来确定要被跟踪的会话
例如在sql*plus中启动一个会话然后找到这个会话的操作系统进行id(spid):
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/

SPID是操作系统进程标识符
PID是oracle进程标识符
如果你不知道要不得被跟踪会话的sid可以使用类似于下面的查询来帮助你识别这个会话:

column line format a79
set heading off
select 'ospid: ' || p.spid ||'  pid: '||p.pid || ' # ''' ||s.sid||','||s.serial#||''' '||
  s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';
执行结果如下:

sys@JINGYONG> column line format a79
sys@JINGYONG> set heading off
sys@JINGYONG> select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||'''
 '||
  2    s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
  3  from v$session s , v$process p
  4  where p.addr = s.paddr
  5  and s.username <> ' ';

ospid: 2529 # '30,32' Administrator WORKGROUP\JINGYONG SYS sqlplus.exe

注意:在oracle12c中对于多线程进程,在v$process视图中加入了新的列stid来找到特定的线程.因为oracle会组合多个进程到一个单独的ospid中.为了找到这个特定的线程使用下面的语法:
oradebug setospid

2.当确定进程的操作系统进程ID后然后可以使用下面的语句来初始化跟踪:
假设要被跟踪进程的操作系统进程ID是2529

SQL>connect / as sysdba
sys@JINGYONG> oradebug setospid 2529
Oracle pid: 21, Unix process pid: 2529, image: oracle@jingyong
sys@JINGYONG> oradebug unlimit
已处理的语句
sys@JINGYONG> oradebug event 10046 trace name context forever,level 12
已处理的语句
sys@JINGYONG> select * from dual;

X

sys@JINGYONG> oradebug event 10046 trace name context off
已处理的语句
sys@JINGYONG> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2529.trc

注意:连接到一个会话也可以使用oradebug setorapid
在这种情况下PID(oracle进程标识符)将被使用(而不是使用SPID):
sys@JINGYONG> oradebug setorapid 21
Oracle pid: 21, Unix process pid: 2529, image: oracle@jingyong
从显示的信息可知道使用oradebug setorapid 21与oradebug set0spid 2529是一样的
sys@JINGYONG> oradebug unlimit
已处理的语句
sys@JINGYONG> oradebug event 10046 trace name context forever,level 12
已处理的语句
sys@JINGYONG> select sysdate from dual;

11-11月-13

sys@JINGYONG> oradebug event 10046 trace name context off
已处理的语句
sys@JINGYONG> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2529.trc

注意:在oracle12c中对于多线程进程,在v$process视图中加入了新的列stid来找到特定的线程.因为oracle会组合多个进程到一个单独的ospid中.为了找到这个特定的线程使用下面的语法:
oradebug setospid

跟踪产生的跟踪文件名称类似于_.trc

实例级别的跟踪
注意:在实例级别启用跟踪因为每一个会话都会被跟踪这样对性能是有影响的
在设置这个跟踪参数后产生的每一个会话都会被跟踪断开的会话将不会被跟踪
设置系统级别的10046跟踪是用于当出现了一个问题会话但不能提前识别这个会话的情况下.在这种情况下跟踪可以被短时间地启用,这个问题可能会记录到跟踪文件中然后禁用跟踪在生成的跟踪文件中找到这个问题的原因

启用系统级别的10046跟踪:
alter system set events ‘10046 trace name context forever,level 12’;
对所有会话禁有系统级别的10046跟踪:
alter system set events ‘10046 trace name context off’;

初始化参数的设置:
当实例重新启动后对每一个会话启用10046跟踪.
event=”10046 trace name context forever,level 12″
要禁用实例级别的10046跟踪可以删除这个初始化参数然后重启实例或者使用alter system语句
alter system set events ‘10046 trace name context off’;

编写登录触发器
在有些情况下可能要跟踪特定用户的会话活动在这种情况下可以编写一个登录触发器来实现例如:

CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/

注意:为了能跟踪会话用户执行触发器需要显式的被授予’alter session’权限:
grant alter session to username;

使用SQLT来收集跟踪信息
什么是SQLTXPLAIN(SQLT)
SQLTXPLAIN也叫作SQLT,它是由专业的oracle服务技术中心提供了一个工具.SQLT输入一个SQL语句后它会输出一组诊断文件.这些诊断文件会被用来诊断性能低下的sql语句.SQLT连接到数据库并收集执行,基于成本优化的统计信息,方案对象元数据,性能统计,配置参数和类似影响SQL性能的元素.

使用SQLTXPLAIN的Xecute选项可以生成10046跟踪作为SQLT输出的一部分.

使用dbms_monitor包来进行跟踪
dbms_monitor是一个新的跟踪包.跟踪基于特定的客户端标识符或者服务名,模块名和操作名的组合形式来启用诊断和工作负载管理.在有些情况下可能会生成多个跟踪文件(例如对于一个模块启用服务级别的跟踪)使用新的trcsess工具来扫描所有的跟踪文件并将它们合成一个跟踪文件.在合并这一组跟踪文件后可以使用标准跟踪文件分析方法进行分析

查看启用的跟踪
可以查询dba_enabled_traces来检测什么跟踪被启用了.
例如:

sys@JINGYONG>select trace_type, primary_id, QUALIFIER_ID1, waits, binds
             from DBA_ENABLED_TRACES;

TRACE_TYPE                   PRIMARY_ID  QUALIFIER_ID1           WAITS        BINDS
---------------------- ---------------   ------------------      --------    -------
SERVICE_MODULE         SYS$USERS        SQL*Plus                 TRUE        FALSE
CLIENT_ID              HUGO                                      TRUE        FALSE
SERVICE                v101_DGB                                  TRUE        FALSE

在这个数据库中已经启用了三个不同的跟踪状态
1.第一行记录显示将会对在SQL*Plus中执行的所有sql语句进行跟踪
2.第二行记录显示将会对带有客户端标识符”HUGO’的所有会话进行跟踪
3.第三行记录显示将会对使用服务”v101_DGB’连接到数据库的所有程序进行跟踪

session_trace_enable函数
可以使用session_trace_enable过程来对本地实例的一个指定的数据库会话启用sql跟踪.
语法如下:
启用sql跟踪
dbms_monitor.session_trace_enable(session_id => x, serial_num => y,
waits=>(TRUE|FALSE),binds=>(TRUE|FALSE) );

禁止sql跟踪
dbms_monitor.session_trace_disable(session_id => x, serial_num => y);
其中waits的缺省值是true,binds的缺省值是false.

可以从v$session视图中查询会话id和序列号

SQL> select serial#, sid , username from v$session;

SERIAL#             SID  USERNAME
-------           -----  --------------
  1                 131
 18                 139
  3                 140
 11                 143     SCOTT

然后可以使用下面的命令来对指定的会话启用跟踪
SQL> execute dbms_monitor.session_trace_enable(143,11);
跟踪状态在数据库重启后就会被删除可以查询dba_enabled_traces视图看到没有记录
sys@JINGYONG> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2529.trc
sys@JINGYONG> select trace_type,primary_id,qualifier_id1,waits,binds
2 from dba_enabled_traces;

未选定行

当会话断开或者使用下面的命令可以禁止跟踪
SQL> execute dbms_monitor.session_trace_disable(143,11);

client_id_trace_enable函数
在多层架构环境中,一个请求从一个终端客户端通过中间层分发到不同的数据库会话.这意味着终端客户端与数据库会话的联系不是静态的.在oracle10g之前没有方法可以对一个客户端跨不同数据库会话进行跟踪.端到端的跟踪可以通过一个新的属性client_identifier来标识它是唯一标识一个特定的终端客户端.这个客户端标识符对应于v$session视图中的client_identifier列.通过系统上下文也可以查看.
语法如下:
启用跟踪
execute dbms_monitor.client_id_trace_enable ( client_id =>’client x’,
waits => (TRUE|FALSE), binds => (TRUE|FALSE) );

禁止跟踪
execute dbms_monitor.client_id_trace_disable ( client_id =>’client x’);
其中waits的缺省值是true,binds的缺省值是false.

例如:
可以使用dbms_session.set_identifier函数来设置client_identifier

sys@JINGYONG> exec dbms_session.set_identifier('JY');

PL/SQL 过程已成功完成。

sys@JINGYONG> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual;

JY


sys@JINGYONG> select client_identifier client_id from v$session where sid=30;

JY

sys@JINGYONG> exec dbms_monitor.client_id_trace_enable('JY');

PL/SQL 过程已成功完成。

使用查询来检查跟踪是否已经启用

sys@JINGYONG> select primary_id,qualifier_id1,waits,binds
  2  from dba_enabled_traces where trace_type='CLIENT_ID';
PRIMARY_ID         QUALIFIER_ID1         WAITS    BINDS
----------------   --------------        -------- --------
JY                                       TRUE     FALSE

这个跟踪在数据库重启之后还是有效的你得调用函数来禁用.
sys@JINGYONG> exec dbms_monitor.client_id_trace_disable(‘JY’);

PL/SQL 过程已成功完成。
检查生成的跟文件

Trace file /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2529.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db
System name:	Linux
Node name:	jingyong
Release:	2.6.18-164.el5
Version:	#1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:	i686
Instance name: jingyong
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 2529, image: oracle@jingyong


*** 2013-11-11 11:31:56.737
*** SESSION ID:(30.32) 2013-11-11 11:31:56.737
*** CLIENT ID:() 2013-11-11 11:31:56.737
*** SERVICE NAME:(jingyong) 2013-11-11 11:31:56.737
*** MODULE NAME:(sqlplus.exe) 2013-11-11 11:31:56.737
*** ACTION NAME:() 2013-11-11 11:31:56.737

PARSING IN CURSOR #8 len=96 dep=0 uid=0 oct=3 lid=0 tim=1384150635839986 hv=3018843459 ad='275fa5ec' sqlid='3gg23wktyzta3'
select primary_id,qualifier_id1,waits,binds
from dba_enabled_traces where trace_type='CLIENT_ID'
END OF STMT

在启用跟踪后执行的语句被记录到了跟踪文件中.

sys@JINGYONG> select primary_id,qualifier_id1,waits,binds
  2  from dba_enabled_traces where trace_type='CLIENT_ID';

未选定行

当你使用MTS时有时将会生成多个跟踪文件,不同的共享服务器进程能执行sql语句这就将会生成多个跟踪文件.对于RAC
环境也是一样.

serv_mod_act_trace_enable函数
端到端跟踪对于使用MODULE,ACTION,SERVICES标识的应用程序能够进行有效地管理和计算其工作量.service名,module和
action名提供了一种方法来识别一个应用程序中重要的事务.
你可以使用serv_act_trace_enable过程来对由一组service,module和action名指定的全局会话启用sql跟踪,除非指定了特定
的实例名.对于一个会话的service名,module名与v$session视图中的service_name和module列相对应.
语句如下:
启用跟踪
execute dbms_monitor.serv_mod_act_trace_enable(‘Service S’, ‘Module M’, ‘Action A’,
waits => (TRUE|FALSE), binds => (TRUE|FALSE), instance_name => ‘ORCL’ );

禁止跟踪
execute dbms_monitor.serv_mod_act_trace_disable(‘Service S’, ‘Module M’, ‘Action A’);
其中waits的缺省值是true,binds的缺省值是false,instance_name的缺省值是null.

例如想要对在数据库服务器使用SQL*Plus执行的所有sql语句进行跟踪可以执行以下命令:

sys@JINGYONG> select module,service_name from v$session where sid=25;
MODULE                                      SERVICE_NAME
-----------------------------               ---------------------
sqlplus@jingyong (TNS V1-V3)                SYS$USERS

sys@JINGYONG> exec dbms_monitor.serv_mod_act_trace_enable('SYS$USERS','sqlplus@j
ingyong (TNS V1-V3)');

PL/SQL 过程已成功完成。

sys@JINGYONG> select primary_id,qualifier_id1,waits,binds
  2  from dba_enabled_traces
  3  where trace_type='SERVICE_MODULE';
PRIMARY_ID       QUALIFIER_ID1                WAITS    BINDS
---------------  -------------------          -------- --------
SYS$USERS        sqlplus@jingyong (TNS V1-V3) TRUE     FALSE


启用跟踪后我们执行一个测试语句
SQL> select 'x' from dual;

'
-
x
检查生成的跟踪文件名
SQL> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
         1 Default Trace File
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_4411.trc

查看跟踪内容如下

trace file /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_4411.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db
System name:	Linux
Node name:	jingyong
Release:	2.6.18-164.el5
Version:	#1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:	i686
Instance name: jingyong
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 4411, image: oracle@jingyong (TNS V1-V3)


*** 2013-11-11 14:34:00.971
*** SESSION ID:(25.412) 2013-11-11 14:34:00.972
*** CLIENT ID:() 2013-11-11 14:34:00.972
*** SERVICE NAME:(SYS$USERS) 2013-11-11 14:34:00.972
*** MODULE NAME:(sqlplus@jingyong (TNS V1-V3)) 2013-11-11 14:34:00.972
*** ACTION NAME:() 2013-11-11 14:34:00.972

WAIT #1: nam='SQL*Net message from client' ela= 152965072 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1384151640937525
CLOSE #1:c=1000,e=521,dep=0,type=0,tim=1384151640973430
=====================
PARSING IN CURSOR #1 len=20 dep=0 uid=0 oct=3 lid=0 tim=1384151640977682 hv=2740543121 ad='275fa9e4' sqlid='04vfkrajpkrnj'
select 'x' from dual

我们执行的测试语句被记录了在跟踪文件中.

sys@JINGYONG> exec dbms_monitor.serv_mod_act_trace_disable('SYS$USERS','sqlplus@
jingyong (TNS V1-V3)');

PL/SQL 过程已成功完成。

sys@JINGYONG> select primary_id,qualifier_id1,waits,binds
  2  from dba_enabled_traces
  3  where trace_type='SERVICE_MODULE';

未选定行

使用trcsess来合并跟踪文件
从某些跟踪操作中会得到多个跟踪文件.在oracle10g之前的版本中你得手动将这些跟踪文件合并到一起.现在可以使用trcsess工具来帮你合并这些跟踪文件.
语句如下:
trcsess [output=

] [session=] [clientid=] [service=] [action=] [module=]

output=

output destination default being standard output.
session= session to be traced.
Session id is a combination of session Index & session serial number e.g. 8.13.

clientid= clientid to be traced.
service= service to be traced.
action= action to be traced.
module= module to be traced.
Space separated list of trace files with wild card ‘*’ suppor
ted.

[oracle@jingyong trace]$ trcsess output=jingyong_ora_88888888.trc service=jingyong jingyong_ora_2529.trc jingyong_ora_4411.trc
[oracle@jingyong trace]$ ls -lrt jingyong_ora_88888888.trc
-rw-r–r– 1 oracle oinstall 16219 Nov 11 14:59 jingyong_ora_88888888.trc

dbms_application_info
可以在过程开始一个事务之前使用dbms_application_info.set*过程来注册一个事务名/客户端信息/模块名为以后检查性能来使用.你应该对以后可能消耗你最多系统资源的活动事务进行指定.
dbms_application_info包有以下过程
SET_CLIENT_INFO ( client_info IN VARCHAR2 );
SET_ACTION ( action_name IN VARCHAR2 );
SET_MODULE ( module_name IN VARCHAR2, action_name IN VARCHAR2 );

例如
sys@JINGYONG> create table emp as select * from scott.emp where 1=0;

表已创建。

sys@JINGYONG> exec dbms_application_info.set_module(module_name=>’add_emp’,actio
n_name=>’insert into emp’);

PL/SQL 过程已成功完成。

sys@JINGYONG> insert into emp select * from scott.emp;

已创建14行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> exec dbms_application_info.set_module(null,null);

PL/SQL 过程已成功完成。
下面查询v$sqlarea视图使用module和action列进行查询
sys@JINGYONG> select sql_text from v$sqlarea where module=’add_emp’;

insert into emp select * from scott.emp

sys@JINGYONG> select sql_text from v$sqlarea where action=’insert into emp’;

insert into emp select * from scott.emp

declare
l_client varchar2(100);
l_mod_name varchar2(100);
l_act_name varchar2(100);
begin
dbms_application_info.read_client_info(l_client);
dbms_application_info.read_module(l_mod_name,l_act_name);
dbms_output.put_line(l_client);
dbms_output.put_line(l_mod_name);
end;

dbms_session包：只能跟踪当前会话，不能指定会话。
跟踪当前会话：
SQL> exec dbms_session.set_sql_trace(true);
SQL> 执行sql
SQL> exec dbms_session.set_sql_trace(false);
dbms_session.set_sql_trace相当于alter session set sql_trace，从生成的trace文件可以明确地看alter session set sql_trace语句。
使 用dbms_session.session_trace_enable过程，不仅可以看到等待事件信息还可以看到绑定变量信息，相当于alter session set events ‘10046 trace name context forever, level 12’;语句，从生成的trace文件可以确认。
SQL> exec dbms_session.session_trace_enable(waits=>true,binds=>true);
SQL> 执行sql
SQL> exec dbms_session.session_trace_enable(); –This procedure resets the session-level SQL trace for the session from which it was called.

dbms_support包：不应该使用这种方法，非官方支持。
系统默认没有安装这个包，可以手动执行$ORACLE_HOME/rdbms/admin/bmssupp.sql脚本来创建该包。
SQL> desc dbms_support
FUNCTION MYSID RETURNS NUMBER
FUNCTION PACKAGE_VERSION RETURNS VARCHAR2
PROCEDURE START_TRACE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
WAITS BOOLEAN IN DEFAULT
BINDS BOOLEAN IN DEFAULT
PROCEDURE START_TRACE_IN_SESSION
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SID NUMBER IN
SERIAL NUMBER IN
WAITS BOOLEAN IN DEFAULT
BINDS BOOLEAN IN DEFAULT
PROCEDURE STOP_TRACE
PROCEDURE STOP_TRACE_IN_SESSION
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SID NUMBER IN
SERIAL NUMBER IN
SQL> select dbms_support.package_version from dual;
PACKAGE_VERSION
——————————————————————————–
DBMS_SUPPORT Version 1.0 (17-Aug-1998) – Requires Oracle 7.2 – 8.0.5
SQL> select dbms_support.mysid from dual;
MYSID
———-
292
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
———- ———- ———-
292 0 1
跟踪当前会话：
SQL> exec dbms_support.start_trace
SQL> 执行sql
SQL> exec dbms_support.stop_trace
跟踪其他会话：等待事件+绑定变量，相当于level 12的10046事件。
SQL> select sid,serial#,username from v$session where …;
SQL> exec dbms_support.start_trace_in_session(sid=>sid,serial=>serial#,waits=>true,binds=>true);
SQL> exec dbms_support.stop_trace_in_session(sid=>sid,serial=>serial#);

dbms_system包：9i时使用
跟踪其他会话：
SQL> select sid,serial#,username from v$session where …;
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
可以等候片刻，跟踪session执行任务,捕获sql操作…
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,false);
ps：dbms_system这个包在10gR2官方文档上面没有找到这个包的说明，但数据库中有。
SQL> exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(sid, serial#, ‘sql_trace’, TRUE);
SQL> exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(sid, serial#, ‘sql_trace’, FALSE);
使用dbms_system.set_ev设置10046事件
SQL> select sid,serial#,username from v$session where …;
SQL> exec dbms_system.set_ev(sid,serial#,10046,12,”);
SQL> exec dbms_system.set_ev(sid,serial#,10046,0,”);
最后一个参数只有为”时，才会生成trace文件，否则不报错，但没有trace文件生成。

3.数据库hang住是一个特定会话出现hang住还是几个会话出现hang住还是所有的会话都出现hang住
如果数据库是一个会话或几个会话出现hang住可以对这个会话执行10046跟踪,可以对这个会话收集一些errorstacks信息,也可以当问题出现时生成一个awr或statspack报告

生成转储和errorstack信息的方法如下:
为了转储跟踪和errorstacks信息,可以使用操作系统进程ID或者oracle进程ID.比如可以通过oracle的sid来查询到操作系统进ID:
SELECT p.pid, p.SPID,s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND s.SID = &SID;

SPID是操作系统标识符
SID是oracle会话标识符
PID是oracle进程标识符

比如一个SPID是1254,pid是56如果使用SPID来生成转储和errorstacks信息可以执行下面的语句:
connect / as sysdba
ALTER SESSION SET tracefile_identifier = ‘STACK_10046’;
oradebug setospid 1254
oradebug unlimit

oradebug event 10046 trace name context forever,level 12

oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug tracefile_name
oradebug event 10046 trace name context off

如果使用PID来生成转储和errorstacks信息可以执行下面的语句:
connect / as sysdba
ALTER SESSION SET tracefile_identifier = ‘STACK_10046’;
oradebug setpid 56
oradebug unlimit

oradebug event 10046 trace name context forever,level 12

oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug tracefile_name
oradebug event 10046 trace name context off

其中oradebug tracefile_name命令会显示跟踪文件的名字和位置,在生成的跟踪文件名字会包含STACK_10046字符

如果要对当前会话收集errorstacks信息首先要找出当前会话的SPID或PID可以执行如下语句来获得:
SELECT p.pid, p.SPID,s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND s.audsid = userenv(‘SESSIONID’) ;

或者

SELECT p.pid, p.SPID,s.SID
FROM v$process p,v$session s
WHERE s.paddr = p.addr
AND s.SID =
(SELECT DISTINCT SID
FROM V$MYSTAT);

如果数据库是所有会话出现hang也就是整个数据库出现hang住了诊断hang住的方法如下:
当一个数据库出现Hang的问题时从数据库中收集信息来诊断挂志的根本原因是非常有用的.数据库Hang的原因往往是孤立的可以使用收集来的诊断信息来解决.另外如果不能解决可以用获得的信息来避免这个问题的再次重现.
解决方法
诊断数据库Hang需要什么信息
数据库Hang的特点是一些进程正在等待另一些进程的完成.通常有一个或多个阻塞进程被困或者正在努力工作但不是迅速的释放资源.为了诊断需要以下信息:
1.Hanganalyze and Systemstate Dumps
2.数据库性能的awr/statspack快照
3.及时的RDA
Hanganalyze and Systemstate Dumps
Hang分析和系统状态转储提供了在一个特定时间点的数据库中的进程信息.Hang分析提供了在Hang链表中所有进程的信息,系统状态提供了数据库中所有进程的信息.当查看一个潜在的Hang情况时你需要判断是否一个进程被因或动行缓慢.通过在两个连续的时间间隔内收集这些转储信息如果进程被困这些跟踪信息可以用于将来的诊断可能帮助你提供一些解决方法.Hang分析用来总结和确认数据库是真的Hang还是只是缓慢并提供了一致性快照,系统状态转储显示了数据库中每一个进程正在做什么
收集Hang分析和系统状态转储信息
登录系统
使用sql*plus以sysdba身份来登录
sqlplus ‘/ as sysdba’
如果连接时出现问题在oracle10gr2中可以使用sqlplus的”preliminary connection’
sqlplus -prelim ‘/ as sysdba’
注意:从oracle 11.2.0.2开始Hang分析在sqlplus的’preliminary connection’连接下将不会生成输出因为它要会请求一个进程的状态对象和一个会话状态对象.如果正试图分析跟踪会输出:
HANG ANALYSIS:
ERROR: Can not perform hang analysis dump without a process state object and a session state object.
( process=(nil), sess=(nil) )
非rac环境收集Hang分析和系统状态的收集命令
有些时候数据库可能只是非常的慢而不是真正的Hang.因此建议收集级别为2的Hang分析和系统状态转储来判断这些进程是正在执行还是已经停止执行
持起分析

sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- Wait one minute before getting the second hanganalyze
oradebug hanganalyze 3
oradebug tracefile_name
exit

系统转储

sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
oradebug dump systemstate 266
oradebug tracefile_name
exit

rac环境收集Hang分析和系统状态的收集命令
如果在你的系统中没有应用相关的补丁程序使用级别为266或267的系统状态转储会有2个bug.因此在没有应用这些补丁收集这些级别的转储是不明智的选择
补丁信息如下:
Document 11800959.8 Bug 11800959 – A SYSTEMSTATE dump with level >= 10 in RAC dumps huge BUSY GLOBAL CACHE ELEMENTS – can hang/crash instances
Document 11827088.8 Bug 11827088 – Latch ‘gc element’ contention, LMHB terminates the instance
在修正bug 11800959和bug 11827088的情况下对于rac环境惧订Hang分析和系统状态的收集命令如下:

sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug  unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 266
oradebug -g all dump systemstate 266
exit

在没有修正bug 11800959和bug 11827088的情况下对于rac环境惧订Hang分析和系统状态的收集命令如下:

sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
oradebug -g all dump systemstate 258
exit

在rac环境中会在每一个实例的跟踪文件中创建所有实例的转储信息
对Hang分析和系统状态转储的级别说明
Hang分析级别
level 3(级别3):在oracle11g之前level 3对Hang链表中的相关进程也会收集一个简短的堆栈信息
系统状态转储级别
level 258(级别258)是一个快速的选择但是会丢失一些锁的元数据信息
level 267(级别267)它包含了理解成本所需要的额外的缓冲区缓存/锁元数据信息
其它的方法
如果不能连接到系统时如何收集系统状态转储信息
通常有两种方法来在系统Hang不能连接时来生成系统状态转储信息
1.alter session set events ‘immediate trace name SYSTEMSTATE level 10’;
2.$ sqlplus
connect sys/passwd as sysdba
oradebug setospid oradebug unlimit
oradebug dump systemstate 10
(注意:在oradebug中不能使用任何半冒号,如果你的数据库是比oracle9i还老的版本你将需要使用svrmgrl来连接到内部)
当你使用这两种方法中的一种时,要确保在两次转储时内部连接断开.这种方法生成的转储将在你的user_dump_dest目录中是分开的ora_.trc文件
在非常严重的情况下不能使用svrmgrl或sqlplus进行连接执行这些必要的命令.在这种情况下仍然有一个后门方法使用调试器比如你的系统有dbx的话可以用dbx来生成系统状态转储信息.被连接到的转储核心进程可能会被杀死所以不能连接到一个oracle后台进程.dbx的语法如下:
dbx -a PID (where PID = any oracle shadow process)
dbx() print ksudss(10)
…return value printed here
dbx() detach
首先你需要找到一个影子进程

(jy) % ps -ef |grep sqlplus
osupport  78526 154096   0 12:11:05  pts/1  0:00 sqlplus scott/tiger
osupport  94130  84332   1 12:11:20  pts/3  0:00 grep sqlplus
(jy) % ps -ef |grep 78526
osupport  28348  78526   0 12:11:05      -  0:00 oracles734 (DESCRIPTION=(LOCAL
osupport  78526 154096   0 12:11:05  pts/1  0:00 sqlplus scott/tiger
osupport  94132  84332   1 12:11:38  pts/3  0:00 grep 78526

这样将会连接到影子进程PID 28348上.当返回提示符时输入ksudss(10)命令和detach:

(jy) % dbx -a 28348
Waiting to attach to process 28348 ...
Successfully attached to oracle.
warning: Directory containing oracle could not be determined.
Apply 'use' command to initialize source path.

Type 'help' for help.
reading symbolic information ...
stopped in read at 0xd016fdf0
0xd016fdf0 (read+0x114) 80410014        lwz   r2,0x14(r1)
(dbx) print ksudss(10)
2
(dbx) detach

在user_dump_dest目录中你将会通过跟踪的pid找到一个系统状态转储文件

(jy) % ls -lrt *28348*
-rw-r-----   1 osupport dba        46922 Oct 10 12:12 ora_28348.trc

core_28348:
total 72
-rw-r--r--   1 osupport dba        16567 Oct 10 12:12 core
drwxr-xr-x   7 osupport dba        12288 Oct 10 12:12 ../
drwxr-x---   2 osupport dba          512 Oct 10 12:12 ./

在跟踪文件中你将会找到常用的头信息.在oracle7.3.4并行操作系统中在这后面紧跟的是锁信息然后才是系统转储信息.
在oracle8并行操作系统中和非并行操作系统和oracle7.3.4非并行操作系统的系统状态信息是紧跟头信息.
在转储文件中头信息如下:

Dump file /oracle/mpp/734/rdbms/log/ora_28348.trc
Oracle7 Server Release 7.3.4.4.1 - Production
With the distributed, replication, parallel query, Parallel Server
and Spatial Data options
PL/SQL Release 2.3.4.4.1 - Production
ORACLE_HOME = /oracle/mpp/734
System name:    AIX
Node name:      saki
Release:        3
Version:        4
Machine:        000089914C00
Instance name: s734
Redo thread mounted by this instance: 2
Oracle process number: 0
Unix process pid: 28348, image:

ksinfy: nfytype = 0x5
ksinfy: calling scggra(&se)
scggra: SCG_PROCESS_LOCKING not defined
scggra: calling lk_group_attach()
ksinfy: returning
*** SESSION ID:(12.15) 2000.10.10.12.11.06.000
ksqcmi: get or convert
ksqcmi: get or convert
*** 2000.10.10.12.12.08.000
===================================================
SYSTEM STATE

.....

确保在这个文件中有一个end of system state.可以对它使用grep或在vi中搜索.如果没有那么这个跟踪文件是不过完整.
可能是因为init.ora文件中的max_dump_file的大小太小了.
对于oracle10g及以后的版本:
在有些情况下不连接到实例是允许的(在有些ora-20的情况下,对于oracle10.1.x,对于sqlplus有一个新选项来允许访问实例来生成跟踪文件)sqlplus -prelim / as sysdba
例如

export ORACLE_SID=PROD                                 ## Replace PROD with the SID you want to trace
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10

在rac系统中,Hang分析,系统转储和其它一些rac信息可以使用racdiag.sql脚本来进行收集:

-- NAME: RACDIAG.SQL
-- SYS OR INTERNAL USER, CATPARR.SQL ALREADY RUN, PARALLEL QUERY OPTION ON
-- ------------------------------------------------------------------------
-- AUTHOR:
-- Michael Polaski - Oracle Support Services
-- Copyright 2002, Oracle Corporation
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is intended to provide a user friendly guide to troubleshoot
-- RAC hung sessions or slow performance scenerios. The script includes
-- information to gather a variety of important debug information to determine
-- the cause of a RAC session level hang. The script will create a file
-- called racdiag_.out in your local directory while dumping hang analyze
-- dumps in the user_dump_dest(s) and background_dump_dest(s) on all nodes.
--
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
-- Script output is as follows:

set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool racdiag_&&dbname&×tamp&&suffix
set lines 200
set pagesize 35
set trim on
set trims on
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
alter session set timed_statistics = true;
set feedback on
select to_char(sysdate) time from dual;

set numwidth 5
column host_name format a20 tru
select inst_id, instance_name, host_name, version, status, startup_time
from gv$instance
order by inst_id;

set echo on

-- WAIT CHAINS
-- 11.x+ Only (This will not work in < v11
-- See Note 1428210.1 for instructions on interpreting.
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
v$instance i
WHERE wc.instance = i.instance_number (+)
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101; -- Taking Hang Analyze dumps  -- This may take a little while...  oradebug setmypid  oradebug unlimit  oradebug -g all hanganalyze 3  -- This part may take the longest, you can monitor bdump or udump to see if  -- the file is being generated.  oradebug -g all dump systemstate 258  -- WAITING SESSIONS:  -- The entries that are shown at the top are the sessions that have  -- waited the longest amount of time that are waiting for non-idle wait  -- events (event column). You can research and find out what the wait  -- event indicates (along with its parameters) by checking the Oracle  -- Server Reference Manual or look for any known issues or documentation  -- by searching Metalink for the event name in the search bar. Example  -- (include single quotes): [ 'buffer busy due to global cache' ].  -- Metalink and/or the Server Reference Manual should return some useful  -- information on each type of wait event. The inst_id column shows the  -- instance where the session resides and the SID is the unique identifier  -- for the session (gv$session). The p1, p2, and p3 columns will show  -- event specific information that may be important to debug the problem.  -- To find out what the p1, p2, and p3 indicates see the next section.  -- Items with wait_time of anything other than 0 indicate we do not know  -- how long these sessions have been waiting.  --  set numwidth 15 set heading on column state format a7 tru  column event format a25 tru  column last_sql format a40 tru  select sw.inst_id, sw.sid, sw.state, sw.event, sw.seconds_in_wait seconds,  sw.p1, sw.p2, sw.p3, sa.sql_text last_sql  from gv$session_wait sw, gv$session s, gv$sqlarea sa  where sw.event not in  ('rdbms ipc message','smon timer','pmon timer',  'SQL*Net message from client','lock manager wait for remote message',  'ges remote message', 'gcs remote message', 'gcs for action', 'client message',  'pipe get', 'null event', 'PX Idle Wait', 'single-task message',  'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',  'listen endpoint status','slave wait','wakeup time manager')  and sw.seconds_in_wait > 0
and (sw.inst_id = s.inst_id and sw.sid = s.sid)
and (s.inst_id = sa.inst_id and s.sql_address = sa.address)
order by seconds desc;

-- EVENT PARAMETER LOOKUP:
-- This section will give a description of the parameter names of the
-- events seen in the last section. p1test is the parameter value for
-- p1 in the WAITING SESSIONS section while p2text is the parameter
-- value for p3 and p3 text is the parameter value for p3. The
-- parameter values in the first section can be helpful for debugging
-- the wait event.
--
column event format a30 tru
column p1text format a25 tru
column p2text format a25 tru
column p3text format a25 tru
select distinct event, p1text, p2text, p3text
from gv$session_wait sw
where sw.event not in ('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
and seconds_in_wait > 0
order by event;

-- GES LOCK BLOCKERS:
-- This section will show us any sessions that are holding locks that
-- are blocking other users. The inst_id will show us the instance that
-- the session resides on while the sid will be a unique identifier for
-- the session. The grant_level will show us how the GES lock is granted to
-- the user. The request_level will show us what status we are trying to
-- obtain.  The lockstate column will show us what status the lock is in.
-- The last column shows how long this session has been waiting.
--
set numwidth 5
column state format a16 tru;
column event format a30 tru;
select dl.inst_id, s.sid, p.spid, dl.resource_name1,
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as grant_level,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as request_level,
decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'KJUSERCA','Canceling','KJUSERCV','Converting') as state,
s.sid, sw.event, sw.seconds_in_wait sec
from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
where blocker = 1
and (dl.inst_id = p.inst_id and dl.pid = p.spid)
and (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by sw.seconds_in_wait desc;

-- GES LOCK WAITERS:
-- This section will show us any sessions that are waiting for locks that
-- are blocked by other users. The inst_id will show us the instance that
-- the session resides on while the sid will be a unique identifier for
-- the session. The grant_level will show us how the GES lock is granted to
-- the user. The request_level will show us what status we are trying to
-- obtain.  The lockstate column will show us what status the lock is in.
-- The last column shows how long this session has been waiting.
--
set numwidth 5
column state format a16 tru;
column event format a30 tru;
select dl.inst_id, s.sid, p.spid, dl.resource_name1,
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as grant_level,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as request_level,
decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'KJUSERCA','Cancelling','KJUSERCV','Converting') as state,
s.sid, sw.event, sw.seconds_in_wait sec
from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
where blocked = 1
and (dl.inst_id = p.inst_id and dl.pid = p.spid)
and (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by sw.seconds_in_wait desc;

-- LOCAL ENQUEUES:
-- This section will show us if there are any local enqueues. The inst_id will
-- show us the instance that the session resides on while the sid will be a
-- unique identifier for. The addr column will show the lock address. The type
-- will show the lock type. The id1 and id2 columns will show specific
-- parameters for the lock type.
--
set numwidth 12
column event format a12 tru
select l.inst_id, l.sid, l.addr, l.type, l.id1, l.id2,
decode(l.block,0,'blocked',1,'blocking',2,'global') block,
sw.event, sw.seconds_in_wait sec
from gv$lock l, gv$session_wait sw
where (l.sid = sw.sid and l.inst_id = sw.inst_id)
and l.block in (0,1)
order by l.type, l.inst_id, l.sid;

-- LATCH HOLDERS:
-- If there is latch contention or 'latch free' wait events in the WAITING
-- SESSIONS section we will need to find out which proceseses are holding
-- latches. The inst_id will show us the instance that the session resides
-- on while the sid will be a unique identifier for. The username column
-- will show the session's username. The os_user column will show the os
-- user that the user logged in as. The name column will show us the type
-- of latch being waited on. You can search Metalink for the latch name in
-- the search bar. Example (include single quotes):
-- [ 'library cache' latch ]. Metalink should return some useful information
-- on the type of latch.
--
set numwidth 5
select distinct lh.inst_id, s.sid, s.username, p.username os_user, lh.name
from gv$latchholder lh, gv$session s, gv$process p
where (lh.sid = s.sid and lh.inst_id = s.inst_id)
and (s.inst_id = p.inst_id and s.paddr = p.addr)
order by lh.inst_id, s.sid;

-- LATCH STATS:
-- This view will show us latches with less than optimal hit ratios
-- The inst_id will show us the instance for the particular latch. The
-- latch_name column will show us the type of latch. You can search Metalink
-- for the latch name in the search bar. Example (include single quotes):
-- [ 'library cache' latch ]. Metalink should return some useful information
-- on the type of latch. The hit_ratio shows the percentage of time we
-- successfully acquired the latch.
--
column latch_name format a30 tru
select inst_id, name latch_name,
round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,
round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
from gv$latch
where round((gets-misses)/decode(gets,0,1,gets),3) < .99
and gets != 0
order by round((gets-misses)/decode(gets,0,1,gets),3);

-- No Wait Latches:
--
select inst_id, name latch_name,
round((immediate_gets/(immediate_gets+immediate_misses)), 3) hit_ratio,
round(sleeps/decode(immediate_misses,0,1,immediate_misses),3) "SLEEPS/MISS"
from gv$latch
where round((immediate_gets/(immediate_gets+immediate_misses)), 3) < .99  and immediate_gets + immediate_misses > 0
order by round((immediate_gets/(immediate_gets+immediate_misses)), 3);

-- GLOBAL CACHE CR PERFORMANCE
-- This shows the average latency of a consistent block request.
-- AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds
-- depending on your system configuration and volume, is the average
-- latency of a consistent-read request round-trip from the requesting
-- instance to the holding instance and back to the requesting instance. If
-- your CPU has limited idle time and your system typically processes
-- long-running queries, then the latency may be higher. However, it is
-- possible to have an average latency of less than one millisecond with
-- User-mode IPC. Latency can be influenced by a high value for the
-- DB_MULTI_BLOCK_READ_COUNT parameter. This is because a requesting process
-- can issue more than one request for a block depending on the setting of
-- this parameter. Correspondingly, the requesting process may wait longer.
-- Also check interconnect badwidth, OS tcp settings, and OS udp settings if
-- AVG CR BLOCK RECEIVE TIME is high.
--
set numwidth 20
column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9
select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED",
b1.value "GCS CR BLOCK RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'global cache cr block receive time' and
b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id
or b1.name = 'gc cr block receive time' and
b2.name = 'gc cr blocks received' and b1.inst_id = b2.inst_id ;

-- GLOBAL CACHE LOCK PERFORMANCE
-- This shows the average global enqueue get time.
-- Typically AVG GLOBAL LOCK GET TIME should be 20-30 milliseconds. the
-- elapsed time for a get includes the allocation and initialization of a
-- new global enqueue. If the average global enqueue get (global cache
-- get time) or average global enqueue conversion times are excessive,
-- then your system may be experiencing timeouts. See the 'WAITING SESSIONS',
-- 'GES LOCK BLOCKERS', GES LOCK WAITERS', and 'TOP 10 WAIT EVENTS ON SYSTEM'
-- sections if the AVG GLOBAL LOCK GET TIME is high.
--
set numwidth 20
column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9
select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS",
b3.value "GLOBAL LOCK GET TIME",
(b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)"
from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3
where b1.name = 'global lock sync gets' and
b2.name = 'global lock async gets' and b3.name = 'global lock get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id
or b1.name = 'global enqueue gets sync' and
b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id;

-- RESOURCE USAGE
-- This section will show how much of our resources we have used.
--
set numwidth 8
select inst_id, resource_name, current_utilization, max_utilization,
initial_allocation
from gv$resource_limit
where max_utilization > 0
order by inst_id, resource_name;

-- DLM TRAFFIC INFORMATION
-- This section shows how many tickets are available in the DLM. If the
-- TCKT_WAIT columns says "YES" then we have run out of DLM tickets which
-- could cause a DLM hang. Make sure that you also have enough TCKT_AVAIL.
--
set numwidth 10
select * from gv$dlm_traffic_controller
order by TCKT_AVAIL;

-- DLM MISC
--
set numwidth 10
select * from gv$dlm_misc;

-- LOCK CONVERSION DETAIL:
-- This view shows the types of lock conversion being done on each instance.
--
select * from gv$lock_activity;

-- INITIALIZATION PARAMETERS:
-- Non-default init parameters for each node.
--
set numwidth 5
column name format a30 tru
column value format a50 wra
column description format a60 tru
select inst_id, name, value, description
from gv$parameter
where isdefault = 'FALSE'
order by inst_id, name;

-- TOP 10 WAIT EVENTS ON SYSTEM
-- This view will provide a summary of the top wait events in the db.
--
set numwidth 10
column event format a25 tru
select inst_id, event, time_waited, total_waits, total_timeouts
from (select inst_id, event, time_waited, total_waits, total_timeouts
from gv$system_event where event not in ('rdbms ipc message','smon timer',
'pmon timer', 'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
order by time_waited desc)
where rownum < 11  order by time_waited desc;  -- SESSION/PROCESS REFERENCE:  -- This section is very important for most of the above sections to find out  -- which user/os_user/process is identified to which session/process.  --  set numwidth 7  column event format a30 tru  column program format a25 tru  column username format a15 tru  select p.inst_id, s.sid, s.serial#, p.pid, p.spid, p.program, s.username,  p.username os_user, sw.event, sw.seconds_in_wait sec  from gv$process p, gv$session s, gv$session_wait sw  where (p.inst_id = s.inst_id and p.addr = s.paddr)  and (s.inst_id = sw.inst_id and s.sid = sw.sid)  order by p.inst_id, s.sid;  -- SYSTEM STATISTICS:  -- All System Stats with values of > 0. These can be referenced in the
-- Server Reference Manual
--
set numwidth 5
column name format a60 tru
column value format 9999999999999999999999999
select inst_id, name, value
from gv$sysstat
where value > 0
order by inst_id, name;

-- CURRENT SQL FOR WAITING SESSIONS:
-- Current SQL for any session in the WAITING SESSIONS list
--
set numwidth 5
column sql format a80 wra
select sw.inst_id, sw.sid, sw.seconds_in_wait sec, sa.sql_text sql
from gv$session_wait sw, gv$session s, gv$sqlarea sa
where sw.sid = s.sid (+)
and sw.inst_id = s.inst_id (+)
and s.sql_address = sa.address
and sw.event not in ('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
and sw.seconds_in_wait > 0
order by sw.seconds_in_wait desc;

-- WAIT CHAINS
-- 11.x+ Only (This will not work in < v11
-- See Note 1428210.1 for instructions on interpreting.
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
v$instance i
WHERE wc.instance = i.instance_number (+)
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;

-- Taking Hang Analyze dumps
-- This may take a little while...
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
-- This part may take the longest, you can monitor bdump or udump to see
-- if the file is being generated.
oradebug -g all dump systemstate 258

set echo off

select to_char(sysdate) time from dual;

spool off

-- ---------------------------------------------------------------------------
Prompt;
Prompt racdiag output files have been written to:;
Prompt;
host pwd
Prompt alert log and trace files are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value
from gv$instance i, gv$parameter p
where p.inst_id = i.inst_id (+)
and p.name like '%_dump_dest'
and p.name != 'core_dump_dest';
v$wait_chains

从oracle11gr1开始,dia0后台进程开始收集Hang分析信息并存储在内存中的"hang analysis cache"中.它会每3秒钟收集一次本地的Hang分析和第10秒钟收集一次全局(rac)Hang分析信息.这些信息在出现Hang时提供快速查看Hang链表的方法.
存储在"hang analysiz cache"中的数据对于诊断数据库竞争和Hang是非常有效的
有许多数据库功能可以利用Hang分析缓存:Hang Management, Resource Manager Idle Blocker Kill,
SQL Tune Hang Avoidance和PMON清除以及外部工具象Procwatcher
下面是oracle11gr2中v$wait_chains视图的描述:

SQL> desc v$wait_chains
  Name                                      Null     Type
  ----------------------------------------- -------- ----------------------
  CHAIN_ID                                           NUMBER
  CHAIN_IS_CYCLE                                     VARCHAR2(5)
  CHAIN_SIGNATURE                                    VARCHAR2(801)
  CHAIN_SIGNATURE_HASH                               NUMBER
  INSTANCE                                           NUMBER
  OSID                                               VARCHAR2(25)
  PID                                                NUMBER
  SID                                                NUMBER
  SESS_SERIAL#                                       NUMBER
  BLOCKER_IS_VALID                                   VARCHAR2(5)
  BLOCKER_INSTANCE                                   NUMBER
  BLOCKER_OSID                                       VARCHAR2(25)
  BLOCKER_PID                                        NUMBER
  BLOCKER_SID                                        NUMBER
  BLOCKER_SESS_SERIAL#                               NUMBER
  BLOCKER_CHAIN_ID                                   NUMBER
  IN_WAIT                                            VARCHAR2(5)
  TIME_SINCE_LAST_WAIT_SECS                          NUMBER
  WAIT_ID                                            NUMBER
  WAIT_EVENT                                         NUMBER
  WAIT_EVENT_TEXT                                    VARCHAR2(64)
  P1                                                 NUMBER
  P1_TEXT                                            VARCHAR2(64)
  P2                                                 NUMBER
  P2_TEXT                                            VARCHAR2(64)
  P3                                                 NUMBER
  P3_TEXT                                            VARCHAR2(64)
  IN_WAIT_SECS                                       NUMBER
  TIME_REMAINING_SECS                                NUMBER
  NUM_WAITERS                                        NUMBER
  ROW_WAIT_OBJ#                                      NUMBER
  ROW_WAIT_FILE#                                     NUMBER
  ROW_WAIT_BLOCK#                                    NUMBER
  ROW_WAIT_ROW#                                      NUMBER

注意:v$wait_chains等价于gv$视图可能在rac环境中报告多个实例
使用sql来查询基本信息

SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30)
 FROM v$wait_chains; 2

 CHAIN_ID   NUM_WAITERS IN_WAIT_SECS OSID           BLOCKER_OSID         SUBSTR(WAIT_EVENT_TEXT,1,30)
 ---------- ----------- ------------ -------------- ------------------------- -----------------------------
1          0           10198        21045          21044                      enq: TX - row lock contention
 1          1           10214        21044                                    SQL*Net message from client

查询top 100 wait chain processs

 set pages 1000
 set lines 120
 set heading off
 column w_proc format a50 tru
 column instance format a20 tru
 column inst format a28 tru
 column wait_event format a50 tru
 column p1 format a16 tru
 column p2 format a16 tru
 column p3 format a15 tru
 column Seconds format a50 tru
 column sincelw format a50 tru
 column blocker_proc format a50 tru
 column waiters format a50 tru
 column chain_signature format a100 wra
 column blocker_chain format a100 wra

 SELECT *
 FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
 ' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
 '',blocker_chain_id) blocker_chain
 FROM v$wait_chains wc,
 v$instance i
 WHERE wc.instance = i.instance_number (+)
 AND ( num_waiters > 0
 OR ( blocker_osid IS NOT NULL
 AND in_wait_secs > 10 ) )
 ORDER BY chain_id,
 num_waiters DESC)
 WHERE ROWNUM < 101;


Current Process:21549                                   SID RAC1                 INST #: 1
Blocking Process: from Instance                   Number of waiters:1
Wait Event:SQL*Net message from client                  P1: 1650815232  P2: 1     P3:0
Seconds in Wait:36                                      Seconds Since Last Wait:
Wait Chaing:1 : 'SQL*Net message from client '< ='enq: TX - row lock contention'
Blocking Wait Chain:

Current Process:25627                                   SID RAC1                 INST #: 1
Blocking Process:21549 from Instance 1                  Number of waiters:0
Wait Event:enq: TX - row lock contention                P1:1415053318 P2: 524316 P3:50784
Seconds in Wait:22                                      Seconds Since Last Wait:
Wait Chain:1 : 'SQL*Net message from client '< ='enq: TX - row lock contention'
Blocking Wait Chain:

ospid 25627正等待一个TX lock正被ospid 21549所阻塞
ospid 21549正空闲等待'SQL*Net message from client'
在oracle11gr2中的最终阻塞会话
在oracle11gr2中可能将v$session.final_blocking_session看作是最终的阻塞者.最终的阻会话/进程在top等待链表上.
这些会话/进程可能是造成问题的原因.

set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column fblocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra

SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
 ' from Instance '||blocker_instance BLOCKER_PROC,
 'Number of waiters: '||num_waiters waiters,
 'Final Blocking Process: '||decode(p.spid,null,'',
 p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,
 'Program: '||p.program image,
 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
 '',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
 gv$session s,
 gv$session bs,
 gv$instance i,
 gv$process p
WHERE wc.instance = i.instance_number (+)
 AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
 and wc.sess_serial# = s.serial# (+))
 AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
 AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
 AND ( num_waiters > 0
 OR ( blocker_osid IS NOT NULL
 AND in_wait_secs > 10 ) )
ORDER BY chain_id,
 num_waiters DESC)
WHERE ROWNUM < 101;



Current Process:2309                                    SID RAC1                 INST #: 1
Blocking Process: from Instance                   Number of waiters:2
Wait Event:SQL*Net message from client                  P1: 1650815232  P2: 1     P3:0
Seconds in Wait:157                                     Seconds Since Last Wait:
Wait Chaing:1 : 'SQL*Net message from client '< ='enq: TM - contention'<='enq: TM - contention'
Blocking Wait Chain:

Current Process:2395                                    SID RAC1                 INST #: 1
Blocking Process:2309 from Instance 1                   Number of waiters:0
Final Block Process:2309 from Instance 1                Program: oracle@racdbe1.us.oracle.com (TNS V1-V3)
Wait Event:enq: TX - contention                         P1:1415053318 P2: 524316 P3:50784
Seconds in Wait:139                                      Seconds Since Last Wait:
Wait Chain:1 : 'SQL*Net message from client '< ='enq: TM - contention'<='enq: TM - contention'
Blocking Wait Chain:

B.对数据库性能生成一个awr/statspack快照
C.收集最新的RDA
最新的RDA提供了大量额外关于数据库配置和性能度量的信息可以用来检测可能影响性能的热点的后台进程问题
有时数据库不是真正的被hang住可是只是'spinning' cpu.可以使用以下方法来检查服务器是hang还是spin如果一个操作执行的时间比期待的时间长或者这个操作损害了其它操作的性能时那么最好是检查v$session_wait视图.这个视图显示了在系统中会话当前正在等待的信息.可以使用下面的脚本来操作.

column sid format 990
column seq# format 99990
column wait_time heading 'WTime' format 99990
column event format a30
column p1 format 9999999990
column p2 format 9999999990
column p3 format 9990
select sid,event,seq#,p1,p2,p3,wait_time
from V$session_wait
order by sid
/

上面的查询最少应该执行三次并比较其它查询结果
列意思
sid– 会话的系统标识符
seq#–序列号.当一个特定会话的等待一个新的事件时这个数字会增加.它能告诉你一个会话是否正在执行
evnet–会话正在等待的或最后等待的操作
p1,p2,p3–它们代表不同的等待值
wait_time–0指示这个会话正在等待的事件.非0指示这个会话最后等待的事件和会话正使用CPU
例如:

 SID EVENT                            SEQ#          P1          P2    P3  WTime
---- ------------------------------ ------ ----------- ----------- ----- ------
   1 pmon timer                        335         300           0     0      0
   2 rdbms ipc message                 779         300           0     0      0
   6 smon timer                         74         300           0     0      0
   9 Null event                        347           0         300     0      0
  16 SQL*Net message from client      1064  1650815315           1     0     -1

如果脚本查询的结果显示正在等待一个enqueue等待事件那么你将需要检查与你hang会话相关的锁信息
column sid format 990
column type format a2
column id1 format 9999999990
column id2 format 9999999990
column Lmode format 990
column request format 990
select * from v$lock
/
Spinning
在spin的情况下事件通常来说是静态的且会话不会是正在等待一个事件–而是在等待cpu(注意在极少数情况下,这个事件依赖于执行spin的代码也可能不会静态的.如果会自豪感是spin它将严重使用cpu和内存资源.
对于一个spin的情况重要的是要检测会话正处于spinning的代码.从事件的一些迹象说明通常需要对一个进程生成几次的错误堆栈信息用来分析:
connect sys/sys as sysdba
oradebug setospid
oradebug unlimit
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
这里的spid是操作系统标识符可以从v$process视图是得到.
Hanging
在正常的情况下在v$session_wait视图中的值应该是用每个会话执行的不同操作来替换.
在hang住的情况下对于一个或一组特定会话的所有系统事件将会是保持静态状态且进程不会消耗任何cpu和内存资源.鉴于会话现在没有请求锁定任何资源这就叫hang
在这种情况下可对实例转储系统状态来获得一些更详细更有用的信息.
ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL XX’;
在oralce9.2.0.6或oracle10.1.0.4或在oracle10g中最高的版本的中这里的xx是266.执行上面的命令在你的user_dump_dest目录中会生成系统状态跟踪文件.
通过下面的查询可以得到问题进程的进程ID
SELECT pid FROM v$process
WHERE addr =
(SELECT paddr FROM v$session
WHERE sid = sid_of_problem_session);
系统状态转储文件包含了每一个进程的信息.可以通过搜索’PROCESS ‘来找到每一个进程的详细信息.通过搜索’waiting for’来找到当前正在等待的事件.

为了转储跟踪和errorstacks信息,可以使用操作系统进程ID或者oracle进程ID.比如可以通过oracle的sid来查询到操作系统进ID:

SELECT p.pid, p.SPID,s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND s.SID = &SID;

SPID是操作系统标识符
SID是oracle会话标识符
PID是oracle进程标识符

比如一个SPID是1254,pid是56如果使用SPID来生成转储和errorstacks信息可以执行下面的语句:

connect / as sysdba
ALTER SESSION SET tracefile_identifier = 'STACK_10046';
oradebug setospid 1254
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug tracefile_name
oradebug event 10046 trace name context off

如果使用PID来生成转储和errorstacks信息可以执行下面的语句:

connect / as sysdba
ALTER SESSION SET tracefile_identifier = 'STACK_10046';
oradebug setpid 56
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug tracefile_name
oradebug event 10046 trace name context off

其中oradebug tracefile_name命令会显示跟踪文件的名字和位置,在生成的跟踪文件名字会包含STACK_10046字符

如果要对当前会话收集errorstacks信息首先要找出当前会话的SPID或PID可以执行如下语句来获得:

SELECT p.pid, p.SPID,s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr
AND s.audsid = userenv('SESSIONID') ;

或者

SELECT p.pid, p.SPID,s.SID
FROM v$process p,v$session s
WHERE s.paddr = p.addr
AND s.SID =
(SELECT DISTINCT SID
FROM V$MYSTAT);

这篇文章介绍在oracle数据文件中出现一个或多个数据块坏块时的处理方法.当出现数据块坏块出误时对于每一个坏块都提供了以下信息:
1.包含这个坏块的数据文件的绝对文件号可以标示为”AFN”.
2.包含这个坏块的数据文件的文件名可以标示为”FILENAME”(如果知道文件号但不知道文件名那么可以执行select name from v$datafile where file#=&AFN来得到文件名,如果文件号在v$datafile中没有记录且AFN比参数db_files参数的值还大那么这个文件可能是临时文件.如果是这种情况可以执行select name from v$tempfile where file#=(&AFN-&DB_FILES_value)
3.数据文件中坏块的块号可以标示为”BL”
4.受坏块影响的表空间号和表空间名称可以标示为”TSN”和”TABLESPACE_NAME”.可以执行select ts# “TSN” from v$datafile where file#=&AFN;select tablespace_name from dba_data_files where file_id=&AFN来查询.
5.出现坏块的表空间的数据块大小可以标示为”TS_BLOCK_SIZE”.对于oracle9i来说可以执行select block_size from dba_tablespace where tablespace_name=(select tablespace_name from dba_data_files where file_id=&AFN);来查询数据块大小.对于oracle7.8.0和8.1在数据库中每一个表空间都有相同的数据块大小.对于这些版本可以使用show parameter db_block_size来显示数据块大小.

例如:ora-1578错识信息
ORA-01578: ORACLE data block corrupted (file # 7, block # 12698)
ORA-01110: data file 22: /oracle1/oradata/V816/oradata/V816/users01.dbf
从上面的错识信息可知:绝对文件号AFN是22,相对文件号RFN是7,数据块BL是12698,文件名FILENAME是/oracle1/oradata/V816/oradata/V816/users01.dbf,表空间号和表空间名可以用上面的查询得到.

处理坏块的步骤
导致坏块的原因有许多种例如:
坏的 IO 硬件/固件
OS 问题
Oracle 问题
对于执行过“UNRECOVERABLE”或“NOLOGGING”操作的数据库进行恢复在这种情况下可能产生 ORA-1578 错误

在遇到坏块时,我们通常无从了解根本原因,并且在大多数情况下,当前最迫切的是重新启动数据库并使其运行起来.
1. 确定坏块问题的范围，并确定这些问题是持久性问题还是暂时性问题
如果问题涉及范围很大,或错误不稳定,则关键在于先识别原因（检查硬件等）.这点很重要.因为如果是底层硬件出现错误.恢复系统便毫无意义

2.更换或拆下任何有问题的或可疑的硬件

3.确定受到影响的数据库对象有哪些

4.选择最合适的数据库恢复/数据抢救选项

确定坏块问题的范围
每次发生坏块错误时,都应记下完整的错误消息,并查看该实例的告警日志和跟踪文件,以了解任何相关的错误.首先进行这些步骤非常重要,这可以评估该损坏是单个块,还是由于UNRECOVERABLE操作产生的错误,或是更严重的问题.

使用DBVERIFY扫描受影响的文件以及一切重要的文件也是不错的办法,这样可以检查是否有其他坏块,从而确定问题的范围.一旦确定了损坏的文件/块组合列表，即可使用以下步骤来帮助确定应采取何种措施:
1.完整记录初始错误,以及发生错误的应用程序的详细信息
2.及时地保存从告警日志中首次 (FIRST) 记录到问题前数小时到当前时间点所提取的内容
3.保存告警日志中提到的任何跟踪文件
4.记录最近遇到的任何 OS 问题
5.记录是否正在使用任何特殊功能，例如：ASYNC IO、快速写入磁盘选项等
6.记录当前的备份位置（日期、类型等）
7.记录数据库是否处于ARCHIVELOG 模式,例如:在SQL*Plus（或 Server Manager）中运行“ARCHIVE LOG LIST”

更换或拆下可疑硬件
大多数坏块问题是由故障硬件导致的.如果出现硬件错误或可疑组件,最好进行修复,或者在执行恢复操作之前,确保在单独的磁盘子系统上有足够的可用空间用于恢复,您可以使用以下步骤移动数据文件：
1.确保要迁移的文件已离线或数据库实例处于 MOUNT 状态（未打开）
2. 将该数据文件物理还原（或复制）到新位置 例如：/newlocation/myfile.dbf
3.将该文件的新位置告知 Oracle.
例如：ALTER DATABASE RENAME FILE ‘/oldlocation/myfile.dbf’ TO ‘/newlocation/myfile.dbf’;
(请注意,您不能对临时文件进行重命名,而应删除临时文件并在新位置重新创建)
4.使相关数据文件/表空间上线(如果数据库已打开)

注意:
如果存在多个错误(不是由于 NOLOGGING操作导致的)或受影响文件所在的OS层面出现错误或错误是暂时性的且游离不定,那么,如果不解决底层问题或准备另外的磁盘空间,那么进行任何操作都是毫无意义的.

如果使用了任何特殊IO选项,例如direct IO,async IO或类似的选项,最好将其禁用,以消除这些选项成为潜在问题原因的可能性

确定受影响的对象有哪些
在决定如何恢复之前,最好先确定哪些对象受到了影响,因为坏块可能发生在那些容易被重新创建的对象中.例如,对于只有5行数据的表中发生的坏块,删除并重新创建表可能要比执行恢复快得多.

对于每个坏块,请收集下表中的信息.进行此操作的步骤如下所述。
1.初始错误
2.绝对文件号AFN
3.相关文件号RFN
4.块编号BL
5.表空间
6.段类型
7.段所有者.名称
8.相关对象
9.恢复选项

在Oracle8/8i/9i/10g中;绝对文件号和相关文件号通常是一样的,但也可能不同(尤其是在数据库是由Oracle7迁移而来的情况下).要获得正确的AFN和RFN编号,否则您可能最终抢救的是错误的对象.

下列查询将显示数据库中数据文件的绝对和相关文件号：
SELECT tablespace_name, file_id “AFN”, relative_fno “RFN” FROM dba_data_files;

在Oracle8i/9i/10g中:除了上述关于Oracle8 的说明外,从 Oracle8i开始将拥有临时文件.下列查询将显示数据库中临时文件的绝对和相关文件号：
SELECT tablespace_name, file_id+value “AFN”, relative_fno “RFN”
FROM dba_temp_files, v$parameter WHERE name=’db_files’;

在Oracle7中:“绝对文件号”和“相关文件号”使用相同的文件号

“段类型”,“所有者”,“名称”和“表空间”
在给定坏块的绝对文件号“&AFN”和块编号“&BL”的情况下,下列查询将显示对象的段类型,所有者和名称,数据库必须打开才能使用此查询：
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &AFN
and &BL between block_id AND block_id + blocks – 1;

如果坏块位于临时文件中,则上述查询将不会返回任何数据,对于临时文件,“段类型”应为“TEMPORARY”

如果上述查询未返回行,也可能是因为坏块是本地管理表空间 (Locally Managed Tablespace, LMT)中的段头.当坏块为LMT中的段头块时,上述查询将在alert.log 中生成一个坏块消息,但查询不会失败.在这种情况下,请使用以下查询：
SELECT owner, segment_name, segment_type, partition_name
FROM dba_segments
WHERE header_file = &AFN and header_block = &BL;

按段类型分类的“相关对象”和可能的“恢复选项”：
相关对象和能够使用的恢复选项取决于SEGMENT_TYPE.对于各种最常见的段类型,其他查询和可能的恢复选项如下所示:
CACHE
如果段类型为 CACHE,请再次检查您是否输入了正确的 SQL语句 和参数。
恢复选项:可能需要恢复数据库。

CLUSTER
如果段类型为 CLUSTER,则应确定它包含哪些表。
例如：
SELECT owner, table_name
FROM dba_tables
WHERE owner=’&OWNER’
AND cluster_name=’&SEGMENT_NAME’;

恢复选项：
如果所有者为“SYS”可能需要恢复数据库。

对于非数据字典cluster,可能的选项包括：
恢复或 抢救cluster中所有表的数据,然后重新创建cluster及其所有表,
cluster可能包含多个表，因此在做出决策之前，最好先收集cluster中每个表的信息。

INDEX PARTITION
如果段类型为INDEX PARTITION,请记录名称和所有者,然后确定哪些分区受到影响：
SELECT partition_name
FROM dba_extents
WHERE file_id = &AFN
AND &BL BETWEEN block_id AND block_id + blocks – 1;
然后按照处理INDEX段的步骤继续下面的操作.
恢复选项：
使用下列语句可以重建索引分区：
ALTER INDEX xxx REBUILD PARTITION ppp;

INDEX
如果段类型为INDEX,对于非字典INDEX或INDEX PARTITION,确定索引位于哪个表中:
例如:
SELECT table_owner, table_name
FROM dba_indexes
WHERE owner=’&OWNER’
AND index_name=’&SEGMENT_NAME’;
并确定索引是否支持约束：
例如:
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE owner=’&TABLE_OWNER’
AND constraint_name=’&INDEX_NAME’;

CONSTRAINT_TYPE 的可能值包括：
P 索引支持主键约束。
U 索引支持唯一约束。
如果索引支持主键约束（类型“P”），则确认主键是否被任何外键约束引用：
例如：
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE r_owner=’&TABLE_OWNER’
AND r_constraint_name=’&INDEX_NAME’
选项：
如果所有者为“SYS”,可能需要恢复数据库。
对于非字典索引,可能的选项包括：
恢复或 重建索引（任何相关联的约束会随之禁用/启用）

ROLLBACK
如果段类型为ROLLBACK,因为 ROLLBACK 段坏块需要特殊处理。
选项可能需要恢复数据库。

TYPE2 UNDO
TYPE2 UNDO 是系统管理的undo段,它是 rollback段的一种特殊形式.这些段的坏块需要特殊处理.
选项可能需要恢复数据库。

TABLE PARTITION
如果段类型为TABLE PARTITION,请记录名称和所有者,然后确定哪些分区受到影响：
SELECT partition_name
FROM dba_extents
WHERE file_id = &AFN
AND &BL BETWEEN block_id AND block_id + blocks – 1;
然后按照处理TABLE段的步骤继续下面的操作.
选项：
如果所有坏块均位于同一个分区，则此时可以采取的一个做法是用一个空表EXCHANGE坏块所在的分区,这可以让应用程序继续运行(无法访问坏块所在的分区中的数据),然后可以从之前的空表中提取任何未损坏的数据

TABLE
如果所有者为“SYS”,可能需要恢复数据库。
对于非字典 TABLE 或 TABLE PARTITION，确定表中存在哪些索引：
例如：
SELECT owner, index_name, index_type
FROM dba_indexes
WHERE table_owner=’&OWNER’ AND table_name=’&SEGMENT_NAME’;
并确定表中是否存在任何主键：
例如:SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE owner=’&OWNER’
AND table_name=’&SEGMENT_NAME’ AND constraint_type=’P’;
如果存在主键，则确认它是否被任何外键约束引用：
例如：
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE r_owner=’&OWNER’
AND r_constraint_name=’&CONSTRAINT_NAME’;
选项：
如果所有者为“SYS”,可能需要恢复数据库。
对于非字典表，可能的选项包括：
恢复或 抢救表(或分区)中的数据,然后重新创建表(或分区)或忽略坏块(例如:使用DBMS_REPAIR标记需要跳过的问题块)

IOT（索引组织表）
IOT 表中的坏块应按照表或分区表中的处理方式来处理。
唯一的例外是如果 PK 损坏。
IOT表的PK就是表本身它不能被删除和重新创建
选项：
如果所有者为“SYS”,可能需要恢复数据库。
对于非字典表，可能的选项包括：
恢复或 抢救表(或分区)中的数据,然后重新创建表(或分区)或忽略坏块(DBMS_REPAIR不适用于IOT)

LOBINDEX
确定LOB属于哪个表：
SELECT table_name, column_name
FROM dba_lobs
WHERE owner=’&OWNER’ AND index_name=’&SEGMENT_NAME’;
如果表的所有者为“SYS”可能需要恢复数据库。
不可以重建 LOB 索引,因此您必须将该问题作为受影响的表中LOB列上的坏块来处理。
选项：
如果所有者为“SYS”可能需要恢复数据库。
对于非字典表，可能的选项包括：
恢复或 抢救表（及其 LOB 列）中的数据,然后重新创建表,忽略坏块的做法通常不可取,除非不大可能对表中的问题列执行任何进一步的 DML 操作。

LOBSEGMENT
确定 LOB 属于哪个表：
例如：
SELECT table_name, column_name
FROM dba_lobs
WHERE owner=’&OWNER’
AND segment_name=’&SEGMENT_NAME’;
如果表的所有者为“SYS”,可能需要恢复数据库。
对于非字典表,要查找引用损坏的 LOB 块的具体行可能比较困难，因为报告的错误中不会显示表中的哪一行数据包含损坏的 LOB 数据。
通常可以参考发生该错误的应用程序日志、任何SQL_TRACE、会话的10046 跟踪文件（如果有）,或通过在会话中设置事件“1578 trace name errorstack level 3”，查看是否有助于标识当前的 SQL/绑定/行。
例如：
ALTER SYSTEM SET EVENTS ‘1578 trace name errorstack level 3’;
然后等待应用程序触发该错误，并查找跟踪文件。
如果没有任何线索，您可以构建 PLSQL 块，逐行扫描问题表以提取 LOB 列数据，扫描将一直循环进行，直至发生错误。此方法可能需要一段时间，但它应该可以找到引用了损坏的 LOB 块的数据行的主键或 ROWID。
例如：
set serverout on
exec dbms_output.enable(100000);
declare
error_1578 exception;
pragma exception_init(error_1578,-1578);
n number;
cnt number:=0;
badcnt number:=0;
begin
for cursor_lob in (select rowid r, &LOB_COLUMN_NAME L
from &OWNER .. &TABLE_NAME) loop
begin
n := dbms_lob.instr(cursor_lob.L, hextoraw(‘AA25889911’), 1, 999999);
exception
when error_1578 then
dbms_output.put_line(‘Got ORA-1578 reading LOB at ‘ ||
cursor_lob.R);
badcnt := badcnt + 1;
end;
cnt := cnt + 1;
end loop;
dbms_output.put_line(‘Scanned ‘ || cnt || ‘ rows – saw ‘ || badcnt ||
‘ errors’);
end;
/
损坏的 LOB 块可能仅显示为旧版本（为保证一致性读取）,且该块未被重新使用，在这种情况下，所有表中所有行都可以访问，但一旦该块被回收重新使用，就不可以插入/更新 LOB 列了。
选项：
如果所有者为“SYS”,可能需要恢复数据库。
对于非字典表，可能的选项包括：
恢复或抢救表（及其 LOB 列）中的数据,然后重新创建表或忽略坏块(不可以在 LOB 段上使用 DBMS_REPAIR）

TEMPORARY
如果段类型为TEMPORARY,则坏块不会影响永久对象.检查发生问题的表空间是否正在被用作TEMPORARY表空间：
SELECT count(*) FROM dba_users
WHERE temporary_tablespace=’&TABLESPACE_NAME’;
选项：
如果是 TEMPORARY_TABLESPACE，则可能可以创建新的临时表空间，并将所有用户切换到该表空间，然后删除有问题的表空间。
如果不是临时表空间，则该块不会再被读取，而且会在下次使用时被重新格式化 — 如果问题的根本原因已经得到解决，则不应再发生该错误。
通常情况下，不需要进行任何还原，但如果磁盘可能有问题，且表空间包含有用数据，则最好对数据库中受影响的文件进行恢复

“无返回行”
如果没有包含坏块的extent,则首先再次检查查询中使用的参数.如果您确定文件号和块编号是正确的,且不属于 DBA_EXTENTS 中的某个对象,则执行以下操作：
再次检查相关文件是否为临时文件。请注意，临时文件的文件号取决于数据库初始化参数 DB_FILES，因此对该参数的任何更改都会改变错误中报告的绝对文件号。

DBA_EXTENTS 不包含本地管理表空间中用于本地空间管理的块

如果您在数据库运行查询语句的时间点与出错的时间点不相同，那么问题对象可能已经被删除，因此针对 DBA_EXTENTS 的查询可能不会显示任何行。

如果您正在调查的错误由 DBVERIFY 报告，则 DBV 将检查所有块，而不管它们是否属于某个对象。因此，坏块可能存在于数据文件中，但却未被任何对象使用。
选项：
未使用的 Oracle 块上的错误可以忽略，因为如果需要使用该块，Oracle 会创建新的块映像（格式化），因此，该块上的任何问题将永不会被读取。
如果您怀疑该块可能是空间管理块，则可以使用 DBMS_SPACE_ADMIN 包来帮助您进行检查：
exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY(‘&TABLESPACE_NAME’);
以上命令会将不一致写入跟踪文件，但如果遇到致命的坏块，它将报告如下错误：
ORA-03216: Tablespace/Segment Verification cannot proceed
位图空间管理块上发生的错误通常可以通过运行以下命令来修正：
exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS(‘&TABLESPACE_NAME’);
对于每个坏块，如果需要尝试并确定实际坏块原因，则收集如下物理证据也是一个比较好的方法：
i) 坏块及位于其任意一侧的块的操作系统 HEX 转储。
在 UNIX 上：
dd if=&FILENAME bs=&TS_BLOCK_SIZE skip=&BL-1 count=3 of=BL.dd
例如：对于BL=1224：
dd if=ts11.dbf bs=4k skip=1223 count=3 of=1223_1225.dd
在 VMS 上：
其中 XXXX=操作系统块编号（512 字节块中）
要计算此值，用报告的块编号乘以“&TS_BLOCK_SIZE/512”。

ii) 处于 ARCHIVELOG 模式时，复制出错时间前后的归档日志文件的安全副本，最好包括报告错误前数小时的日志文件。并且，保存问题数据文件在出错前的所有副本，因为之前的数据文件映像以及 redo 记录有助于找出错误原因DBV 通常可用于检查问题是否存在于文件的备份副本中）.理想的情况是获得没有报告坏块的数据文件备份映像，以及从该时间点开始到首次报告坏块时间之后不久的时段内的所有 redo 记录。

iii) 获得问题块的 Oracle 转储：
ALTER SYSTEM DUMP DATAFILE ‘&FILENAME’ BLOCK &BL;

(4) 选择恢复选项

现在，最佳的恢复选项取决于受影响的对象。前面第 (3) 部分中的说明应该已经重点介绍了针对每个受影响对象的主要可用选项。选择的实际恢复方法可能包含以下一种或多种混合方法：
是否需要进行任何恢复操作？
如果错误发生在TEMPORARY 表空间中，或位于不再属于任何数据库对象的块中,则无需进行任何操作.

可以使用完全恢复吗？
要选用完全恢复，必须满足如下条件：
数据库处于 ARCHIVELOG 模式（“ARCHIVE LOG LIST”命令显示 Archivelog模式）

拥有受影响文件的完好备份。请注意，在某些情况下，坏块可能已经存在，但在很长一段时间内未被发现。如果最近的数据文件备份仍包含坏块，那么只要您拥有所有必需的归档日志，就可以尝试使用更早的备份。
（通常可以使用 DBV START= / END= 选项来检查位于某个备份文件的恢复副本中的特定块是否损坏）

从备份时间开始到当前时间点的所有归档日志均可用

当前的在线日志均可用且完好无缺

错误不是由运行NOLOGGING 操作之后执行的恢复所导致的
如果满足上述条件,完全恢复通常是首选方法
但请注意:
(a) 如果事务回滚已发现坏块位于对象上,而非 rollback 段本身,则 undo 操作可能已被放弃。在这种情况下,可能需要在恢复完成后重建索引/检查数据完整性。

(b) 如果要恢复的文件包含自上次备份以来执行的 NOLOGGING 操作的数据，在使用了数据文件或数据库恢复的情况下，这些块将被标记为“坏块”。在某些情况下，这会使情况更加糟糕。

如果执行数据库恢复后坏块仍然存在，则表示所有备份都包含坏块，底层错误仍存在，或问题通过redo 重现。在这些情况下，需要选择其他一些恢复选项。

如果不需要从对象本身提取任何数据，能否删除或重新创建该对象？
您可以删除对象或从脚本/最近导出的副本重新创建对象。一旦删除一个对象后，该对象中的块将被标记为“空闲”，并且该块在被分配到新对象时将被重新格式化.明智的做法是,对表进行重命名,而不是删除,除非您完全确定不再需要其中的数据。

对于表分区，只需要删除受影响的分区。例如：ALTER TABLE …DROP PARTITION …

如果坏块影响到分区段头，或者包含分区头的文件处于离线状态，则 DROP PARTITION 可能会失败。在这种情况下，首先将其更换为具有相同定义的表，之后仍然可以删除该分区。
例如：ALTER TABLE ..EXCHANGE PARTITION ..WITH TABLE ..;
最常见的可重建对象为索引。始终在处理表中的索引问题之前处理表坏块

对于任何段，如果您拥有坏块的绝对文件号和块号，则可使用以下快速提取对象 DDL 的方法：
set long 64000
select dbms_metadata.get_ddl(segment_type, segment_name, owner)
FROM dba_extents
WHERE file_id=&AFN AND &BL BETWEEN block_id AND block_id + blocks -1;

是否需要在重新创建对象之前抢救数据？
如果问题位于定期更新的关键应用表上，则可能需要尽可能多地抢救表中数据，然后重新创建该表。

当前忽略坏块是否可取？
在某些情况下，最直接的选项可能就是忽略坏块，并阻止应用程序对它进行访问。

最后的选项
将数据库或表空间恢复到较早的时间点（通过时间点恢复）或还原出现坏块前的冷备份或使用现有导出文件

完全恢复
如果数据库处于ARCHIVELOG 模式下,且拥有受影响文件的完好备份,则恢复通常为首选方法.这不保证可以解决问题,但的确可以有效的解决大部分坏块问题.如果恢复再次引发问题,则返回到以上选项列表并选择其他方法.

如果使用的是Oracle9i（或更高版本）,则可以使用RMAN BLOCKRECOVER命令执行块级恢复。

如果使用的是较早版本的Oracle,则可以执行数据文件恢复（数据库其他部分可以继续运行）,或数据库恢复（需要关闭数据库）

如果使用的是Oracle 11g（或更高版本,则可以使用“Data Recovery Advisor（数据恢复指导）”.

块级恢复
自Oracle9i版本起,RMAN允许恢复单个块,同时数据库的其他部分（包括数据文件中的其他块）仍可以进行正常访问.请注意,块级恢复只能将块完全恢复到当前时间点.要使用此选项恢复单个块,不一定要使用 RMAN 进行备份.
例如：
实际情况是,文件6的块30上发生ORA-1578错误,可能是由于介质问题导致的坏块,且您拥有该文件的完好冷备份映像,并已还原到“…/RESTORE/filename.dbf”.假设所有归档日志均存在（位于默认位置）,则可以通过RMAN使用以下命令序列执行块级恢复：
rman nocatalog
connect target
catalog datafilecopy ‘…/RESTORE/filename.dbf’;
run {blockrecover datafile 6 block 30;};
此操作将使用注册的数据文件备份映像和任何需要的归档日志来执行块恢复,仅将有问题的块恢复到当前时间点.

数据文件恢复
数据文件恢复包括下列步骤.如果有多个文件,则针对每个文件重复执行这些步骤,或参阅下面的“数据库恢复”.当数据库处于 OPEN 或 MOUNTED 状态时,均可使用这些步骤.
使受影响的数据文件离线
例如：ALTER DATABASE DATAFILE ‘name_of_file’ OFFLINE;

将文件复制到安全位置（以防备份损坏）

将文件的最新备份还原到完好的磁盘上

使用DBVERIFY检查还原的文件是否有坏块

假设还原的文件完好,则将数据文件重命名并保存到新位置（如果不是原来的位置）
例如：ALTER DATABASE RENAME FILE ‘old_name’ TO ‘new_name’;

恢复数据文件
例如：RECOVER DATAFILE ‘name_of_file’;

使数据文件上线
例如：ALTER DATABASE DATAFILE ‘name_of_file’ ONLINE;

数据库恢复
数据库恢复通常包含以下步骤：
关闭数据库（使用选项 immediate 或 abort）

将待恢复的所有文件的当前副本复制到安全位置

将备份文件还原到完好的磁盘上

请勿还原控制文件或在线REDO 日志文件

使用DBVERIFY检查还原的文件

启动数据库到MOUNT状态（startup mount）

对任何需要重新定位的数据文件进行重命名
例如：ALTER DATABASE RENAME FILE ‘old_name’ TO ‘new_name’;

确保所有必需的文件在线
例如：ALTER DATABASE DATAFILE ‘name_of_file’ ONLINE;

恢复数据库
例如：RECOVER DATABASE

打开数据库
例如：ALTER DATABASE OPEN;

一旦执行了完全恢复,最好在允许使用之前先检查数据库：
针对每个问题对象运行“ANALYZE VALIDATE STRUCTURE CASCADE”，检查表/索引是否存在不匹配。如果有任何 und 操作曾被放弃,此命令可能会显示不匹配,此时需要重建索引。

在应用程序级别检查表中数据的逻辑完整性。

重建索引
损坏对象为用户索引时,如果底层表没有损坏,则可以删除并重建该索引。

如果底层表也已经损坏,则应在重建任何索引之前先解决该表的坏块。

如果收集的信息表示索引有从属外键约束,则需要执行以下操作：
ALTER TABLE DISABLE CONSTRAINT ;

使用以下命令重建主键
ALTER TABLE

DISABLE CONSTRAINT ;
DROP INDEX ;
CREATE INDEX .. with appropriate storage clause
ALTER TABLE

ENABLE CONSTRAINT ;

启用外键约束
ALTER TABLE ENABLE CONSTRAINT ;

对于索引分区,以执行以下命令：
ALTER INDEX …REBUILD PARTITION …;

注意：
(1) 不要使用“ALTER INDEX .. REBUILD”命令重建损坏的非分区索引,这一点非常重要,因为此操作通常会尝试从包含坏块的现有索引段中构建新索引..“ALTER TABLE … REBUILD ONLINE”和“ALTER INDEX … REBUILD PARTITION …”不会从旧索引段中构建新索引,因此可以使用。

(2) 如果新索引包含的列为现有索引的子集,则 Create INDEX 可以使用现有索引中的数据,因此,如果您有两个损坏的索引,应在重建之前将两个都删除。

(3) 重建索引时,请确保使用正确的存储选项。

抢救表中数据
如果损坏的对象为TABLE 或 CLUSTER 或 LOBSEGMENT,则必须明白,坏块内的数据已经丢失.部分数据可能可以从块的HEX转储中，或从索引涵盖的列中抢救回来.

由于可能需要从索引中抢救坏块中的数据,因此最好不要删除任何现有索引,直至所有需要的数据提取完成。

从包含坏块的表中提取数据有多种方法。选择最恰当的方法,详细信息如下所述.这些方法的目的是从可访问的表块中提取尽可能多的数据.通常,将损坏的表重命名是一个比较好的方法,这样就可以使用正确的名称创建新对象.
例如：RENAME TO ;

从坏块表中提取坏块周围数据的方法
(1) 从Oracle 7.2开始(包括 Oracle 8.0、8.1 和 9i)可以跳过表中的坏块。
这是到目前为止最简单的提取表数据的方法,用DBMS_REPAIR.SKIP_CORRUPT_BLOCKS or Event 10231

如果坏块位于IOT overflow 段,则应使用相同的方法,不同的是使用Event 10233和全索引扫描

请注意,此方法只适用于块的“包装”已被标记为“坏块”的情况。例如：如果块报告 ORA-1578 错误。如果问题为 ORA-600 或其他非ORA-1578 错误,则通常可以使用 DBMS_REPAIR 将表中坏块标记为“软坏块”。这样在您访问该数据块时，系统将显示 ORA-1578错误,从而可以使用 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS。

注意:被“FIX_CORRUPT_BLOCKS”程序标记为“坏块”的块在任何还原/恢复操作之后还将被标记为“坏块”.

使用DBMS_REPAIR进行此操作概括起来步骤如下：
使用DBMS_REPAIR.ADMIN_TABLES 创建管理表

使用DBMS_REPAIR.CHECK_OBJECT 找到问题块

在损坏问题块之前将其中所有完好的数据导出。

使用DBMS_REPAIR.FIX_CORRUPT_BLOCKS将找到的问题块标记为“坏块”,然后它们就会显示 ORA-1578

如果需要,使用 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS跳过表中的坏块。

(2) 从 Oracle 7.1 开始,可以使用 ROWID 范围扫描.此功能的语法较为复杂,但可以使用 ROWID提示选择坏块周围的数据.

(3) 如果存在主键，则可以通过此索引选择表数据。也可以通过任何其他索引选择一些数据。此方法较慢，花费时间较长，通常只有 Oracle 7.0 版本才使用

(4) 有多种抢救程序/PLSQL 脚本可用于抢救表中的数据。与上述方法相比，这些方法在设置和使用方面需要花费更长的时间，但常常能够处理除 ORA-1578 之外的各类坏块

从包含损坏的LOBSEGMENT 块的表中提取数据的方法:
在 LOB 段上不可以使用 DBMS_REPAIR,如果坏块 LOB 块未被表中的任何行引用,则应该可以使用 CREATE TABLE as SELECT (CTAS)来按选择创建表,或按原样导出/删除/导入该表。

如果坏块LOB 块被某个行引用,则应该可以使用不包括问题行的WHERE谓词进行选择或导出

注意:可以将问题行的LOB列值更新为NULL,从而使SELECT操作不再返回ORA-1578错误,但是坏块将等待被重新使用,随着对行中的 LOB列进行INSERT或UPDATE操作,当有问题的块被重新使用时,最后还是会报ORA-1578错误,那时的情况比已知行出现坏块更糟糕.因此,只有您打算立刻重新创建表,才应该将LOB列设为NULL.

从坏块本身提取数据
由于坏块本身已经“损坏”,则从该块中提取的任何数据都应被视为可疑数据,从坏块本身获取数据行的主要方法包括：
对于 TABLE 的块，Oracle Support 可以使用一款尝试解释块内容的工具。

使用表中现有索引,利用落在坏块内的ROWID 来提取索引所涵盖的列数据，上文提到的 ROWID 范围扫描文章在接近结束时对此内容有所介绍：
对于 Oracle8/8i，请参阅 Document 61685.1
对于 Oracle7,请参阅 Document 34371.1

在 redo 流上可以使用 LogMiner 来查找向问题块加载数据的初始插入/更新操作。此处的主要因素是数据实际被放入问题块的时间.例如,行2可能在昨天已插入,而行1可能在1年前已插入.

忽略坏块
出错时可以忽略坏块并接受报告的错误,或在应用程序级别阻止对出问题的块行进行访问。
例如：如果问题块/行位于子表中,则可以在应用程序级别阻止对父表中对应行的访问,从而子行就永不会被访问(但要注意级联类约束)

这样做可能不利于批量访问数据的报告和其他任务,因此,为了阻止块在被访问时报错,前面所述的DBMS_REPAIR选项也不失为一个可取的方法.使用这种方法标记并跳过坏块提供了一种短期的解决方案.从而在计划停机时可以尝试进行完全数据抢救和/或恢复,或留出更多时间在第二个（克隆）数据库上尝试其他恢复选项.但请注意,使用DBMS_REPAIR.FIX_CORRUPT_BLOCKS标记块坏块将导致标记的块在恢复后还是“坏块”。

忽略坏块对于快速老化且即将被清除的数据而言是比较好的选择(例如,在按日期分区的表中,较老的分区将在某时间点被删除).

忽略LOB段上的坏块
在应用程序级别,可以忽略损坏的LOB列,直到可以重新构建该表.确保不出现上述“警告”中的情形的一种方法是确保应用程序只能通过表上的包含WHERE 谓词的视图来访分表中的数据.
例如：假设表 MYTAB（a number primary key，b clob）有一行或多行指向损坏的 LOB 数据。
ALTER TABLE MYTAB ADD ( BAD VARCHAR2(1) );

CREATE VIEW MYVIEW AS SELECT a,b FROM MYTAB WHERE BAD is null;

对任何问题行设置 BAD=’Y’

如果只通 MYVIEW 访问 MYTAB，该行将永不可见,因此也无法更新,从而实现了坏块条目隔离,直到问题解决.

很明显,此示例更多的是一个设计时解决方案,但某些应用程序可能已有类似机制,且可能只通过某个视图（或通过 RLS 策略）访问数据,从而提供某些选项来隐藏问题行。

针对忽略坏块的警告
虽然可以忽略坏块,但需要注意的是,坏块在运行DBVERIFY,RMAN 备份时仍然会以警告/错误等形式出现。请务必仔细记录您将在这些工具中看到的任何坏块,尤其是您期望在使用RMAN时跳过的任何块（例如，设置了 MAX_CORRUPT）,并确保在清除坏块后移除任何对错误的“接受”选项.
例如：假设坏块已处理为忽略坏块，并在应用程序级别跳过问题行。RMAN可能被配置为在备份时接受坏块。然后在稍后的表重组期间重新创建表。如果 RMAN 配置未及时更新以反映目前已无任何错误，则 RMAN 可能会忽略稍后出现的某些其他坏块。

此外，还有重要的一点需要注意，忽略table段中的坏块可能导致查询返回不一致的结果。
例如：设置了 SKIP_CORRUPT 的表可能出现不同的结果,具体取决于是使用了了索引扫描还是表访问,其他报告可能只是报错.。

请注意,如果忽略坏块但使用DBMS_REPAIR.FIX_CORRUPT_BLOCKS标记,系统会向坏块中写入redo信息,这可能会限制后续的恢复选项.

最后的选项
如果你有standby环境（物理或逻辑）,请首先对其进行检查。

无论问题发生在何种类型的块上,均可使用一种可能的选项,即将数据库或问题表空间恢复到出现坏块之前的某个时间点.此选项的困难之处在于,并不总能知道问题首次出现的时间.

DBVERIFY通常可用于检查还原的文件是否存在坏块.尤其是,START= / END= DBV选项可用于在还原的备份映像上快速进行首次测试,以检查问题块本身是否出错。

下面列出了一些可用于进行恢复操作的最终选项,当出现其中一种或多种情况：
您丢失了非常重要的数据文件（或数据文件出现坏块），而没有问题文件的正常备份（无坏块）
既不处于ARCHIVELOG 模式，也没有自文件创建以来的全部归档日志
完全恢复后仍重复出现问题

最后的机会：
请注意,如果丢失了数据文件的所有副本,但仍具有自文件创建以来的全部归档日志,则仍有可能恢复该文件。
例如：
ALTER DATABASE CREATE DATAFILE ‘….'[as ‘…’] ;

RECOVER DATAFILE ‘….’

ALTER DATABASE DATAFILE ‘….’ONLINE;
如果您遇到这种情况,请在继续下面的操作之前先尝试使用这些步骤来恢复数据文件。

如果您到达这一步,就说明没有其他办法可以将文件恢复到当前时间点.此时最好关闭实例,并对当前数据库进行备份,以便在选用的措施失败后仍然能够回退到当前时间点.（例如：如果发现备份坏块）.

可用的一些选项概述如下：
恢复到早期的冷备份
例如：如果处于 NOARCHIVELOG 模式
从冷备份建立克隆数据库
并提取（导出）问题表
或传输问题表空间

使用基于时间点的恢复将数据库恢复到一致的时间点
需要完好备份和任何所需的归档日志
必须还原所有文件且将整个数据库前滚到恰当的时间点。
可以在克隆数据库中执行基于时间点的恢复,然后将问题表空间传输到问题数据库,或将问题表利用导出/导入工具从克隆数据库导入到问题数据库.

表空间基于时间点的恢复
可以仅对受影响的表空间执行基于时间点的恢复.

从逻辑导出/副本重新创建数据库
需要具有完好的数据库逻辑备份
注意：要使用此选项,必须重新创建数据库。
与其他选项一样,可以在克隆数据库中进行重新创建,只为获得问题表的完好映像.

总之做好备份是DBA最重要的工作.