Oracle Acs资深顾问罗敏 老罗技术核心感悟:牛! 11g的自动调优和SQL Profile

作者为: 

SHOUG成员 – ORACLE ACS高级顾问罗敏

 

 

 

  1. 多年前的一段往事

记得多年以前在一个10g平台的数据仓库项目上遇到一个非常难优化的SQL语句,当时即便我采集了统计信息、甚至在语句中增加了HINT,Oracle产生的执行计划都不如人意。最后,不得不通过SR寻求老外高手的指点,他建议我采用10g刚出炉的一个新技术,即让我为该语句生成SQL Profile信息,然后再执行该语句。一切OK了,太神了!

也记得当时我问老外,以后是不是遇到非常复杂的、优化难度很大的SQL语句,就扔给Oracle,特别是产生一遍SQL Profile来辅助优化器时?鬼子不无得意地回答:“That’s right!”

 

  1. 再次感叹SQL Profile的牛!

若干年之后的2014年,在面对一条将近200行的SQL语句进行优化时,发现该语句执行计划已经基本找不出明显问题,例如既没有全表扫描,也没有全索引扫描,甚至语句的Cost也非常低(当然Cost并不十分准确)。但是语句执行效率并不高,达到30秒,资源消耗也非常高,例如Buffer Gets达到1,246,155次。客户当然不满意,如何进一步优化?

山穷水尽之际,想起了上述多年前的往事,更想起了神奇的SQL Profile技术。于是,在搜索到最新的11g文档《Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)》之后,照猫画虎般地开练了。效果如何?以下就是优化前后的对比:

这是优化之前的各项指标:

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 30,273 30,272.96 17.76
CPU Time (ms) 29,968 29,968.19 17.79
Executions 1
Buffer Gets 1,246,155 1,246,155.00 14.68
Disk Reads 5,437 5,437.00 0.80

这是优化之后的各项指标:

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 4,653 4,652.71 3.00
CPU Time (ms) 4,470 4,470.23 2.90
Executions 1
Buffer Gets 303,480 303,480.00 2.32
Disk Reads 9,740 9,740.00 1.39

可见,语句响应速度从30秒下降到4秒多,Buffer Gets从1,246,155下降到303,480!我对语句没做任何改动,也没创建新的索引,执行计划就更好了,实际效果更是如此的好!SQL Profile牛啊!

 

  1. 实施细节

下面就是11g自动优化工具和SQL Profile技术综合运用的详细过程:

  • 生成自动优化任务

declare

my_task_name VARCHAR2(30);

my_sqltext CLOB;

begin

my_sqltext := ‘<欲调优的SQL语句文本>’;

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => my_sqltext,

user_name => ‘<用户名>’,

scope => ‘COMPREHENSIVE’,

time_limit => 60,

task_name => ‘test1’,

description => ‘Task to tune a query on a specified table’);

end;

/

  • 执行自动优化任务

begin

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘test1’);

end;

/

  • 查询Oracle产生的自动优化报告

set long 10000

set longchunksize 1000

set linesize 100

set heading off

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘test1’) from DUAL;

set heading on

  • 接受Oracle自动优化任务产生的SQL Profile

DECLARE

my_sqlprofile_name VARCHAR2(30);

begin

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

task_name => ‘test1’,

name => ‘test1’);

end;

/

OK了,可以运行需要调优的语句,并观察优化效果了。效果就是上面显示的那组令人激动不已的数据,而且在Oracle产生的新执行计划中,明白无误地显示采用SQL Profile了:

“SQL profile “test1” used for this statement ”

 

各位读者现在只需将你需要优化的语句和所属用户名填入上述脚本之中,也可以照葫芦画瓢开练了。

 

  1. SQL Profile到底是什么东西?

SQL Profile信息存储在Oracle数据字典之中,除了dba_sql_profiles视图显示的有限信息之外,的确有种看不见、摸不着的讳莫如深的感觉。SQL Profile到底是什么东西?其实SQL Profiling可以与表和统计信息的关系相类比,SQL Profile就是一条SQL语句的统计信息。例如:当我们遇到一个复杂且资源消耗非常大SQL语句时,Oracle可通过一些取样的数据,或者可以执行该语句一个片段,以及分析该语句的历史执行情况,来评估整体执行计划是否最优化。而这些辅助信息,就是SQL Profile信息,并保存在数据字典之中。

SQL Profiling工作原理如下图:

sql profile

即上图上半部分显示11g自动优化工具SQL Tuning Advisor在针对某条SQL语句产生SQL Profile信息之后,在上图的下半部分,当Oracle正式需要执行该SQL语句时,优化器不仅利用该语句所访问对象的统计信息,而且利用SQL Profile信息,来产生整体上更优的执行计划。

 

  1. 什么时候该使用自动调优工具和SQL Profile?

Oracle 11g的自动调优工具和SQL Profile的确像潘多拉盒子一样充满魔力。继续上述优化案例,尽管该语句被Oracle优化了,但我仔细对照了优化前后50多步的执行计划,怎么也没找出到底是哪些步骤被Oracle优化得效果如此之好,真是太神奇了!

是否一遇到复杂语句就依靠自动调优工具和SQL Profile进行优化呢?且慢,首先,尽管应用性能问题很多,但最主要的问题还是一些传统的、基础性问题。例如:缺乏合适的索引;复合索引设计不合理,特别是索引顺序不对,导致索引效率不高;SQL语句中错误地使用函数,导致索引无法使用;等等。针对这些问题,合理运用20%的基础技术,特别是索引技术,其实能解决80%的问题。这些技术也是DBA和应用开发人员的基本功和基本设计开发规范,过度依赖自动化工具反而会让我们自己的基本技能退化的。其次,自动调优工具和SQL Profile也非包治百病的灵丹妙药,也有看走眼的时候。Oracle自动工具怎么可能比你更了解你的数据模型和数据分布情况,进而给出更准确的优化策略呢?第三,Oracle自动工具使用起来也并不简单,而且需要DBA与开发人员紧密配合,针对大部分基础性问题,有经验的DBA和开发人员其实一眼就能看出问题,何必杀鸡用牛刀呢?

那何时使用自动调优工具和SQL Profile进行优化呢?本人的经验:当针对一些复杂SQL语句,运用传统的、人工分析方法难以奏效时,建议尝试使用这些新技术。

 

无论如何,Oracle 11g的自动调优工具和SQL Profile还是牛!不得不服!

Oracel SQL优化器CBO optimizer 案例分析:星形转换

学完本课后,应能完成以下工作:

  • 定义星形方案
  • 展示没有转换的星形查询计划
  • 定义星形转换要求
  • 展示转换后的星形查询计划

 

星形方案模型

star-query-1

星形方案是最简单的数据仓库方案。之所以将其称作星形方案,是因为此方案的实体关系图类似于星形:多个点呈放射状围绕在中心表周围。星形的中心由一个或多个事实表组成,星形的点是维表。星形方案的特点如下:具有一个或多个非常大的事实表,这些表包含数据仓库中的主要信息,同时还具有许多较小的维表(或查找表),每个维表都包含有关事实表中某个特定属性的多项信息。星形查询是事实表和众多维表之间的联接。每个维表都使用主键联接到事实表的外键,但维表彼此之间没有联接。基于成本的优化程序 (CBO) 可识别星形查询,并为其生成高效的执行计划。事实表通常包含关键字和度量。例如,在销售历史方案中,sales 事实表包含 quantity_sold、amount 和 cost 度量以及 cust_id、time_id、prod_id、channel_id 和 promo_id 关键字。维表是 customers、times、products、channels 和 promotions。例如,products 维表包含事实表中的每个产品编号的信息。

注:可以很容易地将此模型扩展成包括多个事实表。

 

雪花方案模型

 

star-query-3

雪花方案是一种比星形方案更复杂的数据仓库模型,是星形方案的一种类型。之所以将其称作雪花方案,是因为此方案的关系图类似于雪花。雪花方案对维进行了规范化,以消除冗余。也就是将维数据划分到多个表中,而不是放在一个大表中。例如,在雪花方案中,星形方案中的产品维表在规范化后,可能变成一个 products 表、一个 product_category 表,以及一个 product_manufacturer 表,或如幻灯片所示,可以使用 countries 表来规范化 customers 表。虽然这样可节省空间,但增加了维表的数量,因此需要更多的外键联接。导致的结果是查询的复杂度增加,并且查询性能有所降低。

注:建议您优先选择星形方案,除非有明确的理由,否则不要选择雪花方案。

 

星形查询:示例

 

星形查询:示例
请考虑幻灯片中的星形查询。为了运行星形转换,假定销售历史方案的 sales 表在 time_id、channel_id 和 cust_id 列上有位图索引。

SELECT ch.channel_class, c.cust_city,  
       t.calendar_quarter_desc, 
       SUM(s.amount_sold) sales_amount 
 
FROM sales s,times t,customers c,channels ch 
WHERE s.time_id = t.time_id AND  
      s.cust_id = c.cust_id AND 
      s.channel_id = ch.channel_id AND  
      c.cust_state_province = 'CA' AND  
      ch.channel_desc IN ('Internet','Catalog') AND  
      t.calendar_quarter_desc IN ('1999-Q1','1999-Q2') 
 
GROUP BY ch.channel_class, c.cust_city,  
         t.calendar_quarter_desc; 

没有星形转换的执行计划

star-query-4

首先观察一下不进行星形转换的情况下星形方案中的联接的处理过程,然后再来了解此转换的优点。
幻灯片中计划的基本问题是查询总是启动 SALES 表到维表的联接。这会导致大量的行,这些行只能由执行计划中的其它父联接进行缩减。

星形转换

 

  • 在事实表外键上创建位图索引。
  • 将 STAR_TRANSFORMATION_ENABLED 设置为 TRUE。
  • 至少需要两个维表和一个事实表。
  • 收集所有相应对象的统计信息。
  • 执行过程分两个阶段:

–首先,基于维过滤器使用位图索引识别相关的事实行。

–将它们联接到维表。

 

要使星形查询获得尽可能高的性能,必须遵循一些基本准则:

  • 位图索引应构建在事实表或表的各个外键列之上。
  • STAR_TRANSFORMATION_ENABLED 初始化参数应设置为 TRUE。这会为星形查询启用一个重要的优化程序功能。为了实现向后兼容,其默认设置为 FALSE。

如果数据仓库满足这些条件,则在其中运行的大部分星形查询都会使用称为“星形转换”的查询执行策略。借助星形转换,星形查询可以具有很高的查询性能。

星形转换是一种功能强大的优化技术,该技术基于隐式重写(或转换)原始星形查询的 SQL。最终用户不需要了解星形转换的任何细节。系统的 CBO 会自动选择适合进行星形转换的地方。Oracle 处理星形查询的过程分两个基本阶段:

  • 第一阶段仅从事实表(结果集)检索必要的行。由于此检索利用了位图索引,因此非常高效。
  • 第二个阶段将此结果集联接至维表。此操作称为半联接。

注:查询中至少使用三个表(两个维表和一个事实表)。

星形转换:注意事项

  • 不会转换包含绑定变量的查询。
  • 不会转换引用远程事实表的查询。
  • 不会转换包含反联接表的查询。
  • 不会转换引用未合并的未分区视图的查询。

星形转换不适用于具有以下任一特征的表:

  • 查询具有与位图访问路径不兼容的表提示
  • 查询包含绑定变量
  • 表具有的位图索引过少。事实表列上必须有一个位图索引,优化程序才能为其生成子查询。
  • 远程事实表。但在所生成的子查询中可以有远程维表。
  • 反联接表。
  • 表已用作子查询中的维表。
  • 表实际上是未合并的视图,而不是视图分区。

 

星形转换:重写示例

系统分两个阶段来处理前面提到的查询。在第一阶段中,系统对维表使用过滤器,检索与过滤器匹配的维主键。之后系统使用这些主键来探测事实表外键列上的位图索引,以便仅识别和检索事实表中的必要行。即系统实际上使用幻灯片中经过重写的查询从 sales 表中检索结果集。
注:幻灯片中的 SQL 是理论上的 SQL 语句,用于表示在第一阶段发生的操作。

SELECT s.amount_sold 
FROM sales s 
 
WHERE time_id IN (SELECT time_id  
                  FROM times 
                  WHERE calendar_quarter_desc  
                      IN('1999-Q1','1999-Q2')) 
 
AND   cust_id IN (SELECT cust_id  
                  FROM customers  
                  WHERE cust_state_province = 'CA') 
 
AND channel_id IN(SELECT channel_id  
                  FROM channels  
                  WHERE channel_desc IN  
                            ('Internet','Catalog')); 


 

根据一个维表检索事实行

star-query-2

幻灯片显示了仅使用一个维表检索事实表行的过程。系统基于相应的维过滤谓词(类似于上一张幻灯片示例中的 t.calendar_quarter_desc IN (‘1999-Q1′,’1999-Q2’))扫描维表,针对每个相应行探测相应的事实表位图索引,获取相应的位图。

BITMAP KEY ITERATION 将其左侧输入的每个关键字作为右侧输入的索引的查找关键字,然后返回该索引获取的所有位图。请注意,在本例中左侧的输入提供了维表的联接关键字。

此树的最后一步将合并在以前步骤中获得的所有位图。此合并操作会生成一个位图,该位图可以表示事实表中与维表的相关行相联接的行。

注: 使用共享服务器模式时,BITMAP_MERGE_AREA_SIZE 在优化此操作性能上起到非常重要的作用。除非对实例配置了共享服务器选项,否则系统不建议使用 BITMAP_MERGE_AREA_SIZE 参数。系统建议您通过设置 PGA_AGGREGATE_TARGET 启用自动调整 SQL 工作区大小功能。保留 BITMAP_MERGE_AREA_SIZE 是为了实现向后兼容。

 

根据所有维表检索事实行

star-query-5

 

在第一阶段,针对各个维表重复上一幻灯片中提到的步骤。因此计划中的每个 BITMAP MERGE 会为一个维表生成一个位图。要从事实表中识别出所有相关的行,系统必须对生成的所有位图取交集。这样做是为了清除只与一个维表联接,但未与所有维表联接的事实行。这是通过对根据每个维表生成的所有位图执行非常高效的 BITMAP AND 操作实现的。最后生成的位图可以表示事实表中与维表的所有合格行相联接的行。

注:到现在为止,仅使用了事实表位图索引和维表。要进一步访问事实表,系统必须将生成的位图转换成行 ID 集。

 

将临时结果集与维表相联接

 

star-query-6

确定结果集之后,系统将进入星形转换算法的第 2 阶段。在此阶段,需要将与结果集对应的销售数据与用于对行分组且属于查询的选择列表的维表数据相联接。

请注意,幻灯片中的图显示了在事实表与其维表之间执行的散列联接。尽管从统计结果看,散列联接是最常用的一种联接星形查询中的行的技术,但可能并不总是如此,具体采用的技术由 CBO 评估。

 

星形转换计划:示例 1

star-query-7

 

此计划可以用来回答“没有星形转换的执行计划”部分中显示的查询。请注意,出于格式化方面的原因,仅显示了通道和时间维。可以很容易地将此案例扩展为 n 维。

注:假定 sales 没有分区。

 

星形转换:进一步优化

star-query-8

  • 在星形转换执行计划中,对维表进行了两次访问;每个阶段访问一次。
  • 如果维表很大,而选择率很低,这可能会造成性能问题。
  • 如果创建临时表的成本较低,系统可能会决定使用此方式,而不访问同一维表两次。
  • 在计划中创建临时表:

查看前面的执行计划时,您会发现每个维表都需要访问两次:
在第一阶段中,系统会访问一次维表来决定必要的事实表行;在第二阶段中,系统会再访问一次维表来将事实行与每个维表相联接。如果维表很大,且在访问这些维表时没有有助于解决问题的快速访问路径,则可能会造成性能问题。在此类情况下,系统可能会决定创建临时表,表中包含两个阶段所需的信息。如果创建一个由维表的谓词和联接列的结果集组成的临时表所花费的成本比访问维表两次的成本低,则系统会采用前一种方式。在前面的执行计划示例中,TIMES 和 CHANNELS 表很小,使用全表扫描对其进行访问的工作量并不是很大。

上面的执行计划显示了这些临时表的创建和数据插入方式。这些临时表的名称是系统生成的,并且不是固定的。幻灯片中显示了某执行计划的一部分,该执行计划对 CUSTOMERS 表使用了临时表。

注:另外,在下列情况下星形转换不使用临时表:

  • 数据库处于只读模式。
  • 星形查询所属的事务处理处于可串行化模式。

 

 

使用位图联接索引

 

  • 减少了要联接的数据量
  • 可以用于消除按位操作
  • 在存储方面比 MJV 效率高

 

CREATE BITMAP INDEX sales_q_bjx

ON sales(times.calendar_quarter_desc)

FROM sales, times

WHERE sales.time_id = times.time_id

 

如果用作联接的联接索引已预先计算好,则会减少需要联接的数据量。

另外,包含多个维表的联接索引可以消除按位操作,这些操作在使用现有位图索引的星形转换中是必不可少的。

最后,位图联接索引在存储方面比实体化联接视图 (MJV) 效率高,因为 MJV 不对事实表的行 ID 进行压缩。

后面假设您已创建了幻灯片中提及的附加索引结构。

 

星形转换计划:示例 2

 

SORT GROUP BY 
 HASH JOIN 
    HASH JOIN 
       TABLE ACCESS BY INDEX ROWID SALES 
         BITMAP CONVERSION TO ROWIDS 
          BITMAP AND 
           BITMAP MERGE 
            BITMAP KEY ITERATION 
             BUFFER SORT 
              TABLE ACCESS FULL CHANNELS 
             BITMAP INDEX RANGE SCAN SALES_CHANNELS_BX 
           BITMAP OR 
             BITMAP INDEX SINGLE VALUE SALES_Q_BJX 
             BITMAP INDEX SINGLE VALUE SALES_Q_BJX 
       TABLE ACCESS FULL CHANNELS 
    TABLE ACCESS FULL TIMES 

对同一星形查询使用位图联接索引进行处理的过程类似于前面的示例。唯一的区别是系统在星形查询的第一阶段使用联接索引,而不是单个表的位图索引来访问 times 数据。
此计划与上一计划的区别在于,在对 times 维做位图索引扫描的内部部分没有第 1 阶段的重写查询中的子选择语句。这是因为 times.calendar_quarter_desc 的联接谓词信息可从 sales_q_bjx 位图联接索引获取。
请注意,系统会访问联接索引两次,因为相应查询的谓词是 t.calendar_quarter_desc IN (‘1999-Q1′,’1999-Q2’)

 

星形转换提示

 

  • STAR_TRANSFORMATION 提示:使用包含星形转换的最佳计划(如果有)。
  • FACT (<table_name>) 提示:应将提示表作为星形转换上下文中的事实表。
  • NO_FACT (<table_name>) 提示:不应将提示表作为星形转换上下文中的事实表。
  • FACT 和 NO_FACT 提示对于包含多个事实表的星形查询很有用。

 

  • STAR_TRANSFORMATION 提示可使优化程序使用其中使用了转换的最佳计划。如果没有提示,优化程序可能会基于成本作出决定,使用已生成的没有转换的最佳计划,而不使用已转换查询的最佳计划,即使给出提示,也不能保证转换一定会发生。优化程序仅在适当情况下生成子查询。如果没有生成子查询,则没有转换的查询,此时不管是否有提示,都使用未转换查询的最佳计划。
  • FACT 提示在星形转换上下文中使用,用于指示转换时应将提示表作为事实表,其它所有表无论大小都作为维表。
  • NO_FACT 提示在星形转换上下文中使用,用于指示转换时不应将提示表作为事实表。

