当前位置：文档库 › Oracle 10g数据库中新的SQL优化介绍

Oracle 10g数据库中新的SQL优化介绍

Oracle使用的hints调整机制一直很复杂，Oracle Technical Network对使用hints调整Oracle SQL的过程有很好的全面评述。根据对10g数据库的介绍，可使用更多新的optimizer hints来控制优化行为。现在让我们迅速了解一下这些强大的新hints:

spread_min_analysis

使用这一hint，你可以忽略一些关于如详细的关系依赖图分析等电子表格的编译时间优化规则。其他的一些优化，如创建过滤以有选择性的定位电子表格访问结构并限制修订规则等，得到了继续使用。

由于在规则数非常大的情况下，电子表格分析会很长。这一提示可以帮助我们减少由此产生的数以百小时计的编译时间。

例如:SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

spread_no_analysis

通过这一hint，可以使无电子表格分析成为可能。同样，使用这一hint可以忽略修订规则和过滤产生。如果存在一电子表格分析，编译时间可以被减少到最低程度。

例如:SELECT /*+ SPREAD_NO_ANALYSIS */ ...

use_nl_with_index

这项hint使CBO通过嵌套循环把特定的表格加入到另一原始行。只有在以下情况中，它才使用特定表格作为内部表格:如果没有指定标签，CBO必须可以使用一些标签，且这些标签至少有一个作为索引键值加入判断;反之，CBO必须能够使用至少有一个作为索引键值加入判断的标签。

例如:SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...

CARDINALITY

此hint定义了对由查询或查询部分返回的基数的评价。注意如果没有定义表格，基数是由整个查询所返回的总行数。

例如:SELECT /*+ CARDINALITY ( [tablespec] card ) */

SELECTIVITY

此hint定义了对查询或查询部分选择性的评价。如果只定义了一个表格，选择性是在所定义表格里满足所有单一表格判断的行部分。如果定义了一系列表格，选择性是指在合并以任何顺序满足所有可用判断的全部表格后，所得结果中的行部分。

例如:SELECT /*+ SELECTIVITY ( [tablespec] sel ) */

然而，注意如果hints CARDINALITY 和 SELECTIVITY都定义在同样的一批表格，二者都会被忽略。

no_use_nl

Hint no_use_nl使CBO执行循环嵌套，通过把指定表格作为内部表格，把每个指定表格连接到另一原始行。通过这一hint，只有hash join和sort-merge joins会为指定表格所考虑。

例如:SELECT /*+ NO_USE_NL ( employees ) */ ...

no_use_merge

此hint使CBO通过把指定表格作为内部表格的方式，拒绝sort-merge把每个指定表格加入到另一原始行。

例如:SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...

no_use_hash

此hint使CBO通过把指定表格作为内部表格的方式，拒绝hash joins把每个指定表格加入到另一原始行。

例如:SELECT /*+ NO_USE_HASH ( employees dept ) */ ...

no_index_ffs

此hint使CBO拒绝对指定表格的指定标签进行fast full-index scan。

Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */

no_index_ss

此hint使CBO拒绝对指定表格的指定标签进行skip scan。

Syntax: /*+ NO_INDEX_SS (tablespecindexspec ) */

no_star_transformation

此hint使CBO忽略star 询问信息。

Syntax: /*+ NO_STAR_TRANSFORMATION */

index_ss

此hint明确地为指定表格选择index skip scan。如果语句使用index range scan，Oracle将以对其索引值的升序排列来检查索引入口。在被分割的索引中，其结果为对每个部分内部的升序排列。

Syntax: /*+ INDEX_SS (tablespecindexspec) */

index_ss_asc

Syntax: /*+ INDEX_SS_ASC (tablespecindexspec) */

index_ss_desc

此hint明确为指定表格选择index skip scan。如果语句使用index range scan，Oracle将以对其索引值的降序排列来检查索引入口。在被分割的索引中，其结果为对每个部分内部的降序排列。

