ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 12618|回复: 31

[精华] Jonathan Lewis关于CBO的系列文章

[复制链接]
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
跳转到指定楼层
1#
发表于 2016-3-22 21:50 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
本帖最后由 newkid 于 2016-3-22 21:56 编辑

基于成本的优化器的基础知识--第1部分

作者:乔纳森·刘易斯(Jonathan Lewis)

原出处:

关于作者:
Jonathan Lewis是Oracle世界的知名人物,他使用该软件的经验超过26年。他已出版了三本关于Oracle的书,并合作了其他三本。他最近的著作:Oracle 核心(Apress出版,ISBN978-1430239543)是对Oracle数据库引擎的中心工作原理的描述。他运行着几个网站,并经常参与新闻组,论坛,用户组杂志和世界各地的活动。

个人博客:



这一系列关于Oracle的基于成本的优化器的文章,针对的是缺乏经验的DBA和开发人员,帮助他们了解优化器想要达到什么目的,它是如何得到一个执行计划,它为什么会犯错误,以及(也许是最重要的)如何识别那些错误的根源,并以适当的方式解决由此所产生的问题。

我会尽量避免陷入极端的技术细节,虽然我将列出较多的技术问题,并会提及一些网址,这些网址就一些特定的主题会进入更深层的讨论。

为了从这个系列得到最佳的收获,你将需要具备一些阅读执行计划的经验,特别是当我们在查看优化的“故障排除”的那一方面。

概述

如果忽略掉那些影响到可读性(代码以及结果的可读性)的点滴细节,一个基本的SQL语句有如下的格式:

select   {表达式清单}
from     {表清单}
where    {谓词清单}
group by {表达式清单}
having   {谓词清单}
order by {表达式清单}

优化器的任务是制定出尽可能快的策略,以获取你所需要的原始数据,并把这些数据转化为你想看到的结果集。要做到这一点,它依赖于一种数值模型,该模型含有关于你的数据的信息(对象及其统计信息),机器性能(系统统计信息),以及可用机器资源(数据库/实例的配置)。

该模型允许优化器估计有多少数据需要检查,访问这些数据需要多少工作量,实际需要的数据有多少,对数据集进行匹配比较需要多少工作量,通过比较能够留下来或者被生成的数据有多少,对数据进行排序和/或聚集又需要多少工作量。

优化器碰到的问题是静态模型可能永远不够好——尽管Oracle公司一直不断增强代码来解决任何静态模型的先天不足——而且该模型不允许数据的使用方式的副作用(这副作用实际上可能是一件好事,除非你使用数据的方式是绝对乏味的,可预测的)。这就意味着如下的情形相对并不罕见,当优化器说:“如果我用策略A, 将需要不到1秒,如果我使用策略B, 将需要2分钟”,而你却知道策略A需要10分钟,但如果你强迫Oracle采取策略B, 你可以在5秒内得到结果。 每当这种情形发生,通常有一个能够轻易识别的原因;最常见的原因是,优化器并不真正了解你的数据是什么样子,或者不知道,有一种被涉及的机器因素,使得某些活动能够比优化器预估的更快完成。

例子

我们用一个很简单的查询来非正式描述一下优化器如何“思考”。 假设我有一个已经运行了近3年的订单处理系统,订单的数量差不多每年会提高一倍,目前为每周大约20,000个订单。大多数订单是单一种产品,但少量订单会包括2或3个项目(订单明细, order line),有一小部分有4到10个。因此,平均而言,每个订单会有1.2个订单明细与之关联,每个订单明细包括单一种产品。

下面是一些SQL来描述表和当前的索引:
create table orders (
        order_id        number(10,0) not null,
        customer_id     number(10,0) not null,
        date_ordered    date         not null,
        other_bits      varchar2(250),
--      constraint ord_fk_cus foreign key(customer_id) references customers,
        constraint ord_pk primary key(order_id)
);

create index ord_fk_cus on orders(customer_id) compress;
create index ord_date_ord on orders(date_ordered) compress;

create table order_lines(
        order_id        number(10,0)    not null,
        order_line      number(4,0)     not null,
        product_id      number(10,0)    not null,
        quantity        number(4,0)     not null,
        unit_price      number(6,2)     not null,
        other_bits      varchar2(100),
        constraint orl_pk primary key(order_id, order_line),
--      constraint orl_fk_prd foreign key(product_id) references products,
        constraint orl_fk_ord foreign key(order_id) references orders
);

create index orl_fk_prd on order_lines(product_id) compress;

有几个外键约束在这个简约的结构中被注释掉了——我还没定义一个产品表或客户表,它们自然是order_lines.product_id和orders.customer_id的参照完整性的目标。

所以,这是一个我们可能运行的查询:“找出上一周的某种特定产品的所有销售记录,找出每日订单明细的数量,以及销量和金额”。

select
        trunc(ord.date_ordered),
        count(*),
        sum(orl.quantity),
        sum(orl.quantity * orl.unit_price) total_value
from
        orders          ord,   
        order_lines     orl
where
        ord.date_ordered >= trunc(sysdate) – 7
and     ord.date_ordered <  trunc(sysdate)
and     orl.order_id     =  ord.order_id
and     orl.product_id   =  101234
group by
        trunc(ord.date_ordered)
order by
        total_value desc
;

优化器会如何解决这个问题?

根据我们的业务知识可知,在相关的时间段大约会有20,000的订单。然而,将横跨三年的数据平均下来,优化器会产生一个接近12,000的估计值。(练习题1:检查后者是否为一个合理的近似值,然后再想想它是否仍然可能不准确)

我们可能知道产品101234很受欢迎,每天都会出现在30个订单明细上; 或者我们可能知道,这是一个高价物品,每周只有几个订单。如果它很受欢迎,并且在order_lines表上的统计数据包括了PRODUCT_ID的直方图(运气足够好的话),则优化器可能有足够的信息来获得一个很好的估计值,否则它可能只是算出有订单的每个产品的ORDER_LINES的平均数量,并将其用在计算中。假设优化器通过三年数据得到了(基于平均值)产品101234有大约1000个订单明细的估计值。

下一步是什么?据其基本预测,优化器需要来比较12000个订单和1000个订单明细,找到匹配(上周有产品101234的订单),然后做一个小小的聚合操作。顺着优化器基于这些数字的算法,我们可以看出,它会产生一个关于上周含有产品101234的订单的估算值,包含6或7个订单——这是一个很小的结果集,但我们必须获取并去除许多数据才能到它。(练习题2:检查该数据的描述,确认每星期6或7个订单是一个合理的估算计;然后解释一下产生这个估算值的逻辑有什么漏洞,即使所有产品受欢迎的程度都一样)

在“多少数据”这个问题之后,下一个问题就是“它在哪里”。因为了解数据库和订单处理系统的工作原理,我们(人类)可以假设上一周的订单是在过去的几天内入表的,并会被放在表的最后几个数据块——也许优化器在其模型中的数字(最明显的是ord_dt_ord索引上的clustering_factor)会允许它识别出上周的12,000个订单(这个估算并不正确)会被放在表中的仅仅400个数据块中。

相反,我们(人类)知道,产品101234的订单明细会分布在整个3年的交易历史中,并且优化器在其模型中的数字(特别是在orl_fk_prd索引上的clustering_factor)应该告诉它我们感兴趣的每一行数据可能是在表中的不同的块,所以(估计的)1000行会分散在表中的1000块,这对我们和优化器都可以认为是一个征兆,表明我们需要做1000个随机物理读来获取数据。

从人类的角度来说,优化器必须使用该信息来比较三种可能的策略:

1. 独立收集所有“可能相关的”订单(ORDERS)和所有的“可能相关”的订单明细(ORDER_LINES),然后比较两组数据,以寻找匹配的(基本上有两种机制来做到这一点, 哈希连接hash join和合并连接merge join)

2. 收集上一周所有的订单,并且当我们在获取数据时,依次查找匹配的订单明细数据(有多行),丢弃那些和产品101234无关的数据(由ORDERS驱动的嵌套循环连接nested loop join)

3. 收集所有产品101234的订单明细(ORDER_LINES),并且正当我们在获取数据时,依次为每个订单明细查找匹配的订单(只有一行),丢弃那些不是上周的数据(由ORDER_LINES驱动的嵌套循环连接nested loop join)

选项(1): 哈希(hash)/合并(merge)连接

如果我们从优化器的角度来考虑选项(1), 它必须决定总共需要多少工作量来读取产品101234的所有订单明细和上周的所有订单。一旦决定了每个订单明细是处于表中的不同的块,它就会再考虑两种策略:使用PRODUCT_ID索引来分别查找和读取每个块,共计需要读取1000个表数据块(还有一些索引块的读取),或者使用暴力法扫描整个表,读取每个块并检查每一行,丢弃它不需要的数据。

选择是由时间决定的——我们假设优化器已经确定,根据系统的统计数据,典型的多块读会成为连续32个块的读取,所花时间仅仅是单块读的两倍,所以假如表的大小不超过16,000个块,它就会决定做表扫描(因为32/2 = 16,而另一选项是1000单块读取)。类似地,优化器也可能选择做订单表的表扫描,但如果它已确定索引访问路径需要访问400个块,那么它仅在表的尺寸小于16 * 400 = 6400块时才会选择表扫描。(事实上,从单一个表获得数据的方法并不仅限于表扫描和单个索引访问路径,但是在这个练习中我们将忽略这一点)

我不会进入到哈希和合并连接的计算细节,但是对于小型数据集而言,使用一点点内存和少量CPU时间来执行连接的额外成本是很小的。

选项(2): 从order表到order_lines表的嵌套循环(nested loop)连接

优化器认为它会收集到12000订单,所以不管它下一步会做什么来找到匹配的订单明细,它认为这个动作会做12000遍。这将产生非常大的总成本,除非它下一步要做的事情每次发生的代价极低。不幸的是,这正是优化算法陷入困境的开端。优化器会计算查找单个订单的订单明细的成本——它从一开始能拿出的最有效的方法,是使用从ORDER_ID开始的索引,根据其运算结果,这会返回1.2个订单明细,至少需要一个对订单明细表的随机读。这意味着嵌套循环连接如此这般的操作将会超过12000个随机单块读取。

当然,我们(人类)知道(预测的)12000* 1.2个订单明细会很紧凑地放置在表的最后数据块,因此我们可以预计,一旦ORACLE已经读了几个订单明细,它就会将所有必需的块加载入缓冲区,而不需要做更多的随机读。该计划可能是最快的执行计划,但优化器可能会认为它所花的时间比我们预期的要多得多。

事实上,在一个典型的订单处理系统中,很可能前几天的大部分订单和订单明细在我们开始查询之前就已经处于缓冲区之中了,所以我们可能根本不需要做任何物理读。在来自优化器的“糟糕的计划选择”的最常见根源之一,就是它对你如何使用数据一无所知,所以它不了解从缓存获得的收益会有多少。

选项(3): 从order_lines表到orders表的嵌套循环(nested loop)连接

论证类似于选项(1)和(2)的混合。首先,优化器会决定是做一个表扫描,或者是使用一种由索引驱动的方法来收集1000个订单明细;然后它必须解决如何为每个订单明细找到匹配订单的问题。在这种情况下,它只需执行“找到订单”1000次,所以如果它能够找到一个合理的廉价的方式来查找订单,那么这条路径的总成本可能小于选项(1)的成本。不幸的是,即使把我们自己限制于嵌套循环,这也可能是最低成本路径花费时间最长的一种情形。

ORACLE从一个订单明细来定位一个订单的最佳方法是使用order_id上的索引,优化器会算出,找到一个订单将需要它读取一个表数据块。
在这种情况下,我们起始于随时间随机散布的1000个订单明细,与其关联的订单也是随时间随机散布的,所以为了完成整个查询,我们最终非常可能读取差不多1,000 + 1,000 = 2,000表数据块。

我在考虑选项(2)的时候说过,我们将从订单明细的这种与时间相关的缓存获益匪浅,在此之上只需读取索引的几个叶子块。如果我们开始于order_lines表,我们将不会从与时间相关的缓存获得任何益处,我们将不得不从另外的订单索引读取更多的索引叶子块。

从优化器的角度来看,从订单查找一次订单明细,和从订单明细查找一次订单的成本几乎是相同的——但是其中一种方法优化器认为它会做12000次搜索,而另外一种它只需做1000次。