注:仅当星形查询要访问多个事实表时,才可能用到 FACT 和 NO_FACT 提示。

 

位图联接索引:联接模型 1

star-query-9

CREATE BITMAP INDEX bji ON f(d.c1)  

FROM f, d  

WHERE d.pk = f.fk;

SELECT sum(f.facts)

FROM d, f

WHERE d.pk = f.fk AND d.c1 = 1;

 

在接下来的三张幻灯片中,F 代表事实表,D 代表维表,PK 代表主键,FK 代表外键。

位图联接索引可以在幻灯片所示的 SELECT 语句中使用,用于避免联接操作。

位图联接索引类似于实体化联接视图,它预先计算联接,并将其存储为数据库对象。二者之间的区别在于:实体化联接视图会使联接实体化为一个表,而位图联接索引会将联接实体化成一个位图索引。

注: C1 是维表中的索引列。

 

 

位图联接索引:联接模型 2

star-query-10

本幻灯片中的模型是模型 1 的扩展形式,需要使用级联位图联接索引来表示。

请注意,本例中的 BJX 也可以用来回答下列选择语句:

select sum(f.facts) from d,f where d.pk=f.fk and d.c1=1

这是因为 D.C1 位于 BJX 的第一部分。

 

 

位图联接索引:联接模型 3

star-query-11

此模型也需要使用幻灯片中所示的级联位图联接索引。在本例中使用了两个维表。

位图联接索引:联接模型 4

star-query-12

本幻灯片显示了在两个或更多维表之间存在联接的雪花模型。可以用位图联接索引来表示它。位图联接索引可以是单个的,也可以是级联的,具体取决于维表中用于索引的列的数量。可以在 D1.C1 上创建一个位图联接索引,在 D1 和 D2 之间以及 D2 和 F 之间建立联接,如幻灯片中的 BJX 所示。

 

 

Oracle SQL optimizer 优化程序简介

结构化查询语言

 

sql_lang

SQL 是所有程序和用户访问 Oracle DB 中的数据时使用的语言。借助应用程序和 Oracle 工具,用户通常不需要直接使用 SQL 访问数据库,但这些应用程序在执行用户请求时必须使用 SQL。Oracle 尽量遵守行业公认的标准,并积极参加 SQL 标准委员会(ANSI 和 ISO)的工作。最新的 SQL 标准是在 2003 年 7 月采用的,通常称为 SQL:2003。可以将 SQL 语句分为六大类:

  • 数据操纵语言 (DML) 语句操纵或查询现有方案对象中的数据。
  • 数据定义语言 (DDL) 语句定义、修改方案对象的结构以及删除方案对象。
  • 事务处理控制语句 (TCS) 管理 DML 语句所做的更改,以及将 DML 语句分组到事务处理中。
  • 系统控制语句更改 Oracle DB 实例的属性。
  • 会话控制语句管理特定用户会话的属性。
  • 嵌入式 SQL (ESS) 语句将 DDL、DML 和 TCS 整合到过程语言程序(如 PL/SQL 和 Oracle 的预编译器)中。这种整合是使用幻灯片中 ESS 类别下列出的语句完成的。

注: SELECT 语句是最常用的语句。虽然本课程的其余部分主要侧重于查询,但仍必须注意这一点:任何类型的 SQL 语句都可能需要优化。

 

 

SQL 语句表示形式

 sql-optimizer-1

Oracle DB 使用一个共享 SQL 区域和一个专用 SQL 区域来表示它运行的每个 SQL 语句。Oracle DB 可识别两个用户执行相同 SQL 语句的情况,从而为这些用户重用共享 SQL 区域。但是对于语句的专用 SQL 区域,每个用户必须有一个单独的副本。

共享 SQL 区域包含执行语句所必需的所有优化信息,而专用 SQL 区域包含与语句的某一次执行相关的所有运行时信息。

Oracle DB 通过对多次运行的 SQL 语句使用一个共享 SQL 区域来节省内存。当许多用户运行同一个应用程序时,同一 SQL 语句通常会多次运行。

注:在评估语句是否相同或相似时,Oracle DB 会考虑用户和应用程序直接发出的 SQL 语句,以及 DDL 语句内部发出的递归 SQL 语句。

 

SQL 语句实施

 sql-optimizer-2

对新的 SQL 语句进行语法分析时,Oracle DB 会将共享池中的内存分配给共享 SQL 区域中的存储。该内存的大小取决于语句的复杂性。如果已经分配了整个共享池,则 Oracle DB 可使用修改后的最近最少使用 (LRU) 算法,对共享池中的项目取消空间分配,直到有足够的空闲空间供新语句的共享 SQL 区域使用。如果 Oracle DB 取消分配某个共享 SQL 区域,则在下一次执行关联的 SQL 语句时必须重新分析该语句,并为其重新分配另一共享 SQL 区域。

 

SQL 语句处理:概览

sql-optimizer-3

SQL 语句处理:步骤

 

  1. 创建游标。
  2. 分析语句。
  3. 描述查询结果。
  4. 定义查询输出。
  5. 绑定变量。
  6. 语句并行化。
  7. 执行语句。
  8. 提取查询的行。
  9. 关闭游标。

请注意,并非所有语句都需要执行以上全部步骤。例如,非并行的 DDL 语句只需要两个步骤:创建和分析。

语句并行化需要确定语句是否可并行化,这与实际建立并行执行结构不同。

 

 

步骤 1:创建游标

  • 游标是专用 SQL 区域的句柄或名称。
  • 它包含语句处理所需的信息。
  • 它是在执行 SQL 语句之前通过编程接口调用创建的。
  • 游标结构独立于其包含的 SQL 语句。

游标可以看成是客户机程序中的游标数据区域与 Oracle 服务器的数据结构之间的关联。大多数 Oracle 工具向用户隐藏了许多游标处理过程,但 Oracle 调用接口 (OCI) 程序需要有一定的灵活性,以便可以单独处理查询执行的每个部分。因此,预编译器允许使用显式游标声明。也可以使用 DBMS_SQL 程序包完成以上大部分操作。

句柄类似于杯子的把手。如果持有了句柄,就持有了游标。它是某个特定游标的唯一标识符,一次只能由一个进程获取。

要处理 SQL 语句,程序必须要有一个打开的游标。游标包含一个指向当前行的指针。指针会在提取行时移动,直到不再有要处理的行为止。

后面的幻灯片将使用 DBMS_SQL 程序包来说明游标管理。这可能会让不熟悉该程序包的人感到困惑;但是,它比 PRO*C 或 OCI 更容易使用。不过存在一点小问题:因为它将 FETCH 与 EXECUTE 一起执行,所以不能在跟踪中单独标识执行阶段。

 

步骤 2:分析语句

  • 语句从用户进程传递到 Oracle 实例
  • 如果共享 SQL 区域中没有相同的 SQL,则会创建 SQL 语句分析后的表示形式,并将其移到共享 SQL 区域
  • 如果存在相同的 SQL,则可重用这些 SQL

在分析过程中,SQL 语句从用户进程传递到 Oracle 实例,SQL 语句分析后的表示形式被加载到共享 SQL 区。

转换和验证时要检查库高速缓存中是否已存在相应的语句。

对于已分配的语句,要检查是否存在数据库链接。

通常,分析阶段代表生成查询计划的阶段。

客户机软件可以延迟分析步骤以降低网络流量。也就是说,PARSE 与 EXECUTE 捆绑在一起执行,因此减少了到服务器的往返次数。

注:检查语句是否相同时,必须是所有方面都相同,包括大小写和空格。

 

 

步骤 3 和步骤 4:描述和定义

  • 描述步骤提供有关选择列表项的信息;通过 OCI 应用程序输入动态查询时,会涉及该步骤。
  • 定义步骤定义在变量中存储提取值所需的位置、大小和数据类型信息。

步骤 3:描述

仅当不知道查询结果的特征时,才需要有描述阶段,例如,用户以交互方式输入查询。在这种情况下,描述阶段确定查询结果的特征(数据类型、长度和名称)。描述告诉应用程序需要哪些选择列表项。例如,如果输入如下查询:

SQL> select * from employees;,

则需要有关 employees 表中的列的信息。

步骤 4:定义

在定义阶段,将为所定义的接收提取值的变量指定位置、大小和数据类型。这些变量称为定义变量。必要时,Oracle DB 将执行数据类型转换。

用户在使用 SQL*Plus 之类的工具时通常看不到这两个步骤。但是,使用 DBMS_SQL 或 OCI 时,必须在客户机上指定输出数据和设置区域。

 

步骤 5 和步骤 6:绑定和并行化

  • 绑定任何绑定值:

–启用内存地址以存储数据值

–即使绑定值发生更改,也允许使用共享 SQL

  • 使语句并行化:

–SELECT

–INSERT

–UPDATE

–MERGE

–DELETE

–CREATE

–ALTER

 

步骤 5:绑定

此时,Oracle DB 已知道 SQL 语句的含义,但现有的信息仍不足以运行语句。Oracle DB 还需要语句中列出的所有变量的值。获取这些值的过程称为绑定变量。

步骤 6:并行化

Oracle DB 可以并行执行 SQL 语句(如 SELECT、INSERT、UPDATE、MERGE 和 DELETE)以及一些 DDL 操作(如创建索引、创建含子查询的表和针对分区的操作)。并行化将使多个服务器进程执行 SQL 语句的工作,因此可以加快完成速度。

并行化会将一条语句的工作拆分给多个从属进程。

