几个简单的步骤大幅提高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
Oracle性能优化基本方法包括一下几个步骤 包括
）设立合理的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
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）