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 Ratio<60%～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性能 我优化数据库的三板斧

数据库优化的讨论可以说是一个永恒的主题 资深的Oracle优化人员通常会要求提出性能问题的人对数据库做一个statspack 贴出数据库配置等等 还有的人认为要抓出执行最慢的语句来进行优化 但实际情况是 提出疑问的人很可能根本不懂执行计划 更不要说statspack了 而我认为 数据库优化 应该首先从大的方面考虑 网络 服务器硬件配置 *** 作系统配置 Oracle服务器配置 数据结构组织 然后才是具体的调整 实际上网络 硬件等往往无法决定更换 应用程序一般也无法修改 因此应该着重从数据库配置 数据结构上来下手 首先让数据库有一个良好的配置 然后再考虑具体优化某些过慢的语句 我在给我的用户系统进行优化的过程中 总结了一些基本的 简单易行的办法来优化数据库 算是我的三板斧 呵呵 不过请注意 这些不一定普遍使用 甚至有的会有副作用 但是对OLTP系统 基于成本的数据库往往行之有效 不妨试试 （注 附件是Burleson写的用来报告数据库性能等信息的脚本 本文用到）

一．设置合适的SGA

常常有人抱怨服务器硬件很好 但是Oracle就是很慢 很可能是内存分配不合理造成的 ( )假设内存有 M 这通常是小型应用 建议Oracle的SGA大约 M 其中 共享池（SHARED_POOL_SIZE）可以设置 M到 M 根据实际的用户数 查询等来定 数据块缓冲区可以大致分配 M M i下需要设置DB_BLOCK_BUFFERS DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于数据块缓冲区大小 i 下的数据缓冲区可以用db_cache_size来直接分配

( )假设内存有 G Oracle 的SGA可以考虑分配 M 共享池分配 M到 M 数据缓冲区分配 M到 M

( )内存 G SGA可以考虑分配 G 共享池 M到 M 剩下的给数据块缓冲区

( )内存 G以上 共享池 M到 M就足够啦 再多也没有太大帮助 (Biti_rainy有专述)数据缓冲区是尽可能的大 但是一定要注意两个问题 一是要给 *** 作系统和其他应用留够内存 二是对于 位的 *** 作系统 Oracle的SGA有 G的限制 有的 位 *** 作系统上可以突破这个限制 方法还请看Biti的大作吧

二．分析表和索引 更改优化模式

Oracle默认优化模式是CHOOSE 在这种情况下 如果表没有经过分析 经常导致查询使用全表扫描 而不使用索引 这通常导致磁盘I/O太多 而导致查询很慢 如果没有使用执行计划稳定性 则应该把表和索引都分析一下 这样可能直接会使查询速度大幅提升 分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令 对于少于 万的表 可以考虑分析整个表 对于很大的表 可以按百分比来分析 但是百分比不能过低 否则生成的统计信息可能不准确 可以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间 索引可以通过DBA_INDEXES的LAST_ANALYZED列

下面通过例子来说明分析前后的速度对比 （表CASE_GA_AJZLZ大约有 万数据 有主键）首先在SQLPLUS中打开自动查询执行计划功能 (第一次要执行\RDBMS\ADMIN\utlxplan sql来创建PLAN_TABLE这个表)

SQL>SET AUTOTRACE ON SQL>SET TIMING ON

通过SET AUTOTRACE ON 来查看语句的执行计划 通过SET TIMING ON 来查看语句运行时间

SQL>select count(*) from CASE_GA_AJZLZCOUNT(*)已用时间: : : Execution Plan SELECT STATEMENT Optimizer=CHOOSE SORT (AGGREGATE) TABLE ACCESS (FULL) OF CASE_GA_AJZLZ ……………………

请注意上面分析中的TABLE ACCESS(FULL) 这说明该语句执行了全表扫描 而且查询使用了 秒 这时表还没有经过分析 下面我们来对该表进行分析