在分析时已经确定了语句是否可并行化,并建立了相应的并行计划。在执行时即可实施此计划(如果有足够的可用资源)。

 

步骤 7 到步骤 9

  • 执行:

–促使 SQL 语句产生所需的结果

  • 提取行:

–放入定义的输出变量中

–以表格式返回查询结果

–数组提取机制

  • 关闭游标。

此时,Oracle DB 拥有所有必要的信息和资源,因此执行语句。如果语句是一个查询(不带 FOR UPDATE 子句)语句,则不需要锁定任何行,因为没有更改任何数据。但是,如果语句是 UPDATE 语句或 DELETE 语句,则在下一次对事务处理执行 COMMIT、ROLLBACK 或 SAVEPOINT 之前受该语句影响的所有行都将被锁住。这样可以确保数据的完整性。

对于有些语句,可以指定执行次数。这称为数组处理。假定执行次数为 n,则绑定和定义位置步骤要在大小为 n 的数组开始时完成。

在提取阶段,选择行并对行进行排序(如果查询要求),而且每个后续提取操作都会检索另一行结果,直到提取完最后一行为止。

处理 SQL 语句的最后一个阶段是关闭游标。

 

SQL 语句处理 PL/SQL:示例

SQL> variable c1 number

SQL> execute :c1 := dbms_sql.open_cursor;

SQL> variable b1 varchar2

SQL> execute dbms_sql.parse

  2  (:c1

  3  ,’select null from dual where dummy = :b1′

  4  ,dbms_sql.native);

SQL> execute :b1:=’Y’;

SQL> exec dbms_sql.bind_variable(:c1,’:b1′,:b1);

SQL> variable r number

SQL> execute :r := dbms_sql.execute(:c1);

SQL> variable r number

SQL> execute :r := dbms_sql.close_cursor(:c1);

 

此示例汇总了前面讨论的各个步骤。

注:在此示例中,未展示提取操作。还可以将 EXECUTE 和 FETCH 操作组合为 EXECUTE_AND_FETCH,在一次调用中一起执行 EXECUTE 和 FETCH。用于远程数据库时,这可以减少网络往返次数。

 

SQL 语句分析:概览

sql-optimizer-4

 

分析是 SQL 语句处理中的一个阶段。应用程序发出 SQL 语句时,会对 Oracle DB 发出一个分析调用。在分析调用过程中,Oracle DB 会执行下列操作:

  • 检查语句的语法和语义是否有效
  • 确定发出语句的进程是否有运行语句的权限
  • 为语句分配一个专用 SQL 区域
  • 确定在库高速缓存中是否已存在共享 SQL 区域(该区域包含语句分析后的表示形式)。如果已存在,则用户进程使用此分析后的表示形式,并立即运行语句。如果不存在,则 Oracle DB 生成语句分析后的表示形式,用户进程在库高速缓存中为语句分配一个共享 SQL 区域,并将语句分析后的表示形式存储在该区域中。

请注意对 SQL 语句发出分析调用的应用程序与实际分析语句的 Oracle DB 之间的差异。

  • 应用程序发出的分析调用将 SQL 语句与某个专用 SQL 区域关联起来。语句与专用 SQL 区域关联之后,可以重复运行该语句,不需要应用程序发出分析调用。

Oracle DB 的分析操作会为 SQL 语句分配一个共享 SQL 区域。为语句分配了共享 SQL 区域后,可以重复运行该语句,无需重新分析。

 

相对于执行来说,分析调用和分析的成本会高得多,因此,请尽可能少执行它们。

注:虽然分析某个 SQL 语句会验证该语句,但分析只能找出可在语句执行之前发现的错误。因此,某些错误可能无法通过分析发现。例如,只有在执行阶段才会发觉并报告数据转换错误或数据错误(例如,尝试在主键中输入重复的值)和死锁这类错误或情形。

 

为什么需要优化程序

 sql-optimizer-5

 

sql-optimizer-6

优化程序的目标始终是尽快返回正确的结果。

查询优化程序会考虑可用的访问路径,并采纳从 SQL 语句访问的方案对象(表或索引)的统计数据中获取的信息,尝试确定最高效的执行计划。

查询优化程序执行下列步骤:

  1. 优化程序根据可用的访问路径为 SQL 语句生成一组可能的计划。
  2. 优化程序在评估每个计划的成本时,会根据数据字典中的统计信息了解表的数据分配和存储特征,还会考虑语句访问的索引。
  3. 优化程序比较各个计划的成本,并选择成本最低的计划。

注:由于为特定查询找出最佳可行计划十分复杂,优化程序的目标是查找一个“好”计划,通常称为成本最佳计划。

 

幻灯片中的示例显示,如果统计信息发生变化,优化程序会调整其执行计划。在本例中,统计信息显示 80% 的雇员是经理。在该假定情形中,与使用索引相比,全表扫描可能是一种更好的解决方案。

 

在硬解析操作过程中进行优化

 

sql-optimizer-7

优化程序为 SQL 语句创建执行计划。

提交到系统的 SQL 查询先通过分析程序运行;分析程序会检查语法并分析语义。此阶段的结果称为语句分析后的表示形式,由一组查询块组成。查询块是一种针对表的自包含 DML。查询块可以是顶层 DML,也可以是子查询。然后,将这种分析后的表示形式发送到优化程序;优化程序执行三种主要功能:转换、评估和生成执行计划。

在执行成本计算之前,系统可能会将语句转换为等效语句,并计算等效语句的成本。
根据 Oracle DB 的版本,有些转换不会执行,有些转换始终执行,还有一些转换,虽然执行了,但由于其高成本,最后被弃用。

查询转换器的输入是分析后的查询,该查询用一组相互关联的查询块表示。查询转换器的主要目标是确定更改查询结构是否有益,以便生成更好的查询计划。查询转换器采用了多种查询转换技术,如应用传递性、合并视图、推入谓词、对子查询解除嵌套、重写查询、星形转换和 OR 扩展。

 

转换器:OR 扩展示例

sql-optimizer-8

如果查询包含 WHERE 子句,并且有多个使用 OR 运算符组合的条件,则优化程序会将其转换为使用集合运算符 UNION ALL 的等效复合查询(如果这样可以提高查询执行效率
的话)。

例如,如果每个条件都可以单独使用索引访问路径,优化程序就可以进行转换。优化程序会为产生的语句选择这样的执行计划:该计划使用不同的索引多次访问表,然后将各次的结果放到一起。如果估计的成本比原始语句的成本低,则执行此转换。

幻灯片中的示例假定 JOB 列和 DEPTNO 列上都有索引。这样,优化程序可以将原始查询转换为等效的查询(转换后的查询如幻灯片中所示)。基于成本的优化程序 (CBO) 在决定是否进行转换时,会将使用全表扫描执行原始查询的成本与执行转换后的查询的成本进行比较。

 

转换器:子查询解除嵌套示例

sql-optimizer-9

 

为了解除查询嵌套,优化程序可能会选择将原始查询转换为等效的 JOIN 语句,然后优化 JOIN 语句。

仅当生成的 JOIN 语句保证能像原始语句一样返回完全相同的行时,优化程序才会执行此项转换。通过此项转换,优化程序可以利用联接优化程序技术。

在幻灯片上的示例中,如果 customers 表的 CUSTNO 列是主键或者有一个 UNIQUE 约束条件,优化程序就可以将复杂查询转换为所示的 JOIN 语句(该语句保证可以返回相同的
数据)。

如果优化程序不能将复杂语句转换为 JOIN 语句,则将为父语句和子查询分别选择执行计划,就像它们是单独的语句一样。然后,优化程序执行子查询,并使用返回的行执行父
查询。

注:子查询包含聚集函数(如 AVG)的复杂查询不能转换为 JOIN 语句。

 

 

转换器:视图合并示例

sql-optimizer-10

为了将视图查询合并到访问语句的一个引用查询块中,优化程序会将视图的名称替换为查询块中其基表的名称,并将视图查询的 WHERE 子句的条件添加到访问查询块的 WHERE 子句中。

这种优化适用于选择-映射-联接视图,这种视图仅包含选择、映射和联接。即,这类视图不包含集合运算符、聚集函数、DISTINCT、GROUP BY、CONNECT BY 等等。

本例中的视图用于显示在部门 10 中工作的所有雇员的信息。

在幻灯片中,位于视图定义下方的查询要访问该视图。该查询将选择 ID 大于 7800 并且在部门 10 中工作的雇员。

优化程序可以将该查询转换为幻灯片中所示的等效查询,该转换后的查询将访问视图的基表。

如果在 DEPTNO 列或 EMPNO 列上有索引,则生成的 WHERE 子句就可使这些索引变为可用。

 

 

转换器:谓词推入示例

sql-optimizer-11

优化程序可以对访问不可合并视图的查询块进行转换,即将查询块的谓词推入视图查询中。

在幻灯片上的示例中,two_emp_tables 视图是两个 employee 表的并集。该视图是用一个复合查询定义的,该复合查询使用集合运算符 UNION 连接。

在幻灯片中,位于视图定义下方的查询要访问该视图。该查询选择任意一个表中在部门 20 中工作的所有雇员的 ID 和姓名。

由于视图被定义为复合查询,因此优化程序无法将视图查询合并到访问查询块中。优化程序此时可以采用另一种方法,即将访问语句的谓词(WHERE 子句的条件 deptno = 20)推入视图的复合查询中,以此转换访问语句。幻灯片中展示了转换后的等效查询。

如果在这两个表的 DEPTNO 列上有索引,则生成的 WHERE 子句就可使这些索引变为可用。

 

转换器:传递性示例

 sql-optimizer-12

如果 WHERE 子句中的两个条件有某个公用列,则优化程序有时可以使用传递性原则推断出第三个条件。然后,优化程序可以使用推断出的条件来优化语句。