从我们的角度来看,我们知道,ORACLE在其预期要做12000次的事情中可以从缓存获得巨大好处,而在其预期要做1000次的事情中则得不到任何缓存的益处。换言之,优化器会赋予选项(2)的成本大约是选项(3)的12倍,而当我们把预计的1000个随机订单所导致的1000个额外的读取,与我们可能需要的把20000个订单明细读入缓存所需的大约400个读取(最坏情况下)相比,我们则期望选项(3)比选项(2)大约慢2.5倍。

在下一篇文章中,我会创建一个这个模型的数据集,于是我就可以向你展示这样一个简单的问题会得到什么样的计划,以及我们可以用什么策略来帮助ORACLE确定最合适的执行计划。

关于练习题:
练习题 1: (Oracle的估计值12,000): 在每年翻番,过了三年之后,现在我们每周大约有20,000个订单,所以第一年每周是 5,000个,第二年每周10,000,第三年每周20,000, 整个时间平均下来大约是每周35,000/3个订单。(我们应该认识到这依然不是一个现实的模型,但它比简单的平均值思考方法要好一点。)

练习题 2: (对产品101234每周 6 至 7 个订单的估计) 优化器并不知道每年销售量会翻番(尽管一个适当的分区表将会允许它捕捉此类信息)所以一个更佳的模型将会针对产品101234建议第一年143个订单,第二年286个订单,第三年571订单,这意味着最近一周有超过11个订单(实际上接近15,如果我们建立的是一个平滑的每周增长而非简单的每年阶梯状增长模型)。当然,在现实世界中一个典型的订单处理系统也会对某些产品显示出季节性变化,并会有新的产品推出,老产品停产这样一个持续的交替。
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
2#
 楼主| 发表于 2016-3-22 21:51 | 只看该作者
本帖最后由 newkid 于 2016-3-22 21:57 编辑

基于成本的优化器的基础知识--第2部分

作者:Jonathan Lewis

原出处:

在这系列文章的第一篇中,我给出了一个非正式的描述,在为我所描述的订单表和订单明细表之间的一个简单的两表连接选择执行计划时,优化器会如何考虑各种各样的可能性。


在该文章中,我提供了表及其索引的DDL; 在本篇文章中我将查看我所创建的数据,向你展示一些相关的数字,然后再演示这些数字是如何影响优化器对路径的选择。然而这些论证仍然是相当非正式的。

本篇文章的关键点是,虽然优化器可以做一些非常聪明的事情,但其缺省模型中仍然存在一些缺陷,而你也许能够通过一些简单的查询来识别,并且通过精确定位的配置更改来解决。

数据

下面是我用来填充两个表的代码:

-- create sequence s1 cache 10000;
-- variable m_ord_id number

insert into orders values (
        1000000 + s1.nextval,
        trunc(dbms_random.value(100000,200000)),
        case
                when s1.nextval < 257143
                        then sysdate + (365 * (s1.nextval /  257143)- 3 * 365)
                when s1.nextval < 771429
                        then sysdate + (365 * (s1.nextval /  771429)- 2 * 365)
                        else sysdate + (365 * (s1.nextval / 1800000)- 1 * 365)
        end,
        rpad('x',100)
)
returning order_id into :m_ord_id
;

insert into order_lines
select
        :m_ord_id,
        rownum,
        trunc(dbms_random.value(100000,102000)),
        trunc(dbms_random.value(1,5)),
        trunc(dbms_random.value(1,100)),
        rpad('x',80)
from
        dual
connect by
        level <= (select mod(ceil(2.80 * ln(abs(dbms_random.normal))),11) from dual)
;

commit;

execute dbms_lock.sleep(0.01)

我创建了18个并发会话,并从每个会话执行代码10万次。这应该给我恰好180万行数据,但我发现有些SQL * Plus会话会随机崩溃,所以又开了几个额外的会话,最终得到的数据比最初的计划多了几行。最终得到的数据是1810995个订单,2166518行个订单明细,平均每个订单有1.196个订单明细,细分如下:

select
        N, count(*) "Orders with N order_lines"
from
        (
        select
                order_id, count(*)      N
        from
                order_lines
        group by
                order_id
        )
group by
        N
order by
        N
;

         N Orders with N order_lines
---------- -------------------------
         1                   1534136
         2                    204382
         3                     66344
         4                      6079
         5                        54

关于这些插入语句有几点值得一提。订单表上的CASE语句模仿出在过去三年中订单的数量每年翻一番;在订单明细级别的对ln()(自然对数)的调用是一种方便的方法,可以得到“真实世界”中的每个订单所带的订单明细(每分钟几个电话,每个家庭几部车,等等)。
最后要记住的一点是,表所在的表空间定义为自动(位图方式)段空间管理——该表空间同时也使用系统管理的数据区分配,但这在本例中并没有显著影响。

我在每个订单之间插入了1/100秒的停顿,这有助于缓解“ITL爆炸”的问题(指的是高密度并行插入导致的索引空间暴涨问题,详见),尽管这个行为模式依然会在索引叶子块分裂这方面产生一些空间低效问题。数据装载所花的时间大约是20分钟,但如果你没有一个巨大的SGA以及高速的磁盘,你可能会发现自己多花了很多时间,用于维护order_lines(product_id)上的索引时的“db file sequential read”等待事件,以及重做日志写入磁盘时的“log file parallel write”等待事件(以及相关的“log file sync”等待事件)。

开端
我们将在下一篇文章中讲述主查询语句,但我想先运行几个比较简单的查询作为起步,看看优化器是想如何处理我们的数据。我们原来的查询是在过去七天中(这是orders的一个属性)查找产品101234(这是order_lines的一个属性),但我想先从一个仅仅针对订单明细的查询开始,我们只是对这个产品的所有订单明细进行求和,这个查询必须访问我们感兴趣的确切数据,但方便的是它只返回一行:


SQL> set autotrace traceonly explain
SQL> select  sum(unit_price * quantity)
  2  from    order_lines
  3  where   product_id = 101234
  4  ;

-------------------------- ------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    11 |  1064   (1)| 00:00:13 |
|   1 |  SORT AGGREGATE              |             |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| ORDER_LINES |  1083 | 11913 |  1064   (1)| 00:00:13 |
|*  3 |    INDEX RANGE SCAN          | ORL_FK_PRD  |  1083 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PRODUCT_ID"=101234)

从业务角度来看,我们预期在每种产品看到大约1000个的订单明细,而(在一些合理的限制下)优化器确实估计了这个数。根据我们的业务知识,我们也预期这些数据行在这三年当中分布得相当均匀,用更接近数据库技术的话来说,我们在典型情况下可能预期每一行在一个单独的数据块中——这正是优化器的估计成本(大约)所告诉我们的。

优化器已确定,获得数据所需的资源大致相当于从数据库文件中物理读取1064个数据块(读索引4个块,另加读表1060个块)。鉴于执行计划是一个索引范围扫描, 跟随着一个通过索引ROWID的表访问,我们可以知道,这个等价性是如下事实的一种直接体现,即优化器确实期望运行引擎做1064个单块读取。

现在,让我们来看看我们的原始查询的另一端——过去7天中的所有订单。为了执行原始查询,我们需要order_id来连接order_lines表,所以我们在这个测试中会选中order_id, 通过查找此日期范围内的最小和最大订单号之差加上1,用它来得到大致的订单数。

SQL> set autotrace traceonly explain
SQL> select  max(order_id) - min(order_id) + 1
  2  from    orders
  3  where   date_ordered between trunc(sysdate) - 7 and trunc(sysdate)
  4  ;

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |     1 |    14 |  4115   (2)| 00:00:50 |
|   1 |  SORT AGGREGATE          |                  |     1 |    14 |            |          |
|*  2 |   FILTER                 |                  |       |       |            |          |
|*  3 |    VIEW                  | index$_join$_001 | 11526 |   157K|  4115   (2)| 00:00:50 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | ORD_DATE_ORD     | 11526 |   157K|    35   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| ORD_PK           | 11526 |   157K|  5083   (1)| 00:01:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!)-7)
   3 - filter("DATE_ORDERED">=TRUNC(SYSDATE@!)-7 AND
              "DATE_ORDERED"<=TRUNC(SYSDATE@!))
   4 - access(ROWID=ROWID)
   5 - access("DATE_ORDERED">=TRUNC(SYSDATE@!)-56 AND
              "DATE_ORDERED"<=TRUNC(SYSDATE@!))
)

(该查询找出了20000行,而所估计的行数为11500 —— 但是这个差异和我们在第一篇中关于订单每年倍增的预测是一致的。)

然而出乎意料的是——这同时显示出优化器是多么的聪明以及多么的无知——这个奇怪的计划执行了两个索引之间的哈希连接来得到结果。优化器的聪明之处在于找到了一个不需要访问表的计划,但令人惊讶的是它以为这个计划比起通过一个简单的索引范围扫描来访问表更加高效;毕竟我们知道,最近七天的订单都被放在表中的最后“少数”几个块中——实际上我们可以验证一下这是对的:

select count(*)
from (
        select distinct
                dbms_rowid.rowid_relative_fno(rowid),
                dbms_rowid.rowid_block_number(rowid)
        from orders
        where date_ordered between trunc(sysdate) - 7 and trunc(sysdate)
        )
;

  COUNT(*)
----------
       447

我们需要的数据分散在仅仅447个块中,如果我们取样的是其他的为期一周的时间间隔,我们可也能会发现类似的数字,所以如果指望优化器算出它应该按顺序取出大约500个块就能得到它所需的所有数据,这看起来是合理的——那么,为什么会在主键索引上做了一个快速全扫描,其成本5083大约是“显而易见”的计划成本的十倍呢?(不要问为什么5083+35的总和似乎为4115 —— 有时优化器会做一些奇怪的事情而你不需要立即搞清楚 —— 即使是在11.2.0.4)

我们可以通过提醒自己两件事情来回答这个关键的问题 ——该表空间是位图管理(ASSM),而我有18个并发进程在插入数据。 ASSM的意义在于减少DML的竞争,特别是并发的插入,通过把不同的会话指向不同的数据块,而这些数据块在典型情况下,虽然并不一定,分布在一个批次的连续的16个块中。这是为了避免“缓冲区忙等待(buffer busy waits)”的一个非常好的策略(以及更极端的在使用RAC时会得到的“全局缓冲区忙等待(gc buffer busy waits)”),但它确实意味着,在非常精细的程度上,(对优化器而言)数据的分散程度看起来要远远高于实际情况。我们可以用另一个简单的查询来展示这个效果——为一批连续订单查找文件和块的ID。

SQL> select
  2          order_id,
  3          dbms_rowid.rowid_relative_fno(rowid) file_id,
  4          dbms_rowid.rowid_block_number(rowid) block_id
  5  from    orders
  6  where   date_ordered >  trunc(sysdate) - 56
  7  and     rownum <= 20
  8  ;

  ORDER_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
   2667873          6      29360        a
   2667874          6      28853         b
   2667875          6      29503              f
   2667876          6      29207          c
   2667877          6      29567
   2667878          6      29272             e
   2667879          6      29823
   2667880          6      29272             e
   2667881          6      27952            d
   2667882          6      29104
   2667883          6      27952            d
   2667884          6      28981
   2667885          6      29616
   2667886          6      29503              f
   2667887          6      28917
   2667888          6      29360        a
   2667889          6      29207          c
   2667890          6      28853         b
   2667891          6      29280
   2667892          6      29207          c

正如你所看到的,我按order_date日期的顺序挑选20个订单,这给了我连续20个订单号(并非巧合);但数据是以随机方式分散在13个不同的块中。这意味着,当Oracle顺着索引来取数据,它常常从一个表数据块跳跃到另一个块——而当优化器在确定一个特定索引路径的效率时,这种“跳跃”的量度被用作一个重要的考量细节。

当你收集索引统计信息时,Oracle使用一个名为sys_op_countchg()的函数来计算它在顺着整个索引按顺序移动时会跳转到“不同的表块”的次数,这个数字作为聚簇因子clustering_factor出现在USER_INDEXES视图中。聚簇因子clustering_factor的值越高,优化器所认为的以索引的顺序读取表数据所需的随机(物理)读就越多。