Syntax: /*+ INDEX_SS_DESC (tablespecindexspec) */

cpu_costing

此hint为SQL语句打开CPU costing。这是对优化器的默认评估模式。优化器评估当执行给定查询时，数据库需要运行的IO操作数、IO操作种类、以及CPU周期数。

Syntax: /*+ CPU_COSTING (tablespecindexspec) */

no_cpu_costing

此hint为SQL语句关闭CPU costing。然后CBO使用IO cost模式，此模式忽略CPU花费，仅测量在single-block reads中的所有指标。

Syntax: /*+ NO_CPU_COSTING */

随着Oracle优化器越来越成熟，Oracle专家必须不断增加自己对调整SQL语句的工具储备。

来源：网络编辑：联动北方技术论坛

OracleSQL的优化

Oracle SQL的优化标签：oraclesql优化date数据库subquery 2009-10-14 21:18 18149人阅读评论(21) 收藏举报分类： Oracle Basic Knowledge（208） SQL的优化应该从5个方面进行调整： 1.去掉不必要的大型表的全表扫描 2.缓存小型表的全表扫描 3.检验优化索引的使用 4.检验优化的连接技术 5.尽可能减少执行计划的Cost SQL语句：是对数据库(数据)进行操作的惟一途径；消耗了70%~90%的数据库资源；独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；可以有不同的写法；易学，难精通。 SQL优化：固定的SQL书写习惯，相同的查询尽量保持相同，存储过程的效率较高。应该编写与其格式一致的语句，包括字母的大小写、标点符号、换行的位置等都要一致 ORACLE优化器：在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是要么结果表达式能够比源表达式具有更快的速度要么源表达式只是结果表达式的一个等价语义结构不同的SQL结构有时具有同样的操作（例如： = ANY (subquery) and IN (subquery)），ORACLE会把他们映射到一个单一的语义结构。 1 常量优化：常量的计算是在语句被优化时一次性完成，而不是在每次执行时。下面是检索月薪大于2000的的表达式： sal > 24000/12

sal > 2000 sal*12 > 24000 如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。否则没有办法优化，比如如果sal上有索引，第一和第二就可以使用，第三就难以使用。 2 操作符优化：优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。例如：优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH' 优化器只能转换涉及到可变长数据类型的表达式，前一个例子中，如果ENAME 字段的类型是CHAR(10)，那么优化器将不做任何转换。一般来讲LIKE比较难以优化。其中： ~~IN 操作符优化：优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式。例如，优化器会把表达式ename IN ('SMITH','KING','JONES')替换为 ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘ oracle 会将 in 后面的东西生成一存中的临时表。然后进行查询。如何编写高效的SQL: 当然要考虑sql常量的优化和操作符的优化啦，另外，还需要： 1 合理的索引设计：例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情况：语句A SELECT count(*) FROM record WHERE date >'19991201' and date <'19991214‘and amount >2000 语句B

2020年(Oracle管理)如何优化SQL语句以提高Oracle执行效率

（Oracle管理）如何优化SQL语句以提高Oracle执行效率

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表drivingtable)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。（2）WHERE子句中的连接顺序： Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。（3）SELECT子句中避免使用‘*’： Oracle在解析的过程中,会将‘*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。（4）减少访问数据库的次数： Oracle在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等。（5）在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200。（6）使用DECODE函数来减少处理时间：使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。（7）整合简单,无关联的数据库访问：如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)。（8）删除重复记录：最高效的删除重复记录方法(因为使用了ROWID)例子：DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)

大型ORACLE数据库优化设计方案

大型ORACLE数据库优化设计方案摘要主要从大型数据库ORACLE环境四个不同级别的调整分析入手，分析ORACLE的系统结构和工作机理，从九个不同方面较全面地总结了ORACLE数据库的优化调整方案。关键词ORACLE数据库环境调整优化设计方案对于ORACLE数据库的数据存取，主要有四个不同的调整级别，第一级调整是操作系统级包括硬件平台,第二级调整是ORACLERDBMS级的调整,第三级是数据库设计级的调整,最后一个调整级是SQL级。通常依此四级调整级别对数据库进行调整、优化，数据库的整体性能会得到很大的改善。下面从九个不同

