Oracle性能优化基本方法详解

Oracle性能优化基本方法包括一下几个步骤，包括：

　　1）设立合理的Oracle性能优化目标。

　　2）测量并记录当前的Oracle性能。

　　3）确定当前Oracle性能瓶颈（Oracle等待什么、哪些SQL语句是该等待事件的成分）。

　　4）把等待事件记入跟踪文件。

　　5）确定当前的OS瓶颈。

　　6）优化所需的成分（应用程序、、I/O、争用、OS等）。

　　7）跟踪并实施更改控制过程。

　　8）测量并记录当前性能

　　9）重复步骤3到7，直到满足优化目标

　　下面来一一详述。

　　1.设立合理的Oracle性能优化目标

　　重点：关于设立目标的最重要的一点是它们必须是可量化和可达到的。

　　方法：目标必须是当前性能和所需性能的的陈述形式的语句。

　　2.测量并记录当前Oracle性能重点：

　　1）需要在峰值活动时间获得当前系统性能快照

　　2）关键是要在出现性能问题的时间段内采集信息

　　3）必须在合理的时间段上采集，一般在峰值期间照几个为期15分钟的快照

　　3.确定当前Oracle性能瓶颈重点：从Oracle 等待接口v$system_event、v$session_event和v$session_wait中获得等待事件，进而找出影响性能的对象和sql语句。方法如下：

　　1)首先，利用v$system_event视图执行下面的查询查看数据库中某些常见的等待事件：

　　1.select * from v$system_event

　　2.

　　3.where event in ('buffer busy waits',

　　4.

　　5.'db file sequential read',

　　6.

　　7.'db file scattered read',

　　8.

　　9.'enqueue',

　　10.

　　11.'free buffer waits',

　　12.

　　13.'latch free',

　　14.

　　15.'log file parallel write',

　　16.

　　17.'log file sync');

　　18.

　　2)接着，利用下面对v$session_event和v$session视图进行的查询，研究具有对上面显示的内容有贡献的等待事件的会话：

　　1.select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait

　　2.

　　3.from v$session s,v$session_event se

　　4.

　　5.where s.sid = se.sid

　　6.

　　7.and se.event not like 'SQL*Net%'

　　8.

　　9.and s.status = 'ACTIVE'

　　10.

　　11.and s.username is not null;

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

　　1.select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait SEC_IN_WAIT

　　2.

　　3.from v$session s,v$session_wait sw

　　4.

　　5.where s.sid = sw.sid

　　6.

　　7.and sw.event not like 'SQL*Net%'

　　8.

　　9.and s.username is not null

　　10.

　　11.order by sw.wait_time desc;

　　12.

　　4)查询会话等待事件的详细信息

　　1.select sid,event,p1text,p1,p2text,p2,p3text,p3

　　2.

　　3.from v$session_wait

　　4.

　　5.where sid between &1 and &2

　　6.

　　7.and event not like '%SQL%'

　　8.

　　9.and event not like '%rdbms%';

　　10.

　　5)利用P1、P2的信息，找出等待事件的相关的段

　　1.select owner,segment_name,segment_type,tablespace_name

　　2.

　　3.from dba_extents

　　4.

　　5.where file_id = &fileid_in

　　6.

　　7.and &blockid_in between block_id and block_id + blocks - 1;8.

　　6)获得操作该段的sql语句：

　　1.select sid, getsqltxt(sql_hash_value,sql_address)

　　2.

　　3.from v$session

　　4.

　　5.where sid = &sid_in;

　　6.

　　7)getsqltxt函数

　　8)至此已经找到影响性能的对象和sql语句，可以有针对性地优化

　　4.把等待事件记入跟踪文件

　　重点：如果在跟踪系统上的等待事件时，由于某种原因遇到了麻烦，则可以将这些等待事件记入一个跟踪文件。方法如下：

　　1)对于当前会话：

　　1.alter session set timed_statistics=true;

　　2.

　　3.alter session set max_dump_file_size=unlimited;

　　4.

　　5.alter session set events '10046 trace name context forever, level 12';

　　6.

　　2)执行应用程序，然后在USER_DUMP_DEST指出的目录中找到跟踪文件。

　　3)查看文件中以词WAIT开始的所有行。

　　4)对于其它的会话

　　5)确定会话的进程ID（SPID）。下面的查询识别出名称以A开始的所有用户的会话进程ID：

　　1.select S.Username, P.Spid from V$SESSION S, V$PROCESS P

　　2.

　　3.where S.PADDR = P.ADDR and S.Username like 'A%';

　　4.

　　6)以sysdba进入sqlplus执行

　　1.alter session set timed_statistics=true;

　　2.

　　3.alter session set max_dump_file_size=unlimited;

　　4.

　　5.oradebug setospid

　　6.

　　7.oradebug unlimit

　　8.

　　9.oradebug event 10046 trace name context forever, level X /* Where X = (1,4,8,12) */

　　10.

　　7)跟踪某个时间间隔得会话应用程序。

　　8)在USER_DUMP_DEST 的值指出的目录中利用SPID查看跟踪文件

　　9)查看文件中以词WAIT开始的所有行。

　　5.确定当前OS瓶颈1）Windows NT上的监控

　　使用控制面板-〉管理工具-〉性能即可

　　2）UNIX上的监控

　　使用通用性的工具，包括sar、iostat、cpustat、mtat、netstat、top、osview等。

　　6．Oracle性能优化所需的成分（应用程序、数据库、I/O、争用、OS等）。

　　7．跟踪并实施更改控制过程。

　　8．测量并记录当前Oracle性能

　　9．重复步骤3到7，直到满足优化目标