然而,从历史上看,ORACLE无法注意到,一个到“表中的下一个所需的块”的跳转实际上会把它带回到一个非常近的历史上已经出现过的表数据块,因此,在评估上述例子时,它会看到20个不同的表数据块,而在这里我们看到的只有13。 在12C,以及带反向移植补丁的11.2.0.4版本, 我们可以设置一个“表选项”来告诉ORACLE在计算clustering_factor的时候要记住最近的历史。例如,如果我们设置首选项为8,Oracle在第二次碰到29360号块(上述查询结果的标记a)时,不会记得最近才看到它,所以会把它又多算一次; 另一方面,它会记得访问过29272和27952号块,每块只算一次。29207号块(标记为c)是一个特别有用的例子——当Oracle第二次碰到它时会忘记首次访问,因此会将这个块又算一次,但是当第三次碰到它的时候它会记得前一次访问(第二次),所以不会增加计数。

ASSM往往把插入的数据分散于16个块(这使得16成为此选项的一个很好的默认值),但在我的例子中,我连接18个会话到数据库,并让这些会话保持存活了很长的时间,进行持续的单行插入与提交;所以我可能会期望看到一个比典型情况稍差的分散模式——效果是广泛分布于18个块。正因为如此,我要设置表选项,以告诉Oracle记得18步的历史,然后再次收集索引统计信息,并看看会发生什么。

begin
        dbms_stats.set_table_prefs (user,'orders','table_cached_blocks',18);
        dbms_stats.gather_table_stats(user,'orders');

        dbms_stats.set_table_prefs (user,'order_lines','table_cached_blocks',18);
        dbms_stats.gather_table_stats(user,'order_lines');
end;
/

select  table_name, index_name, sample_size, num_rows, clustering_factor
from    user_indexes
order by
        table_name, index_name
;

下面是聚簇因子CLUSTERING_FACTOR在调整“历史”设定的前后对比:

TABLE_NAME           INDEX_NAME           SAMPLE_SIZE   NUM_ROWS CLUSTERING_FACTOR
-------------------- -------------------- ----------- ---------- -----------------
ORDERS               ORD_DATE_ORD              448630    1913634           1738979
                     ORD_PK                   1810995    1810995           1643965

ORDER_LINES          ORL_FK_PRD               2166518    2166518           2114283
                     ORL_PK                    425450    2164008           1658659


TABLE_NAME           INDEX_NAME           SAMPLE_SIZE   NUM_ROWS CLUSTERING_FACTOR
-------------------- -------------------- ----------- ---------- -----------------
ORDERS               ORD_DATE_ORD              412815    1760865            103293
                     ORD_PK                   1810995    1810995             66790

ORDER_LINES          ORL_FK_PRD               2166518    2166518           2114283
                     ORL_PK                    426762    2170681            115868


正如你所看到的,一旦我们告知Oracle允许并发和ASSM分散数据的结果,三个索引所报告的聚簇因子就小得多了。orl_fk_prd索引缺乏变化,这并不令人吃惊,因为这是对产品ID的索引,而我们知道,单个产品确实是非常地随机分布在整个表的历史中的。


那么,减少的(也更接近真实的)聚簇因子clustering_factor对我们在订单表上的简单查询的执行计划有什么影响?下面就是新的计划:

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |    14 |   691   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE               |              |     1 |    14 |            |          |
|*  2 |   FILTER                      |              |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| ORDERS       | 11526 |   157K|   691   (1)| 00:00:09 |
|*  4 |     INDEX RANGE SCAN          | ORD_DATE_ORD | 11526 |       |    32   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!)-7)
   4 - access("DATE_ORDERED">=TRUNC(SYSDATE@!)-7 AND
              "DATE_ORDERED"<=TRUNC(SYSDATE@!))

基于我们对数据的认识,该计划正是我们所期待的。在索引范围扫描之后,我们必须以单块读的方式访问“少量”的表数据块; 优化器的估计大约是691 - 32 =659的单块读取,这并不完全符合我们对这个特定数据集的精确认识,但随着clustering_factor的调整,优化器得到了对所涉及的工作量的一个更接近现实的估计。

为了便于比较,我们可以把table_blocks_cached选项重新设置为1,重新收集统计信息,再看看如果我们用/*+ index(orders (date_ordered)) */ 提示来强制ORACLE选择这条路径时会得到什么:


----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |    14 | 11109   (1)| 00:02:14 |
|   1 |  SORT AGGREGATE               |              |     1 |    14 |            |          |
|*  2 |   FILTER                      |              |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| ORDERS       | 11526 |   157K| 11109   (1)| 00:02:14 |
|*  4 |     INDEX RANGE SCAN          | ORD_DATE_ORD | 11526 |       |    35   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!)-7)
   4 - access("DATE_ORDERED">=TRUNC(SYSDATE@!)-7 AND
              "DATE_ORDERED"<=TRUNC(SYSDATE@!))

在没有修正聚簇因子的情况下,优化器对包含简单的索引范围扫描的计划成本估计是11109 —— 这意味着它估计有 11074个(11109 - 35)不同的表数据块需要读取。如果你把11074和659(读取表块的估计成本)相比,以及1738979与103293(两个clustering_factors的值)相比,你会看到,在这两种情况下的比例大约为16.8(有一些区别,这是由于不同的样本大小引起的)。

总结

我们已经研究了几个查询,它们最终将会把我们领到我们所感兴趣的表连接。因为它们是单表查询,对于有多少行被涉及,有多少个相关的数据块,可用的索引效率如何,我们都有一个很好的认识。

因为我们期待的是一个简单的通过索引范围扫描的表访问,我们知道,优化器对每个查询的估计成本应接近于它所认为的它必须访问的不同数据块的个数(这个数字等同于单块物理读的个数),而我们对总块数应该是多少有一个很好的认识。

但是,对其中的一个查询,优化器选择了一个意想不到的路径,它的成本估算比我们的有根据的估计值要高得多,既然我们可以用一些简单的SQL来证明我们的估计方法是适当的,我们知道,优化程序肯定在它计算中的某处做出了一个不合理的假设。

对于索引范围扫描和索引完全扫描,聚簇因子clustering_factor对访问表的成本计算有着显著影响,而我们知道,我们的订单和订单明细表是按照日期很好地聚集的; 然而,我们对ASSM和应用程序(特别是并发的程度方面)的知识告诉我们,虽然从宏观上看数据聚集得很好,从细节看它又体现出一定的局部分散效果,而ORACLE的传统算法对此过于夸张了。

幸运的是,最近的Oracle版本使我们能够配置一个“表选项”来解决这方面的问题,经过设置这个选项以匹配我们的并发活动的模式,我们可以看到下一次对统计信息收集的调用之后,它已经以我们所希望的方式影响了所有索引的聚簇因子clustering_factor——即把其中的三个大幅减少,而第四个不变。

有了修正后的聚簇因子clustering_factor,优化器已经切换到我们所预期的路径,其成本和我们的期望值很好地相符。

在下一篇文章中,我们将要继续查看我们真正感兴趣的连接,并会看到因为优化器不如我们那样了解数据而碰到的另外一个问题。

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
3#
 楼主| 发表于 2016-3-22 21:51 | 只看该作者
本帖最后由 newkid 于 2016-3-22 21:58 编辑

基于成本的优化器的基础知识--第3部分

作者:Jonathan Lewis

原出处:

在本系列的第二部分,我们查看了一个简单连接中的表的单独访问路径,并指出优化器用于索引的模型的一个重要缺陷。我们利用一个最近的增强功能解决了该缺陷,现在已经准备好转战于作为一个整体的查询所体现出来的问题。

回顾:

我们的查询连接订单表orders到订单明细order_lines,并提取出在一个给定的日期范围内的给定产品的订单以及订单明细的相关数据:

select
        trunc(ord.date_ordered),
        count(*),
        sum(orl.quantity),
        sum(orl.quantity * orl.unit_price) total_value
from
        orders          ord,   
        order_lines     orl
where
        ord.date_ordered >= trunc(sysdate) – 7
and     ord.date_ordered <  trunc(sysdate)
and     orl.order_id     =  ord.order_id
and     orl.product_id   =  101234
group by
        trunc(ord.date_ordered)
order by
        total_value desc
;

我们已经算出本周大约有20000订单(虽然优化器的估计数是11500),它们被很好地放置在订单表的末尾,所请求的产品大约有1000个订单明细(而且优化器和我们的估计相符),在订单明细表中广泛分布。

通过将table_cached_blocks表选项设置为一个适当的值(在两个表上都设,虽然对我们而言,订单表才是关键的),我们允许优化器认识到订单是很好地聚集的,所以它乐于使用(date_ordered)索引来找出一周的订单; 而即使没有调整,优化器本来也乐于使用订单明细上面的(product_id)索引来选择给定产品的所有订单明细。


最好的计划……
那么,当我们尝试连接表,并将两个谓词合起来,会发生什么?通过(再一次)将table_cached_blocks的表选项设置为适当的值和收集统计后,在我的系统中默认的计划是这样子的:

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |  1083 | 33573 |  1775   (1)| 00:00:09 |
|   1 |  SORT ORDER BY                  |              |  1083 | 33573 |  1775   (1)| 00:00:09 |
|   2 |   HASH GROUP BY                 |              |  1083 | 33573 |  1775   (1)| 00:00:09 |
|*  3 |    FILTER                       |              |       |       |            |          |
|*  4 |     HASH JOIN                   |              |  1083 | 33573 |  1773   (1)| 00:00:09 |
|   5 |      TABLE ACCESS BY INDEX ROWID| ORDER_LINES  |  1083 | 18411 |  1064   (1)| 00:00:06 |
|*  6 |       INDEX RANGE SCAN          | ORL_FK_PRD   |  1083 |       |     4   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| ORDERS       | 11525 |   157K|   709   (1)| 00:00:04 |
|*  8 |       INDEX RANGE SCAN          | ORD_DATE_ORD | 11525 |       |    34   (3)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(TRUNC(SYSDATE@!)>TRUNC(SYSDATE@!)-7)
   4 - access("ORL"."ORDER_ID"="ORD"."ORDER_ID")
   6 - access("ORL"."PRODUCT_ID"=101234)
   8 - access("ORD"."DATE_ORDERED">=TRUNC(SYSDATE@!)-7 AND
              "ORD"."DATE_ORDERED"<TRUNC(SYSDATE@!))

我不打算试图去解释我们要查看的所有执行计划的每一个细节,然而,正如你在第四至第八个操作中看到的,优化器已经基本确定要执行我在前一篇文章中展示给你的那两个较为简单的查询,然后用哈希连接(hash join)将两个结果连接起来。

为了执行一个哈希连接,Oracle需要第一个数据集的全部数据(称为“构建”表),并构建一个内存中的哈希表,它基于等值连接谓词中所用到的列; 然后访问第二个数据集(被称为“探测”表),每次一行,在等值连接列上应用相同的哈希函数,并在内存中的哈希表进行探测,看看是否有任何可能的匹配。如果有匹配的散列值,Oracle随后会检查所有连接谓词的实际值,看它是否为一个适当的匹配,或只是一个“哈希碰撞”(即从不同的原始值生成了相同的散列值)。

通常需要注意:在执行计划中,哈希连接的第一个孩子(上述计划中的操作5),总是被标识为构建数据集,第二个孩子(上述计划中的操作7)总是被标识为探测数据集。

我说过哈希表会在内存中,但如果哈希表太大,现有内存装不下,Oracle有一个机制会把它按批次(称为分区)保存到磁盘,并把探测表按照匹配批次也保存到磁盘中,然后在匹配批次之间逐个做连接。(如果你需要更详细的描述,请参见《基于成本的Oracle——基本原理》,2005年Apress出版)。

在我们的例子中,Oracle决定使用订单明细表数据作为构建表,因为它认为需要获取的总数据两只有18KB,相比之下订单表数据量为157KB(注:构建表的选择是基于所需数据的字节数,而不是数据的行数)。

从计算中我们可以看到优化器假定它能够把这18KB的数据放在内存中:从订单明细表获取数据的成本是1064; 从订单获取数据的成本是709,哈希连接的总成本是1773 —— 这正是1064+709:优化器认为执行哈希连接本身的成本几乎为零(舍入到最接近的单位),也即:它会需要一点CPU和很少的内存,但没有额外的磁盘I/O。