方面介绍ORACLE数据库优化设计方案。一.数据库优化自由结构OFA(OptimalflexibleArchitecture) 数据库的逻辑配置对数据库性能有很大的影响,为此,ORACLE公司对表空间设计提出了一种优化结构OFA。使用这种结构进行设计会大大简化物理设计中的数据管理。优化自由结构OFA,简单地讲就是在数据库中可以高效自由地分布逻辑数据对象,因此首先要对数据库中的逻辑对象根据他们的使用方式和物理结构对数据库的影响来进行分类,这种分类包括将系统数据和用户数据分开、一般数据和索引数据分开、低活动表和高活动表分开等等。二、充分利用系统全局区域SGA （SYSTEMGLOBALAREA) SGA是oracle数据库的心脏。用户的进程对这个内存区发送事务，并且以这里作为高速缓存读取命中的数据，以实现加速的目的。正确的SGA大小对数据库

的性能至关重要。SGA包括以下几个部分： 2、字典缓冲区。该缓冲区内的信息包括用户账号数据、数据文件名、段名、盘区位置、表说明和权限，它也采用LRU 方式管理。 3、重做日志缓冲区。该缓冲区保存为数据库恢复过程中用于前滚操作。 4、SQL共享池。保存执行计划和运行数据库的SQL语句的语法分析树。也采用LRU算法管理。如果设置过小，语句将被连续不断地再装入到库缓存，影响系统性能。另外，SGA还包括大池、JA V A池、多缓冲池。但是主要是由上面4种缓冲区构成。对这些内存缓冲区的合理设置，可以大大加快数据查询速度，一个足够大的内存区可以把绝大多数数据存储在内存中，只有那些不怎么频繁使用的数据，才从磁盘读取，这样就可以大大提高内存区的命中率。三、规范与反规范设计数据库

oraclesql优化笔记

基本的Sql 编写注意事项尽量少用IN 操作符，基本上所有的IN 操作符都可以用EXISTS 代替。不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。 Oracle 在执行IN 子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。NOT EXISTS:匕NOT IN效率稍高。但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。不用“<>”或者“ !=”操作符。对不等于操作符的处理会造成全表扫描，可以用“ <” or “>”代替。 Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOT NULL这样就可以用其他操作来取代判断NULL的操作。当通配符“ %”或者“ _”作为查询字符串的第一个字符时，索引不会被使用。对于有连接的列“ || ”，最后一个连接列索引会无效。尽量避免连接，可以分开连接或者使用不作用在列上的函数替代。如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。 Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。对数据类型不同的列进行比较时，会使索引失效。

用“ >=”替代“ >”。 UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。如果不需要删除重复记录，应该使用UNION ALL。 Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。 Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。（只在采用RBO 优化时有效，下文详述） Order By 语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By 语句中使用表达式。不同区域出现的相同的Sql 语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。多利用内部函数提高Sql 效率。当在Sql 语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。需要注意的是，随着Oracle 的升级，查询优化器会自动对Sql 语句进行优化，某些限制可能在新版本的Oracle 下不再是问题。尤其是采用CBO （Cost-Based Optimization ，基于代价的优化方式）时。我们可以总结一下可能引起全表扫描的操作：

OracleSQL性能优化方法

OracleSQL性能优化方法 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访咨询Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访咨询数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访咨询 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14运算记录条数 (9) 15用Where子句替换HA VING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11) 18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)