SQL>*** yze table CASE_GA_AJZLZ pute statistics

表已分析 已用时间: : : 然后再来查询

SQL>select count(*) from CASE_GA_AJZLZCOUNT(*)已用时间: : : Execution Plan 　 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost= Card= ) SORT (AGGREGATE) INDEX (FAST FULL SCAN) OF PK_AJZLZ (UNIQUE) (Cost= Card= ) …………………………

请注意 这次时间仅仅用了 秒！这要归功于INDEX(FAST FULL SCAN) 通过分析表 查询使用了PK_AJZLZ索引 磁盘I/O大幅减少 速度也大幅提升！下面的实用语句可以

用来生成分析某个用户的所有表和索引 假设用户是GAXZUSR

SQL>set pagesize SQL>spool d:\ *** yze_tables sqlSQL>select *** yze table ||owner|| ||table_name|| pute statisticsfrom dba_tables where owner= GAXZUSR SQL>spool off SQL>spool spool d:\ *** yze_indexes sqlSQL>select *** yze index ||owner|| ||index_name|| pute statisticsfrom dba_indexes where owner= GAXZUSR SQL>spool off SQL>@d:\ *** yze_tables sql SQL>@d:\ *** yze_indexes sql

解释 上面的语句生成了两个sql文件 分别分析全部的GAXZUSR的表和索引 如果需要按照百分比来分析表 可以修改一下脚本 通过上面的步骤 我们就完成了对表和索引的分析 可以测试一下速度的改进啦 建议定期运行上面的语句 尤其是数据经过大量更新

当然 也可以通过dbms_stats来分析表和索引 更方便一些 但是我仍然习惯上面的方法 因为成功与否会直接提示出来

另外 我们可以将优化模式进行修改 optimizer_mode值可以是RULE CHOOSE FIRST_ROWS和ALL_ROWS 对于OLTP系统 可以改成FIRST_ROWS 来要求查询尽快返回结果 这样即使不用分析 在一般情况下也可以提高查询性能 但是表和索引经过分析后有助于找到最合适的执行计划

三．设置cursor_sharing=FORCE 或SIMILAR

这种方法是 i才开始有的 oracle 不支持 通过设置该参数 可以强制共享只有文字不同的语句解释计划 例如下面两条语句可以共享

SQL>SELECT * FROM MYTABLE WHERE NAME= tom SQL>SELECT * FROM MYTABLE WHERE NAME= turner

这个方法可以大幅降低缓冲区利用率低的问题 避免语句重新解释 通过这个功能 可以很大程度上解决硬解析带来的性能下降的问题 个人感觉可根据系统的实际情况 决定是否将该参数改成FORCE 该参数默认是exact 不过一定要注意 修改之前 必须先给ORACLE打补丁 否则改之后oracle会占用 %的CPU 无法使用 对于ORACLE i 可以设置成SIMILAR 这个设置综合了FORCE和EXACT的优点 不过请慎用这个功能 这个参数也可能带来很大的负面影响！

四．将常用的小表 索引钉在数据缓存KEEP池中

内存上数据读取速度远远比硬盘中读取要快 据称 内存中数据读的速度是硬盘的 倍！如果资源比较丰富 把常用的小的 而且经常进行全表扫描的表给钉内存中 当然是在好不过了 可以简单的通过ALTER TABLE tablename CACHE来实现 在ORACLE i之后可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP) 一般来说 可以考虑把 数据块之内的表放在keep池中 当然要根据内存大小等因素来定 关于如何查出那些表或索引符合条件 可以使用本文提供的access sql和access_report sql 这两个脚本是著名的Oracle专家 Burleson写的 你也可以在读懂了情况下根据实际情况调整一下脚本 对于索引 可以通过ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)来钉在KEEP池中

