能优化简要培训手册.doc

SQL优化简要培训手册1 性能优化的目的和必要性在一个数据库应用当中，SQL调整是整个数据库调整当中最花费时间并且最优挑战性的操作。每一个SQL语句都必须进行单独的调整，并且这些调整必须使用提示。另外，SQL调整对ORACLE性能有着直接的正面影响。优化过的SQL通常可以使查询速度翻3倍以上。网管系统目前运行速度较慢，无论是前端界面查询还是数据处理。这里面很大部分是因为SQL编写的不规范，导致数据库操作速度偏慢。实际上网管系统如今可以稳定运行，已经经过那几次大的SQL优化，一次是指标组优化，一次是NOKIA性能预处理优化，一次是原始数据处理。这几次优化的效果都非常显著。以指标组优化为例，原先全省GSM报表生成要将近半个小时时间，目前不到3分钟即可。正如前面提到的，SQL优化是一个繁琐的工作，需要每一个人都参与到其中来，否则数据库的运行效率将越来越慢；同时，如果大家都注意对SQL进行优化，那么这又是一种提升系统运行效率的最有效快捷的途径。今天编写这份文档，简要的介绍一下SQL优化，更加详细的优化方法请参见杨辉凤的ORACLE高性能SQL调整.2 SQL优化简介ORACLE本身自带有两种SQL优化器，一种是基于规则的，这也是缺省的；另外一种是基于成本的，也就是我们要掌握的。通常情况下，基于规则的优化器可以很好的稳定的工作，但是基于成本的优化器可以更有效率的工作。2.1 成本优化器简介基于成本的优化器简单的说就是通过一系列的提示来告知数据库如何执行SQL。常用的提示如下：all_rows：这是一个基于成本的方法，它用来提供最佳的总体吞吐量和最少的资源消耗。and_equal：这个提示将引发25个单独字段索引的合并扫描。cluster(table_name)：这个提示要求一个table_name的簇扫描。first_rows：这是一个基于成本的方法，它用来提供最快的反应时间。full：提个提示要求忽略索引并执行一个全表扫描。hash(table_name)：这个提示将引发对table_name的散列扫描。hash_aj：这个提示放置于not in子查询中，并执行散列反连接（antijoin）。index(table_name index_name) ：这个提示要求对表使用指定的索引。如果没有指定索引，oracle将选择最佳的索引。当多表关联有别名时，一定要用别名。index_asc(table_name index_name)：这个提示要求在范围扫描操作中使用升序索引。index_combine(table_name index_name)：这个提示要求使用指定的位图索引。index_desc(table_name index_name)：这个提示要求在范围扫描操作中使用降序索引。merge_aj：这个提示放置于not in子查询中，并执行反连接。no_expand：。no_merge：这个提示在一个视图中使用，它用于阻止该视图合并到一个父查询。nocache：这个查询将导致忽略table cache选项。noparallel：这个提示将关闭并行查询选项。ordered：这个提示将要求表按照指定的顺序（从左到右）进行连接。parallel(table_name degree) ：对于全表扫描来说，这个提示将要求以带有服务于表访问的degree过程的并行模式执行查询。push_subq：这个提示将导致查询区中所有的子查询在尽可能早的时间内执行。rowid：这个提示将对指定的表执行ROWID扫描。rule：这个提示将指定调用基于规则的优化器。star：这个提示将使用星型连接查询。use_concat：这个提示将要求所有的OR条件使用UNION ALL操作。use_hash（table_name1 talbe_name2）：这个提示将对指定的表执行散列连接。use_merge：这个提示将要求排序合并操作。use_nl(table_name) ：这个提示将对指定的做为驱动表的表执行嵌套循环操作。2.2 优化经验 对于大表，最好要建索引，索引的建立与你所使用的SQL语句有关；可以通过指定index(table_name,index_name)强行使用索引 当大表与小表进行关联时，最好将小表作为基本表，将大表与小表进行关联，具体做法可以这样：（1）指定ordered；（2）将小表排在from后面的最前面，大表放最后面 小表与小表之间的关联可以指定use_hash(a,b)或use_merge(a,b)，与大表关联时最好指定use_nl(a)。注：a,b代表表的别名 如果要进行全表扫描，可以指定full(tabe_name)以及parallel(table_name,devel) 建cluster 对大表按照关键字段做分区，对已经做分区的表查询时，尽量使用关键字段做为查询条件，并且避免对关键字段做函数操作。 时间查询尽量使用to_date函数而不是使用to_char函数。 缺省情况下，可以多使用first_rows提示，以获得最快响应。2.3 优化工具2.3.1 SQL分析工具通常的SQL分析工具可以使用PLSQL的Explain Plan Window。2.3.2 ORACLE分析工具可以使用TADO软件。TADO软件可以在190的soft inst目录下的QUEST.TOAD.XPERT.EDITION.V7.4.0.3获得。可以使用oracle的企业管理套件。安装软件在192.120.101.169shareto_h_clinstallA91662-01下。可以通过查询相关视图获得SQL的执行情况，SQL