九大Oracle性能优化基本方法详解

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

创新互联建站长期为近1000家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为玉泉企业提供专业的成都网站建设、成都网站设计，玉泉网站改版等技术服务。拥有十余年丰富建站经验和众多成功案例,为您定制开发。

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

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

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

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

）确定当前的OS瓶颈

）优化所需的成分（应用程序 数据库 I/O 争用 OS等）

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

）测量并记录当前性能

）重复步骤 到 直到满足优化目标

下面来一一详述

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

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

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

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

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

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

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

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

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

select * from v$system_event

where event in ( buffer busy waits

db file sequential read

db file scattered read

enqueue

free buffer waits

latch free

log file parallel write

log file sync );

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

select se sid s username se event se total_waits se time_waited se average_wait

from v$session s v$session_event se

where s sid = se sid

and se event not like SQL*Net%

and s status = ACTIVE

and s username is not null;

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

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 sid = sw sid

and sw event not like SQL*Net%

and s username is not null

order by sw wait_time desc;

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

select sid event p text p p text p p text p

from v$session_wait

where sid beeen and

and event not like %SQL%

and event not like %rdbms% ;

)利用P P 的信息 找出等待事件的相关的段

select owner segment_name segment_type tablespace_name

from dba_extents

where file_id = fileid_in

and blockid_in beeen block_id and block_id + blocks ;

)获得操作该段的sql语句

select sid getsqltxt(sql_hash_value sql_address)

from v$session

where sid = sid_in;

)getsqltxt函数

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

把等待事件记入跟踪文件

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

)对于当前会话

alter session set timed_statistics=true;

alter session set max_dump_file_size=unlimited;

alter session set events trace name context forever level ;

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

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

)对于其它的会话

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

select S Username P Spid from V$SESSION S V$PROCESS P

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

)以sysdba进入sqlplus执行

alter session set timed_statistics=true;

alter session set max_dump_file_size=unlimited;

oradebug setospid

oradebug unlimit

oradebug event trace name context forever level X /* Where X = ( ) */

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

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

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

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

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

）UNIX上的监控

使用通用性的工具 包括sar iostat cpustat mpstat netstat top osview等

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

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

．测量并记录当前Oracle性能

lishixinzhi/Article/program/Oracle/201311/18709

如何进行oracle数据库性能优化