1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及储备参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存体会，从而更好地进行数据库的调优。数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用咨询题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用关于海量数据的表，能够考虑建立分区以提高操作效率。建立分区一样以关键字为分区的标志，也能够以其他字段作为分区的标志，但效率不如关键字高。建立分区的语句在建表时能够进行讲明： create table TABLENAME() partition by range (PutOutNo) (partition PART1 values lessthan (200312319999) partition PART2 values lessthan (200412319999) 。。。。。。如此，在进行大部分数据查询，数据更新和数据插入时，Oracle自动判定操作应该在哪个分区进行，幸免了整表操作，提高了执行的效率

oracle数据库优化报告

目录 1、概述 (3) 2、数据库优化部分 (3) 2.1、环境优化 (3) 2.1.1 统计信息收集被关闭 (3) 2.1.2 部分索引失效 (4) 2.2、设计优化 (4) 2.2.1 设计类问题概述 (4) 2.2.2 设计类问题优化建议 (5) 2.3、SQL优化 (5) 2.3.1 SQL_ID= 7gf3typgc469a (5) 2.3.2 SQL_ID= bdcfdz26x5hm9 (6) 3、数据库优化总结 (7)

1、概述随着应用软件用户负载的增加和愈来愈复杂的应用环境，操作系统的各项性能参数、数据库的使用效率、用户的响应速度、系统的安全运行等性能问题逐渐成为系统必须考虑的指标之一。性能测试以及优化通常通过自动化的测试工具模拟多种正常、峰值以及异常负载条件来对系统的各项性能指标进行测试，用来检测系统是否达到用户提出的性能指标，及时发现系统中存在的瓶颈，最后起到优化系统的目的。随着需求不断增加，特别是复杂逻辑的需求，一旦出现高并发量时，也将可能导致数据库主机无法承载，因此数据库优化亟待解决。 2、数据库优化部分从2018年1月份开始跟踪及分析，发现托管区数据库在环境、设计及SQL三方面，都存在不少问题。在SQL类优化中，本地化代码编写和设计不良，是比较明显的问题。下面将分成环境、设计、SQL优化三类进行持续分析，并给出相关建议、整改方案、整改进度。 2.1、环境优化 2.1.1 被关闭 zonghe托管区数据库统计信息未自动收集，如果未打开收集，会对系统性能造成较大的影响。

需要开启统计信息开启方法如下： --执行 BEGIN dbms_auto_task_admin.enable(client_name => 'auto optimizer statscollection', operation => NULL, window_name =>NULL); END; 2.1.2 部分索引失效需要将索引进行删除。删除命令参考如下： drop index index_name; 2.2、设计优化 2.2.1 设计类问题概述序号类型问题描述 1 表 ZJ_KZH_DATE 、ZJ_CRM_S_ORDER_GATHER 等本地表，设计了大量的V1，V2，需要开发人员核对需求 2 索引索引定义较混乱，常与其他表进行连接的表，在连接

Oracle SQL性能优化方法研究

Oracle SQL性能优化方法探讨 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访问Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访问数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访问 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14计算记录条数 (9) 15用Where子句替换HAVING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11)

18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)

1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及存储参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存经验，从而更好地进行数据库的调优。数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用问题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用关于海量数据的表，能够考虑建立分区以提高操作效率。建

Oracle_SQL规范与优化

