ORACLE性能调整

多的时侯，做Oracle DBA的我们，当应用管理员向我们通告现在应用很慢、数据库很慢的时侯，我们到数据库时做几个示例的Select也发现同样的问题时，有些时侯我们会无从下手，因为我们认为数据库的各种命种率都是满足Oracle文档的建议。实际上如今的优化己经向优化等待(waits)转型了，实际中性能优化最根本的出现点也都集中在IO，这是影响性能最主要的方面，由系统中的等待去发现Oracle库中的不足、操作系统某些资源利用的不合理是一个比较好的办法，下面把我的一点实践经验与大家分享一下，本文测重于Unix环境。

一、通过操作系统的一些工具检查系统的状态，比如CPU、内存、交换、磁盘的利用率，根据经验或与系统正常时的状态相比对，有时系统表面上看起来看空闲这也可能不是一个正常的状态，因为cpu可能正等待IO的完成。除此之外我们还应观注那些占用系统资源(cpu、内存)的进程。

1、如何检查操作系统是否存在IO的问题？使用的工具有sar,这是一个比较通用的工具。

Rp1#Sar -u 2 10

即每隔2秒检察一次，共执行20次，当然这些都由你决定了。

示例返回：

HP-UX hpn2 B.11.00 U 9000/800 08/05/03

18:26:32 %usr %sys %wio %idle

18:26:34 80 9 12 0

18:26:36 78 11 11 0

18:26:38 78 9 13 1

18:26:40 81 10 9 1

18:26:42 75 10 14 0

18:26:44 76 8 15 0

18:26:46 80 9 10 1

18:26:48 78 11 11 0

18:26:50 79 10 10 0

18:26:52 81 10 9 0

Average 79 10 11 0

其中的%usr指的是用户进程使用的cpu资源的百分比，%sys指的是系统资源使用cpu资源的百分比，%wio指的是等待io完成的百分比，这是值得我们观注的一项，%idle即空闲的百分比。如果wio列的值很大，如在35%以上，说明你的系统的IO存在瓶颈，你的CPU花费了很大的时间去等待IO的完成。Idle很小说明系统CPU很忙。像我的这个示例，可以看到wio平均值为11说明io没什么特别的问题，而我的idle值为零，说明我的cpu已经满负荷运行了。

当你的系统存在IO的问题，可以从以下几个方面解决

＊联系相应的操作系统的技术支持对这方面进行优化，比如hp-ux在划定卷组时的条带化等方面。

＊查找Oracle中不合理的sql语句，对其进行优化

＊对Oracle中访问量频繁的表除合理建索引外，再就是把这些表分表空间存放以免访问上产生热点，再有就是对表合理分区。

2、关注一下内存。

常用的工具便是vmstat，对于hp-unix来说可以用glance,Aix来说可以用topas,当你发现vmstat中pi列非零，memory中的free列的值很小，glance,topas中内存的利用率多于80%时，这时说明你的内存方面应该调节一下了，方法大体有以下几项。

＊划给Oracle使用的内存不要超过系统内存的1/2,一般保在系统内存的40%为益。

为系统增加内存

＊如果你的连接特别多，可以使用MTS的方式

＊打全补丁，防止内存漏洞。

3、如何找到点用系用资源特别大的Oracle的session及其执行的语句。

Hp-unix可以用glance,top

IBM AIX可以用topas

此外可以使用ps的命令。

通过这些程序我们可以找到点用系统资源特别大的这些进程的进程号，我们就可以通过以下的sql语句发现这个pid正在执行哪个sql，这个sql最好在pl/sql developer,toad等软件中执行, 把<>中的spid换成你的spid就可以了。

SELECT a.username,

a.machine,

a.program,

a.sid,

a.serial#,

a.status,

c.piece,

c.sql_text

FROM v$session a,

v$process b,

v$sqltext c

WHERE b.spid=

AND b.addr=a.paddr

AND a.sql_address=c.address(+)

ORDER BY c.piece

我们就可以把得到的这个sql分析一下，看一下它的执行计划是否走索引，对其优化避免全表扫描，以减少IO等待，从而加快语句的执行速度。

提示：我在做优化sql时，经常碰到使用in的语句，这时我们一定要用exists把它给换掉，因为Oracle在处理In时是按Or的方式做的，即使使用了索引也会很慢。

比如：

SELECT col1,col2,col3 FROM table1 a

WHERE a.col1 not in (SELECT col1 FROM table2)

可以换成：

SELECT col1,col2,col3 FROM table1 a

WHERE not exists

(SELECT 'x' FROM table2 b

WHERE a.col1=b.col1)

4、另一个有用的脚本：查找前十条性能差的sql.

SELECT * FROM