你最好买一本专门讲ORACLE性能优化的书，好好看看\x0d\x0a1、调整数据库服务器的性能\x0d\x0aOracle数据库服务器是整个系统的核心，它的性能高低直接影响整个系统的性能，为了调整Oracle数据库服务器的性能，主要从以下几个方面考虑： \x0d\x0a1.1、调整操作系统以适合Oracle数据库服务器运行\x0d\x0aOracle数据库服务器很大程度上依赖于运行服务器的操作系统，如果操作系统不能提供最好性能，那么无论如何调整，Oracle数据库服务器也无法发挥其应有的性能。 \x0d\x0a1.1.1、为Oracle数据库服务器规划系统资源 \x0d\x0a据已有计算机可用资源, 规划分配给Oracle服务器资源原则是：尽可能使Oracle服务器使用资源最大化,特别在Client/Server中尽量让服务器上所有资源都来运行Oracle服务。 \x0d\x0a1.1.2、调整计算机系统中的内存配置 \x0d\x0a多数操作系统都用虚存来模拟计算机上更大的内存，它实际上是硬盘上的一定的磁盘空间。当实际的内存空间不能满足应用软件的要求时，操作系统就将用这部分的磁盘空间对内存中的信息进行页面替换，这将引起大量的磁盘I/O操作，使整个服务器的性能下降。为了避免过多地使用虚存，应加大计算机的内存。 \x0d\x0a1.1.3、为Oracle数据库服务器设置操作系统进程优先级 \x0d\x0a不要在操作系统中调整Oracle进程的优先级，因为在Oracle数据库系统中，所有的后台和前台数据库服务器进程执行的是同等重要的工作，需要同等的优先级。所以在安装时，让所有的数据库服务器进程都使用缺省的优先级运行。 \x0d\x0a1.2、调整内存分配\x0d\x0aOracle数据库服务器保留3个基本的内存高速缓存，分别对应3种不同类型的数据：库高速缓存，字典高速缓存和缓冲区高速缓存。库高速缓存和字典高速缓存一起构成共享池，共享池再加上缓冲区高速缓存便构成了系统全程区(SGA)。SGA是对数据库数据进行快速访问的一个系统全程区，若SGA本身需要频繁地进行释放、分配，则不能达到快速访问数据的目的，因此应把SGA放在主存中，不要放在虚拟内存中。内存的调整主要是指调整组成SGA的内存结构的大小来提高系统性能，由于Oracle数据库服务器的内存结构需求与应用密切相关，所以内存结构的调整应在磁盘I/O调整之前进行。 \x0d\x0a1.2.1、库缓冲区的调整 \x0d\x0a库缓冲区中包含私用和共享SQL和PL/SQL区，通过比较库缓冲区的命中率决定它的大小。要调整库缓冲区，必须首先了解该库缓冲区的活动情况，库缓冲区的活动统计信息保留在动态性能表v$librarycache数据字典中，可通过查询该表来了解其活动情况，以决定如何调整。 \x0d\x0a \x0d\x0aSelect sum(pins),sum(reloads) from v$librarycache; \x0d\x0a \x0d\x0aPins列给出SQL语句，PL/SQL块及被访问对象定义的总次数；Reloads列给出SQL 和PL/SQL块的隐式分析或对象定义重装载时在库程序缓冲区中发生的错误。如果sum(pins)/sum(reloads) ≈0，则库缓冲区的命中率合适；若sum(pins)/sum(reloads)1, 则需调整初始化参数 shared_pool_size来重新调整分配给共享池的内存量。 \x0d\x0a1.2.2、数据字典缓冲区的调整 \x0d\x0a数据字典缓冲区包含了有关数据库的结构、用户、实体信息。数据字典的命中率，对系统性能影响极大。数据字典缓冲区的使用情况记录在动态性能表v$librarycache中，可通过查询该表来了解其活动情况，以决定如何调整。 \x0d\x0a \x0d\x0aSelect sum(gets),sum(getmisses) from v$rowcache; \x0d\x0a \x0d\x0aGets列是对相应项请求次数的统计；Getmisses 列是引起缓冲区出错的数据的请求次数。对于频繁访问的数据字典缓冲区，sum(getmisses)/sum(gets)10%～15%。若大于此百分数，则应考虑增加数据字典缓冲区的容量，即需调整初始化参数shared_pool_size来重新调整分配给共享池的内存量。 \x0d\x0a1.2.3、缓冲区高速缓存的调整 \x0d\x0a用户进程所存取的所有数据都是经过缓冲区高速缓存来存取，所以该部分的命中率，对性能至关重要。缓冲区高速缓存的使用情况记录在动态性能表v$sysstat中，可通过查询该表来了解其活动情况，以决定如何调整。 \x0d\x0a \x0d\x0aSelect name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads'); \x0d\x0a \x0d\x0adbblock gets和consistent gets的值是请求数据缓冲区中读的总次数。physical reads的值是请求数据时引起从盘中读文件的次数。从缓冲区高速缓存中读的可能性的高低称为缓冲区的命中率，计算公式： \x0d\x0a \x0d\x0aHit Ratio=1-(physical reds/(dbblock gets+consistent gets)) \x0d\x0a \x0d\x0a如果Hit Ratio60%～70%，则应增大db_block_buffers的参数值。db_block_buffers可以调整分配给缓冲区高速缓存的内存量，即db_block_buffers可设置分配缓冲区高速缓存的数据块的个数。缓冲区高速缓存的总字节数=db_block_buffers的值*db_block_size的值。db_block_size 的值表示数据块大小的字节数，可查询 v$parameter 表： \x0d\x0a \x0d\x0aselect name,value from v$parameter where name='db_block_size'; \x0d\x0a \x0d\x0a在修改了上述数据库的初始化参数以后，必须先关闭数据库，在重新启动数据库后才能使新的设置起作用。

Oracle设置系统参数进行性能优化

一 SGA

Shared pool tunning

Shared pool的优化应该放在优先考虑 因为一个cache miss在shared pool中发生比在data buffer中发生导致的成本更高 由于dictionary数据一般比library cache中的数据在内存中保存的时间长 所以关键是library cache的优化

Gets （parse）在namespace中查找对象的次数

Pins （execution）在namespace中读取或执行对象的次数