将表定在KEEP池中需要做一些准备工作 对于ORACLE i 需要设置DB_KEEP_CACHE_SIZE 对于 i 需要设置buffer_pool_keep 在 i中 还要修改db_block_lru_latches 该参数默认是 无法使用buffer_pool_keep 该参数应该比 * *CPU数量少 但是要大于 才能设置DB_KEEP_CACHE_BUFFER buffer_pool_keep从db_block_buffers中分配 因此也要小于db_block_buffers 设置好这些参数后 就可以把常用对象永久钉在内存里

五．设置optimizer_max_permutations

对于多表连接查询 如果采用基于成本优化(CBO) ORACLE会计算出很多种运行方案

从中选择出最优方案 这个参数就是设置oracle究竟从多少种方案来选择最优 如果设置太大 那么计算最优方案过程也是时间比较长的 Oracle 和 i默认是 建议改成 对于 i 已经默认是 了

六．调整排序参数

( ) SORT_AREA_SIZE:默认的用来排序的SORT_AREA_SIZE大小是 K 通常显得有点小 一般可以考虑设置成 M（ ） 这个参数不能设置过大 因为每个连接都要分配同样的排序内存

lishixinzhi/Article/program/Oracle/201311/18879

数据库慢一般有三种情况

逐渐变慢

突然变慢

不定时变慢

第一种情况 逐渐变慢 要建立一个长期的监控机制 比如 写个shell脚本每天的忙时(通常 ~ etc )定时收集os neork db的信息 每个星期出report对收集到的信息进行分析 这些数据的积累 可以决定后期的优化决策 并且可以是DBA说服manager采用自己决策的重要数据 DBA的价值 就在每个星期的report中体现

第二种情况 突然变慢 也是最容易解决的 先从业务的角度看是DB的使用跟以前有何不同 然后做进一步判断 硬件/网络故障通常也会引起DB性能的突然下降

第一步: 察看DB/OS/NEORK的系统log 排除硬件/网络问题

第二步 察看数据库的等待事件 根据等待事件来判断可能出问题的环节 如果 没有等待事件 可以排除数据库的问题 如果有等待时间 根据不同的等待事件 来找引起这些事件的根源

比如latch free等跟SQL parse有关系的等待事件 OS的表现是CPU 的占用率高

db file scattered read等跟SQL disk read有关系的等待时间 OS的表现是iostat可以看到磁盘读写量增加

第三步: 察看os的信息 CPU/IO/MEMORY等

a Cpu 的占用率

CPU占用率与数据库性能不成反比 CPU占用率高 不能说明数据库性能慢 通常情况 一个优化很好 而且业务量确实很大的数据库 CPU的占用率都会高 而且会平均分布在每个进程上 反过来 CPU的占用率都会高也不代表数据库性能就好 要结合数据库的等待事件来判断CPU占用率高是否合理

如果某个进程的cpu占用高 肯定是这个进程有问题 如果 不是oracle的进程 可以让application察看是否程序有死循环等漏洞 如果 是oracle的进程 可以根据pid查找oracle数据字典看看这个进程的发起程序 正在执行的sql语句 以及等待事件 然后 不同情况使用不同的方法来解决

b IO

排除硬件的IO问题 数据库突然变慢 一般来说 都是一个或几个SQL语句引起的

如果IO很频繁 可以通过优化disk reads高的TOP SQL来解决 当然这也是解决IO问题的最笨也是最有效的办法

OS以及存储的配置也是影响IO的一个重要的原因

比如 最常见的HP unix下异步IO的问题 如果DBA GROUP没有MLOCK的权限 ORACLE是不使用AIO的 偏偏OS与DB的两方的admin如果配合不够好地话 这个配置就很容易给漏掉了

c Memory

第二种情况与memory的关系比较小 只要SGA区配置合理没有变化 一般来说 只要不是Application Memory leak 不会引起突然变慢的现象

第三种情况 不定时变慢 是最难解决的 现场出现的问题原因也是五花八门千奇百怪 最重要的是 出现慢的现象时 以最快的速度抓取到最多的信息以供分析 先写好抓取数据的shell 脚本 并在现象发生时及时按下回车键