备选方案:
在第一篇文章中,我们考虑了其他两种可行方案——从订单表orders开始的嵌套循环连接,或者从订单明细表order_lines开始的嵌套循环连接。我们可以通过一些相当简单的提示来得到这些计划:

从订单明细表开始的嵌套循环连接: /*+ leading(orl ord) use_nl(ord) index(ord(order_id)) */
从订单表开始的嵌套循环连接: /*+ leading(ord orl) use_nl(orl) index(orl(order_id)) */

这些提示告诉优化器它所允许考虑的单一的连接顺序,也告诉它连接到所标识出的表的连接机制,还告诉它在连接到该表时必须使用始于特定列的一个索引。下面是这两种提示的第一种所生成的计划(去除了谓语部分):

------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |  1083 | 33573 |  3232   (1)| 00:00:17 |
|   1 |  SORT ORDER BY                   |             |  1083 | 33573 |  3232   (1)| 00:00:17 |
|   2 |   HASH GROUP BY                  |             |  1083 | 33573 |  3232   (1)| 00:00:17 |
|*  3 |    FILTER                        |             |       |       |            |          |
|   4 |     NESTED LOOPS                 |             |  1083 | 33573 |  3230   (1)| 00:00:17 |
|   5 |      NESTED LOOPS                |             |  1083 | 33573 |  3230   (1)| 00:00:17 |
|   6 |       TABLE ACCESS BY INDEX ROWID| ORDER_LINES |  1083 | 18411 |  1064   (1)| 00:00:06 |
|*  7 |        INDEX RANGE SCAN          | ORL_FK_PRD  |  1083 |       |     4   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN          | ORD_PK      |     1 |       |     1   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS BY INDEX ROWID | ORDERS      |     1 |    14 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

这里一个最重要的特点是,该计划的成本比哈希连接成本更高; 所以在缺省情况下它不会被采用。


这个执行计划所产生的数字有点小毛病,所以请包涵一下听听我对运算的解释。这里所展示的连接机制,是Oracle可用的新式嵌套循环机制之一(称为“批量嵌套循环连接”),并且它最终被显示为两个嵌套循环操作,第一个访问索引,第二个访问表。

不幸的是,第一个嵌套循环(操作5)显示的行数,字节数,成本和时间数据,报告的是第二嵌套循环(操作4)完成的数据。理想的情况下,操作5的数据应该看起来更像这样:


------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   5 |      NESTED LOOPS                |             |  1083 | 24909 |  2147   (1)| 00:00:11 |
------------------------------------------------------------------------------------------------

因此,我们将忽略这一行,并直接进入在操作4的数据(练习题:在读完接下来的几个段落后,检查上面这一理想化数据是否有道理)。

实质上,该计划告诉我们,对于我们在操作6收到的来自order_lines表访问的每一行,我们将会探测ord_pk索引(优化器估计的是需要一个物理读——因此操作8的成本列为1——然后第二个物理读访问订单表——因此操作9的成本列为2。从技术上而言,我们可以较真,根据一致性这个真正的数字应该是1,这个2来自同一个历史遗留下来的漏洞,它也造成操作5所显示的误导性的数字)。

有了这些信息,我们可以看到嵌套循环的总成本来自哪里:这是一个从订单明细获取数据的成本,加上1083次从订单表中获取一行的成本,即:1064+1083*2=3,230。让我们清除块缓存再执行查询,来检查一下这个模型有多少道理。下面是启用了rowsource_execution_statistics之后从内存中取出的执行计划(虽然我已经重排了列的顺序,并删除了内存相关的信息):

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Starts | E-Rows |  A-Rows |   A-Time   | Buffers | Reads  |Cost (%CPU)|
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |      1 |        |       7 |00:00:00.76 |    4829 |   3189 | 3232 (100)|
|   1 |  SORT ORDER BY                   |             |      1 |   1083 |       7 |00:00:00.76 |    4829 |   3189 | 3232   (1)|
|   2 |   HASH GROUP BY                  |             |      1 |   1083 |       7 |00:00:00.76 |    4829 |   3189 | 3232   (1)|
|*  3 |    FILTER                        |             |      1 |        |      17 |00:00:00.76 |    4829 |   3189 |           |
|   4 |     NESTED LOOPS                 |             |      1 |   1083 |      17 |00:00:00.76 |    4829 |   3189 | 3230   (1)|
|   5 |      NESTED LOOPS                |             |      1 |   1083 |    1215 |00:00:00.37 |    3623 |   2008 | 3230   (1)|
|   6 |       TABLE ACCESS BY INDEX ROWID| ORDER_LINES |      1 |   1083 |    1215 |00:00:00.10 |    1191 |   1191 | 1064   (1)|
|*  7 |        INDEX RANGE SCAN          | ORL_FK_PRD  |      1 |   1083 |    1215 |00:00:00.01 |       5 |      5 |    4   (0)|
|*  8 |       INDEX UNIQUE SCAN          | ORD_PK      |   1215 |      1 |    1215 |00:00:00.26 |    2432 |    817 |    1   (0)|
|*  9 |      TABLE ACCESS BY INDEX ROWID | ORDERS      |   1215 |      1 |      17 |00:00:00.38 |    1206 |   1181 |    2   (0)|
--------------------------------------------------------------------------------------------------------------------------------

正如我们之前所看到的,优化器对订单明细数量的估算已经很接近,但还是不像我们了如指掌。然后,我们看到,在操作6的(A-Rows)的每一行我们启动了操作8和9,在做索引探测时每次都能找到一行,但时在访问表的时候却偶尔才能找到一个合适的行(上周的那几行数据)。需要注意的是——要允许starts * E-rows = A-rows中的舍入误差,其显著舍入误差在于,优化器“真正的(未舍入的)” E-rows 估算值是每次访问远远小于1。(译者注:在比较操作9的starts * E-rows 和 A-rows的时候其差异并不是真的有那么大,只是计划的E-rows没有办法显示真正的估计值,把小数进位显示成1了)

该计划中最显著的特点是读取和成本之间的密切关系。我们所做的都是单块读取,所以我们不需要做任何“多块”调整来从读取总数得到优化器的成本,而应该有一个一对一的匹配。这正是我们所看到的(且相当准确)。

    首先我们先读1191个块,得到A-rows里的1215个订单明细; 这相当接近于Oracle估计的1,064个读取得到预计的1083行。

    对于这些订单明细中的每一条数据,我们访问ord_pk索引——优化器预测的是每次都有一个单块读,而我们看到的是一共有817次读取,所以有一些(尽管不是大多数)的索引叶块进入了缓存,在查询运行的时候被重新使用了。

    对于每次索引访问,我们随后访问了订单表,我们再次执行了1,215次访问,Oracle在此步骤报告了总共1181个物理读——此处我们几乎没有从块缓存的再访问获得任何益处。顺便说一句,这两个步骤演示了一个相当普遍的规律:除非是特殊情况,在大量访问随机分布的数据时,索引探测倾向于比表访问会从缓存中获得更多的益处。

请注意,reads这一列如何证实了我关于在操作5的cost, rows, 和 bytes列应该出现的“正确”的值的说法。操作5的reads是 1,191 + 817 = 2,008 (相比之下我的假设是2,147);然后操作4的reads达到了2008+1181=3189,这和预测的成本3,230 密切相符。虽然嵌套循环遵循的是新算法,其向执行计划提供数字的代码仍然是遵循旧的(8i和更早版本)嵌套循环模式。

该计划的一个关键性能特性(这也使得那个哈希连接计划的一个缺陷更为清晰)是我们必须做大量的随机物理读来获取我们不需要的数据。在执行1064个物理读以获集订单明细数据之后(到目前为止我的两个计划都有这个工作负载),这个计划进一步执行了1998个物理读来找到1,215行,然后丢弃了除了其中17行之外的所有数据。这是很大的无用功。

权衡

如果出现在第前两个计划中的计算是极其准确的,那第三个又如何?下面就是我们在启用了行源执行统计信息rowsource_execution_statistics之后,再运行查询的计划(同样经过一些重排版):

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Cost (%CPU)|
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |      1 |        |      7 |00:00:01.30 |   38533 |   1201 | 35339 (100)|
|   1 |  SORT ORDER BY                   |              |      1 |   1083 |      7 |00:00:01.30 |   38533 |   1201 | 35339   (1)|
|   2 |   HASH GROUP BY                  |              |      1 |   1083 |      7 |00:00:01.30 |   38533 |   1201 | 35339   (1)|
|*  3 |    FILTER                        |              |      1 |        |     17 |00:00:01.30 |   38533 |   1201 |            |
|   4 |     NESTED LOOPS                 |              |      1 |   1083 |     17 |00:00:01.30 |   38533 |   1201 | 35337   (1)|
|   5 |      NESTED LOOPS                |              |      1 |  11525 |  23628 |00:00:00.83 |   19182 |    635 | 35337   (1)|
|   6 |       TABLE ACCESS BY INDEX ROWID| ORDERS       |      1 |  11525 |  19667 |00:00:00.44 |   18425 |    570 |   709   (1)|
|*  7 |        INDEX RANGE SCAN          | ORD_DATE_ORD |      1 |  11525 |  19667 |00:00:00.07 |      55 |     55 |    34   (3)|
|*  8 |       INDEX RANGE SCAN           | ORL_PK       |  19667 |      1 |  23628 |00:00:00.17 |     757 |     65 |     2   (0)|
|*  9 |      TABLE ACCESS BY INDEX ROWID | ORDER_LINES  |  23628 |      1 |     17 |00:00:00.32 |   19351 |    566 |     3   (0)|
---------------------------------------------------------------------------------------------------------------------------------

如果我们从检查cost开始,我们可以看到在工作的基本嵌套循环算法。操作6和7被预测以709成本提供11525行; 事实上,我们通过读取570块得到了19,667行——所以这是来自优化器的一个相当不错的估计值。虽然,带索引范围扫描的表访问确实需要访问缓存18425次,这意味着CPU时间和潜在的闩锁争用。

对于上述的每一行,我们会进入订单明细表order_lines的主键索引做一个索引(范围)扫描进行探测,然后访问订单明细表,通常是一次,偶尔也会访问几次,总共23628次访问,除17行之外丢弃了来自表的所有数据。

由于订单明细order_lines的数据在日期(和order_id)上聚簇得很好,这个查询在执行的时候我们从缓存得到了巨大的好处,所以我们只读取了65个索引块和566个表数据块来获取数据——读取需求的总数为1,201个物理块读取。

然而,纵观优化的成本计算:它允许ORDER_LINES索引的每次探测有2个物理读,再加上一个表访问的读取。如果这是单独一个订单的估算值,这当然是合理的,但是优化器预测了11525个订单,仅仅是简单地把成本乘以行数,得到总共增加的成本为34575——得到 34575+709=35284 的总成本(所报告的估算成本展现为 35337,这里面有一个舍入的小误差)。

关键是优化器屏蔽了大量的“自我缓存”的好处,而该缓存在查询运行的时候是必然出现的。这里即使是最糟糕的情况,我们要读取过去7天所有的订单的块,以及在过去7天内创建的所有订单明细的块——而这两个数据集被很好地包装在总共1,201个块中。此外还得加上这一事实,即在最近7天内所下的订单很可能在我们执行查询之前就已经很好地缓存(因为订单处理、包装、投递、开票、付款等等都倾向于发生在紧随下单之后的几天的过程中),我们的查询成本被大大地高估了,因为ORACLE不理解订单处理业务,并且其简单化的嵌套循环模型只会拿一个合理的“单一随机事件”的成本来乘以“驱动数据的总行数”,而没有意识到这个从驱动表选择的数据可以很大程度上去除随机性。

小结
为便于比较,下面列出了运行活动的摘要:
哈希连接(未显示):1191+570=1761 个物理读; 19600 个缓存读; 成本=1775
从订单明细的嵌套循环连接:1191+1998=3189 个物理读; 4,829 个缓存读; 成本=3,232
从接单的嵌套循环连接:570+631=1,201 个物理读; 38533 个缓存读; 成本=35339

具有讽刺意味的是,对三个计划的前两个,成本计算是一个很好的模型,而第三个计划的成本估算高得吓人,它却很可能是最好的计划。

