`
快乐让让
  • 浏览: 18455 次
  • 性别: Icon_minigender_1
  • 来自: 上海
最近访客 更多访客>>
社区版块
存档分类
最新评论

使用Oracle提示进行调整SQL语句的简介

阅读更多
提示(hint)从Oracle7中引入,目的是弥补基于成本优化器的缺陷。提示通常用来改变SQL执行计划,提高执行效率。




1.使用提示需要遵循的原则
1)仔细检查提示语法。尽量使用完整注释语法/*+ hint */
2)使用表别名。如果在查询中指定了表别名,那么提示必须也使用表别名。例如:select /*+ index(e,dept_idx) */ * from emp e;
3)不要在提示中使用模式名称:如果在提示中指定了模式的所有者,那么提示将被忽略。例如:
select /*+ index(scott.emp,dept_idx) */ * from emp;
4)检验提示。如果提示指定了不可用的访问路径,那么这个提示将被忽略。

2.导致提示无效的条件:

提示

被忽略的条件


cluster

与非簇表一同使用


hash

与非簇表一同使用


hash_aj

不存在子查询


index

指定的索引不存在


index_combine

不存在位图索引


merge_aj

不存在子查询


parallel

调用的不是TABLE ACCESS FULL计划


push_subq

不存在子查询


star

事实表中存在不恰当的索引


use_concat

在where子句中不存在多个or条件


use_nl

表中不存在索引



3几种主要的优化模式:
1)all_rows:all_rows是基于成本的优化方法,目的是提供整体最佳的吞吐量和最小的资源消耗。all_rows提示倾向使用全表扫描,而且不适用于OLTP数据库。使用all_rows提示应该保障查询中涉及的表和索引拥有使用analyze命令分析得到的统计资料。
2)rule:rule提示使Oracle为查询提供基于规则的优化模式。在怀疑CBO生成了非优化的执行计划时,通常首先尝试使用rule提示。Rule提示忽略表和索引的统计资料,并且使用基本的试探法生成执行计划。
3)first_rows:这个提示是基于成本的优化方法,目的是提供最快的反应时间。使用first_rows提示应该保障查询中涉及的表和索引拥有使用analyze命令分析得到的统计资料。
4.表的连接提示
1)use_hash提示
use_hash 提示对指定的表进行散列连接。散列连接是Oracle用以驱动表(最小的表)向RAM区中装载记录的方法,RAM区由HASH_AREA_SIZE定义。散列连接适合中间结果比较大的情况。使用散列连接时,HASH_AREA_SIZE对速度影响非常大,如果驱动表不能一次装入内存,那么需要使用TEMP表空间,这种情况下速度比较慢。这个参数可以在session级别动态修改,需要进行散列连接时可以临时增大,速度可能显著增加。
2)use_merge 提示
use_merge 提示强制执行一个排序合并操作。排序合并操作通常与并行查询结合使用,因为排序合并操作倾向于全表扫描。该提示适合于生成大型结果集的查询。
3)use_nl:
use_nl提示将强制对目标表执行嵌套循环连接。use_nl提示很少用于SQL调整,因为CBO和RBO更倾向于使用循环嵌套连接。
4)star提示
star 提示强制使用星型查询计划。前提是查询中至少三个表,而且在事实表中存在恰当的索引
5.表反连接提示
SQL反连接是指在语句中包含NOT IN 或者NOT EXISTS子句时执行的操作。
1)merge_aj
在使用全表访问比索引访问更好的情况下,可以在NOT IN子查询中使用merge_aj提示以便执行反连接。
2)hash_aj
hash_aj 提示放在NOT IN 子查询中用来希望执行散列连接时,执行散列反连接。
hash_aj和merge_aj要求子查询列非空。
6INDEX提示
1)INDEX提示简介:
INDEX提示被用于显示指定表名或表名与索引。如果只指定了表名,那么优化器将使用表中的"最优"索引。在永久优化SQL语句中,建议指定表和索引。
2)index_join 提示
index_join 提示明确要求优化器使用索引连接来作为访问路径。
3)and_equal 提示
and_equal 提示可以使多个非唯一的索引合并索引,并且使这些索引操作时就象单个连续索引一样。该提示如果被应用,在查询计划中显示的是AND-EQUAL
4)index_asc 提示
index_asc 提示使用升序索引。这是默认的优化器行为
5)no_index 提示
该提示忽略索引存在,类似full
6)index_combine提示
index_combine 提示用来强制使用位图索引作为表的访问路径。
7)index_ffs提示
索引快速完全扫描可以在不访问任何记录的情况下完成查询。
8)use_concat提示
use_concat提示要求为所有的OR条件使用UNION ALL执行计划,并将这个查询重新书写为多个查询。如果在WHERE子句中存在大量OR条件,可以考虑使用use_concat提示。
7.总结
1)因为提示放在注释中,所以如果提示通现存的执行计划不兼容,或者提示不正确,有可能被忽略。
2)在使用RBO时,可以通过提示将指定的查询更改为CBO。切记要对查询中涉及的所有表和索引进行分析
3)在使用CBO的时候,可以通过添加RULE提示或者FIRST_ROWS提示来开始调整一个可以的SQL语句
4)提示可以在子查询中使用,但是外部查询的提示不会带入子查询。
5)如果在查询计划中发现卡笛尔积(CARTESIAN),则要尽量解决。

一、问题的提出

在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,随着数据库中数据的增加,系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据,劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍,可见对于一个系统不是简单地能实现其功能就可,而是要写出高质量的SQL语句,提高系统的可用性。

在多数情况下,Oracle使用索引来更快地遍历表,优化器主要根据定义的索引来提高性能。但是,如果在SQL语句的where子句中写的SQL代码不合理,就会造成优化器删去索引而使用全表扫描,一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引,这有助于写出高性能的SQL语句。

二、SQL语句编写注意问题

下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中,即使某些列存在索引,但是由于编写了劣质的SQL,系统在运行该SQL语句时也不能使用该索引,而同样使用全表扫描,这就造成了响应速度的极大降低。

1. IS NULL 与 IS NOT NULL

不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

2. 联接列

对于有联接的列,即使最后的联接值为一个静态值,优化器是不会使用索引的。我们一起来看一个例子,假定有一个职工表(employee),对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME),现在要查询一个叫比尔.克林顿(Bill Cliton)的职工。

下面是一个采用联接查询的SQL语句:


select * from employss
where
first_name||''||last_name ='Beill Cliton';

上面这条语句完全可以查询出是否有Bill Cliton这个员工,但是这里需要注意,系统优化器对基于last_name创建的索引没有使用。

当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。


Select * from employee
where
first_name ='Beill' and last_name ='Cliton';

遇到下面这种情况又如何处理呢?如果一个变量(name)中存放着Bill Cliton这个员工的姓名,对于这种情况我们又如何避免全程遍历,使用索引呢?可以使用一个函数,将变量name中的姓和名分开就可以了,但是有一点需要注意,这个函数是不能作用在索引列上。下面是SQL查询脚本:


select * from employee
where
first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1)
and
last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1)

3. 带通配符(%)的like语句

同样以上面的例子来看这种情况。目前的需求是这样的,要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:


select * from employee where last_name like '%cliton%';

这里由于通配符(%)在搜寻词首出现,所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时,优化器就能利用索引。在下面的查询中索引得到了使用:


select * from employee where last_name like 'c%';


          4. Order by语句

ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。

5. NOT

我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:


... where not (status ='VALID')

如果要使用NOT,则应在取反的短语前面加上括号,并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中,这就是不等于(<>)运算符。换句话说,即使不在查询where子句中显式地加入NOT词,NOT仍在运算符中,见下例:


... where status <>'INVALID';

再看下面这个例子:


select * from employee where salary<>3000;

对这个查询,可以改写为不使用NOT:


select * from employee where salary<3000 or salary>3000;

虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引,而第一种查询则不能使用索引。

6. IN和EXISTS

有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。

第一种格式是使用IN操作符:


... where column in(select * from ... where ...);

第二种格式是使用EXIST操作符:


... where exists (select 'X' from ...where ...);

我相信绝大多数人会使用第一种格式,因为它比较容易编写,而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。

第二种格式中,子查询以‘select 'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句中使用的列存在索引)。相对于IN子句来说,EXISTS使用相连子查询,构造起来要比IN子查询困难一些。

通过使用EXIST,Oracle系统会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。Oracle系统在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。

同时应尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度),NOT EXISTS要比NOT IN查询效率更高。

=====================================================

Oracle的SQL调优是一个复杂的主题,甚至是需要整本书来介绍OracleSQL调优的细微差别。不过有一些基本的规则是每个OracleDBA都需要跟从的,这些规则可以改善他们系统的性能。SQL调优的目标是简单的:

  .消除不必要的大表全表搜索:不必要的全表搜索导致大量不必要的I/O,从而拖慢整个数据库的性能。调优专家首先会根据查询返回的行数目来评价SQL。在一个有序的表中,如果查询返回少于40%的行,或者在一个无序的表中,返回少于7%的行,那么这个查询都可以调整为使用一个索引来代替全表搜索。对于不必要的全表搜索来说,最常见的调优方法是增加索引。可以在表中加入标准的B树索引,也可以加入bitmap和基于函数的索引。要决定是否消除一个全表搜索,你可以仔细检查索引搜索的I/O开销和全表搜索的开销,它们的开销和数据块的读取和可能的并行执行有关,并将两者作对比。在一些情况下,一些不必要的全表搜索的消除可以通过强制使用一个index来达到,只需要在SQL语句中加入一个索引的提示就可以了。

  .在全表搜索是一个最快的访问方法时,将小表的全表搜索放到缓存中,调优专家应该确保有一个专门的数据缓冲用作行缓冲。在Oracle7中,你可以使用altertablexxxcache语句,在Oracle8或以上,小表可以被强制为放到KEEP池中缓冲。

  .确保最优的索引使用:对于改善查询的速度,这是特别重要的。有时Oracle可以选择多个索引来进行查询,调优专家必须检查每个索引并且确保Oracle使用正确的索引。它还包括bitmap和基于函数的索引的使用。

  .确保最优的JOIN操作:有些查询使用NESTEDLOOPjoin快一些,有些则是HASHjoin快一些,另外一些则是sort-mergejoin更快。

  这些规则看来简单,不过它们占SQL调优任务的100%,并且它们也无需完全懂得OracleSQL的内部运作。以下我们来简单概览以下OracleSQL的优化。

  我们首先简要查看Oracle的排序,并且看一看排序操作是如何影响性能的。

  调整Oracle的排序操作

  排序是SQL语法中一个小的方面,但很重要,在Oracle的调整中,它常常被忽略。当使用createindex、ORDERBY或者GROUPBY的语句时,Oracle数据库将会自动执行排序的操作。通常,在以下的情况下Oracle会进行排序的操作:

  使用Orderby的SQL语句

  使用Groupby的SQL语句

  在创建索引的时候

  进行tablejoin时,由于现有索引的不足而导致SQL优化器调用MERGESORT

  当与Oracle建立起一个session时,在内存中就会为该session分配一个私有的排序区域。如果该连接是一个专用的连接(dedicatedconnection),那么就会根据init.ora中sort_area_size参数的大小在内存中分配一个ProgramGlobalArea(PGA)。如果连接是通过多线程服务器建立的,那么排序的空间就在large_pool中分配。不幸的是,对于所有的session,用做排序的内存量都必须是一样的,我们不能为需要更大排序的操作分配额外的排序区域。因此,设计者必须作出一个平衡,在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序(disksorts)的同时,对于那些并不需要进行很大排序的任务,就会出现一些浪费。当然,当排序的空间需求超出了sort_area_size的大小时,这时将会在TEMP表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢14,000倍。

  上面我们已经提到,私有排序区域的大小是有init.ora中的sort_area_size参数决定的。每个排序所占用的大小由init.ora中的sort_area_retained_size参数决定。当排序不能在分配的空间中完成时,就会使用磁盘排序的方式,即在Oracle实例中的临时表空间中进行。

  磁盘排序的开销是很大的,有几个方面的原因。首先,和内存排序相比较,它们特别慢;而且磁盘排序会消耗临时表空间中的资源。Oracle还必须分配缓冲池块来保持临时表空间中的块。无论什么时候,内存排序都比磁盘排序好,磁盘排序将会令任务变慢,并且会影响Oracle实例的当前任务的执行。还有,过多的磁盘排序将会令freebufferwaits的值变高,从而令其它任务的数据块由缓冲中移走。
分享到:
评论

相关推荐

    Oracle Sql语句转换成Mysql Sql语句

    当需要将一个基于Oracle SQL的应用程序迁移到MySQL环境时,就需要进行SQL语句的转换工作。本项目提供了一个Java源码工具,能够帮助用户便捷地将Oracle SQL语句转换为MySQL SQL语句。 Oracle SQL与MySQL SQL的主要...

    oracle 查看当前会话执行的sql语句

    ### Oracle 查看当前会话执行的SQL语句 在Oracle数据库管理中,有时我们需要了解某个特定会话(Session)正在执行哪些SQL语句。这在性能调优、问题诊断等场景下尤为重要。以下将详细介绍如何通过不同的方法来查看...

    sqlserver自动生成sql语句工具sqlserver转oracle

    本篇文章将详细探讨如何利用工具实现SQL Server自动生成SQL语句并转换到Oracle。 首先,标题中的"sqlserver自动生成sql语句工具"指的是可以分析SQL Server数据库结构和数据,自动生成对应的SQL创建语句的软件。这种...

    oracle监听执行sql语句

    ### Oracle监听执行SQL语句详解 #### 一、Oracle监听执行概述 在Oracle数据库管理与维护过程中,有时候我们需要了解应用程序正在执行哪些SQL语句,这不仅有助于性能优化,还可以帮助我们诊断潜在的问题。通过监听...

    Oracle数据库sql语句 跟踪器

    Oracle数据库SQL语句跟踪器,通常被称为SQL Monitor,是一种强大的工具,用于监控和分析数据库中的SQL语句执行情况。在Oracle环境中,理解SQL语句的行为是优化数据库性能的关键。SQL Monitor提供实时视图,帮助DBA...

    oracle利用批处理文件执行SQL语句,bat连接oracle数据库并执行语句

    在这个场景中,我们可以利用批处理文件来简化Oracle数据库的操作,如删除用户、创建用户、创建表和触发器,以及执行其他SQL语句。 首先,我们需要了解如何在批处理文件中连接Oracle数据库。这通常通过Oracle的...

    压测Oracle的SQL语句的性能情况

    在IT行业中,数据库性能优化是至关重要的,...总的来说,对Oracle SQL语句进行压力测试是提升数据库性能的有效途径,它涉及到多个层面的技术和策略。理解并运用上述知识点,可以帮助我们构建高效、稳定的数据库系统。

    ORACLE常用SQL语句大全.pdf

    本文档总结了 Oracle 中常用的 SQL 语句,包括数据库的创建、删除、备份、表的创建、删除、修改、索引的创建、视图的创建等基本操作,以及一些高级查询运算符的使用。 一、数据库操作 * 创建数据库:CREATE ...

    oracle性能优化之SQL语句优化

    除了上述方法,还可以通过重构SQL语句、使用索引、避免全表扫描、减少子查询、优化联接顺序等方式进行SQL优化。例如,创建合适的索引可以加速查询,但过多的索引可能会增加插入、更新和删除操作的开销。因此,索引的...

    通过分析SQL语句的执行计划优化SQL

    本文档主要介绍了与SQL调整有关的内容,涉及多个方面:SQL语句执行的过程、ORACLE优化器、表之间的关联、如何得到SQL执行计划、如何分析执行计划等内容。通过从浅入深的方式了解SQL优化的过程,使大家逐步步入SQL...

    Oracle批处理:使用C# 自带Oracle驱动一次执行多条Sql语句

    Oracle批处理是数据库操作中提高效率的重要手段,尤其是在C#编程环境下,利用Oracle的数据驱动进行批处理,可以显著提升大量SQL语句执行的速度。本文将深入探讨如何在C#中利用Oracle自带的驱动来实现批处理,以一次...

    oracle Sql语句二

    oracle Sql语句二 oracle Sql语句二

    ORACLE数据库SQL语句美化器

    对ORACLE-SQL进行一些布局优化,更新它的格式

    oracle常用SQL语句(汇总版).docx

    Oracle 常用 SQL 语句汇总 Oracle 是一个功能强大且复杂的关系数据库管理系统,它提供了多种 SQL 语句来管理和操作数据库。在本文中,我们将详细介绍 Oracle 中常用的 SQL 语句,包括数据控制语句(DML)、数据定义...

    Oracle基本sql语句

    Oracle数据库是全球最广泛使用的数据库管理系统之一,它遵循SQL标准,提供了一套强大的SQL语句,使得数据库操作变得高效和灵活。在Oracle数据库中,SQL语句是与数据库进行交互的主要方式。本文将总结一些Oracle基本...

    oracle查看执行最慢与查询次数最多的sql语句

    通过分析这些数据,DBA可以识别出频繁执行且性能低效的SQL语句,进而采取优化措施,如调整索引、重构SQL语句或优化应用程序代码。在某些情况下,可能需要与开发团队合作,以确保业务逻辑的合理性。 总之,监控和...

    Oracle高性能SQL调整

    主要内容包括理解SQL调整在oracle总体微调中的地位,使用诸如内嵌视图和bif扩展提高oracle SQL性能,确定并报告程序库缓存中的SQL语句,调整SQL表访问、完整表描述和平行查询,运行TYPEOF获得SQL跟踪报告,使用...

    oracle消耗资源的sql查询语句记录

    通过对Oracle数据库中消耗资源较多的SQL语句进行监控和优化,可以显著提高系统的整体性能。上述SQL查询语句仅为一种基础方法,实际工作中还需要结合具体的业务场景和技术需求来进行综合分析与优化。此外,Oracle...

    Oracle常用的SQL语句维护

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统之...通过上述SQL语句和操作,你可以有效地维护Oracle数据库的表空间,并进行日常的SQL运维工作。不断学习和实践,将有助于提升你在Oracle SQL领域的技能和经验。

    Oracle实验二-SQL语句综合应用

    在本实验中,我们主要探讨了Oracle数据库中的SQL语句应用,涉及到了表、视图、索引以及序列等核心概念。以下是对这些知识点的详细解释: 1. **创建表**: - 使用`CREATE TABLE`语句创建了一个名为`person`的表,...

Global site tag (gtag.js) - Google Analytics