(
SELECT PARSING_USER_ID

EXECUTIONS,

SORTS,

COMMAND_TYPE,

DISK_READS,

sql_text

FROM v$sqlarea

ORDER BY disk_reads DESC

)

WHERE ROWNUM<10 ;

二、迅速发现Oracle Server的性能问题的成因，我们可以求助于v$session_wait这个视图，看系统的这些session在等什么，使用了多少的IO。以下是我提供的参考脚本：

脚本说明：查看占io较大的正在运行的session

SELECT se.sid,

se.serial#,

pr.SPID,

se.username,

se.status,

se.terminal,

se.program,

se.MODULE,

se.sql_address,

st.event,

st.p1text,

si.physical_reads,

si.block_changes

FROM v$session se,

v$session_wait st,

v$sess_io si,

v$process pr

WHERE st.sid=se.sid

AND st.sid=si.sid

AND se.PADDR=pr.ADDR

AND se.sid>6

AND st.wait_time=0

AND st.event NOT LIKE '%SQL%'

ORDER BY physical_reads DESC

对检索出的结果的几点说明：

1、我是按每个正在等待的session已经发生的物理读排的序，因为它与实际的IO相关。

2、你可以看一下这些等待的进程都在忙什么，语句是否合理？

Select sql_address from v$session where sid=;

Select * from v$sqltext where address=;

执行以上两个语句便可以得到这个session的语句。

你也以用alter system kill session 'sid,serial#';把这个session杀掉。

3、应观注一下event这列，这是我们调优的关键一列，下面对常出现的event做以简要的说明：

a、buffer busy waits，free buffer waits这两个参数所标识是dbwr是否够用的问题，与IO很大相关的，当v$session_wait中的free buffer wait的条目很小或没有的时侯，说明你的系统的dbwr进程决对够用，不用调整；free buffer wait的条目很多，你的系统感觉起来一定很慢，这时说明你的dbwr已经不够用了，它产生的wio已经成为你的数据库性能的瓶颈，这时的解决办法如下：

a.1增加写进程，同时要调整db_block_lru_latches参数

示例：修改或添加如下两个参数
db_writer_processes=4

db_block_lru_latches=8

a、2开异步IO，IBM这方面简单得多，hp则麻烦一些，可以与Hp工程师联系。

b、db file sequential read，指的是顺序读，即全表扫描，这也是我们应该尽量减少的部分，解决方法就是使用索引、sql调优，同时可以增大db_file_multiblock_read_count这个参数。

c、db file scattered read,这个参数指的是通过索引来读取，同样可以通过增加db_file_multiblock_read_count这个参数来提高性能。

d、latch free,与栓相关的了，需要专门调节。

e、其他参数可以不特别观注。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ljian_mail/archive/2005/12/03/542884.aspx

 

 

--最主要的等待视图
select * from v$session_wait;
select * from v$session_event;
select * from v$system_event;
select * from v$waitstat  where count>0;

--相关视图
select * from v$session_wait  
 
select * from v$filestat

select * from dba_data_files

select * from dba_extents  where OWNER='WANG'

SELECT * FROM SYS.UET$

SELECT * FROM V$LATCH

select * from v$filestat  where file# in(select p1 from v$session_wait   )

 

--基于等待事件的性能优化方法

--常见等待事件
select * from  V$system_event 
where event  in('buffer busy waits','free buffer waits','db file sequential read',
'db file scattered read','enqueue','latch free','log file parallel write','log file sync')

--查看对上面显示有贡献的等待事件

select B.USERNAME,B.PROGRAM,B.STATUS ,A.EVENT,A.TOTAL_WAITS,A.TOTAL_TIMEOUTS,A.TIME_WAITED,A.AVERAGE_WAIT
from V$SESSION_EVENT A , V$SESSION B WHERE B.USERNAME is not null and  A.SID=B.SID
AND A.EVENT NOT LIKE 'SQL*Net%'
and b.status='ACTIVE'

--为了找到与连接的会话有关的当前等待事件,使用下面的查询.这些信息是动态的,为了查看一个会话的等待最多的事件是什么,需要多次执行此查询

select sw.sid,s.Username,sw.Event,sw.wait_time,
sw.state,sw.seconds_in_wait SEC_IN_WAIT
from V$SESSION s,V$SESSION_WAIT sw
where s.Username is not null 
and s.sid=sw.sid 
and sw.event  NOT LIKE 'SQL*Net%'
order by sw.wait_time desc;

--查询显示了有关试验的等待事件的其他信息

select sid ,event,p1text,p1,p2text,p2,p3text,p3
from v$session_wait
where event not like '%SQL%'
AND  EVENT NOT LIKE '%rdbms%'