Reloads (reparse)在执行阶段library cache misses的次数 导致sql需要重新解析

） 检查v$librarycache中sql area的gethitratio是否超过 ％ 如果未超过 ％ 应该检查应用代码 提高应用代码的效率

Select gethitratio from v$librarycache where namespace= sql area ;

) v$librarycache中reloads/pins的比率应该小于 ％ 如果大于 ％ 应该增加参数shared_pool_size的值

Select sum(pins) executions sum(reloads) cache misses sum(reloads)/sum(pins) from v$librarycache;

reloads/pins %有两种可能 一种是library cache空间不足 一种是sql中引用的对象不合法

）shared pool reserved size一般是shared pool size的 ％ 不能超过 ％ V$shared_pool_reserved中的request misses＝ 或没有持续增长 或者free_memory大于shared pool reserved size的 % 表明shared pool reserved size过大 可以压缩

）将大的匿名pl/sql代码块转换成小的匿名pl/sql代码块调用存储过程

）从 i开始 可以将execution plan与sql语句一起保存在library cache中 方便进行性能诊断 从v$sql_plan中可以看到execution plans

）保留大的对象在shared pool中 大的对象是造成内存碎片的主要原因 为了腾出空间许多小对象需要移出内存 从而影响了用户的性能 因此需要将一些常用的大的对象保留在shared pool中 下列对象需要保留在shared pool中

a 经常使用的存储过程

b 经常操作的表上的已编译的触发器

c Sequence 因为Sequence移出shared pool后可能产生号码丢失

查找没有保存在library cache中的大对象

Select * from v$db_object_cache where sharable_mem and

type in ( PACKAGE PROCEDURE FUNCTION PACKAGE BODY ) and kept= NO ;

将这些对象保存在library cache中

Execute dbms_shared_pool keep( package_name );

对应脚本 dbmspool sql

)查找是否存在过大的匿名pl/sql代码块 两种解决方案

A．转换成小的匿名块调用存储过程

B．将其保留在shared pool中

查找是否存在过大的匿名pl/sql块

Select sql_text from v$sqlarea where mand_type= and length(sql_text) ;

）Dictionary cache的优化

避免出现Dictionary cache的misses 或者misses的数量保持稳定 只能通过调整shared_pool_size来间接调整dictionary cache的大小

Percent misses应该很低 大部分应该低于 ％ 合计应该低于 ％

Select sum(getmisses)/sum(gets) from v$rowcache;

若超过 ％ 增加shared_pool_size的值

Buffer Cache

）granule大小的设置 db_cache_size以字节为单位定义了default buffer pool的大小

如果SGA M granule= M 否则granule＝ M 即需要调整sga的时候以granule为单位增加大小 并且sga的大小应该是granule的整数倍

) 根据v$db_cache_advice调整buffer cache的大小

SELECT size_for_estimate buffers_for_estimate estd_physical_read_factor estd_physical_reads

FROM v$db_cache_advice WHERE NAME= DEFAULT AND advice_status= ON

AND block_size=(SELECT Value FROM v$parameter WHERE NAME= db_block_size );

estd_physical_read_factor=

) 统计buffer cache的cache hit ratio % 如果低于 ％ 可以用下列方案解决

◆增加buffer cache的值

◆使用多个buffer pool

◆Cache table

◆为 sorting and parallel reads 建独立的buffer cache

SELECT NAME value FROM v$sysstat WHERE NAME IN ( session logical reads

physical reads physical reads direct physical reads direct(lob) );

Cache hit ratio= (physical reads physical reads direct physical reads direct (lob))/session logical reads;Select (phy value dir value lob value)/log value from v$sysstat log v$sysstat phy v$sysstat dir v$sysstat LOB where log name= session logical reads and phy name= physical reads and dir name= physical reads direct and lob name= physical reads direct (lob) ;

影响cache hit ratio的因素 全表扫描 应用设计 大表的随机访问 cache hits的不均衡分布

）表空间使用自动空间管理 消除了自由空间列表的需求 可以减少数据库的竞争

其他SGA对象

）redo log buffer

对应的参数是log_buffer 缺省值与 OS相关 一般是 K 检查v$session_wait中是否存在log buffer wait v$sysstat中是否存在redo buffer allocation retries

A 检查是否存在log buffer wait

Select * from v$session_wait where event= log buffer wait ;