1.性能优化 ●【规则6】尽量避免相同语句由于书写格式的不同，而导致多次语法分析。 ●【规则7】尽量使用共享的SQL语句，也就是说，在SQL中尽量采用绑定变量的方式，而不是常量； ●【规则8】尽量不使用“SELECT *”这样的语句，即使需要查询表中的所有行，也需列出所有的字段名； ●【规则9】尽量避免4个以上表的链表操作，例如：A = B and B = C and C = D，如果业务上需要，可以考虑通过中间表的方式进行变通； ●【规则9】大量的排序操作影响系统性能，所以尽量减少order by和group by排序操作。如必须使用排序操作，请遵循如下规则：（1）排序尽量建立在有索引的列上。（2）如结果集不需唯一，使用union all代替union。 ●【规则10】系统可能选择基于规则的优化器，所以将结果集返回数据量小的表作为驱动表（from后边最后一个表）。说明：驱动表的选择和很多的因素有关系，不仅仅是表的顺序，这点仅做参考，不过养成这个习惯有助于以后进行SQL的优化。 ●【规则11】索引的使用。（1）尽量避免对索引列进行计算。（2）尽量注意比较值与索引列数据类型的一致性，避免使用数据库的类型自动转换功能（3）对于复合索引，SQL语句必须使用主索引列（4）索引中，尽量避免使用NULL。（5）对于索引的比较，尽量避免使用NOT=（!=）（6）查询列和排序列与索引列次序保持一致 ●【规则12】查询的WHERE过滤原则，应使过滤记录数最多的条件放在最前面。 ●【规则13】使用%TYPE、%ROWTYPE方式声明变量，使变量声明的类型与表中的保持同步 ●【规则14】在IF/ELSE查询中，使用DECODE ●【规则15】在SQL 中使用WHERE 子句过滤数据，而不是在程序中到处使用它进行过滤 ●【规则16】执行动态SQL，建议用execute immediate SQL子句； ●【规则17】尽量避免使用union，若需要排重，建议使用from 子句把查询结果union all 起来后，再通过group by 排重，如： SELECT id FROM ( SELECT id FROM a UNION ALL SELECT id

确保Oracle数据库sql语句高效执行的优化总结(一)

SQL 的优化主要涉及几个方面：（1）相关的统计信息缺失或者不准确（2）索引问题（3）SQL的本身的效率问题，比如使用绑定变量，批量DML 采用bulk等，这个就考验写SQL的基本功了，这一点也是最主要的一点。一、SQL 编写注意事项 1.1 查看SQL 对于生产环境上的SQL,可以从AWR 或者Statspack 报告中获取相关的SQL信息。这部分参考：查看SQL 的性能怎么样，最直接的工具就是通过执行计划，通过执行计划可以看到SQL 的执行路径，逻辑读，物理读等信息，可以这些信息，可以帮助我们判断SQL 是否还有优化的余地。 1.2 SQL 编写的具体注意事项这部分工作是基本功。在SQL 编写过程中，避免一些低效的写法，能将SQL 的效率提高几倍。如：与使用TRUNC 相比，使用TO_CHAR 所用的CPU 时间与前者相差一个数量级（即相差12倍）。因为TO_CHAR 必须把日期转换为一个串，这要使用一

个更大的代码路径，并利用当前的所有NLS来完成这个工作。然后必须执行一个串与串的比较。另一方面，TRUNC 只需把后5 个字节设置为1.然后将两个7 字节的二进制数进行比较。因此，如果只是要截断一个DATE 列，你将应该避免使用TO_CHAR。 1.3 多表关联方式表之间的关联有如下三种方式：（1）Nested Loop Inner table 循环与outer table匹配，这种是表有索引，选择性较好，表之间的差距不大。===》两层for 循环，小表匹配大表。（2）Hash John 小表做hash ，放内存，然后拿大表的每条记录做hash，然后与之前小表的Hash 值匹配。==》大表匹配小表。（3）Sorted Merge Into 表有序，并且没有索引。二. 相关理论说明 2.1 Oracle 优化器：CBO 和RBO Oracle 的优化器有两种：（1）RBO(Rule-BasedOptimization): 基于规则的优化器（2）CBO(Cost-BasedOptimization): 基于代价的优化器

Oracle_SQL性能优化技巧大总结

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. （2） WHERE子句中的连接顺序．： ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾. （3） SELECT子句中避免使用 * ： ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间（4）减少访问数据库的次数： ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等；（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200 （6）使用DECODE函数来减少处理时间：使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. （7）整合简单,无关联的数据库访问：如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系) （8）删除重复记录：最高效的删除重复记录方法 ( 因为使用了ROWID)例子： DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO); （9）用TRUNCATE替代DELETE：当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML) （10）尽量多使用COMMIT：只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求

Oracle中sql优化原则