通过该推断出的条件,基于原始条件不可用的索引访问路径就能变为可用。

幻灯片中的示例展示了这种情况。原始查询的 WHERE 子句包含两个条件,其中的每个条件都使用 EMP.DEPTNO 列。通过使用传递性,优化程序可以推断出下列条件:dept.deptno = 20

如果在 DEPT.DEPTNO 列上存在索引,通过此条件,使用该索引的访问路径就变为可用。

注:优化程序仅推断将列与常量表达式关联的条件,而非将列与其它列关联的条件。

 

基于成本的优化程序

  • 代码片段:

–评估器

–计划生成器

  • 评估器确定计划生成器提出的优化建议的成本。

–成本:优化程序对优化特定语句所需的标准化 I/O 次数的最恰当评估

  • 计划生成器:

–尝试不同的语句优化技术

–使用评估器计算每个优化建议的成本

–根据成本选择最佳优化建议

–为最佳优化方案生成执行计划

 

评估器代码和计划生成器代码通常合称为基于成本的优化程序 (CBO)。

评估器生成三种类型的度量:选择性、基数和成本。这些度量彼此相关。基数是根据选择性导出的,成本通常取决于基数。评估器的最终目的是评估指定计划的整体成本。如果有可用的统计信息,则评估器在计算度量时将使用这些信息提高准确度。

计划生成器的主要功能是尝试指定查询的各种可能计划,并挑选出成本最低的计划。有许多不同的计划可以使用,因为可以使用不同访问路径、联接方法和联接顺序的各种组合,以不同方式访问和处理数据,并生成相同的结果。某个查询块可能计划的数量与 FROM 子句中的联接项目的数量成比例。此数量会随联接项目的数量呈幂指数增长。

优化程序使用各种信息来确定最佳路径:WHERE 子句、统计信息、初始化参数、提供的提示和方案信息。

 

评估器:选择性

 sql-optimizer-13

  • 选择性是特定谓词或谓词组合检索到的行集占总行数的估计
    比例。
  • 它以一个 0.0 到 1.0 之间的值表示:

–选择性高:行数比例小

–选择性低:行数比例大

  • 选择性计算:

–如果没有统计信息:使用动态采样

–如果没有直方图:假定行平均分布

  • 统计信息:

–DBA_TABLES 和 DBA_TAB_STATISTICS (NUM_ROWS)

–DBA_TAB_COL_STATISTICS(NUM_DISTINCT、DENSITY、HIGH/LOW_VALUE…)

 

选择性表示行集中的一部分行。行集可以是一个基表、一个视图或者联接或 GROUP BY 运算符的结果。选择性与查询谓词(如 last_name = ‘Smith’)或谓词组合(如 last_name = ‘Smith’ AND job_type = ‘Clerk’)相关联。谓词的作用相当于过滤器,可以从行集中过滤掉一定数量的行。因此,谓词的选择性表示行集中通过谓词测试的行占总行数的百分比。选择性用从 0.0 到 1.0 的值表示。选择性 0.0 表示未从行集中选择任何行,选择性 1.0 表示选择了所有行。

如果没有可用的统计信息,则优化程序将使用动态采样或内部默认值,具体取决于 OPTIMIZER_DYNAMIC_SAMPLING 初始化参数的值。如果有可用的统计信息,则评估器将使用这些信息来评估选择性。例如,对于等式谓词 (last_name = ‘Smith’),选择性等于 LAST_NAME 非重复值的数量 (n) 的倒数,因为查询选择的行包含 n 个不同值中的一个。因此,这假定数值是平均分布的。如果在 LAST_NAME 列中有可用的直方图,则评估器将使用该图,而不使用非重复值的数量。直方图获取了列中不同值的分布,因此得出的选择性估计值更符合实际。

注:如果列包含的值的重复项数量变化很大(数据偏差),则列必须有直方图。

 

评估器:基数

 sql-optimizer-14

  • 执行计划中的某个特定操作预计会检索的行数。
  • 用于确定联接、过滤和排序成本的重要数值
  • 简单示例:

SELECT days FROM courses WHERE dev_name = ‘ANGEL’;

 

–DEV_NAME 中的不同值数量为 203。

–COURSES 中的行数(原始基数)为 1018。

–选择性 = 1/203 = 4.926xe-03

–基数 = (1/203)x1018 = 5.01(舍入到 6)

 

查询的执行计划中的特定操作的基数表示该特定操作估计会检索的行数。大多数时候,行源会是基表、视图,或者是联接或 GROUP BY 运算符的结果。

计算联接操作的成本时,必须了解驱动行源的基数。例如,对于嵌套循环联接,驱动行源定义了系统探测内部行源的频率。

因为排序成本与要排序的行的大小和数量相关,所以基数数值对排序成本的计算也至关
重要。

在幻灯片上的示例中,优化程序根据假定的统计信息知道在 DEV_NAME 列中有 203 个不同的值,并且 COURSES 表的总行数为 1018。根据上述假定,优化程序推导出 DEV_NAME=’ANGEL’ 谓词的选择性为 1/203(假定不存在直方图),还推导出查询的基数是 (1/203)x1018。然后,将此数值舍入到最近的整数 6。

 

评估器:成本

 sql-optimizer-15

  • 成本是优化程序对优化特定语句所需的标准化 I/O 次数的最恰当评估。
  • 成本单位是一次标准化的单个块随机读取 (SRds):

–1 成本单位 = 1 SRds

  • 成本公式将三种不同的成本单位合并为标准成本单位。

语句的成本是优化程序对优化该语句所需的标准化输入/输出 (I/O) 次数的最恰当评估。本质上,成本是以单个块随机读取次数为单位的一个标准化值。

优化程序测量出的标准成本度量以单个块随机读取次数为单位,因此一个成本单位对应于一次单个块随机读取。幻灯片中所示的公式合并了三种不同的成本单位:

  • 完成所有单个块随机读取的估计时间
  • 完成所有多块读取的估计时间
  • CPU 将语句处理为一个标准成本单位的估计时间

该模型包括了 CPU 成本计算,因为在大多数情况下,CPU 使用率和 I/O 次数一样重要;它经常是成本的唯一来源(如在内存中排序、散列运算、谓词求值和高速缓存的 I/O)。

这种模型可直接用于串行执行。对于并行执行,会在计算 #SRds、#MRds 和 #CPUCycles 的估计值时进行必要的调整。

注: #CPUCycles 包括处理查询的 CPU 成本(纯 CPU 成本)和检索数据的 CPU 成本(获取缓冲区高速缓的 CPU 成本)。

 

&nbsp

计划生成器

select e.last_name, c.loc_id 
from   employees e, classes c  where  e.emp_id = c.instr_id; 


Join order[1]:  DEPARTMENTS[D]#0  EMPLOYEES[E]#1 NL Join:  Cost: 41.13  Resp: 41.13  Degree: 1 SM cost: 8.01 HA cost: 6.51Best::JoinMethod: Hash  
Cost: 6.51  Degree: 1  Resp: 6.51  Card: 106.00Join order[2]:  EMPLOYEES[E]#1  DEPARTMENTS[D]#0 NL Join:  Cost: 121.24  Resp: 121.24  Degree: 1 SM cost: 8.01 HA cost: 6.51Join order abortedFinal cost for query block SEL$1 (#0) 
All Rows Plan:Best join order: 1 
+----------------------------------------------------------------+ 
| Id  | Operation           | Name       | Rows  | Bytes | Cost  | 
+----------------------------------------------------------------+
| 0   | SELECT STATEMENT    |            |       |       |     7 | 
| 1   |  HASH JOIN          |            |   106 |  6042 |     7 |   
| 2   |   TABLE ACCESS FULL | DEPARTMENTS|    27 |   810 |     3 |   
| 3   |   TABLE ACCESS FULL | EMPLOYEES  |   107 |  2889 |     3 |   
+----------------------------------------------------------------+ 


计划生成器会尝试不同的访问路径、联接方法和联接顺序,为查询块查找各种计划。最后,计划生成器将为语句提供最佳执行计划。幻灯片展示了为 select 语句生成的优化程序跟踪文件的一个片段。如该跟踪文件所示,计划生成器有六种可能的计划(即六种不同的计划)需要测试:两种联接顺序,每种顺序有三种不同的联接方法。此示例假定不存在索引。
要检索行,可以首先将 DEPARTMENTS 表与 EMPLOYEES 表联接。对于该特定联接顺序,可以使用优化程序知道的三种可能联接机制:嵌套循环、排序合并或散列联接。对于每种可能的机制,都有相应计划的成本。最佳计划是显示在跟踪记录末尾的那个计划。
在查找成本最低的计划时,计划生成器使用内部中断来减少计划测试数量。基于当前最佳计划的成本确定是否中断。如果当前最佳成本很大,则计划生成器将提高难度(即查找其它备选计划),以便找出成本更低的更好计划。如果当前最佳成本很小,则计划生成器会快速结束搜索,因为成本的降低空间已经很小了。如果计划生成器首先尝试的初始联接顺序很合适,即所生成的计划的成本接近最佳成本,则中断的效果会很好。要找出合适的初始联接顺序很难。
注:访问路径、联接方法和计划将在“优化程序运算符”和“解释执行计划”这两课中做详细讨论。

 

控制优化程序的行为

  • CURSOR_SHARING: SIMILAR、EXACT、FORCE
  • DB_FILE_MULTIBLOCK_READ_COUNT
  • PGA_AGGREGATE_TARGET
  • STAR_TRANSFORMATION_ENABLED
  • RESULT_CACHE_MODE: MANUAL、FORCE
  • RESULT_CACHE_MAX_SIZE
  • RESULT_CACHE_MAX_RESULT
  • RESULT_CACHE_REMOTE_EXPIRATION

以下参数可以控制优化程序行为:

  • CURSOR_SHARING 确定什么样的 SQL 语句可以共享相同的游标:

-FORCE:强制那些只是有一些文字差异但其它方面相同的语句共享游标(除非这些文字会影响语句的意义)。

-SIMILAR:使那些只是有一些文字差异但其它方面相同的语句共享游标(除非这些文字会影响语句的意义或者影响计划的优化程度)。如果强制在相似(但不相同)的语句间共享游标,则可能会在某些决策支持系统 (DSS) 应用程序中或者使用存储大纲的应用程序中产生意外结果。

-EXACT:仅允许具有相同文本的语句共享同一个游标。这是默认设置。

  • DB_FILE_MULTIBLOCK_READ_COUNT 是可用于在表扫描或索引快速完全扫描过程中最大程度减少 I/O 次数的参数之一。该参数指定在一次顺序扫描过程中,一次 I/O 操作的最大块读取数。执行全表扫描或索引快速完全扫描所需的 I/O 总数取决于多种因素,如段的大小、多块读取计数以及是否对操作使用了并行执行。从 Oracle Database 10gR2 开始,此参数的默认值对应于可高效执行的最大 I/O 大小。此值与平台相关,在多数平台上为 1 MB。
  • 由于参数用块表示,因此系统会自动计算一个值,该值等于可高效执行的最大 I/O 大小除以标准块大小。请注意,如果会话数量极大,则会减少多块读取计数值,以避免缓冲区高速缓存中充斥太多表扫描缓冲区。即使默认值较大,但如果不设置此参数,优化程序也不会倾向于选择大型计划。仅当将此参数显式设置为一个较大值时,优化程序才会选择大型计划。一般情况下,如果不显式设置此参数(或设置为 0),则在计算全表扫描和索引快速完全扫描的成本时,优化程序将使用默认值 8。对于联机事务处理 (OLTP) 和批处理环境,此参数的值通常在 4 到 16 这个范围内。对于 DSS 和数据仓库环境,此参数的值越大越好。如果此参数的值很大,则优化程序就更有可能选择全表扫描,而不选择索引。
  • PGA_AGGREGATE_TARGET 指定可供与该实例关联的所有服务器进程使用的目标 PGA 内存总计。将 PGA_AGGREGATE_TARGET 设置为一个非零值相当于将 WORKAREA_SIZE_POLICY 参数设置为 AUTO。这意味着系统将自动调整内存密集型 SQL 运算符(如排序、GROUP BY、散列联接、位图联接和位图创建)使用的 SQL 工作区大小。非零值是此参数的默认值,因为除非另行指定,否则系统会将其设置为 SGA 大小的 20% 或 10 MB(取两者中的较大值)。将 PGA_AGGREGATE_TARGET 设置为 0 时,也会自动将 WORKAREA_SIZE_POLICY 参数设置为 MANUAL。这意味着会使用 *_AREA_SIZE 参数来调整 SQL 工作区的大小。系统会尝试通过调整工作区大小来适应专用内存,将专用内存大小保持在此参数指定的目标值以下。如果增加该参数值,则也会间接地增加分配给工作区的内存。因此,可以有更多的内存密集型操作完全在内存中运行,只有较少的内存密集型操作将转至磁盘运行。设置此参数时,应检查系统上可供 Oracle 实例使用的总内存,然后从中减去 SGA。可以将剩余内存分配至 PGA_AGGREGATE_TARGET。
  • STAR_TRANSFORMATION_ENABLED 确定是否将基于成本的查询转换应用于星形查询。此项优化将在“案例分析:星形转换”一课中介绍。
  • 查询优化程序根据初始化参数文件中 RESULT_CACHE_MODE 参数的设置管理结果高速缓存机制。可以使用此参数确定优化程序是否将查询结果自动发送到结果高速缓存中。可能的参数值包括 MANUAL 和 FORCE:

-设置为 MANUAL(默认值)时,必须使用 RESULT_CACHE 提示,指定要在高速缓存中存储特定结果。

-如果设置为 FORCE,则所有结果都将存储在高速缓存中。对于 FORCE 设置,如果语句中包含 [NO_]RESULT_CACHE 提示,则该提示优先于参数设置。

 

  • 分配给结果高速缓存的内存大小取决于 SGA 的内存大小以及内存管理系统。可以通过设置 RESULT_CACHE_MAX_SIZE 参数来更改分配给结果高速缓存的内存。如果将结果高速缓存的值设为 0,则会禁用此结果高速缓存。此参数的值将四舍五入到不超过指定值的 32 KB 的最大倍数。如果四舍五入得到的值是 0,则会禁用该功能。
  • 使用 RESULT_CACHE_MAX_RESULT 参数可以指定任一结果可使用的最大高速缓存量。默认值为 5%,但可指定 1 到 100 之间的任何百分比值。
  • 使用 RESULT_CACHE_REMOTE_EXPIRATION 参数可以指定依赖于远程数据库对象的结果保持有效的时间(以分钟为单位)。默认值为 0,表示不会高速缓存使用远程对象的结果。例如,如果结果使用的远程表在远程数据库上发生了更改,则将此值设置为一个非零值可能会产生过时的答案。

 

控制优化程序的行为

  • OPTIMIZER_INDEX_CACHING
  • OPTIMIZER_INDEX_COST_ADJ
  • OPTIMIZER_FEATURES_ENABLED
  • OPTIMIZER_MODE: ALL_ROWS、FIRST_ROWS、FIRST_ROWS_n
  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
  • OPTIMIZER_USE_SQL_PLAN_BASELINES
  • OPTIMIZER_DYNAMIC_SAMPLING
  • OPTIMIZER_USE_INVISIBLE_INDEXES
  • OPTIMIZER_USE_PENDING_STATISTICS
  • OPTIMIZER_INDEX_CACHING 参数与嵌套循环或 INLIST 迭代程序一起使用时,可控制索引探测的成本计算。OPTIMIZER_INDEX_CACHING 的值范围是 0 到 100,表示缓冲区高速缓存中的索引块的百分比,可用于修改优化程序有关嵌套循环和 INLIST 迭代程序的索引高速缓存的假设。值 100 表示有可能在缓冲区高速缓存中找到 100% 的索引块,优化程序会相应地调整索引探测或嵌套循环的成本。此参数的默认值为 0,该值将使优化程序采取默认行为。请慎重使用此参数,因为执行计划可能会因采用索引高速缓存而更改。
  • OPTIMIZER_INDEX_COST_ADJ 可用于调整优化程序在选择访问路径时的行为,提高或降低其选择索引的倾向性。即,提高或降低优化程序选择索引访问路径,而不选择全表扫描的可能性。值的范围是 1 到 10000。此参数的默认值为 100%,此时优化程序将按常规成本评估索引访问路径。设为其它值时,优化程序以其相对于常规成本的百分比评估访问路径。例如,设置为 50 将使索引访问路径的成本大约是正常成本的一半。
  • OPTIMIZER_FEATURES_ENABLED 相当于一个综合参数,用于启用某一 Oracle 版本号对应的一系列优化程序功能。

例如,如果您将数据库从版本 10.1 升级到 11.1,但希望  保持版本 10.1 的优化程序行为,则可通过将此参数设置为 10.1.0 做到这一点。以后,可以通过将参数设置为 11.1.0.6,尝试版本 11.1 及其之前的版本引入的增强功能。但是,不建议将 OPTIMIZER_FEATURES_ENABLE 参数显式设置为一个较早的版本。要避免因执行计划更改而可能出现的 SQL 性能下降,请考虑换用 SQL 计划管理。

  • OPTIMIZER_MODE 用于建立在选择实例或会话的优化方法时使用的默认行为。可能的值包括:

-ALL_ROWS:优化程序对会话中的所有 SQL 语句使用基于成本的方法,不考虑是否存在统计信息;且优化目标为吞吐量达到最佳(用尽可能少的资源完成整个语句)。这是默认值。

-FIRST_ROWS_n:优化程序使用基于成本的方法,不考虑是否存在统计信息;且优化目标为返回前 n 个行的响应时间达到最佳,其中 n 可以等于 1、10、100 或 1000。

-FIRST_ROWS:优化程序结合使用成本和试探值查找一个最佳计划,实现快速提供前几行。使用试探值有时会导致查询优化程序生成的计划的成本明显大于不应用试探值的计划的成本。提供 FIRST_ROWS 是为了向后兼容和保持计划稳定性;其它情况下,可换用 FIRST_ROWS_n

  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 启用或禁用自动识别可重复的 SQL 语句,以及为此类语句生成 SQL 计划基线。
  • OPTIMIZER_USE_SQL_PLAN_BASELINES 启用或禁用 SQL 管理库中存储的 SQL 计划基线。如果启用,优化程序将为正在编译的 SQL 语句查找 SQL 计划基线。如果在 SQL 管理库中找到了一个 SQL 计划基线,则优化程序将计算每个基线计划的成本,并挑选成本最低的计划。
  • OPTIMIZER_DYNAMIC_SAMPLING 控制优化程序执行的动态采样的级别。如果 OPTIMIZER_FEATURES_ENABLE 设置为:

-10.0.0 或更高版本,则默认值为 2

-9.2.0,则默认值为 1

-9.0.1 或更低,则默认值为 0

  • OPTIMIZER_USE_INVISIBLE_INDEXES 允许或禁止使用不可见的索引。
  • OPTIMIZER_USE_PENDING_STATISTICS 指定优化程序在编译 SQL 语句时是否使用暂挂统计信息。

注:本课程以后将介绍不可见的索引、暂挂统计信息和动态采样。

 

优化程序功能和 Oracle DB 版本  OPTIMIZER_FEATURES_ENABLED

sql-optimizer-16

OPTIMIZER_FEATURES_ENABLED 相当于一个综合参数,用于启用某一 Oracle 版本号对应的一系列优化程序功能。幻灯片中的表描述了部分优化程序功能,这些功能是根据 OPTIMIZER_FEATURES_ENABLED 参数中指定的值启用的。

