oracle查询优化--修改执行计划_20090425
Oracle的执行计划查看方法
Oracle的执行计划查看方法Oracle在执行一个SQL之前，首先需要看一下SQL的执行计划，然后在按照执行计划执行SQL，分析执行计划的工作是由优化器来执行的，在不同的条件下，一个SQL可能存在多条执行计划，但是在某个特定的时间点，特定的环境下，一定只有一种执行计划是最优的。
1、 Oracle的优化方式Oracle有两种优化方式：RBO(Rule-Based Optimization)和CBO(Cost-Based Optimization)RBO: 优化器在分析SQL语句的时候所遵循的优化规则是基于Oracle内部预定的一些规则。
CBO: 优化器字分析SQL语句的时候主要是看语句执行的开销(Cost)，这里的代价主要指CPU和内存，优化器在判断是否使用这种方式的时候，主要参照的是表及索引的统计信息。
统计信息给出表的大小，有多少行，每行的长度等信息。
2、优化器的优化模式优化模式包括：Rule, Choose, First Rows, All Rows四种模式。
Rule：走基于规则的模式。
Choose：默认情况下Oracle使用这种优化模式，当一个表或索引有统计信息的时候，走CBO的方式；如果表或索引没有统计信息，表又不是特别的小，且相应的字段有索引，则走RBO的方式。
在缺省情况下，ORACLE采取Choose的优化模式，为了避免不必要的Full Table Scan，尽量避免采取此种优化模式，而直接采取CBO或者RBO的优化模式。
First Rows：它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。
All Rows：也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。
没有统计信息则走基于规则的方式。
需要注意的是：First Rows和All Rows是有冲突的。
oracle优化方案
千里之行，始于足下。
oracle优化方案Oracle优化方案Oracle数据库是当今企业界最受欢迎的关系型数据库管理系统之一。
但是，随着数据量的不断增加和业务需求的不断增长，数据库的性能问题也会逐渐变得突出。
因此，对Oracle数据库进行优化是提高系统性能和运行效率的关键。
本文将介绍几个常见的Oracle数据库优化方案，帮助您更好地管理和优化您的数据库环境。
1. 索引优化索引是提高查询性能的关键。
可以通过以下几个方面对索引进行优化：（1）合理选择索引类型：根据查询的特点和数据分布选择合适的索引类型，如B-tree索引、位图索引等。
（2）避免过多的索引：过多的索引会增加数据插入、更新和删除的成本，并降低查询性能。
只保留必要的索引，可以有效提高性能。
（3）定期重建和重新组织索引：定期重建和重新组织索引可以提高索引的查询效率，减少碎片和冗余。
2. SQL优化SQL语句是Oracle数据库的核心，对SQL进行优化可以显著提高数据库的性能。
以下是一些SQL优化的建议：第1页/共3页锲而不舍，金石可镂。
（1）优化查询语句：避免使用不必要的子查询，尽量使用连接查询代替子查询，减少查询次数。
同时，避免使用全表扫描，可以通过创建合适的索引来提高查询效率。
（2）避免使用不必要的OR运算符：OR运算符的查询效率较低，应尽量避免使用。
可以通过使用UNION或UNION ALL运算符代替OR运算符来提高性能。
（3）避免使用ORDER BY和GROUP BY子句：ORDER BY和GROUP BY子句会造成排序和分组操作，对于大数据集来说是非常耗时的。
如果可能，可以考虑使用其他方式来实现相同的功能。
3. 系统资源优化合理配置和管理系统资源是确保数据库运行稳定和高效的重要因素。
以下是一些建议：（1）合理分配内存：根据系统和数据库的实际需求，合理分配内存资源。
调整SGA（System Global Area）区域的大小，确保适当的内存分配给缓冲池和共享池。
查看ORACLE执行计划的几种常用方法
查看ORACLE执⾏计划的⼏种常⽤⽅法SQL的执⾏计划实际代表了⽬标SQL在Oracle数据库内部的具体执⾏步骤，作为调优，只有知道了优化器选择的执⾏计划是否为当前情形下最优的执⾏计划，才能够知道下⼀步往什么⽅向。
执⾏计划的定义：执⾏⽬标SQL的所有步骤的组合。
我们⾸先列出查看执⾏计划的⼀些常⽤⽅法：1. explain plan命令PL/SQL Developer中通过快捷键F5就可以查看⽬标SQL的执⾏计划了。
但其实按下F5后，实际后台调⽤的就是explain plan命令，相当于封装了该命令。
explain plan使⽤⽅法：(1) 执⾏explain plan for + SQL(2) 执⾏select * from table(dbms_xplan.display);实验表准备：SQL> desc test1;Name Null Type----------------------------------------- -------- ----------------------------T1ID NOT NULL NUMBER(38)T1V VARCHAR2(10)SQL> desc test2;Name Null Type----------------------------------------- -------- ----------------------------T2ID NOT NULL NUMBER(38)T2V VARCHAR2(10)实验：SQL> set linesize 100SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;Explained.第⼀步使⽤explain plan对⽬标SQL进⾏了explain，第⼆步使⽤select * from table(dbms_xplan.display)语句展⽰出该SQL的执⾏计划。
oracle执行计划优化深入解读
oracle执行计划优化深入解读优化执行计划是Oracle数据库性能优化的重要步骤之一。
执行计划是Oracle查询优化器生成的一组指令，告诉数据库引擎如何执行查询语句。
通过深入解读和优化执行计划，可以显著提高查询性能。
首先，了解执行计划的基本原理是十分重要的。
执行计划是由优化器生成的，它是一种指导数据库引擎在执行查询语句时的操作顺序和方法的方式。
了解执行计划中的各种操作符（如全表扫描、索引扫描、连接操作等）以及它们的执行顺序对于优化执行计划至关重要。
在深入解读执行计划时，我们可以从几个方面入手。
首先，要关注执行计划中的成本估算。
优化器会根据统计信息、索引选择和查询语句的逻辑结构等因素，估算每个操作的成本，并选择成本最低的执行计划。
通过审查这些成本估算，我们可以找到性能瓶颈，并进行相应的优化。
其次，考虑适当的索引设计是优化执行计划的关键因素之一。
索引可以极大地提高查询性能。
通过创建正确的索引，我们可以帮助优化器选择最佳的执行计划。
例如，对于频繁进行查询的列，我们可以创建索引来加速查询的速度。
此外，我们还应该关注查询的统计信息。
统计信息是优化器做出决策的重要依据。
正确收集和维护统计信息对于优化执行计划至关重要。
定期收集统计信息并确保其准确性是优化执行计划的关键步骤之一。
最后，我们可以使用Oracle提供的工具来调整和优化执行计划。
例如，通过使用HINTS，我们可以直接指示优化器选择特定的执行计划。
此外，通过使用SQL Profile或SQL Plan Baseline，我们可以将已经优化过的执行计划应用于特定的查询语句。
在优化执行计划时，还需要注意一些常见问题。
例如，避免全表扫描、避免不必要的连接操作、合理设置索引等。
通过综合分析查询的逻辑结构和数据特征，我们可以识别出这些问题，并进行相应的优化。
综上所述，深入解读和优化执行计划是提高Oracle数据库查询性能的重要步骤。
通过了解执行计划的基本原理，审视成本估算、索引设计、统计信息和使用合适的工具，我们可以显著提高查询性能，并优化数据库的整体性能。
Oracle数据库的查询优化技巧
Oracle数据库的查询优化技巧在实际的应用中，Oracle数据库查询一直是数据库开发人员和DBA 们关注的焦点之一。
为了更加高效地使用数据库，查询优化技术变得尤为重要。
本文将介绍一些Oracle数据库查询优化的技巧。
1. 索引的使用索引是加速查询的主要手段之一。
但是，索引不是越多越好。
对于频繁更新或插入数据的表，索引会对性能产生不利影响。
因此，需要根据实际情况来选择索引。
a. 索引的选择选择能够支持特定查询的索引是至关重要的。
在选择索引之前，需要根据实际查询来确定具体需要的索引类型。
以下是一些常见的索引类型：- 唯一索引：能够保证表中的每个行都有一个唯一的索引值。
- 主键索引：能够唯一标识表中的每一行，通常用来确保数据的完整性。
- 辅助索引：用于非唯一或非主键列上的查询，这些列通常是用于过滤或排序的列。
b. 索引的设计当需要创建索引时，需要考虑创建哪些列的索引。
以下是一些常见的考虑点：- 频繁查询的列：对于经常用于查询的列，需要考虑创建索引来提升查询性能。
- 外键列：为外键列创建索引可以大大提升关联查询的性能。
- 过滤、排序和分组的列：对于这些列，也需要考虑是否需要创建索引。
2. 优化SQL语句在查询优化的过程中，SQL语句的优化也非常重要。
以下是一些优化SQL语句的技巧：a. 避免使用*查询使用*查询会查找整个表，包括可能不需要的列。
这样会增加查询的开销。
因此，在查询中最好只查询需要的列。
b. 使用合适的操作符在查询中使用适当的操作符可以提高查询的效率。
使用等于操作符时，可以使用IN操作符代替，同时可以使用BETWEEN操作符来代替大量的等于操作符。
c. 避免在WHERE子句中使用表达式在WHERE子句中使用表达式会导致全部扫描，从而降低查询性能。
3. 使用视图视图是从一个或多个表中选择数据的虚拟表。
视图可以简化复杂的查询，使查询更加容易。
使用视图的好处是可以将一些计算转移到查询之前，从而降低了查询的成本。
Oracle数据库中的查询优化技巧
Oracle数据库中的查询优化技巧在Oracle数据库中，查询是最为常见的操作之一。
然而，查询的效率受多种因素影响，如数据量、索引、数据类型、查询语句的复杂度等。
为了提高查询效率，我们需要利用一些查询优化技巧。
本文将介绍一些常见的Oracle数据库中的查询优化技巧，从而让查询更加高效。
一、使用索引索引是提高查询效率的重要工具。
Oracle数据库支持多种索引类型，如B-Tree索引、位图索引、函数索引等。
在使用索引时，需要注意以下几点：1. 确定索引列在确定索引列时，需要考虑到查询的条件和频率。
通常选择经常被查询条件所使用的列，并且列的基数越大，使用索引的效果越好。
2. 索引列排序索引列的排序方式对查询效率影响很大。
如果查询语句中使用到的列和索引列完全一致，并且查询条件为“=”或“in”，那么使用升序索引或降序索引都可以。
但是如果查询条件中包含“>”、“>=”、“<”或“<=”等符号，就需要选择升序或降序索引来优化查询效率。
3. 索引的覆盖查询覆盖查询是指查询语句可以完全通过索引来完成，而不需要访问表的数据区域。
这种查询方式可以大大提高查询效率。
在使用索引时，需要考虑将查询语句的列列表设计成只用索引列。
如果查询语句需要访问表的数据区域，就需要适当地调整查询语句的列列表，以减少查询的负担。
二、优化查询语句1.使用EXISTS和IN子句在进行复杂查询时，可能需要使用子查询来实现。
而使用EXISTS和IN子句可以避免比较大量的数据，提高查询效率。
一般来说，使用EXISTS子句比使用IN子句要快一些。
2.避免使用“SELECT *”如果查询时只需要部分列的数据，那么就应该只查询所需的列，而不是使用“SELECT *”。
这样可以避免查询多余的数据，浪费时间和系统资源。
3.使用内联视图内联视图是指在查询语句中包含子查询，提高查询效率的一种方式。
但是，内联视图的查询语句必须先于外部查询语句执行，而在查询语句中使用过多的内联视图会导致查询效率降低。
Oracle优化之执行计划解析
Oracle优化之执行计划解析执行计划是Oracle数据库中一个非常重要的概念。
它是在执行SQL语句之前由Oracle优化器生成的一种指导性的路线图，用于指导数据库引擎执行查询和更新操作。
执行计划可以帮助我们理解SQL语句的执行过程，以及找出可能存在的性能瓶颈和优化机会。
执行计划是一个树状结构，其中每个节点表示一个SQL操作，如表扫描、索引扫描、排序、连接等。
每个节点都包含了一些关键信息，如访问方法、访问对象、访问行数等。
一般而言，执行计划中的节点都按照一定的顺序执行。
例如，首先进行表扫描，然后进行索引扫描，最后进行连接操作。
执行计划中的每个节点都有一个估计的成本，该成本与执行该操作所需的时间和资源有关。
优化器会根据这些成本来选择最佳的执行计划。
要解析执行计划，我们需要关注以下几个方面：1.访问方法：执行计划中的每个节点都有一个访问方法，用于告诉数据库引擎如何获取数据。
常见的访问方法包括表扫描、索引扫描、索引范围扫描、连接等。
通过分析访问方法，我们可以了解到数据库引擎是如何获取数据的，从而找出潜在的性能问题。
2.访问对象：执行计划中的每个节点都会访问一个或多个数据库对象，如表、索引等。
通过分析访问对象，我们可以了解到数据库引擎是如何获取和处理数据的，从而找出可能存在的性能瓶颈。
3.访问行数：执行计划中的每个节点都会访问一定数量的数据行。
通过分析访问行数，我们可以了解到数据库引擎是如何处理数据的，从而找出性能优化的机会。
4.执行顺序：执行计划中的节点是按照一定的顺序执行的。
通过分析执行顺序，我们可以了解到查询的执行过程，从而找出可能存在的性能问题。
在解析执行计划时，我们可以使用多种工具和技术。
常用的工具包括SQL*Plus的AUTOTRACE功能、Oracle SQL Developer的执行计划窗口等。
这些工具可以将执行计划以图形或文本的形式展示出来，方便我们进行分析和理解。
此外，我们还可以使用一些Oracle提供的视图和函数来获取和分析执行计划的信息。
ORACLE数据库查看执行计划
ORACLE数据库查看执⾏计划基于ORACLE的应⽤系统很多性能问题，是由应⽤系统SQL性能低劣引起的，所以，SQL的性能优化很重要，分析与优化SQL的性能我们⼀般通过查看该SQL的执⾏计划，本⽂就如何看懂执⾏计划，以及如何通过分析执⾏计划对SQL进⾏优化做相应说明。
⼀、什么是执⾏计划（explain plan）执⾏计划：⼀条查询语句在ORACLE中的执⾏过程或访问路径的描述。
⼆、如何查看执⾏计划1: 在PL/SQL下按F5查看执⾏计划。
第三⽅⼯具toad等。
很多⼈以为PL/SQL的执⾏计划只能看到基数、优化器、耗费等基本信息，其实这个可以在PL/SQL⼯具⾥⾯设置的。
可以看到很多其它信息，如下所⽰2: 在SQL*PLUS(PL/SQL的命令窗⼝和SQL窗⼝均可)下执⾏下⾯步骤SQL>EXPLAIN PLAN FORSELECT*FROM SCOTT.EMP; --要解析的SQL脚本SQL>SELECT*FROM TABLE(DBMS_XPLAN.DISPLAY);3: 在SQL*PLUS下(有些命令在PL/SQL下⽆效)执⾏如下命令：SQL>SET TIMING ON --控制显⽰执⾏时间统计数据SQL>SET AUTOTRACE ON EXPLAIN --这样设置包含执⾏计划、脚本数据输出，没有统计信息SQL>执⾏需要查看执⾏计划的SQL语句SQL>SET AUTOTRACE OFF --不⽣成AUTOTRACE报告，这是缺省模式SQL>SET AUTOTRACE ON --这样设置包含执⾏计划、统计信息、以及脚本数据输出SQL>执⾏需要查看执⾏计划的SQL语句SQL>SET AUTOTRACE OFFSQL>SET AUTOTRACE TRACEONLY --这样设置会有执⾏计划、统计信息，不会有脚本数据输出SQL>执⾏需要查看执⾏计划的SQL语句SQL>SET AUTOTRACE TRACEONLY STAT --这样设置只包含有统计信息SQL>执⾏需要查看执⾏计划的SQL语句SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]参考⽂档：SQLPlus User’s Guide and Reference Release 11.1注意：PL/SQL Developer ⼯具并不完全⽀持所有的SQL*Plus命令，像SET AUTOTRACE ON 就如此，在PL/SQL Developer⼯具下执⾏此命令会报错SQL> SET AUTOTRACE ON;Cannot SET AUTOTRACE4：SQL_TRACE可以作为参数在全局启⽤，也可以通过命令形式在具体SESSION启⽤4.1 在全局启⽤，在参数⽂件（pfile/spfile)中指定SQL_TRACE =true，在全局启⽤SQL_TRACE时会导致所有进程活动被跟踪，包括后台进程以及⽤户进程，通常会导致⽐较严重的性能问题，所以在⽣产环境要谨慎使⽤。
oracle的查询优化
oracle的查询优化⼀、SQL 语句转换<⼀> 试探查询转换1、视图合并例⼦：简单视图合并优化前：create view test_view asselect ename,dname,salfrom emp e,dept dwhere e.deptno = d.deptno;select ename,dname from test_view where sal > 10000;优化后：select ename,dnamefrom emp e,dept dwhere e.deptno = d.deptnoand e.sal > 10000;复杂视图合并优化前：create view avg_sal_view asselect deptno,avg(sal) avg_sal_deptfrom empgroup by deptno;select ,avg_sal_deptfrom dept d,avg_sal_view awhere d.deptno = a.deptnoand d.loc = 'OAKLAND';优化后：select ,avg(e.sal)from dept d,emp ewhere d.deptno = e.deptnoand d.loc = 'OAKLAND'group by d.rowid,;2、⼦查询展平Oracle有⼀些转换能将不同类型的⼦查询转变为联接、半联接或反联接。
⼦查询展平也是获得良好查询执⾏性能的基本优化办法。
例⼦：优化前：select d.dname from dept d where d.deptno in (select e.deptno from emp e where e.sal > 10000);优化后：select d.dnamefrom (select distinct deptno from emp where sal > 10000) e,dept dwhere d.deptno = e.deptno;3、传递谓词⽣成在某些查询中，由于表间的联接关系，⼀个表中的谓词可以转化为另⼀个表中的谓词。
oracle执行计划怎么看
oracle执行计划怎么看Oracle执行计划怎么看。
Oracle数据库系统是当今世界上应用最广泛的关系型数据库管理系统之一，它的执行计划对于数据库性能的优化和调优起着至关重要的作用。
执行计划是Oracle数据库在执行SQL语句时生成的一种执行策略，它告诉我们数据库是如何执行SQL语句的，通过分析执行计划，我们可以了解SQL语句的执行效率，找到优化的空间，提高数据库的性能。
本文将介绍如何查看Oracle执行计划，以及如何解读执行计划，帮助大家更好地理解和优化SQL语句的执行效率。
一、查看执行计划的方法。
1. 使用EXPLAIN PLAN语句。
在Oracle中，我们可以使用EXPLAIN PLAN语句来获取SQL语句的执行计划。
具体的语法如下：EXPLAIN PLAN FOR。
SQL语句;然后可以使用如下语句来查看执行计划：SELECT FROM TABLE(DBMS_XPLAN.DISPLAY);2. 使用AUTOTRACE。
在SQLPlus或者SQL Developer中，我们可以使用AUTOTRACE功能来查看SQL语句的执行计划。
在SQLPlus中，可以使用如下语句开启AUTOTRACE功能：SET AUTOTRACE ON;然后执行需要查看执行计划的SQL语句即可。
3. 使用SQL Developer。
对于Oracle数据库开发人员来说，SQL Developer是一个非常常用的工具，它提供了直观的图形界面来查看SQL语句的执行计划。
在SQL Developer中，执行SQL语句后，可以通过右键菜单选择“Explain Plan”来查看执行计划。
二、执行计划的解读。
1. 表的访问方式。
在执行计划中，我们可以看到表的访问方式，包括全表扫描、索引扫描、唯一索引扫描等。
全表扫描意味着数据库将会扫描整张表，而索引扫描则表示数据库将会利用索引来快速定位数据，不同的访问方式对于SQL语句的性能影响很大。
oracle优化方案
Oracle优化方案引言在使用Oracle数据库时，为了提高性能和效率，我们需要对数据库进行优化。
本文将介绍一些常用的Oracle优化方案，包括索引优化、SQL优化、统计信息优化等。
索引优化索引是提高数据库查询性能的重要手段之一。
以下是一些优化索引的方法：1. 创建合适的索引在设计表结构时，应该通过分析和了解业务需求，选择合适的列创建索引。
常见的索引类型包括B树索引、位图索引等。
2. 考虑联合索引联合索引是指多个列组合在一起创建的索引。
在某些情况下，使用联合索引可以提高查询速度。
但是需要注意，联合索引过多或列顺序不当会导致索引失效。
3. 避免使用过多的索引虽然索引可以提高查询性能，但是过多的索引会增加写操作的开销，并且会占用更多的存储空间。
因此，应该根据实际情况，避免创建过多的索引。
4. 定期维护索引定期检查和维护索引是保证索引效率的重要步骤。
可以通过重建索引、收集统计信息等方法来优化索引性能。
SQL优化SQL查询是Oracle数据库最常用的操作之一。
以下是一些优化SQL查询的方法：1. 减少不必要的查询避免在查询中使用不必要的表，减少不必要的连接操作，可以大幅提高查询性能。
2. 使用正确的查询方式在编写SQL查询时，应该选择合适的查询方式。
常用的查询方式有嵌套查询、子查询、联接等。
根据实际情况选择最合适的查询方式，可以减少数据量和运算量，提高查询速度。
3. 优化WHERE条件在查询语句中，应该尽量避免使用通配符查询（如%）、使用函数在WHERE条件中进行运算，因为这样会导致全表扫描，影响查询性能。
4. 使用分页查询当查询结果集较大时，可以考虑使用分页查询。
通过limit和offset来限制查询结果的返回范围，可以减少数据的传输和处理，提高查询效率。
统计信息优化Oracle数据库中的统计信息对查询优化器的决策至关重要。
以下是一些优化统计信息的方法：1. 收集统计信息及时收集和更新表的统计信息是保证数据库查询性能稳定的重要步骤。
oracle中查询效率的优化
Oracle中怎样提高查询效率（sql优化）一、执行顺序及优化细则1.表名顺序优化(1) 基础表放下面,当两表进行关联时数据量少的表的表名放右边表或视图:Student_info (30000条数据)Description_info (30条数据)select *from description_info di,student_info si --学生信息表where si.student_id = di.lookup_code(+)and di.lookup_type(+) = 'STUDENT_ID'与select *from student_info si--学生信息表,description_info diwhere si.student_id = di.lookup_code(+)and di.lookup_type(+) = 'STUDENT_ID'以student_info作为基础表,你会发现运行的速度会有很大的差距。
(2) 当出现多个表时,关联表被称之为交叉表,交叉表作为基础表select *from description_info di,description_info di2,student_info si --学生信息表where si.student_id = di.lookup_code(+)and di.lookup_type(+) = 'STUDENT_ID'and si.school_id = di.lookup_code(+)and di.lookup_type(+) = 'SCHOOL_ID'与select *from student_info si--学生信息表,description_info di,description_info di2where si.student_id = di.lookup_code(+)and di.lookup_type(+) = 'STUDENT_ID'and si.school_id = di.lookup_code(+)and di.lookup_type(+) = 'SCHOOL_ID'以student_info作为基础表,你会发现运行的速度会有很大的差距,当基础表放在后面,这样的执行速度会明显快很多。
oracle优化方案
oracle优化方案Oracle优化方案1. 引言Oracle数据库是一种常用的关系型数据库管理系统，广泛应用于企业级应用程序。
随着数据规模的增长和业务需求的提升，数据库优化成为提高系统性能和响应速度的关键要素之一。
本文将介绍一些常见的Oracle优化方案，帮助您优化数据库性能。
2. 查询优化查询是数据库最常用的操作之一，因此查询优化是数据库性能优化的重要组成部分。
2.1 创建索引索引可以加快查询的速度，特别是对于经常被查询的列。
通过使用CREATE INDEX语句可以创建索引，例如：```sqlCREATE INDEX idx_name ON table_name (column_name);```2.2 使用合适的查询语句使用合适的查询语句可以减少不必要的资源消耗。
避免使用SELECT * 来获取全部列的数据，只查询需要的字段。
另外，避免使用模糊查询（如使用LIKE ‘%value%’）和通配符查询，因为它们会导致全表扫描。
2.3 分析执行计划执行计划是Oracle数据库在查询过程中生成的优化器的一个输出结果，可以帮助我们理解查询过程中的资源消耗和性能瓶颈。
可以使用以下命令获取执行计划：```sqlEXPLAIN PLAN FOR [your_query];SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);```分析执行计划后，可以根据性能瓶颈进行优化。
3. 数据库配置优化在数据库配置的过程中，有一些参数可以调整以提高数据库的性能。
3.1 SGA设置SGA（System Global Area）是Oracle数据库中的一个关键组件，用于缓存数据和SQL 查询执行计划。
通过适当调整SGA的大小，可以提高数据库的性能。
以下是一些与SGA相关的参数：- DB_CACHE_SIZE：设置数据库缓冲区的大小，最大限度地利用内存。
- SHARED_POOL_SIZE：设置的共享池的大小，用于缓存SQL查询和执行计划。
oracle数据库查询原理及优化【可编辑】
Oracle 语句提高查询效率的方法1：.. where column in(select * from ... where ...);2：... where exists (select 'X' from ...where ...);第二种格式要远比第一种格式的效率高。
在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询使用EXISTS，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中避免使用having字句避免使用HA VING子句, HA VING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。
SQL Select语句完整的执行顺序：1、from子句组装来自不同数据源的数据；2、where子句基于指定的条件对记录行进行筛选；3、group by子句将数据划分为多个分组；4、使用聚集函数进行计算；5、使用having子句筛选分组；6、计算所有的表达式；7、使用order by对结果集进行排序。
例：update tablename set columnName=colunName+1,columnNameBa group by在select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。
group by 子句可以将查询结果分组，并返回行的汇总信息Oracle 按照group by 子句中指定的表达式的值分组查询结果。
在带有group by 子句的查询语句中，在select 列表中指定的列要么是group by 子句中指定的列，要么包含聚组函数select max(sal),job emp group by job;(注意max(sal),job的job并非一定要出现，但有意义)查询语句的select 和group by ,having 子句是聚组函数唯一出现的地方，在where 子句中不能使用聚组函数。
ORACLE执行计划和SQL调优
ORACLE执行计划和SQL调优
Oracle执行计划是一种察看并分析查询处理过程的工具，即可以通
过执行计划了解Oracle数据库在执行SQL查询时的行为，以及查询性能
的一般情况。
Oracle的执行计划分析待查询的SQL语句及其执行路径，
可以在查询性能不理想的情况下，做出相应的调整，以提高查询速度和运
行效果。
针对Oracle数据库执行计划的调优，通常采用五种方法：
（1）使用创建索引的方法.需要分析SQL语句，把经常出现的列和表
给创建索引，以提高查询的速度。
（2）使用查看表空间的方法，如果表空间太小，则把表空间扩展，
以提高SQL语句的执行效率。
（3）调整Oracle的配置参数，把一些参数调大，以提高执行计划的
效率。
（4）优化查询语句，尽量减少不必要的查询，减少查询时间的消耗，提高查询速度。
（5）尽可能采用通过内存进行SQL查询，而不是使用磁盘I/O，以
便提高查询性能。
总之，Oracle数据库的执行计划调优是一个非常重要的任务，可以
通过上述几种方法，以改善查询性能，降低查询延迟，提高数据库的性能。
oracle查询优化改写技巧与案例
oracle查询优化改写技巧与案例在进行Oracle数据库查询优化时，我们经常会遇到各种复杂的查询语句和性能瓶颈。
本文将介绍一些Oracle查询优化改写技巧，并结合实际案例进行详细讲解，希望能够帮助大家更好地理解和应用这些技巧。
首先，我们需要明确一个概念，即查询优化的目标是尽量减少查询的执行时间，提高查询的效率。
在实际工作中，我们经常会遇到一些常见的性能问题，比如查询语句执行时间过长、索引未命中、全表扫描等。
针对这些问题，我们可以采取一些改写技巧来优化查询。
一、合理使用索引。
索引是提高查询效率的重要手段之一。
在编写查询语句时，我们应该尽量避免全表扫描，而是通过合理使用索引来定位数据。
比如，可以通过使用WHERE子句来限定索引的范围，或者使用ORDER BY子句来优化索引的使用效果。
此外，还可以考虑使用覆盖索引来减少查询的IO开销。
案例，假设我们有一个名为employee的表，其中包含了员工的姓名、部门、工资等信息。
如果我们需要查询工资大于10000的员工信息，我们可以通过在工资字段上创建索引，并使用WHERE子句来限定索引的范围，从而提高查询效率。
二、优化子查询。
在实际查询中，我们经常会遇到嵌套子查询的情况。
对于一些复杂的子查询，我们可以考虑将其改写为连接查询或者临时表来提高查询效率。
此外，还可以考虑使用WITH子句来优化子查询的性能。
案例，假设我们需要查询每个部门的平均工资，并找出工资高于部门平均工资的员工信息。
我们可以通过使用连接查询或者WITH子句来改写子查询，从而提高查询效率。
三、避免使用SELECT 。
在编写查询语句时，我们应该尽量避免使用SELECT 来查询所有字段。
因为这样会增加数据库的IO开销，降低查询效率。
我们应该明确需要查询的字段，并只查询必要的字段。
案例，假设我们需要查询员工的姓名和工资信息，我们应该明确指定需要查询的字段，而不是使用SELECT 来查询所有字段。
四、适当使用分区表。
Oracle数据库查询优化方案（处理上百万级记录如何提高处理查询速度）
Oracle数据库查询优化⽅案（处理上百万级记录如何提⾼处理查询速度）1.对查询进⾏优化，应尽量避免全表扫描，⾸先应考虑在 where 及 order by 涉及的列上建⽴索引。
2.应尽量避免在 where ⼦句中对字段进⾏ null 值判断，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=03.应尽量避免在 where ⼦句中使⽤!=或<>操作符，否则将引擎放弃使⽤索引⽽进⾏全表扫描。
4.应尽量避免在 where ⼦句中使⽤ or 来连接条件，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10union allselect id from t where num=205.in 和 not in 也要慎⽤，否则会导致全表扫描，如：select id from t where num in(1,2,3)对于连续的数值，能⽤ between 就不要⽤ in 了：select id from t where num between 1 and 36.下⾯的查询也将导致全表扫描：select id from t where name like '%abc%'若要提⾼效率，可以考虑全⽂检索。
7. 如果在 where ⼦句中使⽤参数，也会导致全表扫描。
因为SQL只有在运⾏时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运⾏时；它必须在编译时进⾏选择。
然⽽，如果在编译时建⽴访问计划，变量的值还是未知的，因⽽⽆法作为索引选择的输⼊项。
如下⾯语句将进⾏全表扫描：select id from t where num=@num可以改为强制查询使⽤索引：select id from t with(index(索引名)) where num=@num8.应尽量避免在 where ⼦句中对字段进⾏表达式操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫描。
oracle查询优化--修改执行计划_20090425
优化的目标
• 什么是优化
• • 简单点说就是增加吞吐量，减少响应时间, 是一种利用已经证实过的方法, 反复试验/判断和实现优化方案的循序渐进的过程. 也 就是说优化很多情况下要依靠我们对数据库的已有认识. 但这种认识未必是正确的.
• 为什么要优化
• • • • 系统在设计之初没有对应用的可靠理解； 系统经过初始编码之后都对设计做过大的变动； 随着数据量的增加，原来的索引性能下降； 数据的分布不再均匀
认识优化的环境
• 基于成本的优化(CBO)
• • 从oracle7.3开始, oracle引入CBO. 它是看sql语句执行的代价来选择执行语法树的. 这里的代价主要指CPU和内存. 优化器在判断是否用某种执行方式时, 主要参照的是表及索引的统计信息. 统计信息 存储在oracle的动态性能试图中, 记录了表的大小, 有多少行, 每行的长度等信息. 这些 信息起初在库内是没有的. 需要定期对表对象和索引对象执行analyze后才出现. 很多的 时候过期统计信息会令优化器做出一个错误的执行计划. SQL> desc dba_tables; Name Default Comments ------------------------- -----------TABLE_NAME Name of the table NUM_ROWS The number of rows in the table BLOCKS The number of used blocks in the table EMPTY_BLOCKS The number of empty (never used) blocks in the table AVG_SPACE The average available free space in the table AVG_ROW_LEN The average row length, including row overhead NUM_FREELIST_BLOCKS The number of blocks on the freelist
Oracle调优之看懂Oracle执行计划
Oracle调优之看懂Oracle执⾏计划@⽬录1、⽂章写作前⾔简介之前曾经拜读过《收获，不⽌sql调优》⼀书，此书是国内DBA写的⼀本很不错的调优类型的书，是⼀些很不错的调优经验的分享。
虽然读了⼀遍，做了下，觉得很有所收获，但是到实际的实践中觉得还是很缺实践。
刚好最近⼜有⼀次sql调优培训活动，去参加后，重新复习Oracle执⾏计划，所以整理资料，做成笔记分享出来2、什么是执⾏计划？执⾏计划是⼀条查询语句在Oracle中的执⾏过程或访问路径的描述。
执⾏计划描述了SQL引擎为执⾏SQL语句进⾏的操作；分析SQL语句相关的性能问题或仅仅质疑查询优化器的决定时，必须知道执⾏计划；所以执⾏计划常⽤于sql调优。
3、怎么查看执⾏计划？查看Oracle执⾏计划有很多种，详情参考我之前的，本博客只介绍很常⽤的⽅法oracle要使⽤执⾏计划⼀般在sqlplus执⾏sql：explain plan for select 1 from t不过如果是使⽤PLSQL的话，那就可以使⽤PLSQL提供的查询执⾏计划了,也就是按F5打开PLSQL⼯具 -> ⾸选项 -> 窗⼝类型 -> 计划窗⼝，在这⾥加⼊执⾏计划需要的参数找个SQL，⽤PLSQL执⾏⼀下，这是plsql的简单使⽤解释⼀下这些参数的意思:基数(Rows)：Oracle估计的当前步骤的返回结果集⾏数字节(Bytes)：执⾏SQL对应步骤返回的字节数耗费(COST)、CPU耗费：Oracle估计的该步骤的执⾏耗费和CPU耗费时间(Time)：Oracle估计的执⾏sql对于步骤需要的时间4、查看真实执⾏计划之前查看执⾏计划也喜欢按F5，不过最近去培训，听⼀名dba说，这种⽅法有时候不能获取真实的执⾏计划，收集的信息也不全⾯，然后怎么查看sql执⾏过程的真实信息？从培训中学到的经验做成笔记sqlplus窗⼝执⾏：step1：set statistics_levelalter session set statistics_level=ALL;step2：执⾏业务sqlselect /*+ monitor */ * from ... where ....;step3：为了样式，设置linesizeset linesize 200 pagesize 300;step4：查询真实执⾏计划select * from table(dbms_xplan.display_cursor(null, null, 'iostats last'));sqlplus⼀般要数据库管理员才可以使⽤，如果你不是dba，只能使⽤plsql developer的话，只能⽤下⾯的⽅法，⽅法是从培训中学到的使⽤存储过程，SQL：declareb1 date;beginexecute immediate 'alter session set statistics_level=ALL';b1 := sysdate - 1;for test in (/*业务SQL(sql后⾯不需要加";")*/select * from t) loopnull;end loop;for x in (select p.plan_table_outputfrom table(dbms_xplan.display_cursor(null,null,'advanced -bytes -PROJECTION allstats last')) p) loopdbms_output.put_line(x.plan_table_output);end loop;rollback;end;/两种窗⼝：1、SQL窗⼝的，执⾏SQL后只能去output查看；2、command window的，需要先设置set serveroutput on size unlimited，然后再执⾏存储过程output或者命令窗⼝查看的真实执⾏计划和统计信息：SQL_ID abk3ghv9u1tvb, child number 0-------------------------------------SELECT /*+ monitor */ * FROM APPR_HANDLE_INFOPlan hash value: 885170757------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 210 (100)| | 72059 |00:00:00.06 | 2460 || 1 | TABLE ACCESS FULL| APPR_HANDLE_INFO | 1 | 32752 | 210 (1)| 00:00:03 | 72059 |00:00:00.06 | 2460 | ------------------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------1 - SEL$1 / APPR_HANDLE_INFO@SEL$1Outline Data-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('11.2.0.4')DB_VERSION('11.2.0.4')ALL_ROWSOUTLINE_LEAF(@"SEL$1")FULL(@"SEL$1" "APPR_HANDLE_INFO"@"SEL$1")END_OUTLINE_DATA*/关键信息解释：Starts：该SQL执⾏的次数E-Rows：为执⾏计划预计的⾏数A-Rows：实际返回的⾏数，E-Rows和A-Rows作⽐较，就可以看出具体那⼀步执⾏计划出问题了A-Time：每⼀步实际执⾏的时间，可以看出耗时的SQLBuffers：每⼀步实际执⾏的逻辑读或⼀致性读5、看懂Oracle执⾏计划上⾯已经介绍了如何查看执⾏计划，现在简单介绍⼀下⼀些基本⽅法和相关理论知识5.1 查看explain找⼀条⽐较复杂的SQL，执⾏：F5⽅式查看：set statistics_level=ALL⽅式：SQL_ID 4qfq3t2ukm0y1, child number 0-------------------------------------SELECT /*+ monitor*/ ER_CODE, A.FULL_NAME, ER_PWD, C.UNIT_CODE,C.UNIT_NAME FROM BASE_USER A LEFT JOIN (SELECT ER_CODE,UR.UNIT_CODE FROM APPR_USER_ROLE UR WHERE ER_ROLE < 10) B ONER_CODE =ER_CODE LEFT JOIN LZCITY_APPROVE_UNIT_INFO C ONB.UNIT_CODE =C.UNIT_CODE WHERE C.UNIT_CODE ='15803'Plan hash value: 3288287052------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 3 (100)| | 16 |00:00:00.01 | 38 || 1 | NESTED LOOPS | | 1 | 1 | 3 (0)| 00:00:01 | 16 |00:00:00.01 | 38 || 2 | NESTED LOOPS | | 1 | 1 | 3 (0)| 00:00:01 | 16 |00:00:00.01 | 22 || 3 | NESTED LOOPS | | 1 | 1 | 2 (0)| 00:00:01 | 16 |00:00:00.01 | 5 || 4 | TABLE ACCESS BY INDEX ROWID| LZCITY_APPROVE_UNIT_INFO | 1 | 1 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | |* 5 | INDEX UNIQUE SCAN | PK_LZCITY_APPROVE_UNIT_INFO | 1 | 1 | 0 (0)| | 1 |00:00:00.01 | 2 ||* 6 | INDEX RANGE SCAN | PK_APPR_USER_ROLE | 1 | 1 | 1 (0)| 00:00:01 | 16 |00:00:00.01 | 2 ||* 7 | INDEX UNIQUE SCAN | PK_BASE_USER | 16 | 1 | 0 (0)| | 16 |00:00:00.01 | 17 || 8 | TABLE ACCESS BY INDEX ROWID | BASE_USER | 16 | 1 | 1 (0)| 00:00:01 | 16 |00:00:00.01 | 16 |------------------------------------------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------1 - SEL$E3445A694 - SEL$E3445A69 / C@SEL$45 - SEL$E3445A69 / C@SEL$46 - SEL$E3445A69 / UR@SEL$27 - SEL$E3445A69 / A@SEL$38 - SEL$E3445A69 / A@SEL$3Outline Data-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('11.2.0.4')DB_VERSION('11.2.0.4')ALL_ROWSOUTLINE_LEAF(@"SEL$E3445A69")MERGE(@"SEL$2")OUTLINE(@"SEL$A2E96217")OUTER_JOIN_TO_INNER(@"SEL$E9F4A6F9" "B"@"SEL$1")OUTER_JOIN_TO_INNER(@"SEL$E9F4A6F9" "C"@"SEL$4")OUTLINE(@"SEL$2")OUTLINE(@"SEL$E9F4A6F9")MERGE(@"SEL$80808B20")OUTLINE(@"SEL$6")OUTLINE(@"SEL$80808B20")MERGE(@"SEL$4")MERGE(@"SEL$F1D6E378")OUTLINE(@"SEL$5")OUTLINE(@"SEL$4")OUTLINE(@"SEL$F1D6E378")MERGE(@"SEL$1")OUTLINE(@"SEL$3")OUTLINE(@"SEL$1")INDEX_RS_ASC(@"SEL$E3445A69" "C"@"SEL$4" ("LZCITY_APPROVE_UNIT_INFO"."UNIT_CODE"))INDEX(@"SEL$E3445A69" "UR"@"SEL$2" ("APPR_USER_ROLE"."UNIT_CODE" "APPR_USER_ROLE"."USER_CODE" "APPR_USER_ROLE"."AREA_SEQ""APPR_USER_ROLE"."USER_ROLE"))INDEX(@"SEL$E3445A69" "A"@"SEL$3" ("BASE_USER"."USER_CODE"))LEADING(@"SEL$E3445A69" "C"@"SEL$4" "UR"@"SEL$2" "A"@"SEL$3")USE_NL(@"SEL$E3445A69" "UR"@"SEL$2")USE_NL(@"SEL$E3445A69" "A"@"SEL$3")NLJ_BATCHING(@"SEL$E3445A69" "A"@"SEL$3")END_OUTLINE_DATA*/Predicate Information (identified by operation id):---------------------------------------------------5 - access("C"."UNIT_CODE"='15803')6 - access("UR"."UNIT_CODE"='15803' AND "UR"."USER_ROLE"<10)filter("UR"."USER_ROLE"<10)7 - access("A"."USER_CODE"="UR"."USER_CODE")5.2 explain执⾏顺序所以不管是⽤F5⽅式还是set statistics_level=ALL⽅式，都有Operation参数，Operation表⽰sql执⾏过程，查看怎么执⾏的，有两个规则：根据Operation缩进判断，缩进最多的最先执⾏；Operation缩进相同时，最上⾯的是最先执⾏的；如图执⾏计划，根据规则，可以得出执⾏顺序：INDEX UNIQUE SCAN->TABLE ACCESS BY INDEX ROWID->INDEX RANGE SCAN ->NESTED LOOPS ->INDEX UNIQUE SCAN->NESTED LOOPS ->TABLE ACCESS BY INDEX ROWID->NESTED LOOPS-> SELECT STATEMENT5.3 访问数据的⽅法Oracle访问表中数据的⽅法有两种，⼀种是直接表中访问数据，另外⼀种是先访问索引，如果索引数据不符合⽬标SQL，就回表，符合就不回表，直接访问索引就可以。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
--改变oracle执行计划
2009.04.25 张磊
目录
• 优化的目标 • 认识优化的环境 • 基于规则的优化程序
• • 基于成本的优化程序 优化模式的选择
• Oracle表的连接及调整 • 嵌套循环连接
• • • 哈希连接 合并连接 跟表连接相关的hints
• 索引 • 实例
100 200 300 400
..
100 101 .. 200 .. 300 .. 400 ..
• 优势
• • • • 1，对于非等值连接，效果较高， 2，如果关联列上都有索引，效果更好； 3，如果两个row source都比较大，本方法要稍好于NL； 4，如果返回的row source过大则又会导致过多的IO。
•
•
Oracle表的连接方式及调整
• 排序合并连接(merge join)的执行方式
• • • 1，先生成驱动数据，然后对这些数据按照连接操作关联列进行排序； 2，生成被探查数据，然后对这些数据按照连接操作关联列进行排序； 3，对已经排序的两个结果放在一起执行合并操作。 .
1 2
• 缺点
• • 排序是一个费时、费资源的操作，特别对于大表。 基于这个原因，SMJ经常不是一个特别有效的连接方法，
认识优化的环境
• 基于成本的优化(CBO)
• • 从oracle7.3开始, oracle引入CBO. 它是看sql语句执行的代价来选择执行语法树的. 这里的代价主要指CPU和内存. 优化器在判断是否用某种执行方式时, 主要参照的是表及索引的统计信息. 统计信息 存储在oracle的动态性能试图中, 记录了表的大小, 有多少行, 每行的长度等信息. 这些 信息起初在库内是没有的. 需要定期对表对象和索引对象执行analyze后才出现. 很多的 时候过期统计信息会令优化器做出一个错误的执行计划. SQL> desc dba_tables; Name Default Comments ------------------------- -----------TABLE_NAME Name of the table NUM_ROWS The number of rows in the table BLOCKS The number of used blocks in the table EMPTY_BLOCKS The number of empty (never used) blocks in the table AVG_SPACE The average available free space in the table AVG_ROW_LEN The average row length, including row overhead NUM_FREELIST_BLOCKS The number of blocks on the freelist
优化的目标
• 什么是优化
• • 简单点说就是增加吞吐量，减少响应时间, 是一种利用已经证实过的方法, 反复试验/判断和实现优化方案的循序渐进的过程. 也 就是说优化很多情况下要依靠我们对数据库的已有认识. 但这种认识未必是正确的.
• 为什么要优化
• • • • 系统在设计之初没有对应用的可靠理解； 系统经过初始编码之后都对设计做过大的变动； 随着数据量的增加，原来的索引性能下降； 数据的分布不再均匀
Oracle表的连接方式及调整
• 嵌套循环连接(nested loop)的执行方式
• • • 1、 Oracle首先选择一张表作为连接的驱动表 2、 逐条提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的 记录. Nested loop Outer loop Inner loop
Oracle表的连接方式及调整
• 哈希(hash join)连接
自从oracke 7.3以来，oracle提供了一种新的join技术，就是hash join。Hash Join只 能用于相等连接，且只能在CBO优化器模式下。相对于nested loop join，hash join更 适合处理大型结果集。Hash join不需要在驱动表上存在索引。
• •
• 优化模式的设置
优化模式 rule choose first_rows all_rows 修改initsid.ora设置 optimizer_mode= rule/choose/first_rows/ all_rows alter session set optimizer_mode =rule/choose/first_ro ws/all_rows instance session sql /*+ rule */ /*+ choose */ /*+ first_rows */ /*+ all_rows */
• • 表示对表t1、t2关联时采用嵌套循环连接。 select /*+ use_nl(dpi,psj)*/ dpi.model_name,psj.day_sale from dw.d_product_info dpi, dw.pm_stock_sale_jyb psj where dpi.product_id = psj.product_id
B0 0,0,10,1 0 √
B1 1,1,1,1,1 1
B2 2,2, 2
B 3 3
B 4 4
B5 5
B 6 6
B 7 7
B 8 8
B9 9,9, 9
√
√ √
S5
S6 S7 S8
5
NUL L NUL L 8,8,8, 8
√
√
S9
NUL L
Oracle表的连接方式及调整
• 哈希(hash join)连接步骤
hash join的要求和优势
• • • 哈希连接比较适用于返回大数据量结果集的连接。 使用哈希连接必须是在CBO模式下，参数hash_join_enabled设置为true， 哈希连接只适用于等值连接
跟表连接相关的hints
• • • • • 测试环境 数据库版本： oracle9.2.8.0 系统环境 ：aix 数据库实例：BI 查询代码
• 规则1(强规则)
• oracle在建立执行树时,其关联数序是按照表在from后面出现的顺序按照倒叙排列的, 这一点似乎正确.至少,大部分情况下,在from后面最后出现的表一般会作为第一个驱动表. 但这不是绝对的,
• 规则2(弱规则)
• • oracle为了避免更大的结果和另外一个未知(没执行analyze对表进行分析)大小的结果进 行嵌套循环连接,会按照扫描表,扫描索引然后访问表,直接访问索引可以得到数据.这样 的顺序对表进行关联. 比如说,如果一个sql查询语句中,需要对表进行acess full,那么其顺序肯定是靠前的,而 相对于可以直接在index中获得所有的结果, TABLE ACCESS BY INDEX ROWID的表 顺序也比较靠前.在这之后才会考虑表在from后面出现的位置
• Hash join 原理
• • • • • 考虑以下两个数据集, 假设hash函数为求余函数。 S={1,1,1,3,3,4,4,4,4,5,8,8,8,8,10} B={0,0,1,1,1,1,2,2,2, 3,4,5,6,7,8,9,9,9,10,10,11}
分 区 值 S0 S1 S2 S3 S4 10 1,1,1 Null 3,3 4,4,4, 4
• use_merge（t1,t2）
• • 表示对表t1、t2关联时采用排序合并连接。 select /*+ use_merge(dpi,psj)*/ dpi.model_name,psj.day_sale from dw.d_product_info dpi, dw.pm_stock_sale_jyb psj where dpi.product_id = psj.product_id
• • • • • • • • • •
认识优化的环境
• 优化模式的选择
• • • Oracle的优化模式有四种: Rule: 基于规则的方式; Choose: 默认情况下oracle使用的方式. 指的是当一个表或索引有统计信息,则走cbo的 方式, 如果表或索引没有统计信息, 表又不是特别的小, 而且相应的列有索引时, 那么走 rbo方式. First rows: 与choose方式类似, 不同处在于它将以最快的方式返回查询的最先的几行. All rows: 也就是所谓的cbo模式.
select dpi.model_name,psj.day_sale from dw.d_product_info dpi, dw.pm_stock_sale_jyb psj where dpi.product_id = psj.product_id;
•
跟表连接相关的hints
• use_nl（t1,t2）
考虑查询 select * from t1,t2 where t1.f1 = t2.f1
Oracle表的连接方式及调整
• 驱动表和被探查表的概念
• 驱动表又称为外层表（outer table），这个概念用于嵌套与hash连接中，如果驱动 表row source返回较多的行数据，则对所有的后续操作都有负面的影响，因为将大的 row source作为驱动表时在后续的操作中返回的中间结果都比较大。这是比较容易理解 的。 另外在驱动表的选择上，应该选择经限制条件选择之后结果集比较小的row source 作为驱动表，而不是单纯选择原始数据较小的数据表作为driving table。 驱动表的识别，在执行计划那个，靠上的那个row source应该是驱动表，一般都是 在from关键字后靠前位置的表。
1、 构建阶段：优化器首先选择一张小表做为驱动表，运用哈希函数对连接列进行计算 产生一张哈希表。通常这个步骤是在内存（hash_area_size）里面进行的，因此运算 很快。 2、 探测阶段：优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面 形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值 没有与驱动表连接列的值相等的话，那么这些记录将会被丢弃而不进行探测