Oracle中sql优化原则公布时刻：2006.05.17 01:31来源：不详作者：kingshare 1。差不多检验的语句和已在共享池中的语句之间要完全一样 2。变量名称尽量一致 3。合理使用外联接 4。少用多层嵌套 5。多用并发语句的优化步骤一样有： 1。调整sga区，使得sga区的是用最优。 2。sql语句本身的优化，工具有explain,sql trace等 3。数据库结构调整 4。项目结构调整写语句的体会： 1。关于大表的查询使用索引 2、少用in,exist等 3、使用集合运算１．关于大表查询中的列应尽量幸免进行诸如Ｔｏ＿ｃｈａｒ，ｔｏ＿ｄａｔｅ，ｔｏ＿ｎｕｍｂｅｒ等转换２．有索引的尽量用索引，有用到索引的条件写在前面如有可能和有必要就建立一些索引３．尽量幸免进行全表扫描，限制条件尽可能多，以便更快搜索到要查询的数据如何让你的SQL运行得更快交通银行长春分行电脑部任亮 ---- 人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。笔者在工作实践中发觉，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！下面我将从这三个方面分不进行总结： ---- 为了更直观地讲明咨询题，所有实例中的SQL运行时刻均通过测试，不超过１秒的均表示为（< 1秒）。

---- 测试环境-- ---- 主机：HP LH II ---- 主频：330MHZ ---- 内存：128兆 ---- 操作系统：Operserver5.0.4 ----数据库：Sybase11.0.3 一、不合理的索引设计 ----例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情形： ---- 1.在date上建有一非个群集索引 select count(*) from record where date > 19991201 and date < 19991214and amount > 2000 (25秒) select date,sum(amount) from record group by date (55秒) select count(*) from record where date > 19990901 and place in (BJ,SH) (27秒) ---- 分析： ----date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在范畴查找时，必须执行一次表扫描才能找到这一范畴内的全部行。 ---- 2.在date上的一个群集索引 select count(*) from record where date > 19991201 and date < 19991214 and amount > 2000 （14秒） select date,sum(amount) from record group by date （28秒） select count(*) from record where date > 19990901 and place in (BJ,SH)（14秒） ---- 分析： ---- 在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范畴查找时，能够先找到那个范畴的起末点，且只在那个范畴内扫描数据页，幸免了大范畴扫描，提高了查询速度。 ---- 3.在place，date，amount上的组合索引 ---- 4.在date，place，amount上的组合索引 ---- 5.总结： ---- 缺省情形下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要建立

ORACLE+SQL性能优化系列

ORACLE SQL性能优化系列 0ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQ L句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statist ics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 1.访问Table的方式 ORACLE 采用两种访问表中记录的方式: a.全表扫描全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(dat abase block)的方式优化全表扫描. b.通过ROWID访问表

你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高. 2.共享SQL语句为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared bu ffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE 就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询. 数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等). 共享的语句必须满足三个条件: A. 字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM EMP;

ORACLE性能优化之SQL优化-优化器

Oracle9i优化器介绍 By Davis E-Mail:todavis@https://www.wendangku.net/doc/cd16582951.html, Blog:https://www.wendangku.net/doc/cd16582951.html, 选择合适的优化器目标默认情况下，CBO 以最佳吞吐量为目标，这意味着Oracle 使用尽可能少的资源去处理被语句访问到的所有行；当然CBO 也可以用最快的响应速度来优化SQL，这意味着Oracle 用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句来说可能消耗更多的资源。优化器产生的执行计划会因―优化器目标‖的不同而不同。如果以最佳吞吐量为目标，结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接；如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环连接。例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。如果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器目标是提高响应速度，则优化器倾向于选择嵌套循环连接。选择优化器目标要以你的应用为基础，一般规则是： 1、对于批处理应用，以最佳吞吐量为优化目标为好。例如Oracle 报表应用程序。 2、对于交互式应用，以最快响应速度为优化目标为好。例如SQLPLUS 的查询。影响优化器优化目标的因素主要有： 1、OPTIMIZER_MODE 初始化参数。 2、数据字典中的CBO 统计数据。 3、用来改变CBO 优化目标的Hints。 OPTIMIZER_MODE初始化参数这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下： Value CHOOSE ALL_ROWS Description 此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。 1、如果在被访问的表中，至少有一个表在数据字典中有可用的统计信息存在，则优化器使用基于成本的方法。 2、如果在被访问的表中，只有部分表在数据字典中有可用的统计信息，优化器仍然会使用基于成本的方法，但是优化器必须为无统计信息的表利用一些内部信息去尝试其他的统计，比如分配给这些表的数据块的数量等，这可能会导致产生不理想的执行计划。 3、如果在被访问的表中，没有一个表在数据字典中有统计信息，则优化器使用基于规则的方法。不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞 1