如果出现等待 一是可以增加log buffer的大小 也可以通过将log 文件移到访问速度更快的磁盘来解决

B

Select name value from v$sysstat where name in

( redo buffer allocation retries redo entries )

Redo buffer allocation retries接近 小于redo entries 的 ％ 如果一直在增长 表明进程已经不得不等待redo buffer的空间 如果Redo buffer allocation retries过大 增加log_buffer的值

C 检查日志文件上是否存在磁盘IO竞争现象

Select event total_waits time_waited average_wait from v$system_event

where event like log file switch pletion% ;

如果存在竞争 可以考虑将log文件转移到独立的 更快的存储设备上或增大log文件

D 检查点的设置是否合理

检查alert log文件中 是否存在 checkpoint not plete

Select event total_waits time_waited average_wait from v$system_event

where event like log file switch (check% ;

如果存在等待 调整log_checkpoint_interval log_checkpoint_timeout的设置

E 检查log archiver的工作

Select event total_waits time_waited average_wait from v$system_event

where event like log file switch (arch% ;

如果存在等待 检查保存归档日志的存储设备是否已满 增加日志文件组 调整log_archiver_max_processes

F DB_block_checksum=true 因此增加了性能负担 （为了保证数据的一致性 oracle的写数据的时候加一个checksum在block上 在读数据的时候对checksum进行验证）

）java pool

对于大的应用 java_pool_size应= M 对于一般的java存储过程 缺省的 M已经够用了

）检查是否需要调整DBWn

lishixinzhi/Article/program/Oracle/201311/17744

ORACLE数据库性能优化概述

实际上 为了保证ORACLE数据库运行在最佳的性能状态下 在信息系统开发之前就应该考虑数据库的优化策略 优化策略一般包括服务器操作系统参数调整 ORACLE数据库参数调整 网络性能调整 应用程序SQL语句分析及设计等几个方面 其中应用程序的分析与设计是在信息系统开发之前完成的

分析评价ORACLE数据库性能主要有数据库吞吐量 数据库用户响应时间两项指标 数据库吞吐量是指单位时间内数据库完成的SQL语句数目 数据库用户响应时间是指用户从提交SQL语句开始到获得结果的那一段时间 数据库用户响应时间又可以分为系统服务时间和用户等待时间两项 即

数据库用户响应时间＝系统服务时间 ＋ 用户等待时间

上述公式告诉我们 获得满意的用户响应时间有两个途径 一是减少系统服务时间 即提高数据库的吞吐量 二是减少用户等待时间 即减少用户访问同一数据库资源的冲突率

性能优化包括如下几个部分

ORACLE数据库性能优化之一 调整数据结构的设计

这一部分在开发信息系统之前完成 程序员需要考虑是否使用ORACLE数据库的分区功能 对于经常访问的数据库表是否需要建立索引等

ORACLE数据库性能优化之二 调整应用程序结构设计

这一部分也是在开发信息系统之前完成 程序员在这一步需要考虑应用程序使用什么样的体系结构 是使用传统的Client/Server两层体系结构 还是使用Browser/Web/Database的三层体系结构 不同的应用程序体系结构要求的数据库资源是不同的

ORACLE数据库性能优化之三 调整数据库SQL语句

应用程序的执行最终将归结为数据库中的SQL语句执行 因此SQL语句的执行效率最终决定了ORACLE数据库的性能 ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row level manager）来调整优化SQL语句

ORACLE数据库性能优化之四 调整服务器内存分配

内存分配是在信息系统运行过程中优化配置的 数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区 日志缓冲区和共享池的大小 还可以调整程序全局区（PGA区）的大小 需要注意的是 SGA区不是越大越好 SGA区过大会占用操作系统使用的内存而引起虚拟内存的页面交换 这样反而会降低系统

ORACLE数据库性能优化之五 调整硬盘I/O 这一步是在信息系统开发之前完成的

数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上 做到硬盘之间I/O负载均衡

ORACLE数据库性能优化之六 调整操作系统参数

例如 运行在UNIX操作系统上的ORACLE数据库 可以调整UNIX数据缓冲池的大小 每个进程所能使用的内存大小等参数

lishixinzhi/Article/program/Oracle/201311/17687

分享名称：oracle怎么设计优化 oracle的优化方案
分享地址：http://cqwzjz.cn/article/hhdsjd.html