一个典型的哈希连接(当它被预期在内存中完成)的成本是:

取得第一个数据集的成本 + 取得第二个数据集的成本 + 一点点额外的成本

一个典型的嵌套循环的成本是:

获得第一个数据集的成本 + (第一个数据集的行数*从第二个数据集中获取一组相关数据的成本)

由于Oracle不能充分了解数据事先已被缓存,以及在查询过程中发生的自我缓存,Oracle很容易就会高估嵌套循环连接执行时发生的物理I/O的数量。这是一个不容易被克服的缺陷,除非通过提示纠正(或者用一种经核准的方式进行提示,即提供一个SQL基线或纲要)。


脚注:
如果有人在此时想到鼓捣参数optimizer_index_caching(我应该假设“所有”的索引有多少百分比被缓存)以及optimizer_index_cost_adj(实际取值在0和100之间,当通过索引访问时,“所有”的表“不会”被缓存的百分比),那么请再三思。当我设置optimizer_index_caching的= 95和OPTIMIZER_INDEX_COST_ADJ=5时,默认的路径仍然是哈希连接; 甚至当我设置分别把参数设为99和1,哈希连接的成本仍然最低。

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
4#
 楼主| 发表于 2016-3-22 21:52 | 只看该作者
本帖最后由 newkid 于 2016-3-22 21:59 编辑

基于成本的优化器的基础知识--第4部分

作者:Jonathan Lewis

原出处:

在本系列的前三篇文章中,我概述了Oracle用来分析一个非常简单的查询的机制。沿用这种非正式的方式,我要来看看优化器在处理更复杂的查询时所采用的高层次的策略。为了做到这一点,我将着眼于查询块,略带查询转换,解释为什么给你的所有查询块命名是一个好主意,以及懂得如何找到优化器为任何查询块生成的名字——当它将你的原始查询转换为它更喜欢的处理形式时所产生的名字。

查询块
“优化的单位”—— 即优化器计算的基础—— 是查询块。每次你在SQL语句中看到任何一个这些词:“select”, “insert”, “update”, “delete”, “merge”,它就引入了一个查询块。我们前一篇的查询语句是一个包含了单个查询块的SELECT语句; 下面是另一个语句,它含有两个查询块:

select
        /*+ qb_name(main) */
        *
from    emp     emp1
where
        sal > (
                select
                        /*+ qb_name(subq) */
                        avg(emp2.sal)
                from    emp     emp2
                where   emp2.deptno = emp1.deptno
        )
;

emp表来自演示账号scott, 这个查询报告的是谁赚的钱比自己部门的平均工资更多,而且正如你可看到的,语句包含了一个相关子查询,用于计算每个员工的部门的平均工资。

你会发现我用qb_name()提示给语句中的每个查询块取了一个特定的名字——作为一名前C程序员我情不自禁将主查询块命名为“main”,我还把相关子查询命名为“subq”。至于原因我们稍后会解释,为所有查询块命名是非常有用的,特别是当你试图为一个复杂查询调试或者加提示。如果你不明确地为你的查询块命名,对SELECT语句Oracle将会命名为SEL$1,SEL$2,等等,UPDATE和DELETE也有类似的命名约定。

所以,当我们的查询有两个查询块,我们所说的“优化的单位”是查询块,意味着什么?从一个高层次的角度看,这实际上意味着优化器不想为一个更复杂的查询生成计划,最多是非常简单的查询结构,看起来如下:

select      {表达式清单}
from        {表明清单}
where       {谓词清单}
[group by / having / order by 等等 …]

这意味着相关子查询之类的过于复杂,也就意味着Oracle不得不将我们的查询转换为期望的形式(如果可以合法地完成的话),或者它必须单独优化两个查询块,然后想出如何把两块缝合在一起。特别是在查询转换这一领域,优化器在近几年已经进化,并且变得更加复杂。

选项1: 分别优化

如果优化器决定独立处理我们的两个查询块,这意味着,抛开很多具体细节,它必须优化如下的两段SQL:

主查询块main:

select  *
from    emp
where   sal > :b1
;

子查询块subq:

select
        dept_no, avg(sal)
from    emp
where   deptno = :b2
;

关于这两个查询块要注意的第一件事是,我们正在为“未知的值”进行优化。对主查询块的优化会给我们执行的成本以及它会返回的行数的估计值。对子查询块的优化给了我们执行一次子查询的成本。有了这些数字,优化器就能估计它将会执行多少次子查询,以及工资值的测试有多少次会得到满足。

下面这个执行计划显示了这种情况的发生。我不得不将一个no_unnest提示放入子查询本身(虽然我可以把提示no_unnest(@subq)放在查询的主体中),以使这个计划得以出现:

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    38 |    12   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  4 |    TABLE ACCESS FULL| EMP  |     5 |    35 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

在这个计划中,我们可以看到主查询块所需要的全表扫描出现在操作2,而需要子查询计划的地方出现在操作3和4。FILTER过滤操作结合了两个子计划,原则上为主查询块返回的每一行执行一次子查询块。

有几个数字值得一提。

在操作4所预测的5行来自(EMP中的行数/在EMP中的不同DEPTNO个数 - 即:14/3)。

查询的总成本为12,因为优化器已经计算出子查询(成本为3)将必须被执行3次(在表扫描过程中可能被返回的不同deptno值的个数),总共为9,加上原始表扫描的成本。

最终的总行数应该被推导为表扫描发现的行数的5%(这是优化器在使用诸如“列> {未知值}”这样的过滤条件时的标准“猜测值”),然而,在这种“子查询过滤器”的情况下,近期许多版本的Oracle有一个bug,它会把最终估计值减少到1, 不管我把emp表变得多大。

我的两个原始查询块是很容易在计划中看到的,但如果我们在调用DBMS_XPLAN.DISPLAY()的时候添加了“alias”格式,我们就可以生成一个被优化的查询块的名称(以及每个对象相关的查询块)的报表。用这个例子你会得到:

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN
   2 - MAIN / EMP1@MAIN
   3 - SUBQ
   4 - SUBQ / EMP2@SUBQ

你会发现,在更复杂的计划中有些行不会报告查询块的名字,但在我们这个简单的例子中,每一行都标出它所属的查询块。此外,每一个对象都是使用它的别名(EMP1,EMP2)来报告,用它原先出现的查询块名称修饰。在我的例子中,你很容易可以从计划中看出哪个emp和我原来语句的那一部分相配,但事实并非总是如此,所以当你要处理复杂的SQL时,明确的标签可能是非常有帮助的。

选项2: 展开

优化器可能决定把子查询转换成一个“表”(实际上是一个内联视图),可以移入到主查询产生一个连接,而不是保留原来的两个块——在较新版本的Oracle中,许多情况下这通常会发生,假如这样的转换是合法的话,这就是为什么我不得不在我原来的查询中放一个提示来阻止这种“展开”。如果没有提示,这就是我会得到的计划:

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    64 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN           |         |     1 |    64 |     7  (15)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1 |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |         |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

我们仍然很容易在VIEW操作符中看到我们的子查询,该操作符引用了一个称为VW_SQ_1的对象(Oracle用于内部生成的视图的几个和转换相关的名字之一)。这个VW_前缀的出现非常普遍,而SQ则通常出现于子查询展开。(关于其他许多生成视图的名称列表,请参阅)

在这种情况下,Oracle仍然必须单独优化SUBQ查询块,但这回是在没有关联谓词和绑定变量的情况下优化; 实际上它优化的是下面这个:

select
        /*+ qb_name (subq) */
        avg(emp2.sal) avg_sal,
        emp2.deptno   item_1  
from
        test_user.emp emp2
group by
        emp2.deptno
;

然后,它用此查询块的基数和不同值个数的估算值来把视图移入内联,在下列的两“表”连接中把它当作一个表来看待:

select
        /*+ qb_name (main) */
        emp1.*
from   (
        select /*+ qb_name (subq) */
                avg(emp2.sal) avgsal,
                emp2.deptno   item_1  
        from
                test_user.emp emp2
        group by emp2.deptno
        )                         vw_sq_1,
        test_user.emp             emp1
where
        emp1.deptno = vw_sq_1.item_1
and     emp1.sal    > vw_sq_1.avgsal
;

一旦优化器开始转换我们的查询,我们原来的查询块就开始消失——这可能使得我们难以理清执行计划的什么地方出了错,而我们又能做些什么来改变它。让我们一起来看看这个展开的执行计划的查询块/别名部分:

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$B4BE209F
   2 - SEL$AA0D0E02 / VW_SQ_1@SEL$D6166863
   3 - SEL$AA0D0E02
   4 - SEL$AA0D0E02 / EMP2@SUBQ
   5 - SEL$B4BE209F / EMP1@MAIN

原来的查询块名字都没留下来——虽然我们仍然可以看到EMP1最初来自main, EMP2来自subq。现在我们有三个查询块名(我不得不说,比我本来期望的多了一个)。

由于子查询被展开,变成了一个内联视图,它得到了一个新的查询块名称(我相信是基于应用到文本“subq”上的散列函数),奇怪的是当这个内联视图被折叠到主查询内部,它似乎被另外一层所包裹,并且产生了另外一个查询块名(基于应用到文本“sel$d8166863”上的散列函数),并且由于主查询已被改动以包含第二个对象,另一个新的查询块名字产生了(基于应用到两个文本“main”和“sel$ aa0d0e02”的散列函数)。

当你确定有必要使用提示来对Oracle转换一个查询的方式进行控制,能够找到这些查询块的名字可能是非常重要的——有时候你需要提示的那个查询块直到转换发生之后才存在,所以你不能把一个简单的提示放到基础SQL中,你必须使用一个全局修饰的提示,也即,把提示指引到一个特别命名的查询块中。(参见:)

虽然我说过,优化器喜欢把复杂的查询转变成一系列简单的表和谓词的清单,到目前为止我们的例子仍然处于一种需要优化两个查询块的状态。我们可以在计划的查询块/别名部分相当明确地看到这一点,但它在计划的纲要部分变得更加清晰了(通过在dbms_xplan的格式化选项添加“outline”来产生):

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$9F3C0132")
      FULL(@"SEL$9F3C0132" "EMP2"@"SUBQ1")
      USE_HASH(@"SEL$FB2CCE48" "EMP1"@"MAIN")
      LEADING(@"SEL$FB2CCE48" "VW_SQ_1"@"SEL$B3936C35" "EMP1"@"MAIN")
      FULL(@"SEL$FB2CCE48" "EMP1"@"MAIN")
      NO_ACCESS(@"SEL$FB2CCE48" "VW_SQ_1"@"SEL$B3936C35")
      OUTLINE(@"MAIN")
      OUTLINE(@"SEL$B3936C35")
      OUTLINE(@"SUBQ1")
      UNNEST(@"SUBQ1")
      OUTLINE_LEAF(@"SEL$FB2CCE48")
      OUTLINE_LEAF(@"SEL$9F3C0132")
      ALL_ROWS
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

如果你读一遍这个纲要,你可以看到有两行使用outline_leaf()提示。一个纲要叶子用来标识一个查询块,它最终被优化器选作最佳计划的一部分。你还可以看到各种outline()提示——当然,原来的查询块都还在,再加上用来包装展开子查询的中间查询块SEL$B3936C35。

选项2b: 展开与合并

我们(或优化器)是否可以往理想目标再接近些,即带有一组简单连接的单个查询块?答案是肯定的(至少在这个例子中)。优化器给我们的计划把emp表按部门汇总,再以deptno连接回emp表。原则上,我们在初步变换之后使用“复杂视图合并”来重写查询,让它在汇总之前就连接emp表本身。我们可以通过一个merge()提示告诉优化器采取这种策略,而不需要在我们的源SQL中重写它,方法如下:

select
        /*+ qb_name(main) merge(@SEL$AA0D0E02)*/
        *
from    emp     emp1