Oracle Optimizer Hint优化器提示分类表

Oracle Optimizer Hint优化器提示分类表

 

 

 

分类 9i R1 9i R2 10g R1 10g R2 11g R1 11g R2
优化器模式 ALL_ROWS ALL_ROWS ALL_ROWS ALL_ROWS ALL_ROWS ALL_ROWS
FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n)
CHOOSE CHOOSE
RULE RULE RULE ※askmaclean.com
Hints OPTIMIZER_FEATURES_ENABLE
针对访问路径 FULL FULL FULL FULL FULL FULL
access path的HINT ROWID ROWID
CLUSTER CLUSTER CLUSTER CLUSTER CLUSTER CLUSTER
HASH HASH HASH HASH HASH HASH
INDEX INDEX INDEX INDEX INDEX INDEX
NO_INDEX NO_INDEX NO_INDEX NO_INDEX NO_INDEX NO_INDEX
INDEX_ASC INDEX_ASC INDEX_ASC INDEX_ASC INDEX_ASC INDEX_ASC
INDEX_COMBINE INDEX_COMBINE INDEX_COMBINE INDEX_COMBINE INDEX_COMBINE INDEX_COMBINE
INDEX_JOIN INDEX_JOIN INDEX_JOIN INDEX_JOIN INDEX_JOIN INDEX_JOIN
INDEX_DESC INDEX_DESC INDEX_DESC INDEX_DESC INDEX_DESC INDEX_DESC
INDEX_FFS INDEX_FFS INDEX_FFS INDEX_FFS INDEX_FFS INDEX_FFS
AND_EQUAL AND_EQUAL NO_INDEX_FFS NO_INDEX_FFS NO_INDEX_FFS NO_INDEX_FFS
INDEX_SS INDEX_SS INDEX_SS INDEX_SS
NO_INDEX_SS NO_INDEX_SS NO_INDEX_SS NO_INDEX_SS
INDEX_SS_ASC INDEX_SS_ASC INDEX_SS_ASC INDEX_SS_ASC
INDEX_SS_DESC INDEX_SS_DESC INDEX_SS_DESC INDEX_SS_DESC
关于转换的HINT NO_QUERY_TRANSFORMATION NO_QUERY_TRANSFORMATION NO_QUERY_TRANSFORMATION NO_QUERY_TRANSFORMATION
USE_CONCAT USE_CONCAT USE_CONCAT USE_CONCAT USE_CONCAT USE_CONCAT
NO_EXPAND NO_EXPAND NO_EXPAND NO_EXPAND NO_EXPAND NO_EXPAND
REWRITE REWRITE REWRITE REWRITE REWRITE REWRITE
EXPAND_GSET_TO_UNION
NOREWRITE NOREWRITE NO_REWRITE NO_REWRITE NO_REWRITE NO_REWRITE
MERGE MERGE MERGE MERGE MERGE MERGE
NO_MERGE NO_MERGE NO_MERGE NO_MERGE NO_MERGE NO_MERGE
STAR_TRANSFORMATION STAR_TRANSFORMATION STAR_TRANSFORMATION STAR_TRANSFORMATION STAR_TRANSFORMATION STAR_TRANSFORMATION
NO_STAR_TRANSFORMATION NO_STAR_TRANSFORMATION NO_STAR_TRANSFORMATION NO_STAR_TRANSFORMATION
FACT FACT FACT FACT FACT FACT
NO_FACT NO_FACT NO_FACT NO_FACT NO_FACT NO_FACT
UNNEST UNNEST UNNEST UNNEST
NO_UNNEST NO_UNNEST NO_UNNEST NO_UNNEST
JOIN order HINT LEADING LEADING LEADING LEADING
ORDERED ORDERED ORDERED ORDERED ORDERED ORDERED
STAR STAR
JOIN操作HINT USE_NL USE_NL USE_NL USE_NL USE_NL USE_NL
NO_USE_NL NO_USE_NL NO_USE_NL NO_USE_NL
USE_NL_WITH_INDEX USE_NL_WITH_INDEX USE_NL_WITH_INDEX USE_NL_WITH_INDEX
USE_MERGE USE_MERGE USE_MERGE USE_MERGE USE_MERGE USE_MERGE
NO_USE_MERGE NO_USE_MERGE NO_USE_MERGE NO_USE_MERGE
USE_HASH USE_HASH USE_HASH USE_HASH USE_HASH USE_HASH
NO_USE_HASH NO_USE_HASH NO_USE_HASH NO_USE_HASH
DRIVING_SITE DRIVING_SITE (参见其他HINT) (参见其他HINT) (参见其他HINT) (参见其他HINT)
LEADING LEADING
HASH_AJ、MERGE_AJ、NL_AJ HASH_AJ、MERGE_AJ、NL_AJ
HASH_SJ、MERGE_SJ、NL_SJ HASH_SJ、MERGE_SJ、NL_SJ
特殊 CHANGE_DUPKEY_ERROR_INDEX
IGNORE_ROW_ON_DUPKEY_INDEX
RETRY_ON_ROW_CHANGE
并行执行HINT PARALLEL PARALLEL PARALLEL PARALLEL PARALLEL PARALLEL
NOPARALLEL NOPARALLEL NO_PARALLEL NO_PARALLEL
PQ_DISTRIBUTE PQ_DISTRIBUTE PQ_DISTRIBUTE PQ_DISTRIBUTE PQ_DISTRIBUTE PQ_DISTRIBUTE
PARALLEL_INDEX PARALLEL_INDEX PARALLEL_INDEX PARALLEL_INDEX PARALLEL_INDEX PARALLEL_INDEX
NOPARALLEL_INDEX NOPARALLEL_INDEX NO_PARALLEL_INDEX NO_PARALLEL_INDEX NO_PARALLEL_INDEX NO_PARALLEL_INDEX
其他HINT APPEND APPEND APPEND APPEND APPEND APPEND
NOAPPEND NOAPPEND NOAPPEND NOAPPEND NOAPPEND NOAPPEND
APPEND_VALUES
CACHE CACHE CACHE 诗檀软件 CACHE CACHE CACHE
NOCACHE NOCACHE NOCACHE NOCACHE NOCACHE NOCACHE
UNNEST UNNEST
NO_UNNEST NO_UNNEST
PUSH_PRED PUSH_PRED PUSH_PRED PUSH_PRED PUSH_PRED PUSH_PRED
NO_PUSH_PRED NO_PUSH_PRED NO_PUSH_PRED NO_PUSH_PRED NO_PUSH_PRED NO_PUSH_PRED
PUSH_SUBQ PUSH_SUBQ PUSH_SUBQ PUSH_SUBQ PUSH_SUBQ PUSH_SUBQ
NO_PUSH_SUBQ NO_PUSH_SUBQ NO_PUSH_SUBQ NO_PUSH_SUBQ NO_PUSH_SUBQ
QB_NAME QB_NAME QB_NAME QB_NAME
ORDERED_PREDICATES ORDERED_PREDICATES
CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT
DYNAMIC_SAMPLING DYNAMIC_SAMPLING DYNAMIC_SAMPLING DYNAMIC_SAMPLING DYNAMIC_SAMPLING
SPREAD_MIN_ANALYSIS
MODEL_MIN_ANALYSIS MODEL_MIN_ANALYSIS MODEL_MIN_ANALYSIS
DRIVING_SITE DRIVING_SITE DRIVING_SITE DRIVING_SITE

分享Tom Kyte的ALL ABOUT BINDS 关注ORACLE绑定变量技术

分享Tom Kyte的ALL ABOUT BINDS 关注ORACLE绑定变量技术完整版,下载地址:

https://www.askmaclean.com/wp-content/uploads/2014/09/AllAboutBinds.ppt

 

 