--利用 p1 和 p2 的信息很容易发现这个段是什么段

select owner,segment_name, segment_type,tablespace_name  from dba_extents
where file_id=&fileid_in
and &blockid_in  between  block_id and block_id + blocks-1

--访问表ITEM_MASTER

--GETSQLTXT.SQL

CREATE OR REPLACE function GetSQLTxt(HashAddr_in  in V$SQLTEXT.Hash_Value%Type , Addr_in  in V$SQLTEXT.Address%Type) return varchar2
is
  Temp_SQLTxt varchar2(32767);
  cursor  SQLPiece_Cur
  is
     select Piece,SQL_text 
     from v$sqltext
     where Hash_value=HashAddr_in
     and Address=Addr_in
     order by Piece;
begin
  for  SQLPiece_Rec   IN    SQLPiece_Cur
  loop
      Temp_SQLTxt:=Temp_SQLTxt||SQLPiece_Rec.Sql_Text;
  end loop    
   
  return Temp_SQLTxt;
end GetSQLTxt;
/

--看数据库启动以来 发生多少全表扫描!
select * from v$sysstat  where name like '%table scan%'

--监视全表扫描

select sid,serial#,opname,to_char(start_time,'HH:MI:SS') "START TIME", SOFAR/TOTALWORK "% COMPLETE"

from  v$session_longops

********************************************************************************************************************
--全表扫描时间长 解决过程

当前oracle系统性能 
查询 MUSICSONG 表  ,全表查询用了  400秒 
查询 使用  ____多少资源

最基本的时间花费
1.数据库读取,把某个表装入内存
2.计算部分(整个cpu),处理这些表
3.数据库写回

--确定oracle系统瓶颈

select  * from v$system_event  where total_timeouts>0  order  by   total_timeouts  desc

drop  table begin_sys_event ;
drop  table end_sys_event;

/*create table begin_sys_event at time T1 */
create table begin_sys_event  as
select * from v$system_event

/*wait n seconds or n minutes */

/*create table end_sys_event at time T2 */
create table end_sys_event  as
select * from v$system_event

select t1.event,(T2.total_waits-T1.total_waits) "Delta waits",
(T2.total_timeouts-T1.total_timeouts) "Delta timeouts",
(T2.time_waited-T1.time_waited) "Delta time waited",
(T2.Average_wait-t2.Average_wait) "Delta   Average  Wait"
from begin_sys_event  t1, end_sys_event  t2 
where t1.event=t2.event and t2.total_waits!=0;

select * From v$event_name;

select * from v$system_event  where  event  not in '%pmon timer%'
and event not like '%rdbms ipc message%'

--指定 LRU 闩锁集数量的上限。只有在 V$LATCH 中的失败率超过 3% 的情况下，才需要增大该值。
select * From V$LATCH;

--如何标识内部latch的冲突

Server manager monitor是一个相当有用的来监视latch等待、请求和冲突的工具。
也可查询相关的数据字典表：
v$latch, v$latchholder, v$latchname。

--latch有40余种，但作为DBA关心的主要应有以下几种：

Cache buffers chains latch: 当用户进程搜索SGA寻找database cache buffers时需要使用此latch。

Cache buffers LRU chain latch: 当用户进程要搜索buffer cache中包括所有 dirty blocks的LRU (least recently used) 链时使用该种latch。

Redo log buffer latch: 这种latch控制redo log buffer中每条redo entries的空间分配。

Row cache objects latch: 当用户进程访问缓存的数据字典数值时，将使用Row cache objects latch

Redo Copy Latch只应用于多CPU的系统。在多CPU的instance中，如果一个redo entry太大，超过了LOG_SMALL_ENTRY_MAX_SIZE定义值，则不能进行“在redo allocation latch上的拷贝”, 此时用户进程必须获取redo copy latch。一个instance中可以有多个redo copy latch，其数目由初始参数LOG_SIMULTANEOUS_COPIES决定，缺省值为CPU数目。

在单CPU情况下，不存在redo copy latch，所有的redo entry无论大小, 都进行“在redo allocation latch上的拷贝”。

对redo log buffer的过多访问将导致redo log buffer latch的冲突，latch冲突将降低系统性能，我们可通过如下查询来检测这种latch冲突：

col name for a40

SELECT ln.name,gets,misses,immediate_gets,immediate_misses

FROM v$latch l,v$latchname ln

WHERE ln.name IN('redo allocation','redo copy') AND ln.latch#=l.latch#

/

若misses与gets的比例超过1%或immediate_misses与(immediate_gets+immediate_misses)比例超过1%时，应考虑采取措施减少latch的冲突。

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/llmmysun/archive/2004/10/14/136417.aspx