个人经验总计优化数据库大幅度进步Oracle的性质方法

数据库优化的座谈可以说是一个稳住的主旨。资深的Oracle优化人员一般会要求提议性能问题的人对数据库做一个statspack,贴出数据库配置等等。
还有的人觉得要抓出执行最慢的语句来拓展优化。但实际上意况是,提出问题的人很可能根本不懂执行计划,更不用说statspack了。而自己觉得,数据库优
化,应该率先从大的下面考虑:网络、服务器硬件配置、操作系统配置、Oracle服务器配置、数据结构社团、然后才是现实的调动。实际上网络、硬件等往往
无法控制更换,应用程序一般也无从修改,因而应该首要从数据库配置、数据结构上来动手,首先让数据库有一个优良的布置,然后再考虑实际优化某些过慢的语
句。

   
我在给本人的用户系统举办优化的历程中,统计了一些骨干的,简单易行的办法来优化数据库,算是自己的三板斧,呵呵。不过请小心,这个不自然普遍利用,甚至有的
会有副效能,不过对OLTP系统、基于成本的数据库往往行之有效,不妨尝试。(注:附件是Burleson写的用来报告数据库性能等信息的脚本,本文用
到)

    一.设置合适的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来对待一下排序查询时间变化。注意,这些参数的最大值与平台有关系.

相关文章