[gview file=”https://www.askmaclean.com/wp-content/uploads/2014/09/AllAboutBinds.ppt”]

分享一张Oracle CBO(cost based optimizer)的图 从APP到DB到HW

分享一张Oracle CBO(cost based optimizer)的图 从APPlication 到DB数据库到Hardware 硬件

cbo sql performance

【转】11.2.0.4 上需要注意的 Performance 与 Wrong Results 问题 (文档 ID 1985032.1)

适用于:

Oracle Database – Enterprise Edition – 版本 11.2.0.4 到 11.2.0.4 [发行版 11.2]
Oracle Database – Standard Edition – 版本 11.2.0.4 到 11.2.0.4 [发行版 11.2]
Oracle Database – Personal Edition – 版本 11.2.0.4 到 11.2.0.4 [发行版 11.2]
本文档所含信息适用于所有平台

用途

本文档的目的是公布一些在 11.2.0.4.x 版本中推荐的补丁,以避免有关 Performance 与 Wrong Results 方面的问题。
对于其他版本,请参考:

Document 1320966.1 Things to Consider to Avoid Poor Performance or Wrong Results on 11.2.0.2
Document 1392633.1 Things to Consider to Avoid Poor Performance or Wrong Results on 11.2.0.3
Document 2034610.1 Things to Consider to Avoid Poor Performance or Wrong Results on 12.1.0.2

最佳实践

如何主动避免问题发生及做好诊断信息的收集

有些问题是无法预见的,但大部分其它的问题如果及早发现一些征兆其实是可以避免的。同时,如果问题确实发生了,那么收集问题发生时的信息就非常重要。有关于如何主动避免问题及诊断信息的收集,请参见:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues

Document 601807.1 Oracle 11gR1 Upgrade Companion
Document 785351.1 Oracle 11gR2 Upgrade Companion

适用范围

本文档主要推荐一些 11.2.0.4 的 PSU 中未包含的补丁,因为这些补丁或者包含了 Optimizer(优化器)层面的修复,或者 PSU 即将纳入但是尚未发布。
如果没有找到对应平台和版本的补丁,请提交一个服务请求,同时附上已打过补丁的列表(opatch lsinventory -detail),以及其它准备要打的补丁列表。

 

详细信息

尽管我们尝试在每个季度发行的 PSU 中包含重要的修复,但是 PSU 能包含的修复是有限制的。限制之一是对 Optimizer 层的修复,因为这些修复可能会改变执行计划,导致从一个 PSU 移植到另一个 PSU 需要做更多的测试;另外,有些修复可能还没有包括在已发布的 PSU 中。

除了打最新的 PSU 外,请考虑如下补丁,以避免某些 Performance、ORA-600/ORA-7445 错误和一些 Wrong Results 问题。

请注意:补丁的发布是基于具体案例的。这篇文档中提到的补丁可能不是所有的平台都有,或者有的平台不便于提供。它们在这篇文档中被列出不代表我们保证它们 一定会被提供。就像其它的补丁一样,提供的前提是这个问题确实对客户的业务造成了影响。对于不同的应用程序及使用的数据库特性,遇到这些 Defect 的可能性是很低或者没有的。比如特定于 AIX 上的 Defect 是不会在别的平台上遇到的,还有就是 RAC 特定的 Defect 不会在非 RAC 的环境中遇到。更多关于错误修正政策的信息,请参见以下文档:

Document 209768.1 Database, FMW, EM Grid Control, and OCS Software Error Correction Support Policy

适用于所有平台的补丁

作为最佳实践,请根据 Document 756671.1 安装 11.2.0.4 最新的 PSU,最新的 PSU 修复了许多这个文档中没有列出的已知问题。此外,基于不同的 PSU 版本选择应用以下补丁:

No PSU PSU 1 2 3 4 5 6 7 8 160119 Bugs Fixed
Patch 21392660 for 11.2.0.4.0 Patch 21765124 for 11.2.0.4.4 Document 18255105.8 Patch for upgrade scripts to identify histograms affected by fix of bug 15898932
注意,需要在运行11.2.0.4升级脚本之前打这个patch, 否则就没有用了.
Document 19855835.8 Upgrade slow when reorganizing large stats history tables
注意,需要在运行11.2.0.4升级脚本之前打这个patch, 否则就没有用了.
Patch 16188701 for 11.2.0.4.0 Document 16188701.8 ORA-942 / ORA-7445 [ksscct] invoker right procedures run by users in parallel
Patch 17551261 for 11.2.0.4.0 Document 17551261.8 ORA-904 “from$_subquery$_003”.with query rewrite
Patch 21394872 for 11.2.0.4.01 Document 16837274.8 Cardinality feedback produces poor subsequent plan
Document 20476175.8 High VERSION_COUNT (in V$SQLAREA) for query with OPT_PARAM(‘_fix_control’) hint
Document 21091518.8 Extend fix of bug 18304693 to Partition Views (取代了 Document 18304693.8 Suboptimal higher cost plan chosen for Cost-Based Query Transformation)
Patch 20879889 for 11.2.0.4.0 Patch 20879889 for 11.2.0.4.2 Document 20879889.8 Open cursor leak from DML on table with a materialized view log
Patch 18199537 for 11.2.0.3.0 PSU 4 及之后的版本已修复 Document 18199537.8 RAC database becomes almost hung when large amount of row cache are used in shared pool
Patch 13645875 for 11.2.0.4.0 PSU 3 及之后的版本已修复 Document 13645875.8 ORA-7445[qerixFetchFastFullScan] on update of IOT with secondary index
Patch 17332800 for 11.2.0.4.0 PSU 1 及之后的版本已修复 Document 17332800.8 Wrong results with IOTs and fix of bug 13705338 present

1 如果您也想安装 Document 2034706.1里的补丁,那么Patch 21394872 会与 Patch 22307860 发生冲突。建议安装11.2.0.4.0上的Patch 21788385 来取代这两个补丁从而解决冲突

【Oracle SQL优化】Oracle优化器如何计算索引成本

我们介绍过  【CBO Optimizer优化器】IX_SEL索引选择率  在这个基础上介绍Oracle CBO优化器如何计算索引成本Index Cost

 

 

基本上这些信息可以通过10053 trace去猜测和获得,但下面仅仅是简单的组合例子解释CBO如何计算index cost的公式,实际的情况千差万别,复杂得多。

 

基本上索引的成本被分成2部分:

  •  访问索引块的成本
  • 对应去表上查找的成本

 

比较细的粒度的公式如下:
Selectivity of the combined leading columns * Number of Blocks in the Index
+
Selectivity of all indexed columns * Number of blocks likely to be visited in the
table (ie Clustering factor)
+ usually more minor points such as levels in the index +CPU

 

但大多数情况下 我们用下面的公式就可以了:

 

 

Index cost + Table look up costs:
(ix_sel: * #LB: ) + (ix_sel_with_filters: * CLUF: )

 

IX_SEL常为查询中参考到的所有被索引的字段的DISTINCT值累乘,如在查询中涉及到3个索引字段 A,B,C则选择性为:

1/ NDV( A * B * C)

NDV 为 number of distinct values

 

 

举个例子来说

 

 

select *
from zzrfaccna
where rclnt=:A0 and rldnr=:A1 and rbukrs=:A2
and ryear=:A3 and racct=:A4 and poper=:A5

在10053中可以看到

Column (#14): RBUKRS(VARCHAR2)
AvgLen: 5.00 NDV: 20 Nulls: 0 Density: 0.05
Column (#15): RACCT(VARCHAR2)
AvgLen: 11.00 NDV: 2213 Nulls: 0 Density: 4.5188e-04
Index: ZZRFACCNA~Z02 Col#: 14 15 16 21 22
LVLS: 4 #LB: 4180960 #DK: 222921 LB/K: 18.00 DB/K: 918.00 CLUF: 204796900.00

Access Path: index (RangeScan)
Index: ZZRFACCNA~Z02
resc_io: 4727.00 resc_cpu: 48015032
ix_sel: 2.2594e-05 ix_sel_with_filters: 2.2594e-05
Cost: 4753.68 Resp: 4753.68 Degree: 1

 

这里仅仅索引的前导列用来计算IX_SEL,所以这里的IX_SEL为

 

1/ (20 * 2213) = 1 / 44260 = 2.2593 e-5

 

由于查询中没有更多索引字段,所以其ix_sel_with_filters 等于IX_SEL

 

则该成本Cost计算为

 

( ix_sel: 2.2594e-05 * #LB: 4180960 ) + ( ix_sel_with_filters: 2.2594e-05 *
CLUF: 204796900.00 )
(94) + (4627) (+ LVL + CPU) = ~ 4727

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库服务团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

【Oracle性能优化】从10.2.0.5升级到11.2出现LOG FILE SYNCS等待事件显著增长的性能问题

如果 你遇到从10.2.0.5升级到11.2出现LOG FILE SYNCS等待事件显著增长的性能问题,那么有必要读一下这篇文章了。

 

在以往的经验中如果遇到这种场景 ,那么 优先考虑设置 “_use_adaptive_log_file_sync”=false, adaptive log file sync是 11.2中提出的一个优化重做日志写的新特性, 在11.2.0.3以后默认为TRUE。

有客户在将”_use_adaptive_log_file_sync”=false后,log file sync等待事件的平均等待时间从10ms 下降到 1~2ms的案例。

 

_use_adaptive_log_file_sync造成性能下降的原因可能是其导致LGWR使用了polling 方式来取代 post/wait,并且polling的间隔是10ms,这个间隔是在代码里写死的。

 

此外如果使用了Veritas/symantec 的ODM的话也需要特别注意:你可能遇到了Bug 13551402  High “log file parallel write” and “log file sync” after upgrading 11.2 with Veritas/Symantec ODM,这个BUG已经确认在11.2.0.3和11.2.0.2上存在。

对于该bug的内部讨论最后确认是由于 11.2中lgwr的 IO使用了一种批量同步I/O接口,导致当配合Veritas/symantec 的ODM一起使用时会导致性能下降。

目前该BUG已经在多个Unix/Linux平台上提供补丁:

 

high logfile sync after upgrade

 

 

 

【Oracle性能优化】Maclean调优课程大纲

SQL优化课程:

 

 

  1. 了解 Oracle DB 体系结构
  2. SQL 优化简介
  3. 优化程序简介
  4. 优化程序运算符
  5. 解释执行计划
  6. 案例分析:星形转换
  7. 优化程序统计信息
  8. 使用绑定变量
  9. 使用优化程序提示 HINT
  10. 应用程序跟踪
  11. 自动 SQL 优化

 

AWR性能报告分析课程:

 

  1. AWR体系介绍
  2. AWR必要技巧
  3. AWR案例精讲1,等待事件、DB TIME、AAS指标分析
  4. AWR案例精讲2,RAC特定的AWR环节
  5. AWR主要指标解析

 

10g/11g优化课程:

 

  1. 优化体系介绍
  2. 基础调优诊断步骤
  3. 使用AWR性能报告
  4. 如何定义问题
  5. 度量和警告
  6. 使用SQL基线
  7. 使用基于AWR的工具
  8. 监控应用程序
  9. 找出问题TOP SQL
  10. 分析优化器影响
  11. 如何减少成本
  12. 使用SQL Performance Analyzer
  13. SQL性能管理
  14. 使用Database Replay
  15. 调优共享池
  16. 调优buffer cache
  17. 调优PGA和临时空间
  18. 自动内存管理
  19. 调优I/O
  20. 11g优化新特性
    ORACLE securefiles
    SQL Profile
    SQL Baseline
    SQL Performance Analyzer
    SQL Diagnosis
    SQL Monitor
    Adaptive Cursor Sharing
    其他特性
    Q&A

沪ICP备14014813号

沪公网安备 31010802001379号