我把这个提示指引到(注意“@”符号)查询块SEL$aa0d0e02 —— 我们在上一节看到这个查询块标注在执行计划的操作2,3和4。我告诉Oracle这个查询块(内联视图)应该合并到计划的主体,而不是作为整个查询的一个独立部分进行优化并且(实际上)执行。这给了我以下的计划:

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     4 |   180 |     7  (15)| 00:00:01 |
|*  1 |  FILTER              |      |       |       |            |          |
|   2 |   HASH GROUP BY      |      |     4 |   180 |     7  (15)| 00:00:01 |
|*  3 |    HASH JOIN         |      |    65 |  2925 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$9E18A930
   4 - SEL$9E18A930 / EMP2@SUBQ
   5 - SEL$9E18A930 / EMP1@MAIN

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMP1"."SAL">AVG("EMP2"."SAL"))
   3 - access("EMP2"."DEPTNO"="EMP1"."DEPTNO")

我们以deptno连接,在某列做汇总,然后应用过滤条件emp1.sal > avg(emp2.sal)。转换后的查询看起来像这样——正是优化器喜欢优化的那种东西:

select
        emp1.*
from
        emp emp2,
        emp emp1  
where
        emp2.deptno = emp1.deptno
group by
        emp2.deptno,
        emp1.rowid, emp1.deptno, emp1.comm, emp1.sal, emp1.hiredate,
        emp1.mgr, emp1.job, emp1.ename, emp1.empno
having
        emp1.sal > avg(emp2.sal)

如果我们检查优化跟踪文件(10053事件),我们会发现优化器实际上已考虑过并拒绝了这一路径(就像它已考虑并拒绝了第一个过滤子查询的路径)。回头检查以前的计划,你会看到最新的计划似乎与前一个有着同样的成本,所以你可能会问,为什么优化器选择了它所选的那个—— 事实上,如果精确到4位小数,前一计划的成本是7.0712,相比之下这个计划的成本是7.0735,所以这是很接近的,但前一个计划还是更低廉一点。

总结

Oracle有查询块的概念,它其实是优化器的基本优化单元,其形式体现为表间的一种简单连接。如果我们写出一个包含子查询,内联聚合等等的复杂语句,我们应该认识到这一概念,并为每个查询块命名。

我们分析了一个由2个查询块构成的语句,并注意到Oracle会尝试用多种方式对语句进行变换,以减少所涉及的查询块的数目。无论它选择了那种变换,它必须分别对每个查询块进行优化(进行优化运算),并利用一些最终的运算来为它的策略进行建模,以将查询块的最终结果连接到一起。

我们的第一种转换实际上什么也没做,只给我们两个未经修改的查询块,它们必须通过一个FILTER过滤操作进行连接——此处的最后运算和嵌套循环连接是非常相似的。我们的第二种个转型也给我们留下了两个查询块进行优化,但其中的一个被设计为生成一个内联视图,在算术上它可以被视为一个简单的表连接的一部分——然而,这种转换丢弃了我们原有的查询块名称,并引入了一些新名字。第三种转换将查询减少为为单个查询块,从优化的角度来说这是很理想的,但结果它的执行计划竟然比之前的选项之一更加昂贵。

因为优化器会犯错误,而且优化器对于复杂谓词的选择性必须做一些猜测,最终被优化器选中的转换可能不是最好的,所以我们必须能够阻止一个坏的选择,并把优化器重新导向一个好的选择。我们可以利用提示做到这一点,但有时我们需要将一个提示直接指引到一个特定的查询块,它可能是一个在我们原有的查询中不存在的查询块,所以要注意在执行计划中查看生成查询块名称的选项,这在手工优化复杂的SQL语句中是很有用的一步。

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
5#
 楼主| 发表于 2016-3-22 21:54 | 只看该作者
本帖最后由 newkid 于 2016-3-23 00:08 编辑

基于成本的优化器的基础知识--第5部分

作者:Jonathan Lewis

原出处:

距离我上一篇关于基于成本的优化器的文章的发布已经有三个月了,所以看起来很有必要列出前几篇的缩略轮廓:

在第一篇中我给出了一个非正式的概述,优化器对一个简单查询会怎样进行“思考”。

在第二篇中我创建了一个数据集,并执行了几个单表查询来显示数据的模式,指出了优化器在估算处理查询所需的资源时会碰到的问题之一,并对一个普遍的优化器问题提供了正式的修正方法。

在第三篇中我展示了优化器关于哈希连接和嵌套循环连接的基本运算可能轻易地为一个较好计划给出比一个更糟计划更高的成本,这是由于优化器对缓存效果的无知所引起的。

在第四篇中我介绍了“优化的单位”,即表之间的简单连接,并解释了优化器会如何试图将一个复杂的查询转换成更简单的东西——通常是更少的查询块——这发生在对查询块进行优化,以及对合并这些查询块的效果进行估算之前。“过滤子查询”的例子说明了优化器的估算可能出问题的两种方式。

在这一篇中,我将着眼于优化器可能选择较差执行计划的其他方式,并对如何快速找到错误的根源进行一些指点,以及解决其中一些错误的几种策略。

错误的根源

我们已经看到,嵌套循环连接的基础成本是“从第一个表中选择的行数”*“从第二个表选择一组相关的数据行的成本”。我们注意到,对于一个索引来说,索引的聚簇因子clustering_factor对于选择标称的“一组相关数据”的成本有很大的影响,而12C(以及带反向移植补丁的11.2.0.4版本)给了我们一个机制来告诉Oracle如何推导出聚簇因子的更真实的值。不过,由于乘以倍数的关系,我们还需要知道一点关于Oracle对“选择的行数”的错误估计是如何产生的,以及我们能够做什么来帮助优化器获得更好的估计值; 所以我们需要开始对选择性进行思考。

基数和选择性

虽然,作为数据的用户,我们倾向于认为,就“行数”(基数)而言,在优化器使用的多数关键运算中,都和“行数的比例”(选择性)有关。如果我们执行类似于select * from all_objects where type = 'SYNONYM' 这样的查询,我们可能会看到计划中的“行数”(基数)列预测值为2,365,但在底层优化器使用的是比例(选择性)1/30,并且将其应用到它所估计的表中具有的70,963行。。

选择性的一个大问题是,Oracle通常不会有一个消息灵通的机制来算出在执行计划的每一步应该使用的选择性。如果是这样的话,那么它有两个选项:其一选择是简单地猜测并使用一个相当任意的分数,其二是应用一种合理的统计方法,但并不一定适合你的数据。

这里有一个小脚本可以生成一些数据,我们可以用它来演示一些问题。我将要展示的这个例子值得一试(以及尝试其主题的各种变化),一旦你做了一点点练习,你就会更好地感觉到,查出错误技术估算的底层原因是多么的容易。我在下面的所有例子都使用11.2.0.4。


