latch row cache objects等待事件的诊断

在awr报告中的top wait中显示了’latch: row cache objects’竞争
Top 5 Timed Foreground Events

Event	                        Waits	        Time(s)	        Avg wait (ms)	% DB time	Wait Class
latch: row cache objects	11,509,891	291,065	        25	        71.65	        Concurrency
DB CPU	 	                                37,417	 	                9.21	 
db file sequential read	        2,221,943	19,023	        9	        4.68	        User I/O
read by other session	        1,464,324	9,340	        6	        2.30	        User I/O
enq: TX - row lock contention	19,750	        4,928	        250	        1.21	        Application

	        Per Second	Per Transaction	 Per Exec	Per Call
DB Time(s):	     112.9	            2.4	     0.02	    0.05
DB CPU(s):	      10.4	            0.2	     0.00	    0.00
Redo size:	 580,567.8	       12,152.7	 	 
Logical reads:	 417,007.5	        8,729.0	 	 
Block changes:	   2,931.4	           61.4	 	 
Physical reads:	  34,146.3	           714.8	 	 
Physical writes      288.6	             6.0	 	 
User calls:	   2,507.1	            52.5	 	 
Parses:	           1,023.5	            21.4	 	 
Hard parses:	     528.4	            11.1	 	 
W/A MB processed:      7.6	             0.4	 	 
Logons:	               0.3	             0.0	 	 
Executes:	   5,231.8	            109.5	 	 
Rollbacks:	       0.7	            0.0	 	 
Transactions:	      47.8