一个例子

数据库突然变慢

背景: 一个新应用上线后 数据库突然变慢

第一步 调查新应用

据开发人员讲新应用访问的都是新建立的表 表的数据量很小 没有复杂的SQL查询

查询 v$sqlarea 分别按照disk_reads / buffer_gets / executions 排序 TOP SQL 中没有新应用的SQL 排除新应用数据库访问照成的性能问题

第二步 察看数据库log/ OS log

数据库log中可以看到大量的ORA 错误 以及大量的dump文件 分析dump文件(时间久了 没有dump文件可参考 具体细节没法描述下来 ) 发现是新应用通过dblink访问remote DB时生成的dump文件 应用开发人说没法修改 Oracle也没有相应的patch解决

OS log中没有错误信息

第三步 察看statspack report

从wait events中看到 Top event是 buffer busy waits db file parallel write 等于IO相关的等待事件

从buffer busy waits 的统计信息来看 是等待data block

还有些physical reads等信息与从前比没有太多的异常

Tablespace 的IO reads/writes也没有异常 但是wait明显增加

初步确定是IO问题

第四步 察看OS的信息

top 命令(输出为实验室数据 仅作格式参考)

load averages: : :

processes: sleeping zombie stopped on cpu

CPU states: % idle % user % kernel % iowait % swap

Memory: M real M free M swap in use M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU MAND

a K K cpu/ : % top

mpgj M K sleep : % view_server

当时现场数据显示 iowait 值与以前相比大很多 没有异常进程

sar –d (输出为实验室数据 仅作格式参考)

SunOS sc Generic_ sun u / /

: : device %busy avque r+w/s blks/s avwait avserv

sd

sd a

sd b

sd c

sd g

当时现场数据显示 放数据文件的设备 avwait avque blks/s值偏大

第五步 察看数据库的等待事件

一个大业务量的数据库如果性能不好的话 一般来说都会有大量的等待事件 上百个等待事件很常见 我通常会按照EVENT进行group

Select count(*) event from v$session_wait where event not in ( *** on timer pmon timer rdbms ipc message SQL*Net message from client ) group by event order by desc

输出结果显示最多的等待事件是buffer busy waits

进一步分析 找出等待的原因

Select count(*) p p p from v$session_wait where event = buffer busy waits group by p p p

在buffer busy waits等待事件中

P = file#

P = block#

P = id ( 此id对应为等待的原因)

按照p p p group是为了明确buffer busy waits的等待集中在哪些对象上

Metalink对buffer busy waits等待事件的描述有如下一段话

If P shows that the buffer busy wait is waiting for a block read to plete then the blocking session is likely to be waiting on an IO wait (eg: db file sequential read or db file scattered read for the same file# and block#

输出结果显示 等待分布在多个不同的对象上 等待原因为 waiting for a block read to plete 进一步分析为IO的问题

如果 buffer busy waits等待集中在某个对象上 说明有hot block 通过重新rebuild这个对象增加freelist来解决 RAC环境增加freelist group

通过以下SQL可以找到具体的object

Select owner segment_name segment_type from dba_extents where file_id=P and P beeen block_id and block_id+blocks

P P 是上面v$session_wait查出的具体的值

第六步 明确原因 找出解决步骤

分析

磁盘的IO流量增加

磁盘的IO等待增加

DB的IO流量没有增加

DB的IO等待增加

由 可以推出 有数据库以外的IO访问磁盘

察看磁盘配置 该VG只存放了数据库数据文件和数据库系统文件 排除数据文件 产生IO的是数据库系统文件

数据库系统文件一般来说不会产生IO 有IO读写的地方只有log和dump文件

结论 ora 产生的大量core dump文件堵塞IO

解决办法

消除ora (应用不改的情况下 无法解决)

把dump目录指向别的VG

让oracle尽量少的去写core dump文件

background_core_dump = partial

lishixinzhi/Article/program/Oracle/201311/18969

---