create table t1
nologging
as
with generator as (
        select        --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        rownum                                        id,
        mod(rownum-1,200)                        mod_200,
        mod(rownum-1,200) +
                trunc(dbms_random.value(0,5))        mod_200_fudge,
        trunc((rownum-1)/5)                        trunc_5,
        (sysdate - 2e5/86400) +
                trunc((rownum-1)/5) / 86400        five_per_sec,
        trunc(sqrt(rownum-1))                        skewed
from
        generator        v1,
        generator        v2
where
        rownum <= 1e6
;

begin
        dbms_stats.gather_table_stats(
                owner            => user,
                tabname                 =>'T1',
                method_opt         => 'for all columns size 1'
        );
end;
/

我生成了一百万行数据——它仅仅用了几秒钟就可以做到这一点——在mod_200列保存着200个不同的值(每个值5000行),均匀扩散在整个表中,mod_200_fudge列和mod_200密切相关,但我引入了一个小小的随机调整,使得针对mod_200列的每个值开始的连续五个数,mod_200_fudge都会有大约1000行与之对应。

trunc_5列保存着200,000个不同的值,每5行为一组聚簇于相同的值,从0开始,结束于199,999。这个5行聚簇随后在基于日期型的five_per_sec列被重复,它从过去的200,000秒开始,每秒存有5行数据,总共持续200,000秒。

最后一列是一些非常倾斜的数据,是用平方根函数生成的。由于对TRUNC()的调用,它拥有1,000个不同的值(从0到999); 只有一行的值为0,  3行值为1,5行值为2,等等,(2N+1行具有值N),直到我们得到1999行,其值为999。尽管数据分布不均匀,我只在此列收集了简单的统计:在本期文章中我不打算提及关于直方图影响的任何东西。

演示

我要运行一些查询,这样做之后再从内存中把执行计划取出来。我用的是下面的框架,我将只显示第一个例子:

set pagesize 60
set linesize 180
set trimspool on
set serveroutput off
select count(*) from t1 where sign(mod_200) = 1;
select * from table(dbms_xplan.display_cursor);

下面是我们将会在未来的几篇文章中研究的查询的完整列表:


select count(*) from t1 where sign(mod_200) = 1;

column new_date new_value m_date
select to_char(sysdate,'dd-mon-yyyy') new_date from dual;

select count(*) from t1
where trunc(five_per_sec) > to_date('&m_date','dd-mon-yyyy');

select count(*) from t1 where mod_200 = mod_200_fudge;

select count(*) from t1 where mod_200 = 100 and mod_200_fudge = 100;

select count(*) from t1 where trunc_5 > (
        select max(trunc_5) from t1 where mod_200 = 0
);

select count(*) from t1 where five_per_sec > sysdate - 60/86400;

execute dbms_lock.sleep(10)
select count(*) from t1 where five_per_sec > sysdate - 10/86400;


variable b1 number
exec :b1 := 10
select count(*) from t1 where skewed = :b1;

exec :b1 := 100
select count(*) from t1 where skewed = :b1;

在下面的各个部分中,我会报告每个查询的基本执行计划,并给出一个简要描述,这个查询试图要说明什么。

函数(列名)=常数

优化器不知道一个函数的效果是什么。我们可以看看函数和输入数据,而且在本例中,我们知道大部分数据将满足谓词,因为sign(mod_200)仅当mod_200为零才返回0,其余数据都将返回1。该查询返回结果995,000,但计划预测是10,000。这是一个固定1%的猜测:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   660 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |   660  (10)| 00:00:04 |
---------------------------------------------------------------------------

幸运的是,假如我们确定预测值过于糟糕,以致成为一种威胁,我们可以利用虚拟列来解决此类问题:

alter table t1
add (
        sign_mod_200 generated always as ( sign(mod_200) ) virtual
)
;

begin
        dbms_stats.gather_table_stats(
                ownname    => user,
                tabname    =>'T1',
                method_opt => 'for columns sign_mod_200 size 3'
        );
end;
/

需要注意的是,当你创建一个虚拟列,你需要在此列上收集统计信息,才能起到你所期望的作用,这一点很重要——尽管在你忘记的情况下,如果走运的话也可能有用。你可以一直添加虚拟列,直到你碰到表的1000个列的标准限值——但不要急于添加虚拟列来解决所有的问题,记住Oracle将会在其统计信息的自动收集任务中收集所有这些列的统计信息。(如果你正在使用分区表,并且执行所有的全自动增量统计,那么你还会创建并且存储概要(synopses))。

在本例中我用“for columns XXX size 3”收集了统计信息,因为sign()函数只能返回3个可能的值(-1,0,1),既然我期待数据会大量倾斜,此列上的频度直方图也许对我有帮助。

有了虚拟列——代码没有变化——优化器对返回行数的估计跃升至正确的值。

当需求略有不同时——如果我感兴趣的只是少数sign()为零的行——我可能会选择创建不同的虚拟列并对它建索引:

alter table t1
add (
      mod_200_zeroes generated always as ( case sign(mod_200) when 0 then 0 end )
         virtual
)
;

begin
        dbms_stats.gather_table_stats(
                ownname    => user,
                tabname    =>'T1',
                method_opt => 'for columns mod_200_zeroes size'
        );
end;
/

create index t1_i1 on t1(mod_200_zeroes) nologging;

select * from t1 where mod_200_zeroes = 0;

这给了我们一个尽可能小的索引——它包含的条目仅仅是我们感兴趣的行。即使这是个特例,这也说明了一个有用的原则,我们不会对sign()建索引,这既不明智也没有用处,因为索引的相关数据太多了以至于它无法起作用,哪怕目标数据集小得多,我们只会建立一个基于函数的索引来定位我们感兴趣的mod_200本身为零的那些行。

函数(列名)>常数

在这个例子中我把当前日期复制到一个替换变量中然后才使用它。和前面一样,优化器对列上的函数效果一无所知。


column new_date new_value m_date
select to_char(sysdate,'dd-mon-yyyy') new_date from dual;

select count(*) from t1
where  trunc(five_per_sec) > to_date('&m_date','dd-mon-yyyy');

因为我是从当前日期往后创建数据,这个查询将找不到匹配的行——但是优化器另有一番猜测:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   812 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 50000 |   390K|   812  (27)| 00:00:05 |
---------------------------------------------------------------------------

猜测值是5%。在这个基于范围的谓词的其他变化上也同样如此,而BETWEEN谓词则报告0.25%的选择性(这不是巧合,恰好就是5%的5%)。同样的5%(以及相对应的0.25%)也出现在“列> {未知值}”这种形式的谓词中。

同样的,我们可以增加一个虚拟列并收集统计——虽然在这样一个日期范围大得多的同种类型的例子中,直方图很可能是不太适当的:

alter table t1
add (
        trunc_five_per_sec generated always as ( trunc(five_per_sec) ) virtual
)
;

同样的,无需更改查询文本,行数的预测变化了。不幸的是这个具体的例子展示了一个不同的问题。

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   812 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   333K|  2604K|   812  (27)| 00:00:05 |
---------------------------------------------------------------------------

现在,优化器认为我们会得到333000行,而我们知道不会得到任何数据。问题在于(我们将在下一期中再次看到)我们的谓词是落在虚拟列的统计信息的已知范围(低/高值)之外,所以优化器使用了1/num_distinct作为选择性——这是当谓词不“友好”的时候它对自己应该怎么做的另一种不幸的猜测。因为我在trunc_five_per_sec上只有3个不同的值,预测的行数为1,000,000/3,就是你看到的333,333。

列的比较

虽然分析一个在同一个表中比较两个列的查询似乎有点不切实际,我将要展示的原则适用于在连接查询中跨表比较列的情形。

select count(*) from t1 where mod_200 = mod_200_fudge;

我们知道,对应mod_200的每个值会有大约1,000行的mod_200_fudge有相同的值,另外有4000行相当平均地分布在接下来的4个连续的值。既然涉及到的不同的值有200个,我们期望结果的计数是大约200,000——但优化器的估计却是这样:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   654 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  4902 | 39216 |   654   (9)| 00:00:04 |
---------------------------------------------------------------------------

这里面的原理是,优化器知道mod_200有200个不同的值,mod_200_fudge有204个不同的值,所以它使用了不同的值较大的个数(选择性值较低)来计算预期的基数。实际上,这已经考虑了两个谓词“mod_200={常数}”和“mod_200_fudge={常数}”并在返回较少行数的谓词上使用了算法:1,000,000 / 204=4901.96。

在这种情况下,你可能得依赖于动态采样以获得更好的估计——除了虚拟列还可以给你一种解决这个(特定的)问题的方法。如果mod_200= mod_200_fudge那么mod_200_fudge - mod_200=0:让我们创建一个虚拟列,收集相关统计信息,并修改查询。请注意,在这种情况下,我预计虚拟列持有5个不同的值,每个大约200,000行——在其他情况下,如果数据遵循另一种更有趣的模式,你可能想创建一个直方图,或者甚至引入一个更复杂的虚拟列(以及相匹配的代码更改),以便利用一些精确的索引。

alter table t1
add (
        mod_200_diff generated always as ( mod_200_fudge - mod_200 ) virtual
)
;

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8vrjgbqkqds8u, child number 0
-------------------------------------
select count(*) from t1 where mod_200_diff = 0

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   665 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   200K|   585K|   665  (10)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("MOD_200_FUDGE"-"MOD_200"=0)

有了虚拟列(和统计信息)以及代码的修改,优化器在查询中正确地预测了200,000行。(我在这里的DBMS_XPLAN输出中包含了查询和谓词部分,以演示ORACLE能够显示底层的列作为谓词的含义, 尽管我在查询中使用了虚拟列)。

值得注意的是,加入虚拟列可能会暴露出写得很糟糕的应用程序的性能问题,甚至有崩溃的危险。首先,如果您的应用程序包含使用“SELECT *”写法的代码,那么它会选中虚拟列:这可能只是增加数据在网络上移动的代价,但如果代码不是设计成能够处理开放式列清单,你就会发现代码崩溃,因为你没有为SELECT列表保留足够的接收变量。其次,如果你的应用程序代码含有“insert into tableX values(...)”而不指定表的列清单,那么它就会崩溃并报告Oracle错误“ORA-00947:没有足够的值”。在12C我们可以绕过这两个问题,因为我们可以把虚拟列声明为不可见。

列的组合

这是一个广为人知的问题。Oracle假定在一个表中的不同列之间没有相关性,并利用独立变量的概率理论推导出AND和OR的选择性,我会在向你展示一些结果之后进行描述:

select count(*) from t1 where mod_200 = 100 and mod_200_fudge = 100
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   654 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    25 |   200 |   654   (9)| 00:00:04 |
---------------------------------------------------------------------------

我们知道,在where mod_200 = 100的5000行当中,大约有1000行将具有mod_200_fudge=100; 所以优化器的估计值是远远太低了。这是因为优化器没有任何信息告诉它这两列是密切相关的。它的计算公式这么说:

每200行中会有一行mod_200 = 100, 每200行中会有一行mod_200_fudge = 100, 所以在 200 * 200 行中会有一行两个谓词都为真,这给出了 1,000,000/40,000 = 25 行。

Oracle允许我们创建列群组来解决这类问题——仅当所有涉及到的谓词都使用等值匹配,它们才能起作用,并且如果单个列上有直方图,它们也不再有效(但请参见Chris Antognini的注释,特别是附录部分),或者是当谓词超出了任意一个列的低值/高值范围之外,也不再有效。

begin
        dbms_stats.gather_table_stats(
                ownname         => user,
                tabname         => 'T1',
                method_opt      => 'for columns (mod_200, mod_200_fudge) size 1'
        );
end;
/

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   727 |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |       |
|*  2 |   TABLE ACCESS FULL| T1   |  1000 |  8000 |   727 |
-----------------------------------------------------------

对视图user_tab_cols快速检查一下会告诉我们,我们有一个新的(隐藏的,由系统生成)的列,名字为SYS_STUHEAW8XLV$A#9LXJ65QQJU13,拥有1,000个不同的值。它在内部使用名为sys_op_combined_hash()的散列函数创建了一个虚拟列,然后计算这个谓词的选择性:“sys_op_combined_hash(mod_200,mod_200_fudge)= sys_op_combined_hash(100,100)”。

你最多可以创建20个这样的“列群组”——所以不要浪费这一功能给你的有限的范围。在这个特例中有一个替代方案,使得我们能够避免“用光了”这一个有限的供给。我们已经创建了一个基于(mod_200_fudge - mod_200)的虚拟列mod_200_diff,看看如果我们重写我们的查询会发生什么:


PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  dgdusd4tad9nv, child number 0
-------------------------------------
select count(*) from t1 where mod_200 = 100 and mod_200_diff = 0
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   727 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |       |
|*  2 |   TABLE ACCESS FULL| T1   |  1000 |  7000 |   727 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("MOD_200"=100 AND "MOD_200_FUDGE"-"MOD_200"=0))

该查询在逻辑上等同于原查询,并且我们通过使用虚拟列,而不是列群组得到了正确的基数估计。计算的工作原理如下:

(mod_200 = 100)的选择性为 1/200
(mod_200_diff) = 0 的选择性为 1/5
(mod_200 = 100 and mod_200_diff = 0) 的选择性为 1/200 * 1/5 = 1/1000.

由于我在数据中创建的精心构造的模式,这些数字恰好是数据的有效表示。在实际数据中你可能无法找到这样一个完美的模型,但你可能会发现一些场合,此时类似这样的东西已经是足够好的近似值,足以让你为优化器提供一个对基数更好的认识,比起它用的其他方法要好得多。

关于升级到12C的一点警告。如果你启用所有的自动优化功能,Oracle能够创建列群组并且为其收集统计,而不会告诉你它已经这么做。这是基数反馈功能(cardinality feedback)、动态统计、SQL指令机制的一个副作用。因为你在每张表只能有20组扩展的统计信息,你可能需要密切关注视图user_tab_cols和user_stat_extensions,以确保你知道在你的表上创造了什么列群组。

结论

这篇文章已经太长了,而关于优化器在获得合理的基数估计方面存在的问题,还有几种情况没被我涵盖。然而,我们已涵盖的情况通常可以通过创建虚拟列或者“列群组”这一扩展统计信息的变种来很好地处理。

在下一篇文章中,我将继续梳理在本篇文章开头所引入的问题清单,我们会发现有一些问题更为棘手。

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
6#
 楼主| 发表于 2016-3-22 21:54 | 只看该作者
本帖最后由 newkid 于 2016-3-23 02:35 编辑

基于成本的优化器的基础知识--第6部分

作者:Jonathan Lewis

原出处:

在本系列的第5部分,我开始梳理一个查询清单,旨在演示优化器可能产生不好的基数估计的几种方式。在这一篇中,我将理顺清单的剩余部分。剩下的查询如下:

select count(*) from t1 where trunc_5 > (
        select max(trunc_5) from t1 where mod_200 = 0
);

select count(*) from t1 where five_per_sec > sysdate - 60/86400;

execute dbms_lock.sleep(10)
select count(*) from t1 where five_per_sec > sysdate - 10/86400;


variable b1 number
exec :b1 := 10
select count(*) from t1 where skewed = :b1;

exec :b1 := 100
select count(*) from t1 where skewed = :b1;

第一个查询演示了优化器最喜欢做的猜测之一又出现了—— 5% —— 这经常被用于估算一个子查询的影响。下一对(之间有10秒的睡眠)演示了即将过期的统计信息的潜在的灾难性的副作用。最后一对向我们介绍了绑定变量和倾斜数据分布的一些问题。

请记住我所有的演示例子都是在11.2.0.4上运行的,并且到目前为止,还没有在查询所引用的任何一个列生成直方图。

子查询效应

这就是我们的清单中的第一个查询的缺省执行计划:

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |  1308 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL  | T1   | 50000 |   244K|   658   (9)| 00:00:04 |
|   3 |    SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  4 |     TABLE ACCESS FULL| T1   |  5000 | 45000 |   650   (8)| 00:00:04 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TRUNC_5">)
   4 - filter("MOD_200"=0)

该查询识别出195行,但预测为50,000——这是起初的1,000,000行的5%。

在这个简单例子中这个错误其实无关紧要,但你能想象,在一个更复杂的查询——一个把第二行连接到另一个表的查询——50000的估计将鼓励优化器做一个哈希连接到下一个表,而一个更接近实际数值195的估计值可能会鼓励优化器做一个嵌套循环连接。

当然,你可以先简单地制定出最佳的计划,并创建一套完整的提示来强制该计划(在得到你想要的计划之后,可能通过生成等价的SQL计划基线来改善提示的强壮性)。如果查询足够简单,你也许通过在主查询块填充/*+ leading() */提示,以及在子查询中填入 / *+ no_unnest push_subq */ 这一对提示就得以脱身。

此外,如果你有性能包的许可,你可以使用DBMS_SQLTUNE包(也许是通过OEM的图形界面)来创建用于查询的SQL概要(SQL Profile)。这将会执行查询,并且发现默认的5%是一个糟糕的估计,并可以创建一组“opt_estimate()”提示来帮助优化器算出更好的估计值,这应该导致一个更好的计划被选定。

最后,你可能会幸运地能够为SQL添加一个“简单”的cardinality()提示,使得优化器一开始能够有一个恰当的估计值,并且允许它为计划的其余部分生成合理的估计值。不幸的是,cardinality()提示不怎么被支持,没有真正的文档记录,而且比起互联网上能找到的几个注释所建议的更加微妙(因此也更难用得多)。

在我这个小小的例子中,我可以简单地把提示/*+ cardinality(t1 195) */ 添加到SQL中:

select
        /*+ cardinality(t1 195) */
        count(*)
from    t1
where   trunc_5 > (
                select max(trunc_5) from t1 where mod_200 = 0
        )
;
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |  1308 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL  | T1   |   195 |   975 |   658   (9)| 00:00:04 |
|   3 |    SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  4 |     TABLE ACCESS FULL| T1   |  5000 | 45000 |   650   (8)| 00:00:04 |
-----------------------------------------------------------------------------