SQL ordered by Elapsed Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
%Total – Elapsed Time as a percentage of Total DB time
%CPU – CPU Time as a percentage of Elapsed Time
%IO – User I/O Time as a percentage of Elapsed Time
Captured SQL account for 5.1% of Total DB Time (s): 406,212
Captured PL/SQL account for 0.3% of Total DB Time (s): 406,212

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	   %IO	     SQL Id	                                   SQL Module	SQL Text
       7,316.50	           3	                2,438.83    1.80	0.74	97.96	4ux9hyv2xrxqp	 JDBC Thin Client	                            select * from (select page$tem...
       3,240.46	           0	 	                  0.80	  0.00	0.00	      3jkhph1czbx52	 JDBC Thin Client	                            update (select c.paf045 c045, ...
       3,092.00	      41,263	                    0.07	  0.76	1.08	98.03	44njhv5k4r2bx	 JDBC Thin Client	                            select a.akb020 hospital_id, a...
       1,405.07	           4	                  351.27	  0.35	2.50	93.19	92st5dkx3b59d	 JDBC Thin Client	                            select count(1) from ( SELECT ...
       1,399.77	           1	                1,399.77	  0.34	0.00	0.00	0q7cfvguf04k9	 JDBC Thin Client	                            update (select c.paf045 c045, ...
       1,037.16	      11,147	                    0.09	  0.26	29.24	35.89	1w68qv0t71aak	 siserv@el01cn04.exalogic.gzsi.cn (TNS V1-V3)	begin PKG_SERVICES . PRC_BIZ_S...
         707.83	         169	                    4.19	  0.17	38.56	23.96	b29wgyggbpj29	 JDBC Thin Client	                            select * from ( select b.akb02...
         615.84	  10,590,959	                    0.00	  0.15	40.13	0.00	grwydz59pu6mc	                                              select text from view$ where r...
         408.78	           4	                  102.20	  0.10	13.63	66.25	5xtsfsftqxj7v	 JDBC Thin Client	                            select v.aac001, (select c.aac...
         359.40	      78,750	                    0.00	  0.09	2.95	0.00	7dsyr136296ty	 JDBC Thin Client	                            select cur_num_no from SYS_SER...

SQL ordered by CPU Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
%Total – CPU Time as a percentage of Total DB CPU
%CPU – CPU Time as a percentage of Elapsed Time
%IO – User I/O Time as a percentage of Elapsed Time
Captured SQL account for 3.3% of Total CPU Time (s): 37,417
Captured PL/SQL account for 0.8% of Total CPU Time (s): 37,417

CPU Time (s)	Executions	CPU per Exec (s)	%Total	Elapsed Time (s)	%CPU	  %IO	       SQL Id	                                  SQL Module	SQL Text
     303.26	      11,147	           0.03	    0.81	       1,037.16	 29.24	35.89	1w68qv0t71aak	 siserv@el01cn04.exalogic.gzsi.cn (TNS V1-V3)	begin PKG_SERVICES . PRC_BIZ_S...
     272.93	    169	1.61	           0.73	  707.83	          38.56	 23.96	      b29wgyggbpj29	                            JDBC Thin Client	select * from ( select b.akb02...
     247.13	  10,590,959	           0.00	    0.66	         615.84	 40.13	 0.00	grwydz59pu6mc	                            	                select text from view$ where r...
      68.86	       4,972	           0.01	    0.18	         131.43	 52.40	 1.14	df3fssxryx4g5	                            JDBC Thin Client	select nvl(ac01.aac012, ac01.B...
      55.86	          18	           3.10	    0.15	         120.11	 46.51	 0.01	admybmxruwxkz	                            PL/SQL Developer	select * from v$open_cursor wh...
      55.70	           4	          13.93	    0.15	         408.78	 13.63	66.25	5xtsfsftqxj7v	                            JDBC Thin Client	select v.aac001, (select c.aac...
      54.33	           3	          18.11	    0.15	       7,316.50	  0.74	97.96	4ux9hyv2xrxqp	                            JDBC Thin Client	select * from (select page$tem...
      37.54	           4	           9.38	    0.10	         113.28	 33.14	30.75	453gakrhkhmnw	                            JDBC Thin Client	with rs as(select m.aac001, m....
      37.35	           4	           9.34	    0.10	         210.48	 17.74	61.40	5w5gchz2ymag5	                            JDBC Thin Client	with rs as(select m.aac001, m....
      35.17	           4	           8.79	    0.09	       1,405.07	 2.50	  93.19	92st5dkx3b59d	                            JDBC Thin Client	select count(1) from ( SELECT ...

在oracle11.2.0.3中,会动态地增加’latch:row cache objects’的等待可以查看awr报告的’Latch Miss Sources’部分,top location calling部分是’row cache objects kqrpre: find obj’:

Latch Miss Sources
only latches with sleeps are shown
ordered by name, sleeps desc
Latch Name	        Where	          NoWait Misses	      Sleeps	Waiter Sleeps
..........
row cache objects	kqrpre: find obj	      0	   6,049,715	    4,177,088
row cache objects	kqreqd: reget	              0	   3,227,630	    3,311,481
row cache objects	kqreqd	                      0	   2,286,734	    4,076,892
row cache objects	kqrso	                      0	       8,571	        7,388
row cache objects	kqrbgl	                      0	         428	          408
row cache objects	kqrpre: init complete	      0	         310	          323
row cache objects	kqrigt	                      0	         232	          210
row cache objects	kqrigt2	                      0	         227	          237
row cache objects	kqrbip	                      0	         220	          114
row cache objects	kqrbtm: pop parent	      0	         209	          222
row cache objects	kqrbpr: KQRRSNRL	      0	         103	          198
row cache objects	kqrbfr	                      0	          15	           47
row cache objects	kqrigt3	                      0	          12	            3
row cache objects	ksucallcbksafely: kqrhngc     0	           8	            0
row cache objects	kqrsrd	                      0	           3	           19
row cache objects	kqrcmt: while loop	      0	           2	            3

‘row cache objects’闩锁被调用的最频繁的是’kqrpre: find obj’.这个模式尝试在行缓存中找到正被解析对象的详细信息.在解析时行缓存会被搜索.进程搜索是在缓存中通过一个由’row cache objects’闩锁所保护的链表来找到它所需要的信息.当另外的进程也在解析并通过行缓存进行查找时就可能会发生竞争,特别是解析活过于频繁或执行一些不必要的解析时产生竞争的机会就会增大.

从系统状态跟踪文件中通常可以看到以下相关进程:
()+492< -kslges()+2152<-kqreqd()+256<-kkoiqb()+9436<-kkooqb()+684<-kkoqbc()+2256<-apakkoqb()+140
<-apaqbdDescendents()+460<-apadrv()+2020<-opitca()+2112 <-kksFullTypeCheck()+72<-rpiswu2()

在这个跟踪堆中大部分时间花在KKOIQB函数.这个函数使用行缓存以顺序调用来找索引名字因此这个表的索引会以索引名字字母的顺序的来排序.在这种情况下在不同执行计划之间优化器需要一个一致的方法来选择一个索引.通过按索引名字字母来排序来完成.

造成这个问题的原因是因为在oracle11.2中优化器的结构的改变.在oracle11.2.0.3中对于使用视图或子查询的查询会有更多的查询转换.这就会产生更多的优化操作也就意味着会有更多的row cache latch调用.

IOStat by Function summary
'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
ordered by (Data Read + Write) desc
Function Name	    Reads:Data Reqs per sec Data per sec Writes:Data Reqs per sec Data per sec Waits:Count Avg Tm(ms)
Direct Reads	        223.3G	     119.22	 63.6844	  0M	     0.00	    0M	         0
RMAN	                 62.2G	      64.06	 17.7467	216M	     2.10      .060147	    113.5K	 0.27
Buffer Cache Reads       37.3G	    1270.31	 10.6290	  0M	     0.00	    0M	   2609.5K	 6.30
Others	                  3.8G	      17.59	 1.08626	3.1G	     7.79      .884942	     65.7K	 0.59
DBWR	                   0M	       0.00	      0M	5.3G	    73.69      1.50228	         0
LGWR	                   2M	       0.04	 .000556	2.4G	    71.72      .683616	    234.4K	 0.11
Direct Writes	           0M	       0.00	      0M	687M	     0.65      .191301	         0
Streams AQ	           0M	       0.00	      0M	  0M	     0.00	    0M	         2	 8.00
TOTAL:	               326.7G	    1471.23	 93.1470       11.7G	   155.95      3.32229	   3023.2K	 5.47

解决方法:
1.检查程序使用绑定变量来降低每秒528的硬解析次数
2.在业务高峰期间停止rman备份
3.优化消耗大量cpu时间和执行时间长的sql语句(执行时间长和消耗cpu时间大的sql有些是重复的)

在使用以上解决方法后在第二天业务高峰期间恢复正常了

怎样诊断数据库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'来找到当前正在等待的事件.

怎样收集10046跟踪文件来诊断性能问题

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

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

 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 12';

会话已更改。

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 value from v$diag_info where name='Default Trace File';  
VALUE
----------------------------------------------------------------------------------
/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
-------           -----  --------------
 20                 21   SYS
 
然后可以使用下面的命令来对指定的会话启用跟踪
SQL> execute dbms_monitor.session_trace_enable(21,20);

跟踪状态在数据库重启后就会被删除可以查询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(21,20);

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

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();  

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);
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,false);

注意: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文件生成。

oracle ash性能报告的使用方法

活动会话历史报告
活动会话历史
v$active_session_history视图提供了在实例级别抽取会话活动信息.活动会话每分钟会被抽样一次且被存储在sga中的循环缓冲区中.任何被连接到数据库且正等待一个不属于空闲等待事件的会话会被考虑是一个活动的会话.每个会话抽样都是一组行数据且通过v$active_session_history视图来返回每个被抽样活动会话的行数据,返回最新被抽样会话的第一行数据.因为活动会话抽样是存储在sga中的循环缓冲区中,系统活动越大的,活动时间越少会话的可以被存储在循环缓冲区中.这意味着在这期间被抽样的每个会话会出现在v$视图中或者会话活动的时间会在v$视图中被显示,这完全依赖于数据库活动情况.

作为awr快照的一部分,v$active_session_history视图的内容也会被刷新到磁盘.因为当有繁忙的系统活动时
v$视图的内容会变得非常大,只有会一部分会话被抽样并写到磁盘.通过只捕获活动的会话,那么只会生成正在被执行的会话的相关的一组数据而不是系统中所有会话的数据.使用ash能让你对v$active_session_history视图中的当前数据和dba_hist_active_sess_history视图中的历史数据进行检查和性能分析,通常可能避免需要重放工作量来收集额外的性能跟踪信息.ash包含了被捕获的每一个sql语句的执行计划.可以使用这个信息来识别哪部分sql执行消耗了大部分的sql执行时间.ash报告展现了以下各种信息:
sql语句的sql标识符
sql执行计划标识符和用于执行sql语句的sql执行计划的哈希值
sql执行计划信息
对象数,文件数和块数
等待事件标识符和参数
会话标识符和会话序列号
模块和操作名
服务哈希标识符
用户组标识符

使用活动会话历史报告来执行以下性能分析:
短暂的性能问题通常只会持续几分钟.
通过各种维度或者象时间,会话,模块,操作或sql_id的组合来进行有范围或针对性的性能分析

短暂的性能问题是短暂的不在出现在addm分析中.addm试图报告指出在分析周期内最对DB时间最有影响的性能问题.如果一个特定的问题持续非常短的时间那么它可的严重性可能会被平均化或者由于其它的性能问题使其影响最小化.然而这个问题可能在addm中不会出现,一个性能问题是否会被addm捕获依赖于这个问题的持续时间与生成awr快照的时间间隔.

如果一个性能问题在两个awr快照生成的时间间隔内持续了很长时间那么这个问题会被addm捕获.例如如果awr快照时间被设置为一个小时,一个性能问题持续了30分钟那么这个问题不会被认为是一个短暂的性能问题因为它在快照时间间隔内持续很长时间它会被addm捕获.然而如果一个性能问题仅仅持续2分钟可能会被认为是一个短暂的性能问题因为在快照时间间隔内它只持续了很短的时间所以这个问题不会出现在addm报告中.例如如果你被告知系统在上午10:00到上午10:10之间很慢,但addm分析的时间间隔是从上午10:00到上午11:00.那么在addm报告中不会显示这个问题.

ASH报告分成多个部分.HTML报告包括能快速导航到不同部分的链接.

生成一个ash报告
ashrpt.sql脚本用来生成一个html或text格式的报告来显示特定持续期间的ash信息.为了生成一个ash报告执行以下操作:
1.在sql提示符处输入:
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
2.指定你想生成的报告格式html或text:
Enter value for report_type: text
在这个例子中选择生成text格式的ash报告
3.指定在ash报告开始时间:
Enter value for begin_time: -10
在这个例子中开始时间是当前时间之前的10分钟
4.输入这个报告持续的时间如果你想捕获从指定的开始时间到当前时间的之间的ash信息:
Enter value for duration:
在这个例子中缺省是持续到当前的系统时间减去指定的开始时间的结果就是持续的时间.
5.输入报告名或使用缺省的报告名:
Enter value for report_name:
Using the report name ashrpt_1_0310_0131.txt
在这个例子中使用缺省的ash名ashrpt_1_0310_0131被生成.这个报告会从当前系统时间之前的10分钟开始收集ash信息一直持续
到当前时间结束.

对一个指定的数据库实例生成ash报告
ashrpti.sql脚本用来对一个指定的数据库和实例生成一个html或text格式的报告来显示特定持续期间的ash信息.这个报告在设置收集ash信息时间之前允许你指定数据库和实例,为了生成一个ash报告执行以下操作:
1.在sql提示符处输入:
@$ORACLE_HOME/rdbms/admin/ashrpti.sql
2.指定你想生成的报告格式html或text:
Enter value for report_type: html
在这个例子中选择生成html格式的ash报告
3.列出了可用的数据库ID和实例号:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———– ——– ———— ———— ————
3349173529 1 cs cs cs1
3349173529 1 test1 test1 test1
输入数据库标识符(dbid)和实例号(inst_num):
Enter value for dbid: 3349173529
Using 3349173529 for database id
Enter value for inst_num: 1

4.这一步仅适用于对一个活动的data guard物理备库实例收集ash报告,如果不是这种情况,可以跳过这一步.为了对一个物理备库生成一个ash报告,这个备库实例必需是以只读方式打开.这个ash报告包含了主数据库存储在磁盘中的ash信息和备数据库内存中的ash信息.指定对于主数据库或备数据库是否使用数据抽样来生成报告:
You are running ASH report on a Standby database.
To generate the report over data sampled on the Primary database, enter ‘P’.
Defaults to ‘S’ – data sampled in the Standby database.
Enter value for stdbyflag:
Using Primary (P) or Standby (S): S
在这个例子中选择了缺省值S备库

5.指定在ash报告开始时间:
Enter value for begin_time: -10
在这个例子中开始时间是当前时间之前的10分钟
6.输入这个报告持续的时间如果你想捕获从指定的开始时间到当前时间的之间的ash信息:
Enter value for duration:
在这个例子中缺省是持续到当前的系统时间减去指定的开始时间的结果就是持续的时间.
7.以秒为单位指定slot_width
Enter value for slot_width:
在这个例子中使用缺省值
8.根据后续提示的说明解释输入报告以下目标信息的值:
target_session_id
target_sql_id
target_wait_class
target_service_hash
target_module_name
target_action_name
target_client_id
target_plsql_entry

9.输入报告名或使用缺省的报告名:
Enter value for report_name:
Using the report name ashrpt_1_0310_0131.txt
在这个例子中使用缺省的ash名ashrpt_1_0310_0131被生成.这个报告会对数据库ID为3349173529的数据库实例从当前系统时间之前的10分钟开始收集ash信息一直持续到当前时间结束.

对oracle rac生成ash报告
ashrpti.sql脚本用来对oracle rac环境中的指定的数据库和实例生成一个html或text格式的报告来显示特定持续期间的ash信息只有被写入磁盘的ash数据才会被用来生成ash报告.这个报告只会对在dba_hist_active_sess_history表中在过去10分钟之内找到的ash进行抽样.为了在oracle rac环境中生成ash报告执行以下操作:
1.在sql提示符处输入:
@$ORACLE_HOME/rdbms/admin/ashrpti.sql
2.指定你想生成的报告格式html或text:
Enter value for report_type: html
在这个例子中选择生成html格式的ash报告
3.列出了可用的数据库ID和实例号:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———– ——– ———— ———— ————
3349173529 1 cs cs cs1
3349173529 1 test1 test1 test1
3349173529 2 test2 test2 test2
3349173529 3 test3 test3 test3
3349173529 4 test4 test4 test4
输入数据库标识符(dbid)和实例号(inst_num):
Enter value for dbid: 3349173529
Using database id: 3349173529
Enter instance numbers. Enter ‘ALL’ for all instances in an Oracle
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.
Enter value for inst_num: ALL
Using instance number(s): ALL

4.指定在ash报告开始时间:
Enter value for begin_time: -1:10
在这个例子中开始时间是当前时间之前的1小时10分钟
5.输入这个报告持续的时间如果你想捕获从指定的开始时间到当前时间的之间的ash信息:
Enter value for duration: 10
在这个例子中持续的时间是10分钟.
6.以秒为单位指定slot_width
Enter value for slot_width:
在这个例子中使用缺省值
7.根据后续提示的说明解释输入报告以下目标信息的值:
target_session_id
target_sql_id
target_wait_class
target_service_hash
target_module_name
target_action_name
target_client_id
target_plsql_entry

8.输入报告名或使用缺省的报告名:
Enter value for report_name:
Using the report name ashrpt_1_0310_0131.txt
在这个例子中使用缺省的ash名ashrpt_1_0310_0131被生成.这个报告会对数据库ID为3349173529的数据库的所有实例从当前系统时间之前的1小时10分钟开始收集ash信息一直持续到当前时间之前的1小时为止结束.

使用ash报告
在生成ash报告之后,可以重新检索哪些标识为短暂性能问题的信息.ash报告的内容分成了以下几个部分:
top events
load profile
top sql
top pl/sql
top java
top sessions
to objects/files/latches
activity over time

top evnets
顶级等待事件部分描述了被抽样会话活动中由用户,后台等产生的顶级等待事件,使用这些信息可以识别是哪些等待事件造成了短暂的性能问题.顶级等待事件包含以下部分:
顶级用户事件
这部分信息显示了在抽样会话活动中占很高百分比的用户进程等待事件.

顶级后以事件
这部分信息显示了在抽样会话活动中占很高百分比的后台进程等待事件.

顶级等待事件参数P1/P2/P3
这部分信息显示了在抽样会话活动中占很高百分比的等待事件的参数值它通过总的等待时间(%Event)百分比进行排序后被显示.对于每一个等待事件p1,p2,p3的值与等待事件参数parameter 1,parameter 2,parameter 3这三个列相关联.

load profile
load profile部分描述了在抽样的会话活动中的负载分析.使用这部分信息可以识别造成短暂性能问题的服务,客户或sql命令类型.负载概要部分包含以下部分信息:
top service/module
这部分信息显示了在抽样会话活动中占很高百分比的服务和模块信息

top client ids
这部分信息显示了在抽样会话活动中占很高百分比的客户端的id信息它是数据库会话中应用程序的特定标识符

top sql command types
这部分信息显示了在抽样会话活动中占很高百分比的sql命令类型比如select或update

top phases of execution
这部分信息显示了在抽样会话活动中占很高百分比的执行步骤比如sql,pl/sql和java的编译和执行操作.

top sql
顶级sql部分描述了抽样会话活动中的顶级sql语句,使用这部分信息可以识别出造成短暂性能问题的高负载sql语句
顶级sql部分包含以下信息:
top sql with top events
top sql with top row sources
top sql using literals
top parsing module/action
complete list of sql text

top sql with top events
这部分信息显示了在抽样会话活动中占总的等待事件很高百分比的sql语句.

top sql with top row sources
这部分信息显示了在抽样会话活动中占很高百分比的sql语句和它们的详细执行计划信息.通过这部分信息可以识别出哪部分的sql执行消耗了大量的sql执行时间

top sql using literals
这部分信息显示了在抽样会话活动中占很高百分比的使用literal值的sql语句.可以重新检查这部分sql语句看是否能使用绑定变量来代替literal值.

top parsing module/action
这部分信息显示了在抽样会话活动中当执行解析sql语句时占很高百分比的模块和操作

complete list of sql text
这部分信息显示了顶级sql语句的完整的文本内容

top pl/sql
这部分信息显示了在抽样会话活动中占很高百分比的pl/sql过程.

top java
这部分信息显示了在抽样会话活动中占很高百分比的java程序

top sessions
这部分信息描述了会话正在等待的一个特定等待事件.使用这部分信息来识别在抽样会话活动中占很高百分比的会话它们可能是造成短暂性能问题的原因.top sessions部分包含以下信息:

top sessions
这部分信息显示了在抽样会话活动中占很高百分比的等待会话

top blocking sessions
这部分信息显示了在抽样会话活动中占很高百分比的阻塞会话

top sessions running pqs
这部分信息显示了哪些在抽样会话活动中占很高百分比的正处于等待的并行查询

top objects/files/latches
这部分信息显示了通常最消耗数据库资源的信息括以下部分:
top db objects
这部分信息显示了在抽样会话活动中占所有引用对象很高百分比的数据库对象(比如表和索引)

top db files
这部分信息显示了在抽样会话活动中占访问量很高百分比的数据库文件

top latches
这部分信息显示了在抽样会话活动中占很高百分比的闩锁信息
闩锁是一种简单低级别串行化机制用来保护sga中的共享数据结构.比如闩锁保护当前访问数据库和缓冲区缓存中数据块结构的用户列表.当维护或查找这些结构时服务器或后台进程请求持有闩锁的时间是非常短暂的.闩锁的实现依赖于操作系统特别是一个进程等待获取一个闩锁多长时间.

activity over time
这一部分是ash报告信息最丰富的一部分.这部分信息对于长时间周期的ash报告来说因为在分析期间它提供了关于活动和工作负载概要深层次的详细信息.activity over time会被分成10个时段.每个时段的大小基于分析所持续的时间.第一个和最后一个时段是奇怪.所有内部时段是相等的大小它们可以相互比较.例如,如果分析时间持续10分钟那么所有的时段将会是每个一分钟.然后如果分析时间持续9分30秒,那么外部的时段可能是每个15秒内部的时段可能每个1分钟

特定时段中每个时段包含的信息如下:
列 描述
slot time(持续时间) 时段的持续时间
solt count 在时段中抽样会话的数量
event 在时段中顶级的三个等待事件
event count ash抽样等待的等待事件的数量
%event ash抽样等待的等待事件在整个分析期间所占的百分比

当比较内部时段时,通过识别异常的event count和slot count列执行一个倾斜分析.event count列的异常指示了在抽样会话中等待的等待事件数量增加了.slot count列的异常指示活动的会自豪感增加了,因为ash数据只从活动会话中进持抽样说明数据库的
负载增加了.通常来说当活动会话抽样的数理和与这些会话相关的等待事件增加时那么这个时段可能会造成短暂性能问题.

Memory Notification: Library Cache Object loaded into SGA Heap size 2098K exceeds notification threshold (2048K)

Memory Notification: Library Cache Object loaded into SGA
Heap size 2098K exceeds notification threshold (2048K)

出现原因:
These are warning messages that should not cause the program responsible for these errors to fail. They appear as a result of new event messaging mechanism and memory manager in 10g Release 2.
这都是警告信息不会因为这些错误信息造成应用程序执行失败.在oracle10gr2中是由于新的事件消息机制和内存管理才出现的.
The meaning is that the process is just spending a lot of time in finding free memory extents during an allocate as the memory may be heavily fragmented. Fragmentation in memory is impossible to eliminate completely, however, continued messages of large allocations in memory indicate there are tuning opportunities on the application.
这个消息意味着进程在内存呆能存在大量碎片的情况下在执行内存分配时花了很长的时间来寻找可用的内存空间.内存中的碎片不可能完全消除,然而在进行内存分配时持续出现这种消息说明可能是时候对应用程序进行优化了.
The messages do not imply that an ORA-4031 is about to happen.
这个消息并不意味着ora-4031错误会立刻出现.

解决方法:
In 10g we have a new undocumented parameter that sets the KGL heap size warning threshold. This parameter was not present in 10gR1. Warnings are written if heap size exceeds this threshold.
在oracle10g中有一个新的隐含参数来设置KGL堆大小警告阈值.这个参数在10gr1中没有.当堆大小超过阈值时就会写入警告信息.
Set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.
将_kgl_large_heap_warning_threshold设置为一个合理的较高的值或为0可以阻止这些警告信息.它的大小单位是byte.

If you want to set this to 8192 (8192 * 1024) and are using an spfile:
(logged in as “/ as sysdba”)

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

SQL> shutdown immediate

SQL> startup

If using an “old-style” init parameter,Edit the init parameter file and add

_kgl_large_heap_warning_threshold=8388608

NOTE: The default threshold in 10.2.0.1 is 2M. So these messages could show up frequently in some application environments.
注意:在10.2.0.1中这个缺省的阈值是2M.因此在有些程序环境中这些警告信息经常出现.
In 10.2.0.2, the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value.
在10.2.0.2中,这个缺省的阈值增加到50M.

获取数据库缓冲区缓存丢失统计数据

set lines 80;
set pages 999;
column mydate heading ‘Yr. Mo Dy Hr.’ format a16
column c1 heading “execs” format 9,999,999
column c2 heading “Cache Misses|While Executing” format 9,999,999
column c3 heading “Library Cache|Miss Ratio” format 999,99999

break on mydate skip 2;
select
to_char(sn.snap_time,’yyyy-mm-dd HH24′) mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) c3
from
stats$librarycache old,
stats$librarycache new,
stats$snapshot sn
where new.snap_id=sn.snap_id
and old.snap_id=new.snap_id-1
and old.namespace=new.namespace
group by to_char(sn.snap_time,’yyyy-mm-dd HH24′);

select
to_char(sn.begin_interval_time,’yyyy-mm-dd HH24′) mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) c3
from
dba_hist_librarycache old,
dba_hist_librarycache new,
dba_hist_snapshot sn
where new.snap_id=sn.snap_id
and old.snap_id=new.snap_id-1
and old.namespace=new.namespace
group by to_char(sn.begin_interval_time,’yyyy-mm-dd HH24′);

优化Shared Pool Latch与Library Cache Latch竞争

这本文章的目的是介绍解决oracle7到oracle11的共享池问题.如果你的系统出现以下任何问题:
对于library cache latch或latch:library cache的闩锁竞争
对于shared pool latch或latch:shared pool的闩锁竞争
高cpu解析时间
v$librarycache的高reloads次数
高版本游标
大量的解析调用
频繁的ora-04031错误

解决问题的步骤
什么是共享池
oracle在sga中的一个区域保留sql语句,包,对象信息和许多其它信息这个区域就叫作共享池.共享池由于一个复杂的缓存和堆管理器组合而成的,它有三个基本的问题要克服:
1.内存分配单元不是一个常量—共享池中的内存分配可以是几个字节到几千字节
2.当用户使用完后不是所有的内存都能释放(这种情况出现在传统的堆管理).共享池的目的是最大化的共享信息.在内存中的信息可能对另外的会话有用—oracle事先并不知道这些信息将来能不能被使用
3.这里没有磁盘page out,所以不象传统的缓存有一个文件备份存储.只会当信息从缓存中消失后当下次需要时进行重建.
基于上面的三点就可以知道管理共享池是一个复杂的工作.下面将介绍影响共享池性能的关键问题和与它相关的闩锁竞争.

Literal SQL
一个literal sql是在谓词中使用了literal值而没有使用绑定变量的sql语句.不同的literal值对于语句来说可能会有不同的执行计划.
例如:
SELECT * FROM emp WHERE ename=’CLARK’;
使用应用程序来调用可能是:
SELECT * FROM emp WHERE ename=:bind1;

例如:
select sysdate from dual;
虽然没有使用绑定变量但不会被认为是一个literal语句,这个语句是能被共享的.

例如:
SELECT version FROM app_version WHERE version>2.0;
如果相同的语句被用来检查应用程序的版本且literal值’2.0’总是相同的那么这个语句会被认为可以被共享.

硬解析
如果一个新调用的sql语句在共享池中不存在那么就要进行全面的解析.oracle会对这个语句从共享池中分配内存,检查语法和语义等等这称为硬解析对于cpu的消耗和latch获取的执行次数来说都是很能昂贵的.

软解析
如果一个会话发出的sql语句它已经在共享池中存在那么对于这个语句能使用一个已经存的版本这称为软解析.对于应用程序来说它已经要求解析这个语句了.

相同的语句
如果两个sql语句的意思相同但有些字符的格式不同oracle会认为这是不同的语句.例如下面是在单个会话中scott用户发出的语句:
SELECT ENAME from EMP;

SELECT ename from emp;
虽然两个语句实际上是相同的但是由于大小写的原因会被认为是不同的语句.例如E与e是不同的.

共享sql
如果两个会话发出相同的语句但是不一定能共享.例如scott用户有一个叫EMP的表并执行以下语句:
SELECT ENAME from EMP;
用户fred也有一个叫EMP的表并执行以下语句:
SELECT ENAME from EMP;
虽然语句的文本相同的但是EMP是来自不同用户的对象.因此对于相同的语句会有不同的游标版本.有许多信息要检查来判断两个语句是否是真的相同包括:
所有的对象名必需是相同的真实对象
发出语句的会话的optimizer goal要相同
任何绑定变量的类型和长度应该是相似的
每个语句的的国示语句支持环境必需相同

语句的版本
在共享sql中如果两个语句的语句文本相同但不能共享那么这些语句就被称作相同语句的版本.在解析期间如果oracle使用多个版本来匹配一个语句那么不得不检查每一个版本来看是否与某个特定的版本语句相同.因此高版本语句最好要通过以下方式来避免:
由客户来指定标准化的绑定变量长度
避免不同用户使用相同的语句
在oracle8.1中将_SQLEXEC_PROGRESSION_COST设置为0

library cache和shared pool latches
共享池闩锁(shared pool latches)是在共享池中分配和释放内存时来保护关键操作的
库缓存闩锁(library cache或oracle7.1中的library cache pin latch)是用来保护库缓存自身的操作

所有的闩锁都是潜在的竞争点.请求闩锁的次数会直接影响共享池中活动的数量,特别是解析操作.任何能够减少共享池中的闩锁请求和真实的活动数量的操作对于性能和可扩展性来说都是有好处的

literal sql与shared sql
literal sql
当语句引用的对象用完全的统计信息和在语句谓词中使用literal值时基于成本的优化器会工作的最好,例如:
SELECT distinct cust_ref FROM orders WHERE total_cost < 10000.0; 与 SELECT distinct cust_ref FROM orders WHERE total_cost < :bindA; 对于第一个语句如果已经收集了直方图信息那么基于成本的优化器会使用直方图信息来判断是对orders表使用全表扫描还是使用total_cost列上的索引进行扫描.对于第二个语句基于成本的优化器不知道小于":bindA"的记录占整个记录的百分比因为在判断 一个执行计划时绑定变量是没有值的例如":bindA"可能是0.0或者99999.9 在这两个语句的两种执行计划的响应时间之间会有数量级的差别.所以你如果想基于成本优化器选择最佳的执行计划最好使用literal sql语句.这是典型的决策支持系统它没有任何标准的语句(发出重复的语句)所以能共享的语句就很少.在解析时消耗的 cpu数量通常占执行语句所消耗cpu数量很小的百分比所以相比减少解析时间来说更重要的是给优化器更多的信息. shared sql 如果一个应用程序使用literal(unshared) sql那么这是非常限制可扩展性和吞吐量的.解析一个新语句在cpu请求和库缓存闩锁 和共享池闩锁方面都是很昂贵的.即使解析一个简单的sql语句可能也需要请求库缓存闩锁20或30次. 最好的方法是使用所有的sql语句被共享除非是很少或不频繁使用的sql语句,给基于成本的优化器更多的住处让其生成一个最佳的执行计划也是很重要的. 减少共享池的加载次数 解析一次/执行多次 到目前为止在OLTP系统中让应用程序对sql语句只解析一次并将游标打开当请求它时就执行.这样做的结果是对于每一个语句只在最初进行解析(可能是软解析也可能是硬解析).很明显有些语句是很少执行的因此对于这些语句保持打开游标会浪费资源. 注意一个会话只有(参数open_cursors)游标可用且保持游标为打开状态时才有可能增加并发打开游标的数量 在预编译程序中hold_cursor参数控制着游标是否保持持开状态而OCI开发者可以直接控制游标. 消除literal sql 如果一个程序你想消除所有的literal sql是不可能的但是在literal sql造成问题时还是要消除造成问题的这些literal sql语 句.通过查看v$sqlarea视图可以看到哪些literal语句是可以转换使用绑定变量.下面的语句查询在sga中有大量相似语句的sql: SELECT substr(sql_text,1,40) "SQL", count(*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5 GROUP BY substr(sql_text,1,40) HAVING count(*) > 30
ORDER BY 2
;

对于oracle10g使用以下查询语句:
SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE,
COUNT(*) cnt
FROM v$sqlarea
WHERE FORCE_MATCHING_SIGNATURE!=0
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 20
)
,
sq AS
(SELECT sql_text ,
FORCE_MATCHING_SIGNATURE,
row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM v$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE
FROM c
)
)
SELECT sq.sql_text ,
sq.FORCE_MATCHING_SIGNATURE,
c.cnt “unshared count”
FROM c,
sq
WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND sq.p =1
ORDER BY c.cnt DESC

如果上面的查询出来的sql造成了library cache latches的竞争那么这些语句可能会更进一步的产生更严重的竞争问题.

避免无效游标
有一些特定的操作会将游标的状态改变为invalidate.这些操作会直接修改与游标相关对象的上下文.这些操作比如对表或索引进行truncate,analyze或dbms_stats.gather_xxx操作,或者改变基础对象的授权.这些相关的游标仍然会保留在sqlarea中但是当它们下次被引用时,它们会被重新加载且重新完全解析,所以会影响整个性能.

下面的查询能够帮我们识别这些无效的游标:
SELECT SUBSTR(sql_text, 1, 40) “SQL”,
invalidations
FROM v$sqlarea
ORDER BY invalidations DESC;

cursor_sharing参数(8.1.6及以后版本)
参数cursor_sharing是在oracle8.1.6中引入的.
在这个版本中使用它要谨慎.如果这个参数被设置为force那么literal值将会可能由系统生成的绑定变量来替换.对于多个相似的 且只有literal值不同的语句将会允许语句共享尽管应用程序提供的sql是使用的literal值.这个参数是动态参数可以在实例或会 话级别进行修改.
ALTER SESSION SET cursor_sharing = FORCE;

ALTER SYSTEM SET cursor_sharing = FORCE;
或者在init.ora文件中进行设置

注意:当这个以数设置为force会用系统生成的绑定变量来替换literal值,这时基于成本的优化器可能会选择与原先不同的执行计划因为在优化器计算最佳执行计划时没有了literal值.

在oralce9i中,cursor_sharing可以设置为similar.similar用于语句可能在某些literal值不同的情况下,这会让这些语句允许被 共享除非literal值影响了语句的意思或者影响了被优化的执行计划的并行度.这增强了这个参数的可用性不象设置为force时通 常会造一个不同的不好的执行计划.当cursor_sharing设置为similar时,oracle会判断哪个literal使用绑定变量来替换是安全的这也会造成一些语句因为为了提供一个更好的执行时而不被共享.

cursor_sharing参数在oracle12c中会被丢弃.

session_cached_cursor参数
参数session_cached_cursor是一个数字参数它能在实例或会话级别使用下面的语句来进行修改:
ALTER SYSTEM SET session_cached_cursors = NNN;

ALTER SESSION SET session_cached_cursors = NNN;
这个NNN决定在你的会话中能缓存多少个游标
每当一个语句被解析时oracle首先会检查你的私有会话缓存中有没有这个语句,如果对于这个语句存在一个共享的版本能被使用,
对于频繁解析的语句与软件解析或硬解析相比会使用更少的cpu和更少的闩锁请求次数从而提供了一个快捷访问.

为了能将相同的语句缓存在会话缓存中这个语句必须要使用相同的游标解析3次然后这个共享游标的一个指针会被增加到你的会话缓存中.如要所有的会话缓存游标都在被使用那么最近最少使用的游标会被丢弃.

如果你没有设置这个参数那么建议将给它设置一个初始值50.在bstat/estat报告中的统计部分有一个’session cursor cache hits’信息显示了会话缓存游标带来的好处.这个会话缓存游标的大小可以根据需要增加或减少.

cursor_space_for_time参数
cursor_space_for_time参数在10.2.0.5和11.1.0.7中被丢弃
参数cursor_space_for_time控制着部分游标是否在一个语句的不同执行计划之间保持pinned.如果所有的失败了它能在这些共享 语句被频繁使用时或者在有显著的pinning/unpinning游标时(查看v$latch_misses视图如果大部分的latch等待是由于”kglpnc:child”和”kglupc:child”,这是由于对游标进行pinning/unpinning产生的)能带来一些好处.

必须确保共享池对于工作负载来说是足够大的否则性能会受到影响且会触发ora-4031错误.
如果你设置此参数要注意:
如果shared_pool对于工作负载来说设置的太小那么可能会经常触发ora-4031错误.
如果你的程序有任何的游标泄漏那么泄漏的游标在经过一段时间的操作后会浪费大量的内存对性能产生影响.
将这个参数设置为true时会出现以下的已知的问题:
Bug:770924 (Fixed 8061 and 8160) ORA-600 [17302] may occur
Bug:897615 (Fixed 8061 and 8160) Garbage Explain Plan over DBLINK
Bug:1279398 (Fixed 8162 and 8170) ORA-600 [17182] from ALTER SESSIONSET NLS…

CLOSE_CACHED_OPEN_CURSORS参数
这个参数在oracle8i中已经废弃了.
参数close_cached_open_cursors控制着当一个事务提交时plsql游标是否关闭.缺省值是false这意味着当事务提交时plsql游标 保持打开这能减少硬解析.如果这个参数设置为true那么这将增加当sql不使用时从共享池中被清除的机会.

SHARED_POOL_RESERVED_SIZE参数
这个参数是在oracle7.1.5引入的对保留共享池大内存分配提供了一种方法.这个共享池保留区来自共享池本身.

从实用的角度shared_pool_reserved_size的大小一般设置为shared_pool_size的10%除非共享池很大或shared_pool_reserved_min_alloc相比于缺省值设置的太小:
如果共享池非常大那么10%可能会浪费大量的内存而实际上只有几MB就够了
如果shared_pool_reserved_min_alloc已经很小那么许多空间请求可能从共享池部分能得到满足那么10%的大小就小了.

可以很容易的监控共享池保留区的使用情况查询v$shared_pool_reserved视图中的free_sapce列.

shared_pool_reserved_min_alloc参数
在oracle8i中这个参数是隐含参数
shared_pool_reserved_min_alloc参数一般使用其缺省值,尽管在特定情况下4100或4200字节可能会帮助解决共享池高负载时的一些竞争.

shared_pool_size参数
参数shared_pool_size控制着共享池本身的大小.共享池的大小会影响性能.如果共享池太小那么它会将一些共享信息从共享池中 清除而后续的请求就要重新加载.如果有大量的literal sql且共享池太大那么长时间的操作会在内部内存的可用列表中创建一些 小的内存块这会导致共享池闩锁会被持有很长时间进而影响性能.在这种情况下小的共享池比大的共享池可能会运行的更好.
注意:共享池它本身不是很大因此会有大量的分页或交换发生那么性能会呈数量级的降低.

_SQLEXEC_PROGRESSION_COST参数
这是一个隐含参数在oracle8.1.5中引入.这个参数的缺省设置会造成一些sql共享的问题,将这个参数设置为0可以避免这个问题 但是又会在共享池中产生多版本语句.

注意如果将这个参数设置为0的另一个问题是在v$session_longops视图中将不会记录长时间执行的查询.

预编译程序的hold_cursor和release_cursor选项
当使用oracle预编译程序共享池的行为可以通过使用参数release_cursor和hold_cursor来进行改变.这些参数将会判断库缓存中游标的状态和会话缓存中一旦执行完成后游标的状态.

在共享池中pinning cursors
dbms_shared_pool.keep
这个过程(它的定义在rdbms/admin目录下的dbmspool.sql脚本中)能被用来将保留对象共享池中.dbms_shared_pool.keep允许保留包,过程,函数,触发器和序列.

一般来说它通常需要标记哪些频繁使用的包这样让它们总是被保留在共享池中.对应该应该在实例启动后不久被保留在共享池中因为数据库在执行重启之后不会自动执行这个操作.

清空共享池
在使用大量literal SQL的系统中,shared pool随时间推移会产生大量碎片进而导致并发能力的下降.Flushing shared pool能 够使得很多小块碎片合并,所以经常能够在一段时间内恢复系统的性能.清空之后可能也会产生短暂的性能下降,因为这个操作同时也会把没造成shared pool碎片的共享SQL也清除了.清空shared pool的命令是:
ALTER SYSTEM FLUSH SHARED_POOL;
注意:如果显式的使用以上命令,即使是用 DBMS_SHARED_POOL.KEEP而被保留的那些对象可能也会被释放掉,包括它们占用的内存.如果是隐式的flush(由于shared pool上的内存压力)这个时候kept”的对象不会被释放.

注意:如果sequence使用了cache选项,冲刷shared pool有可能会使sequence在其范围内产生不连续的记录.使用 DBMS_SHARED_POOL.KEEP(‘sequence_name’,’Q’)来保持sequence会防止这种不连续的情况发生.

DBMS_SHARED_POOL.PURGE

也可以不刷新整个shared pool,而只清空其中的单个对象.

使用 V$ 视图 (V$SQL 和 V$SQLAREA)
注意有一些V$视图需要获取相关的latch来返回查询的数据.用来展示library cache和SQL area的视图就是值得注意的.所以我们建议有选择性的运行那些需要访问这种类型视图的语句.特别需要指出的是,查询V$SQLAREA会在library cache latch上产生大量的负载,所以一般可以使用对latch访问比较少的v$sql做替代——这是因为V$SQLAREA的输出是基于shared pool中所有语句的GROUP BY操作,而V$SQL没有用GROUP BY操作.

MTS, Shared Server 和 XA

由于多线程服务器(MTS)的User Global Area (UGA)是存放在shared pool中的,所以会增加shared pool的负载.在Oracle7上的 XA session也会产生同样的问题,因为他们的UGA也是在shared pool里面(在Oracle8/8i开始XA session不再把UGA放到shared pool中).在Oracle8中Large Pool可以被用来减少MTS对shared pool活动的影响——但是,Large Pool中的内存分配仍然会使 用”shared pool latch”.

使用dedicate connections(专有连接)替代MTS可以使UGA在进程私有内存中分配而不是shared pool.私有内存分配不会使用”shared pool latch”,所以在有些情况下从MTS切换到专有连接可以帮助减少竞争.

在Oracle9i中,MTS被改名为”Shared Server”.但是对于shared pool产生影响的行为从根本上说还是一样的.

使用SQL查看Shared Pool问题
这里展示了一些可以用来帮助找到shared pool中的潜在问题的SQL语句.这些语句的输出最好spool到一个文件中
注意:这些语句可能会使latch竞争加剧
查找literal SQL
SELECT substr(sql_text,1,40) “SQL”,
count(*) ,
sum(executions) “TotExecs”
FROM v$sqlarea
WHERE executions < 5 GROUP BY substr(sql_text,1,40) HAVING count(*) > 30
ORDER BY 2
;
这个语句有助于找到那些经常被使用的literal SQL

检索Library Cache hit ratio
SELECT SUM(PINS) “EXECUTIONS”,
SUM(RELOADS) “CACHE MISSES WHILE EXECUTING”,
SUM(RELOADS)/ SUM(PINS) “MISSES/EXECUTIONS”
FROM V$LIBRARYCACHE;
如果misses/executions高于1%的话,则需要尝试减少library cache miss的发生.

检查 hash chain 的长度:
SELECT hash_value, count(*)
FROM v$sqlarea
GROUP BY hash_value
HAVING count(*) > 5
;
这个语句正常应该返回0行.如果有任何HASH_VALUES存在高的count(两位数)的话,你需要查看是否是bug的影响或者是 literal SQL使用了不正常的形式.建议进一步列出所有有相同HASH_VALUE的语句.例如:
SELECT sql_text FROM v$sqlarea WHERE hash_value= ;
如果这些语句看起来一样,则查询V$SQLTEXT去找完整的语句.有可能不同的SQL文本会映射到相同的hash值,比如:在7.3中, 如果一个值在语句中出现2次而且中间正好间隔32个字节的话,这两个语句会映射出相同的hash值.

检查高版本:
SELECT address, hash_value,
version_count ,
users_opening ,
users_executing,
substr(sql_text,1,40) “SQL”
FROM v$sqlarea
WHERE version_count > 10
;
一个语句的不同”版本”是当语句的字符完全一致但是需要访问的对象或者绑定变量不一致等等造成的.在Oracle8i的不同版本中 因为进度监控的问题也会产生高版本可以把_SQLEXEC_PROGRESSION_COST 设成’0’来禁止进度监控产生高版本
找到占用shared pool 内存多的语句:
SELECT substr(sql_text,1,40) “Stmt”, count(*),
sum(sharable_mem) “Mem”,
sum(users_opening) “Open”,
sum(executions) “Exec”
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) > &MEMSIZE
;
这里MEMSIZE取值为shared pool大小的10%,单位是byte.这个语句可以查出占用shared pool很大内存的那些SQL,这些SQL可 以是相似的literal语句或者是一个语句的不同版本.

导致shared pool 内存’aged’ out的内存分配
SELECT *
FROM x$ksmlru
WHERE ksmlrnum>0
;
注意: 因为这个查询在返回不超过10行记录后就会消除X$KSMLRU的内容,所以请用SPOOL保存输出的内容.X$KSMLRU表显示从上 一次查询该表开始,哪些内存分配操作导致了最多的内存块被清除出shared pool.有些时候,这会有助于找到那些持续的请求 分配空间的session或者语句.如果一个系统表现很好而且共享SQL使用得也不错,但是偶尔会变慢,这个语句可以帮助找到原因

如何诊断与IO相关的性能问题

论断与IO相关的性能问题的方法有:
statspack或awr报告中在top 5等待事件中与IO相关的等待事件,对数据库做sql跟踪显示主要的限制是IO等待事件,操作系统工具显示了很高的利用率或存储数据文件的磁盘正在饱和的使用

诊断IO问题的步骤
在数据库性能调整中一个关键的活动就是响应时间的分析,找出在数据库中时间花在哪了.时间对于性能调整是一个最重要的属性.用户是通过他们运行业务所经历的时间来进行感知的.

oracle数据库的响应时间使用以下的计算公式:
Response Time = Service Time + Wait Time

‘Service Time’就是用统计信息中的’CPU used by this session’来计算’Wait Time’就是等待事件的总时间

性能调优访问就是使用象awr和statspakc一样的工具来评估各种组件对整个响应时间的影响且直接对消耗时间最大的组件进行调整.

确定真正意义的IO等待事件
许多工具包括awr和statspack列出了最有效的等待事件.直到oracle9ir2 statspack包含一个叫”top 5 wait events”部分.
当面对所罗列的等待事件有时间很容易首先处理这些等待事件相关的问题而忘记了它们在整个响应时间中的影响.

在这种情况下’service time’即cpu使用率比’wait time’更有效,很有可能调查等待事件不会对响应时间有影响.因此总是应该拿top等待事件中的各等待事件所用的时间来与’cpu used by this session’的值进行比较并直接对最消耗时间的事件进行调整.

从oracle9ir2开始,”top 5 wait events’部分被重命名为”top 5 timed events” “service time’即”cpu used by this session’称作’cpu time’这意味着现在很容易精确地测量等待事件在整个响应时间中的影响并且能正确的对其进行调整.

误解等待事件的影响
下面的两个例子当在调查数据库性能问题时最重要的是查看’wait time’和’servie time’

例子1:在oracle9ir2以前的statspack
下面是statspack报告中”top 5 wait events’信息两个快照之间的间隔是46分钟
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
——————————————– ———— ———— ——-
direct path read 4,232 10,827 52.01
db file scattered read 6,105 6,264 30.09
direct path write 1,992 3,268 15.70
control file parallel write 893 198 .95
db file parallel write 40 131 .63
————————————————————-
基于上面的信息我们可能会立即查看造成’direct path read’和’db file scattered read’等待事件并试图对它们进行调整
.但是这种做法没有考虑’service time’.

下面的’service time’信息来自同一个statspack报告:
Statistic Total per Second per Trans
——————————— —————- ———— ————
CPU used by this session 358,806 130.5 12,372.6

下面对这些数字进行一些简单的计算:
‘Wait Time’ = 10,827/ 0.5201 = 20,817 cs
‘Service Time’ = 358,806 cs
‘Response Time’ = 358,806 + 20,817 = 379,623 cs

所以计算后各个组件占所有响应时间的百分比为:
CPU time = 94.52%
direct path read = 2.85%
db file scattered read = 1.65%
direct path write = 0.86%
control file parallel write = 0.05%
db file parallel write = 0.03%

现在很明显IO相关的等待事件不是真正影响整个响应时间(所有的IO等待事件的时间只占整个响应时间的6%)的原因.后续的调整应该直接对服务时间组件即CPU消耗.

例子2:在oracle10gr2以后的awr报告
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
—————————— ———— ———– —— —— ———-
DB CPU 33,615 82.0
db file sequential read 3,101,013 7,359 2 18.0 User I/O
log file sync 472,958 484 1 1.2 Commit
read by other session 46,134 291 6 .7 User I/O
db file parallel read 91,982 257 3 .6 User I/O

在awr中非常容易看出cpu在整个响应时间中占了很大一部分,因为cpu组件已经包含在’top 5 timed foreground events’中
.在上面的信息中我们可以看到等待事件的总时间占整个响应时间不到20%因此后续的调整应该直接对服务时间组件即cpu消耗进行.

一般处理IO问题的方法
在使用statspack或awr分析数据库响应时间后确定性能是由IO相关的等待事件所造成的,那么对于IO问题可能有以下解决方法,有些方法不受限于特定的等待事件,下面将解释说明每一种方法的概念和基本原理.

通过调整sql来减少数据库的IO请求:
一个数据库没有用户sql它将生成极少或没有IO.所有的IO最终都是通过数据库直接或间接的执行sql语句所产生的.这意味着可以通过控制单个sql语句的IO生成量来限制IO请求.这可以通过调整sql语句的执行计划来减少IO操作.通常的情况是数据库中只有少许的sql语句没有使用最佳的执行计划生成了太多的不必要的物理IO影响了数据库的整个性能.从oracle10g开始,addm可能自动识别对性能影响最大的sql语句然后sql调整指导可对其进行自动调整来减少对IO的消耗

通过调整实例参数来减少数据库的IO请求:
1.使用内存缓存来限制IO
通过使用较大的内存缓存象buffer cache,log buffer,各种排序区来限制IO请求的数量.增加buffer cache到一个合适的大小让
数据库进程执行更多的缓存访问(逻辑IO)来代替物理磁盘的访问(物理IO).在内存中使用大的排序区,可能会减少排序操作不得不使用临时表空间的可能性尽让排序在内存中完成.

2.调整多块IO的大小
单个多块IO操作的大小可以通过实例参数来控制.当有大量IO操作要执行时多块IO执行的速度要比更多的小IO操作要快.例如,传输100M的数据执行每次传输1M数据的操作100次要比执行每次传输100KB数据的操作1000次或每次传输10KB数据的操作10000次要快.在这个限制达到后,不同的大小将不再重要:传输1GB的数据执行100次每次传输10MB(如果操作系统允许的最大IO大 小)与一次传输1GB的数据几乎有同样的效率,这是因为IO服务请求所花的时间主要包括两部分:
IO setup time:对于不同的IO大小所花的时间基本上是恒定的且对于小的IO大小它的值趋于总的服务时间
IO transfer time:随着IO的大小而增加且对于小的IO大小通常小于IO setup time
可能通过db_file_multiblock_read_count参数来调整多块IO的大小

在操作系统层优化IO
这涉及到IO能力的使用比如象异步IO或使用带有高级功能的直接IO(跨过操作系统文件缓存)的文件系统.另一个可能的做法是提高每次传输IO最大大小

通过使用oracle asm(自动存储管理)来平衡数据库的IO
在oracle10g中asm被引入.它是一个文件系统且卷管理器被内建在数据库内核中.它能以并行方式跨过所有可用的磁盘设备来自动 进行负载平衡来阻止热点的产生和最大化性能,即使是使用快速变化的数据模式.它能阻止碎片因为这里从来不会为了回收空间来重新放置数据,数据将是平衡且条带化在所有的磁盘.

使用条带化,raid,san或nas来平衡数据库IO
这种方法依赖于存储技术象striping,raid,存储局域网(SAN)和网络连接存储(NAS),当在存储硬件上还有可用的磁盘吞吐量时来自动跨多个可用的物理磁盘来自动平等数据库IO来避免磁盘竞争和IO瓶颈.

通过手动将数据文件跨不同文件系统,控制器和物理设备来存储来重新分配数据库IO
这个方法用于缺少高级存储技术的情况下,当仍有磁盘吞吐量时再次分配数据库IO不使用单个磁盘或控制器达到饱和状态.它很难做到准确无误因此与之前的方法相比很少使用.

最重要的是记住有一些IO将总是存在于大多数数据库中的.在上述方法都已经考虑之后如果性能仍不能满足你可能考虑:
通过移走旧的数据来减少当前数据库的数据量
使用更多或更快的硬件

数据文件IO相关的等待事件
‘df file sequential read’
这是一个最常见的IO相关的等待事件,在大多数情况下是单块读取索引块或通过索引来访问表数据块但也可看作是对数据文件头块的读取.在早期的oracle版本中也可能是从磁盘中的排序段执行多块读在缓冲区缓存中组成连顺的缓存.

如果这个等待事件占了等待时间中的一大部分那么有以下方法可以进行调整:
从statspack或awr报告中的”SQL ordered by Reads”或v$sql视图中找出物理读取的top sql语句,然后对它们进行调整以减少它们的IO请求
如果索引范围扫描被调用,如果索引是非选择性的那么可能与必须要访问的数据块相比会有更多的数据块被访问.

如果索引分布很分散,那么我们将不得不访问更多的数据块因为每一个数据块中的索引数据很少,在这种情况下重建索引让索引数据存放在少理数据块中可以提高性能.

如果被使用的索引有大量的集族因子,那么为了得到每一个索引块会有更多的数据块要求被访问,可以按特定索引列对数据进行排序并按排序的结果重新创建该表来减小集族因子.例如一个表有a,b,c,d四个列且创建一个索引(b,d),那么我们可以使用
CREATE TABLE new AS SELECT * FROM old ORDER BY b,d语句来重建该表.

使用分区让每一个sql语句使用分区修剪功能来减少要被访问的索引数据块和表数据块.

如果没有执行计划很差的特定sql语句执行不必要的物理IO操作的话那么可能出现了以下情况:
特定数据文件的IO由于存储这些数据文件的磁盘上有过度的活动造成了服务缓慢.在这种情况下可以查看statspack或awr报告中的”File I/O Statistics”部分或v$filestat视图来找到哪些热点磁盘并通过手动移到数据文件到其它的存储上或通过使用条带 化,raid和其它自动执行IO负载平衡的技术来分散IO

从oracle9.2开始可以从v$segment_statistics视图中使用新的段统计信息来找到是哪一个段(表或索引)执行了最多的物理读取.
在找出具体的段之后可以对索引,表进行重建或分区来减少IO请求,如果使用statspack来生成”segment statistics”报告需要修 改收集统计的级别为7.
如果没有使用次优执行计划的sql且从所有磁盘执行请求的时间相似IO分布均匀那么设置一个大缓冲区缓存可能有帮助:
在oracle8i中可以通过逐步增加db_block_buffers的值来检测缓冲区缓存的撞击率直到不能再提高缓冲区缓存的撞击率为止.

在oracle9i中我们可以使用缓冲区缓存指导功能来调整缓冲区缓存的大小

在oracle10g中使用自动共享内存管理(asmm)来让数据库自动根据最近的工作负载来设置最佳的缓冲区缓存的大小

对于热点段可以使用多个缓冲池,将哪些热点索引和表放置在保留缓冲池中.

最后你可以考虑减少最频繁访问段中的数据(通过将旧的不需要的数据从数据库中移出)或将这些访问频繁的段移动到新的快速的磁盘上来减少它们IO请求的时间

‘db file scattered read’
这也是一个常见的等待事件,当数据库从执行多块读从磁盘上将数据块读取到缓冲区缓存中不连续的缓存中.这样的读一次能够读取的数据块个数是由db_file_multiblock_read_count参数值所决定的.这样的情况通常是发生在全表扫描和快速完全索引扫描.

如果这个等待事件占了总等待时间中的一大部分那么有以下方法可以进行调整:
找出哪个sql语句执行了全表扫描或快速完全索引扫描并对它们进行调整来确保这些扫描是必需的且不会造成使用一个次优的执行计划.从oracle9i开始新的v$sql_plan视图能帮助找出这些语句.

对于全表扫描:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation=’TABLE ACCESS’
and p.options=’FULL’
order by p.hash_value, t.piece;

对于快速完全索引扫描:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation=’INDEX’
and p.options=’FULL SCAN’
order by p.hash_value, t.piece;

在oracle8i中可以通过查询v$session_event视图来找出执行多块读取这个等待事件且对它们进行sql跟踪,另外可以查看物理读 取的top sql语句来查看是否它们的执行计划中有没有包含全表扫描或快速完全索引扫描.

在这种情况下当最佳执行计划执行多块读时可以通过设置实例参数db_file_multiblock_read_count来调整多块读的IO大小.因此
db_block_size x db_file_multiblock_read_count=系统的最大IO大小

正如前面所说的,从oracle10gr2开始db_file_multiblock_read_count初始化参数现在是自动调整当这个参数没有被显式设置时 使用缺省值.这个缺省值与能有效执行的最大IO大小有关.这个参数值依赖于平台且对于大多数平台的最大IO大小是1MB.因为这个参数是以数据块为单位的,它能设置成一个等于最大IO大小的值(它的值可以是有效执行最大IO大小的值除以标准块大小)

当使用全表扫描和快速完全索引扫描读取数据块时会将这些数据块放在缓冲区缓存替换列表中的最近最少使用端,有时使用多个缓冲区会有帮助象将段放在保留池中.

当分区修剪能在查询中限制扫描段分区的子集时分区也能被用来减少扫描数据的数量.

最后你可以考虑减少最频繁访问段中的数据(通过将旧的不需要的数据从数据库中移出)或将这些访问频繁的段移动到新的快速的磁盘上来减少它们IO请求的时间

‘db file parallel read’
这个等待事件出现在当oracle以并行读取从多个数据文件中读取数据块到不连续的缓存池时.在恢复操作或当执行缓存预取作为一种优化手段来代替执行多次单块读取是会发生.

如果这个等待事件占了总等待时间中的一大部分,关这个等待事件的优化方法可参考’db file sequential read’事件的优化方法

direct path reads and writes
‘direct path read’
‘direct path write’
‘direct path read(lob)’
‘direct path write(lob)’
这些等待事件当在磁盘和进程pga内存之间执行特定类型的多块IO时会发生.因此跳过了缓冲区缓存.IO可以被同步和异步执行.

它们会在以下情况下出现:
排序IO:当内存排序区已经筋疲力尽且正在使用临时表空间来执行排序操作时.
并行执行(查询和DML)
预取操作(缓冲预取)
直接路径加载操作
LOB段的IO(它们不会被缓存在缓冲区缓存中)

由于这些等待事件的等待时间都被记录(它不检测试执行IO的时间),它们会出现在statspack报告中的”top 5 wait/timed events”中但不能用来评估真实的影响.

调整方法:
当支持异步IO时尽可能的使用异步IO
在oracle8i中最小的IO请求数是通过设置db_file_direct_io_count参数来设置的因此
db_block_size x db_file_direct_io_count=系统的最大IO大小.

在oracle8i中这个缺省值是64个数据块.

在oracle9i中,使用bytes为单位的_db_file_direct_io_count来替换.缺省值是1MB但如果系统的max_io_size较小的话会降低这个值.

调整内存排序区来最小化磁盘IO排序操作,在oracle9i及以后的版本中使用自动sql执行内存管理,在8i中要调整各种排序区的大小.

对于lob段,将它们作为操作系统文件存储在文件系统上,缓冲区缓存能提供一些内存缓存.

通过查询v$session_event来识别执行直接IO的会话或通过v$sesstat来识别统计信息:
‘physical reads direct’,’physical reads direct(lob)’,’physical writes direct’,’physical writes direct(lob)’
并调整这些sql语句.

通过使用v$filestat或statspack或awr报告中的”file io statistics”部分来识别存储数据文件的磁盘是否有瓶颈并将其移到其它磁盘上.

控制文件相关的IO等待事件
这些等待事件是在对控制文件的一个或所有副本执行IO时出现,控制文件的访问频率是由日志文件切换和检查点来控制的.因此它只能通过间接地调整这些活动才能受到影响.
‘control file parallel write’
这个等待事件发生在服务器进程正在更新所有控制文件副本时会出现.如果这个等待事件很严重,检查控制文件所有副本的IO路径(控制器,物理磁盘)的瓶颈.可能的解决方法:
减少控制文件的数量来最小化确保在同一时间不会丢失所有控制文件副本.
在你的平台支持异步IO的情况下使用异步IO
移动控制文件副本到很少达到饱和状态的存储中

‘control file sequential read’和’control file single write’
这些等待事件在对单个控制文件执行驶IO时可能会出现.如果这些等待事件很严重找出这些等待事件是出现在哪些控制文件副本上并查看它们的确良IO路径是否已经达到饱和.

下面的查询可以用来找出哪个控制文件正被访问.当出现这些等待事件时可以运行:
select P1 from V$SESSION where EVENT like ‘control file%’ and STATE=’WAITING’;

select P1 from V$SESSION_WAIT where EVENT like ‘control file%’ and STATE=’WAITING’;
可能的解决方法:
移动有问题的控制文件副本到很少达到饱和状态的存储中
在你的平台支持异步IO的情况下使用异步IO

重做日志相关的IO等待事件
这里有许多的等待事件发生在重做日志活动期间且它们大多数都是与IO相关的.它们中最重要的两个是’log file sync’和
‘log file parallel write’.oracle前台进程等待’log file sync’而lgwr进行等待’log file parallel write’.

虽然在”top 5 wait/timed events”中经常看到’log file sync’等待事件,为了理解它们首先来看’log file parallel write’:

‘log file parallel write’
当lgwr后台进程从内存日志缓存中复制重做条目到磁盘上的当前重做日志组的成员日志文件中时会等待这个事件.如果支持异步IO的话,异步IO被用来保证以并行方式进写操作否则将会按顺序来对重做日志组中的成员日志文件进行写操作.

然而在这个等待完成之前lgwr进程不得不等到所有成员日志文件的所有IO操作完成.因此因为这个原因IO子系统的写入成员日志文件的速度决定了这个等待的时间长短.

为了减少这个等待事件的等待时间一种文学就是通过数据库来减少生成的重做日志的数量
利用unrecoverable/nologging选项
在保证在同一时间不会丢失所有重做日志成员的前提下减少重做日志组的成员
不要让表空间处于备份模式下超过其必要的时间
使用最小级别的supplemental logging来完成你所要请求完成的功能.例如logminer,logical standby或streams

另一种方法就是调整IO本身:
在存储上放置重做日志组成员因此对于每个成员并行写不会产生竞争
对于重做日志文件不要使用raid-5
对于重做日志文件使用裸设备
对于重做日志文件使用快速磁盘
如果启用归档请单独设置重做存储空间因此这样写当前重做日志组的成员时不会与归档进程读取当前组的成员产生竞争.

‘log file sync’
这个等待事件发生在oracle前台进程中当他们发出一个commit或rollback操作时正等待这个等待事件的应该完成部分,因为这个 等待事件包含了lgwr进程对于这个会话事务从重做日志缓存中复制重做条目到磁盘.所以前台进程正等待’log file sync’而lgwr 进程在这个时间正等待’log file parallel write’

理解是什么延迟’log file sync’是关键是比较’log file sync’和’log file parallel write’的平均等待时间:
如果它们平均等待时间几乎相同,那么重做日志IO是造成这个等待的主要原因
如果’log file parallel wirte’的平均等待时间非常小,那么造成这个等待的主要原因是当发出commit或rollback命令时重做日志机制的其它部分(与IO不相关),有时在重做日志闩锁上存在着闩锁竞争,可以通过’latch free’或’lgwr wait for redo copy’ 等待事件来证实这一点.

‘log file sequential read’和’log file single write’
这两个等待事件是与IO相关的如果在重做日志上存在着IO竞争那么它们会与’log file parallel write’一起出现

‘log file switch(checkpoint incomplete)’ 这个等待事件当检查点活动不能够迅速发生时会出现

‘log switch/archive’ and ‘log file switch(archiving needed)’
这些等待事件当归档启用时归档不能快速完成时会出现
调整这些等待事件的方法与前面所描述的相似.