注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

一路奔跑

奔跑着一路向前...

 
 
 

日志

 
 

从事Oracle性能优化时常用几个简单的步骤  

2013-02-01 14:48:49|  分类: Oracle书架 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
数据库优化,应该首先从大的方面考虑:网络服务器硬件配置、操作系统配置、Oracle服务器配置、数据结构组织、然后才是具体的调整。实际上网络、硬件等往往无法决定更换,应用程序一般也无法修改,因此应该着重从数据库配置、数据结构上来下手,首先让数据库有一个良好的配置,然后再考虑具体优化某些过慢的语句。我在给我的用户系统进行优化的过程中,总结了一些基本的,简单易行的办法来优化数据库的方法.  
  一.设置合适的SGA
  
  常常有人抱怨服务器硬件很好,但是Oracle就是很慢。很可能是内存分配不合理造成的。
  
  (1)假设内存有512M,这通常是小型应用。建议Oracle的SGA大约240M,其中:共享池(SHARED_POOL_SIZE)可以设置 60M到80M,根据实际的用户数、查询等来定。数据块缓冲区可以大致分配120M-150M,8i下需要设置 DB_BLOCK_BUFFERS,DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于数据块缓冲区大小。9i 下的数据缓冲区可以用db_cache_size来直接分配。
  
  (2)假设内存有1G,Oracle 的SGA可以考虑分配500M:共享池分配100M到150M,数据缓冲区分配300M到400M。
  
  (3)内存2G,SGA可以考虑分配1.2G,共享池300M到500M,剩下的给数据块缓冲区。
  
  (4)内存2G以上:共享池300M到500M就足够啦,再多也没有太大帮助;(Biti_rainy有专述)数据缓冲区是尽可能的大,但是一定要注意两个问题:一是要给操作系统和其他应用留够内存,二是对于32位的操作系统,Oracle的SGA有1.75G的限制。有的32位操作系统上可以突破这个限制,方法还请看Biti的大作吧。
  
  二.分析表和索引,更改优化模式
  
  Oracle默认优化模式是CHOOSE,在这种情况下,如果表没有经过分析,经常导致查询使用全表扫描,而不使用索引。这通常导致磁盘I/O太多,而导致查询很慢。如果没有使用执行计划稳定性,则应该把表和索引都分析一下,这样可能直接会使查询速度大幅提升。分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令。对于少于100万的表,可以考虑分析整个表,对于很大的表,可以按百分比来分析,但是百分比不能过低,否则生成的统计信息可能不准确。可以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间,索引可以通过DBA_INDEXES的 LAST_ANALYZED列。
  
  下面通过例子来说明分析前后的速度对比。(表CASE_GA_AJZLZ大约有35万数据,有主键)首先在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_AJZLZ;
  COUNT(*)
  ----------
  346639
  已用时间: 00: 00: 21.38
  Execution Plan
  ----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE
  1 0 SORT (AGGREGATE)
  2 1 TABLE ACCESS (FULL) OF 'CASE_GA_AJZLZ'
  ……………………
  请注意上面分析中的TABLE ACCESS(FULL),这说明该语句执行了全表扫描。而且查询使用了21.38秒。这时表还没有经过分析。下面我们来对该表进行分析:
  SQL> analyze table CASE_GA_AJZLZ compute statistics;
  
  表已分析。
  
  已用时间: 00: 05: 357.63
  
  然后再来查询:
  SQL> select count(*) from CASE_GA_AJZLZ;
  COUNT(*)
  ----------
  346639
  
  已用时间: 00: 00: 00.71
  Execution Plan
  ----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=351 Card=1)
  1 0 SORT (AGGREGATE)
  2 1 INDEX (FAST FULL SCAN) OF 'PK_AJZLZ' (UNIQUE) (Cost=351
  Card=346351)
  …………………………
  
  请注意,这次时间仅仅用了0.71秒!这要归功于INDEX(FAST FULL SCAN)。通过分析表,查询使用了PK_AJZLZ索引,磁盘I/O大幅减少,速度也大幅提升!下面的实用语句可以用来生成分析某个用户的所有表和索引,假设用户是GAXZUSR:
  SQL> set pagesize 0
  SQL> spool d:\analyze_tables.sql;
  SQL> select 'analyze table '||owner||'.'||table_name||' compute statistics;' from dba_tables where owner='GAXZUSR';
  SQL> spool off
  SQL> spool spool d:\analyze_indexes.sql;
  SQL> select 'analyze index '||owner||'.'||index_name||' compute statistics;' from dba_indexes where owner='GAXZUSR';
  SQL> spool off
  SQL> @d:\analyze_tables.sql
  SQL> @d:\analyze_indexes.sql
  
  解释:上面的语句生成了两个sql文件,分别分析全部的GAXZUSR的表和索引。如果需要按照百分比来分析表,可以修改一下脚本。通过上面的步骤,我们就完成了对表和索引的分析,可以测试一下速度的改进啦。建议定期运行上面的语句,尤其是数据经过大量更新。
  
  当然,也可以通过dbms_stats来分析表和索引,更方便一些。但是我仍然习惯上面的方法,因为成功与否会直接提示出来。
  
  另外,我们可以将优化模式进行修改。optimizer_mode值可以是RULE、CHOOSE、FIRST_ROWS和ALL_ROWS。对于 OLTP系统,可以改成FIRST_ROWS,来要求查询尽快返回结果。这样即使不用分析,在一般情况下也可以提高查询性能。但是表和索引经过分析后有助于找到最合适的执行计划。
  
  三.设置cursor_sharing=FORCE 或SIMILAR
  
  这种方法是8i才开始有的,oracle805不支持。通过设置该参数,可以强制共享只有文字不同的语句解释计划。例如下面两条语句可以共享:
  
  SQL> SELECT * FROM MYTABLE WHERE NAME='tom'
  
  SQL> SELECT * FROM MYTABLE WHERE NAME='turner'
  
  这个方法可以大幅降低缓冲区利用率低的问题,避免语句重新解释。通过这个功能,可以很大程度上解决硬解析带来的性能下降的问题。个人感觉可根据系统的实际情况,决定是否将该参数改成FORCE。该参数默认是exact。不过一定要注意,修改之前,必须先给ORACLE打补丁,否则改之后oracle会占用100%的CPU,无法使用。对于ORACLE9i,可以设置成SIMILAR,这个设置综合了FORCE和EXACT的优点。不过请慎用这个功能,这个参数也可能带来很大的负面影响!
  
  四.将常用的小表、索引钉在数据缓存KEEP池中
  
  内存上数据读取速度远远比硬盘中读取要快,据称,内存中数据读的速度是硬盘的14000倍!如果资源比较丰富,把常用的小的、而且经常进行全表扫描的表给钉内存中,当然是在好不过了。可以简单的通过ALTER TABLE tablename CACHE来实现,在ORACLE8i之后可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP)。一般来说,可以考虑把200数据块之内的表放在keep池中,当然要根据内存大小等因素来定。关于如何查出那些表或索引符合条件,可以使用本文提供的access.sql和access_report.sql。这两个脚本是著名的Oracle专家 Burleson写的,你也可以在读懂了情况下根据实际情况调整一下脚本。对于索引,可以通过ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)来钉在KEEP池中。
  
  将表定在KEEP池中需要做一些准备工作。对于 ORACLE9i 需要设置DB_KEEP_CACHE_SIZE,对于8i,需要设置buffer_pool_keep。在8i中,还要修改 db_block_lru_latches,该参数默认是1,无法使用buffer_pool_keep。该参数应该比2*3*CPU数量少,但是要大于 1,才能设置DB_KEEP_CACHE_BUFFER。buffer_pool_keep从db_block_buffers中分配,因此也要小于 db_block_buffers。设置好这些参数后,就可以把常用对象永久钉在内存里。
  
  五.设置optimizer_max_permutations
  
  对于多表连接查询,如果采用基于成本优化(CBO),ORACLE会计算出很多种运行方案,从中选择出最优方案。这个参数就是设置oracle究竟从多少种方案来选择最优。如果设置太大,那么计算最优方案过程也是时间比较长的。Oracle805和8i默认是80000,8建议改成2000。对于9i,已经默认是2000了。
  
  六.调整排序参数
  
  (1) SORT_AREA_SIZE:默认的用来排序的SORT_AREA_SIZE大小是32K,通常显得有点小,一般可以考虑设置成1M(1048576)。这个参数不能设置过大,因为每个连接都要分配同样的排序内存。
  
  (2) SORT_MULTIBLOCK_READ_COUNT:增大这个参数可以提高临时表空间排序性能,该参数默认是2,可以改成32来对比一下排序查询时间变化。注意,这个参数的最大值与平台有关系。
  
  七.调整其它几个关键的性能参数
  
  很多人认为使用oracle数据库,系统的默认参数就是最好的,其实不是这样,很多参数都需要调整,而且调整前后性能大不一样。
  
  (1) log_buffer
  
  日志缓冲区大小默认设置32k太小了,建议设置成512K或者1M。
  log_buffer=524288
  
  (2) optimizer_index_caching
  
  这个参数可以设置索引的缓冲度,范围是0到100,默认是0,可以考虑设置成90
  
  (3) optimizer_index_cost_adj
  
  这个参数是一个百分比,表明索引扫描与全表扫描的代价范围是1到1000。默认=100表名索引扫描与全表扫描代价一样。将这个参数设小表名索引代价要小于全表扫描,这样就使得使用CBO进行成本计算时更倾向于使用索引扫描。建议把这个参数设置成30到50。
  
  八.改变联机日志文件大小(一般用于oracle805)
  
  Oracle805的联机日志文件默认只有1M大小,这实在是太小了,通过查看数据库的日志,很可能发现“checkpoint not complete”之类的错误提示。这会导致系统稳定性,同样也降低了数据库性能。建议修改成10M。修改方法是删除一个组、添加一个组,直到3个组都换成新的大小。说明:这个操作需要实施人员具有较多的数据库知识,如果不太了解,最好不要试验。
  
  九.改变数据块大小(一般用于oracle805)
  
  Oracle805默认的块(DB_BLOCK_SIZE)大小是2K,太小了,因为块小,所以请求同样的数据量的时候,读的次数就要增多,导致性能低下。当然如果服务器性能比较好,还是升级Oracle更好,如果服务器配置比较差,建议改成8K。但是数据块不能直接修改,唯一的办法就是将数据导出,重新创建数据库,然后将数据导入。说明:这个操作需要实施人员具有较多的数据库知识,如果不太了解,最好不要试验。
  
  十.设置合适的表存储参数
  
  对于有很多并发写入用户的系统来说,如果系统没有经过调整,经常会有数据等待现象。这是因为9i之前的表设置的默认的自由队列freelists为1,这样就可能造成数据等待。通过查看v$waitstat,如果发现data block 或者free list类的count次数很大,则说明等待情况严重,需要增加freelists。这个参数在8i、9i中可以动态修改(需要打补丁,否则会有ORA- 10620: Operation not allowed on this segment)在ORACLE805中,只能通过重新创建表来修改。
  SQL> select * from v$waitstat;
  CLASS COUNT TIME
  ------------------ ---------- ----------
  data block 11922013 342456
  sort block 0 0
  save undo block 0 0
  segment header 1 0
  free list 0 0
  
  如果测算经常有10个并发的写用户,可以把表的freelists改成10。例如下面的脚本可以把GAXZUSR用户的所有表重新设置FREELISTS的语句写在D:\FREELISTS.SQL里:
  SQL> SET PAGESIZE 0
  SQL> SPOOL D:\FREELISTS.SQL
  SQL> SELECT 'ALTER TABLE '||TABLE_NAME||' STORAGE(FREELISTS 10);' FROM DBA_TABLES WHERE OWNER=’GAXZUSR’;
  SQL>SPOOL OFF
  
  检查D:\FREELISTS.SQL,没有错误后运行修改FREELISTS:
  SQL>@D:\FREELISTS.SQL
  
  十一.重新组织表结构
  
  (1) 按照主键重新排序。
  
  数据库运行了一段时间后,可能会有很多数据,而这些数据又可能是经常按照某个字段来选取区段数据。如果我们能够把主键按照顺序重新来组织一下表,那么用主键进行的查询就会明显快很多,主要是因为经过排序后,相似的编号都放在同一个数据块里,ORACLE在进行主键范围查找的时候,就会大大减少物理块度读取数量。在对表和索引分析之后,可以通过DBA_INDEXES的CLUSTERING_FACTOR列来判断表是否需要重新排序。如果该字段的值与表的 BLOCK数量差不多,则不需要重新排序,如果和表的行数差不多,则应该考虑重新组织一下了。下面的例子示意性说明怎样对表CASE_GA_AJZLZ按照主键PK_AJZLZ进行重新排序:
  
  <1> 将表CASE_GA_AJZLZ的索引、外间约束引用等找出来备用。
  SET PAGESIZE 0
  SET LINESIZE 300
  SPOOL DISABLE_CONSTRAINTS.SQL
  SELECT 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';' FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND R_CONSTRAINT_NAME='PK_AJZLZ';
  SPOOL OFF
  SPOOL CREATE_CONSTRAINTS.SQL
  SELECT 'ALTER TABLE '||TABLE_NAME||' ADD CONSTRAINT '||CONSTRAINT_NAME||' FOREIGN KEY(CASEID) REFERENCES CASE_GA_AJZLZ(CASEID);' FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND R_CONSTRAINT_NAME='PK_AJZLZ';
  SPOOL OFF
  SPOOL CREATE_INDEX.SQL
  SELECT 'CREATE INDEX '||INDEX_NAME||' ON '||TABLE_NAME||'('||COLUMN_NAME||') TABLESPACE INDX ;' FROM USER_IND_COLUMNS WHERE TABLE_NAME='CASE_GA_AJZLZ' AND INDEX_NAME<>'PK_AJZLZ';
  
  <2> 创建新的表CASE_GA_AJZLZ_NEW:
  SQL> CREATE TABLE CASE_GA_AJZLZ_NEW AS SELECT
  /*+INDEX(CASE_GA_AJZLZ PK_AJZLZ) */ * FROM CASE_GA_AJZLZ ;
  
  注意,上面的注释(红颜色部分)表名按照PK_AJZLZ排序来重新组织表。
  
  <3>禁用CASE_GA_AJZLZ的外间约束,将表CASE_GA_AJZLZ TRUNCATE,然后删除之
  
  <4> 将表CASE_GA_AJZLZ_NEW更名为CASE_GA_AJZLZ
  
  SQL> ALTER TABLE CASE_GA_AJZLZ_NEW RENAME TO CASE_GA_AJZLZ;
  
  <5>创建CASE_GA_AJZLZ的所有索引、主键约束等。
  
  SQL> ALTER TABLE CASE_GA_AJZLZ ADD CONSTRAINT PK_AJZLZ PRIMARY KEY(CASEID);
  SQL> @CREATE_INDEX.SQL
  SQL> @CREATE_CONSTRAINTS.SQL
  
  (2) 将BLOB字段存储到单独的表空间中。
  
  基本上每个业务系统都有很多BLOB字段,而且很可能占据了整个数据库大小的大部分。默认情况下,BLOB字段会将4000个字节的指针与表的行存在一起,这直接会导致行迁移。而且BLOB字段会与表处于同一个表空间,这也对性能有不小的影响。从设计角度来说,BLOB字段都应该单独存储,遗憾的是我所遇到的很多系统都没有单独存储BLOB字段。如果BLOB字段占据了很大的存储,那么将BLOB字段单独存储后,带来的整体性能收益可能会非常的大。 另外BLOB字段存储子句中有一个DISABLE STORAGE IN ROW 属性,在将BLOB字段单独存放时,也应该实用该属性,这样可以有效避免行迁移。
  评论这张
 
阅读(338)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018