这并没有改变查询的成本,但它已经按照要求在第二行修改了基数估计值。Oracle在内部不使cardinality()——如果你查看优化器的跟踪文件(10053事件),你会看到提示已被翻译为以下形式的opt_estimate()提示:

opt_estimate(table t1@sel$1 rows=195.000000)

不过,正如所有的“微调”的提示一样,你通常需要比你原先想像的远远更多的cardinality提示。因为它代表这个提示是“单表访问”的提示,但在更复杂的情况下,你可能要包括大量的提示来表达这样的意思:“如果你从T1开始它会给你195行,但是如果你从T2开始它会给你5000行”,然后你可能需要用“连接基数”版本的提示来表达类似这样的意思:“如果你从T1 - > T2开始,这个连接会给你800行,但T1 - > T3会给你650行”。一般来说,尝试并找出你所需要的所有的cardinality提示以确保你所需要的计划会出现,这是个有点痛苦的过程。最好能够避免使用这个技术并且坚持使用更加“传统”的提示。

时间的推移

为了运行我的测试,我创建了数据,收集统计信息,然后执行几个不同的查询,并从内存取出它们的执行计划。等到我执行那个把five_per_sec列和“SYSDATE - 60秒”进行比较的查询,在创建数据和运行查询之间已经过去了几秒钟。在five_per_sec的时间部分的最高值是17:15:17,而我执行查询的时间已经移到17:15:31,差距14秒——这意味着表中有46秒的数据匹配谓词“five_per_sec > sysdate – 60/86400”。下面是我运行查询时得到的执行计划:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   752 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   230 |  1840 |   752  (21)| 00:00:04 |
---------------------------------------------------------------------------

正如你所看到的,优化器的估计是230行——和46秒完美匹配,每秒五行。

但如果我再多等10秒钟(现在时间就是17:15:41),然后查询sysdate – 10/86400,会发生什么?我查询的是比17:15:31更迟的数据,在表中没有匹配该谓词的数据,但优化器期望找到的是什么呢:


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   752 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     5 |    40 |   752  (21)| 00:00:04 |
---------------------------------------------------------------------------

优化器的估计是5行。一些额外的测试会告诉你,这是符合“列=常数”的行数——一旦你执行一个带有基于范围的谓词的查询,而优化器能看到要求的数据完全落在已知的低值/高值范围之外,那么算式就会回到“列=常数”。

时间的推移——案例分析

在我的模型中的误差不算太糟——如果在我做测试的过程中,数据继续以同样的速度到达,那么在最近的10秒就会有50行,所以5不会是(不一定是)一个糟糕的估计,也可能不会引发问题。但这个基本行为可能有可怕的影响,如以下产品例子所示。

数据的到达速度是每分钟300行(每秒5行),有一个非常重要的轮询作业每隔几分钟执行一个查询来确定最近15分钟收到(并且仍然处于某种状态)的数据——通常会识别出大约2000到4000行数据。该查询相当简单,包括关键的时间谓词:

where arrival_time > sysdate – 1/96

由于数据的改变速率的关系,自动统计信息收集作业每天晚上会在此表上运行,因此其统计数据的精确度就如一个简单的默认配置所能做到的那样。

每隔几天——看起来是完全随机的——这个轮询作业就会陷入一个可怕的性能问题,并且执行一次需要几分钟而不是几秒钟。毫无意外,这已经对业务经营产生影响,而用户对系统的看法也会大不相同。这是怎么回事?

就在统计信息收集之后的15分钟,优化器对该查询的估计就会从几千跌到仅仅为5(因为对于任何给定的arrival_time平均有5行数据)如果发生这种情况,计划就会发生灾难性的变化,因为优化器确定它可以找到很好的策略来处理这5行,而这对于真正存在的几千行数据却是很糟糕的策略。

该查询每隔几分钟执行,所以它通常被重新优化——产生良好的执行计划——在统计信息刚被收集不久之后。大多数日子,游标会保留在内存中,并且在这一天剩下的时间内不断地被重用; 所以优化器从不需要再次优化该语句,也就没有机会生成坏计划。不幸的是偶尔会发生这种情况,对共享池中的内存产生了巨大的需求,这个好计划就从内存中被冲走,查询在统计信息被收集之后的几个小时重新被优化,这就生成了那个“只有5行”的坏计划——然后该查询就表现很差,直到第二天,或者他们在此表上收集了统计信息(客户有天发现了)。

基于时间(或者序列)的列可能会引发各种奇怪的问题——尽管其后果是你可能只在运气不好的情况下才会吃到苦头; 但如果你有这样的代码,它一直在寻找“最近的几行数据”,这就是一个要注意的问题。如果你看到了这个问题,有三种基本策略可以遵循:

通过(或者SQL计划基线/存储纲要)把好计划固定。

设定一个作业,可能频繁地(在这个案例中每5分钟一次是适当的)在此关键列上收集统计信息,并使得所有相关的游标失效。

编写一些代码,调用dbms_stats.set_column_stats()来把列统计信息中的高值设置为未来的值,这个值必须足够远使得可以避免计划的灾难性变化(但也不能太远,防止导致优化器在相反的方向犯错)。这其实只是第二个方法的变种,但是(一)它执行的代价更低廉并且(二)比起收集统计信息的作业,你也许能够以低得多的频率执行它。

绑定变量

在列表中的最后两个查询引入了绑定变量的问题。我们将在这个系列的未来篇章中更详细地访问这个话题,我在这里想说的唯一的一点是倾斜的数据模式和绑定变量无法融合——你必须帮助优化器。

看看数据被生成的方式,我们可以计算出,这两个查询的第一个应该返回21行,第二个应该返回201行(“skewed = N”的数据总共有有2N + 1),但是执行计划在两种情况下,都预计1000行:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   666 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  1000 |  4000 |   666  (11)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SKEWED"=:B1)

这是因为我们没有做任何事情使得优化器知道数据值的分布是极其不均匀的; 所以现有的统计数字只是告诉它总共有1,000,000行以及1000个不同的值,所以任何一个值将返回1,000,000 /1000=1000行。即使我们在查询中用了字面常量值10和100,而不是使用绑定变量,我们也会得到同样的结果。

为了帮助Oracle理解这种倾斜数据,我们必须在列上创建的直方图。不幸的是我在列中有1,000个不同的值,而在11g中一个直方图最多能包含255行(等同于254个桶),所以Oracle被迫在列上生成一个等高直方图。事实上,虽然我们可以看到,在最稀少和最常见的数据之间的变化是巨大的(skewed=0有一行,skewed=999有1999行),如果让它“自动检测”是否需要,Oracle甚至都没有创建直方图。如果我们强制Oracle创建一个254桶的直方图,它不会捕获任何常见的值(1M行数据只有254个桶,一个值必须出现在大约4000行然后才能被注册为常见值,如果你不走运的话,甚至出现在约8000行也不会被注意到)。因此在11g中一个“被收集”的直方图也无济于事。

为了继续示范,我切换到12c。我如果再次告诉Oracle使用“size auto”在列上收集直方图,它会决定使用254个桶,然后又会决定这样做是不值得的。然而,在12c我可以在直方图拥有多达2048的行,当我告诉Oracle使用最大值时,它会创建一个频度直方图,列中的1000个值每个都会有一行。然后我得到了两个查询的计划如下:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   665 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    21 |    84 |   665  (11)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SKEWED"=:B1)

这两个查询的计划还是没变,但它至少在第一次执行时预测了正确的行数。优化器有办法做到这一点,是因为在第一次执行时查询必须被优化,而优化器“窥视”了绑定变量的实际值,以帮助它选择计划,所以查询就被优化成如同谓词看起来是“skewed = 10”,而直方图的信息表明有21行的值为10。

不幸的是,当我们在绑定变量使用一个新值来执行查询,优化器认出它在最近看到的查询文本完全相同,并发现它在内存中有这个查询的合法执行计划,就只是简单地重新使用了这个计划。

绑定变量窥视和执行计划的重用是有益的,因为我们不必在每一次查询的执行都竞争资源并用光资源来优化它;但是,当数据分布是如此的不均匀,使得不同的输入值所产生的结果集大小会显著不同,计划的重用就变成一种弊端,我们可能需要对那些看起来一样但实质上却是非常不同的语句重新优化。

绑定变量和直方图之间的冲突是一个长期存在的问题,而Oracle在过去的20年中推出了许多策略来限制这种冲突的破坏性副作用; 然而,应用程序开发人员最终需要意识到这种威胁,并确定在什么样的特殊情况下,有必要使得数据库和客户端代码协同操作以处理极端倾斜的数据分布。

总结

我介绍了优化的三个“较难”的问题。首先是子查询引入的5%的“固定猜测值”,这个是很不容易解决的,除非你准备好提供一个实际执行路径的提示,或者使用没有文档的cardinality()提示,或使用优化工具来生成一个SQL概要(一系列的opt_estimate()提示)。

第二个问题是,一个基于时间或序列值的列可能会引入随机的不稳定性,如果最重要的查询是找到“最新的数据”的话。当查询使用一个“超出范围”的值的时候,优化器用于优化一个基于范围的谓词的算法可能会导致非常糟糕的基数估计,从而导致了糟糕的执行计划。要么你使用提示(或者创建一个SQL计划基线)得到所需的执行计划,要么你采取一些行动把关键列的HIGH_VALUE保持在一个适当的最近(或未来)的值,使得优化器在重新优化查询的时候总能得到正确的计划。

最后,我们必须认识到,绑定变量和倾斜的数据模式不能融合——即使是在有直方图的情况下。关于这点我们会在未来的篇章中更多说一些,但目前我们注意到,如果同样的查询文本可能会产生极其不同的数据量,那么你可能需要在关键列上创建直方图,但你需要确保你的代码与数据库协同工作(比如在关键谓词使用字面常量),以最有效地利用直方图。


使用道具 举报

回复
论坛徽章:
264
布鲁克
日期:2016-10-08 10:06:50秀才
日期:2016-05-20 15:09:32射手座
日期:2016-05-26 14:02:50双子座
日期:2016-05-25 16:05:44白羊座
日期:2016-05-23 11:49:19双鱼座
日期:2016-04-29 17:13:05秀才
日期:2016-04-29 15:03:39秀才
日期:2016-04-29 15:04:10技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13
7#
发表于 2016-3-22 23:53 | 只看该作者
Newkid 造福人类啊,多谢!!

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
8#
 楼主| 发表于 2016-3-23 02:38 | 只看该作者
老刘的文章都是干货。虽然他的语言风格比较难懂,有很多长句,这几篇比起他关于CBO的那本书而言还是更通俗一点。
这个系列还没写完,未来会写关于绑定变量和直方图的话题,但是他说目前完全没有计划,所以我先把这几篇发布了,有新的再补。

使用道具 举报

回复
求职 : 数据库管理员
招聘 : Java研发
认证徽章
论坛徽章:
6340
ITPUB9周年纪念徽章
日期:2014-05-02 10:36:402011新春纪念徽章
日期:2014-12-29 12:11:142010广州亚运会纪念徽章:卡巴迪
日期:2014-08-06 08:44:25马上加薪
日期:2017-01-10 16:49:34马上有钱
日期:2017-01-10 16:49:34马上有钱
日期:2014-12-26 15:39:08马上有钱
日期:2014-12-26 15:39:08马上有房
日期:2014-12-26 15:42:55马上有车
日期:2017-01-10 16:49:34马上有钱
日期:2014-12-31 17:16:56
9#
发表于 2016-3-23 07:29 | 只看该作者
O(∩_∩)O谢谢!!!!!!!!!

使用道具 举报

回复
论坛徽章:
21
娜美
日期:2016-06-22 19:49:292015年新春福章
日期:2015-04-27 10:18:48懒羊羊
日期:2015-03-17 13:44:33懒羊羊
日期:2015-04-01 08:19:10天枰座
日期:2015-07-17 09:51:12沸羊羊
日期:2015-06-17 14:51:07金牛座
日期:2016-03-21 21:10:40摩羯座
日期:2016-03-29 15:07:30奥运会纪念徽章:篮球
日期:2016-09-02 09:45:252015年新春福章
日期:2015-03-06 11:58:39
10#
发表于 2016-3-23 07:58 | 只看该作者
Newkid 造福人类啊,多谢!!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表