oracle sql性能优化题目

1.下面哪些是sql语句处理过程ABCD （A）分析（B）优化（C）行资源生成（D）执行 2.sql语句在分析过程中要进行哪些操作？ABC （A）语法分析（B）语义分析（C）如果是DML，还有共享池检查（D）优化 3.下面对索引的描述哪些是正确的ABCD （A）类似书的目录结构（B）可以提高sql的查询速度（C）会降低insert、update、delete的速度（D）与所索引的表是互相独立的物理结构（E）储存null 4.索引有哪几种扫描方式ABCDE （A）唯一索引扫描（B）索引范围扫（C）索引跳跃扫描（D）索引全扫描（E）索引快速扫描 5.下列哪些属于索引的类型：ABCD （A）B-tree索引（B）函数索引（C）全局索引（D）本地索引 6.下列对建立索引说法正确的是：AD （A）where后面的条件具备建立索引的先天条件（B）索引的列越多越好（C）所有的列都可以建立索引（D）哪个列能快速定位数据，那么那个列就是建立索引的列 7.一般来说2张表连接有哪几种方式？ABC （A）NESTED LOOPS（B）HASH JOIN（C）SORT MERGE JOIN（D）FULL JOIN 8.对NESTED LOOPS表连接来说，下面哪些说法是正确的：AC （A）drivingrowsource(外部表)比较小（B）只能用于等值连接中（C）innerrowsource(内部表) 有高选择率的索引

（D）连接之前需要排序 9.sql 在数据库共享池中能否共享的说法哪些是正确的？ACD （A）sql必须是同一个用户执行的（B）执行的sql不区分大小写（C）执行的sql所处的当时的数据库环境必须是一样的（D）同样的sql生成的HASH值一定是一样的 10.以下对绑定变量的说法正确的是：ABD （A）绑定变量能减少硬解析的次数（B）绑定变量有的时候会引起执行计划的错误选择（C）绑定变量不会带来性能问题（D）对数据分布很不均匀的列不适合使用绑定变量 12.下面哪些方法可以取得sql的执行计划ABCD （A）PL/SQL DEVELOP 按F5 （B）Dbms_xplan.display_cursor （C）查询视图v$sql_plan （D）SET AUTOTRACE ON 18.下面哪些sql的写法是可能会造成性能问题的（where条件的字段均有索引）？ABCD （A）SELECT * FROM T_NULL WHERE OBJECT_ID ISNULL; （B）SELECT * FROM A_PAY_FLOW WHERE C.SETTLE_MODE=NVL(:B1,C.SETTLE_MODE) ; （C）SELECT * FROM A_CASHCHK WHERE TO_CHAR(RELATE_NO)=TO_CHAR(:B4); （D）SELECTCOUNT(*) FROM S_REGION_OUTGE WHERESYSDATE-A.START_TIME<=30; 19.下面哪些sql的写法是可能会造成性能问题的（where条件的字段均有索引）？ABCDE （A）SELECTCOUNT(*) FROM O_ORG WHERE SCCIFSTATORG(ORG_NO, ‘02’) =1; （B）SELECT T1.OWNER, T1.OBJECT_ID, F_GETNAME(OBJECT_ID) FROM T_FROM1 T1 WHERE OBJECT_ID <2000; （C）SELECT * FROM S_APP WHERE CONS_NAME LIKE‘%’||:2||’%’ （D）SELECTMIN(OBJECT_ID),MAX(OBJECT_ID) FROM T1; （E）INSERTINTOTABLESELECT XXXX FROM DUAL;

Oracle数据库性能优化(碎片整理)

1系统问题 XX公司BI系统上线运行以来，客户反映系统目前存在着下面的几个问题，涉及到数据库和ETL. 问题一：表空间增长太快，每个月需增加3—5G空间。问题二：ETL JOB会经常导致数据库产生表空间不足错误。 2系统优化分析 2.1分析思路要解决表空间的问题，我们必须搞清楚下面几个问题：思路一：真正每个月数据仓库增量是多少空间？目的：得出一个正确的月表空间增长量。思路二：目前的数据仓库表空间是是如何分布的。目的：找出那些对象是最占空间，分析其合理性。 2.2分析过程要得到真实的数据分布必须对表进行分析，首先需要对数据仓库的oracle数据库进行表分析，。执行下面脚本可以对数据库进行表分析。脚本一 analyze table SA_IMS_PRODUCT_GROUP compute statistics; analyze table SA_CONSUMP_ACT_DEL compute statistics; analyze table SA_FINANCE_ACT compute statistics;

analyze table SA_CONSUMP_TGT_DEL compute statistics; analyze table SA_FACT_IS compute statistics; analyze table SA_CPA compute statistics; analyze table SA_REF_TERR_ALIGNMENT_DEL compute statistics; analyze table SA_IMS_MTHLC_BK compute statistics; analyze table SA_IMS_CHPA compute statistics; analyze table SA_FINANCE_PNL compute statistics; analyze table SA_CUST_TARG_SEG compute statistics; analyze table SA_CONSUMP_ACT compute statistics; analyze table SA_FINANCE_BS compute statistics; analyze table SA_FINANCE_BGT_QTY compute statistics; analyze table SA_CONSUMP_ACT0423 compute statistics; analyze table SA_CALLS compute statistics; analyze table SA_COMPANY_DAILY_SALES_ALL compute statistics; analyze table SA_IMS_MTHLC compute statistics; analyze table SA_IMS_MTHUS compute statistics; analyze table SA_CONSUMP_TGT compute statistics; analyze table TEST_TABLE compute statistics; analyze table SA_DOCTOR_CYCLE_EXTRACT compute statistics; analyze table SA_EXCHANGE_ACT compute statistics; analyze table SA_IMS_MTHST compute statistics; analyze table SA_FINANCE_CONCUR_DETAIL compute statistics; analyze table WK_SA_CPA compute statistics; analyze table SA_REF_TERR_ALIGNMENT compute statistics; analyze table SA_CONSUMP_TGT0316 compute statistics; analyze table SA_CUSTOMER compute statistics; analyze table SA_CUST compute statistics; analyze table SA_HKAPI compute statistics; analyze table SA_CONSUMP_TGT_AMT compute statistics; analyze table SA_CUST0423 compute statistics; analyze table SA_COMMUNITY_TGT compute statistics; analyze table SA_CM_WORKING_DATE compute statistics; analyze table SA_CM_IN_MARKET_SALES_CU compute statistics; analyze table SA_DASH_SFE compute statistics; analyze table SA_CPA_TERR compute statistics; analyze table IDX_SA_CUST compute statistics; analyze table SA_REF_EMP_TERR compute statistics; analyze table SA_CM_IN_MARKET_SALES_OCM compute statistics; analyze table SA_COMPANY_MONTHLY_SALES compute statistics; analyze table SA_MAP_YEARMONTH_RATE compute statistics; analyze table SA_FINANCE_ACT_BPCS_TEST compute statistics; analyze table SA_REF_EMP_TERR0413 compute statistics; analyze table SA_FINANCE_ACT_BPCS compute statistics; analyze table IDX$$_143D0001 compute statistics;