Oracle性能分析4:数据访问方法之全扫描

SQL语句执行时,Oracle的优化器会根据统计信息确定表的访问方式,一般来说,有两种基本的数据访问方式:1)全扫描。在全扫描(全表扫描或者快速全索引扫描)中,多个块被读入到一个IO运算中。2)索引扫描。索引扫描首先扫描索引叶子块以取得特定的行id(rowid),然后利用这些行id来访问父表取得实际的行数据,访问通过单块读取来完成。这里主要讲解全扫描方式,后面将介绍索引扫描。

使用全扫描

当对一个表进行全扫描时,会将表中所有数据块(block)取出并进行处理,筛选出符合条件的数据。注意Oracle必须将整个数据块(block)中的数据读到内存中,再取得符合条件的数据。因此Oracle的优化器需要关心两个信息:获取块的数量和每个块中舍弃的数据量。优化器将根据这两个信息来判断是否使用全扫描,首先我们来看看获取块的数量怎么影响优化器的选择。

获取块的数量

总的来说,如果查询需要取出表的大部分数据块,则应该采用全扫描。但由于很难评估查询将取出的表的数据块的数量,因此在使用全扫描上存在很多这样的“经验法则”:当你的查询会取出表中x%的数据行,则应该选择全扫描。这些法则有一定的道理,但是并不准确,因为当取出的数据行较大时,自然取出的数据块也会较大,这时采用全扫描并没有问题,但有时虽然取出的数据行较小,会取出的数据块也可能会较大,实际上这时也应该采用全扫描,但这些“经验法则”则不再生效。我们看看下面具体的例子。
我们创建一个表T1:

create table t1 as 
select trunc((rownum - 1) / 100) id, rownum value
  from dba_source
 where rownum <= 10000

然后为T1创建索引:

create index idx_t1_id on t1(id)

然后为T1收集统计信息:

BEGIN
  dbms_stats.gather_table_stats(user,
                                't1',
                                method_opt => 'FOR ALL COLUMNS SIZE 1',
                                cascade    => TRUE);
END;

然后我们执行查询:

select * from t1 where id = 0

该查询的执行计划如下:

SELECT STATEMENT, GOAL = ALL_ROWS
 TABLE ACCESS BY INDEX ROWID
  INDEX RANGE SCAN	

该执行计划使用了索引范围扫描,由于符合条件id为0的数据在表中只有100行数据,而整个表有1万行数据,查询出的数据只占整个数据的1%,因此我们认为这是一种合理的执行计划。
接下来我们看下面的例子,创建一个表格T2:

create table t2 as 
select mod(rownum,100) id, rownum value
  from dba_source
 where rownum <= 10000

同样为T2创建索引:

create index idx_t2_id on t2(id)

然后为T2收集统计信息:

BEGIN
  dbms_stats.gather_table_stats(user,
                                't2',
                                method_opt => 'FOR ALL COLUMNS SIZE 1',
                                cascade    => TRUE);
END;

然后执行查询:

select * from t2 where id = 0

该查询的执行计划如下:

SELECT STATEMENT, GOAL = ALL_ROWS
 TABLE ACCESS FULL

我们看到表的执行计划变成的全表扫描,我们可以很容易的得到该查询的结果任然是100条数据,占T2表总数据量的1%,如果我们进一步比较T2和T1的数据,会发现两张表的id字段完全一样,那为什么T1选择的是索引扫描,而T2却选择了全表扫描呢?
要了解原因,我们需要从数据在数据块上的分布来分析,在T1表中,id字段的分布如下:

0 0...0 0 1 1...1 1 2 2...2 2......88 88...88 88......99 99...99 99

而T2表中id字段的分布如下:

0 1 2 3 ... 98 99 0 1 2 3 ... 98 99 ...... 0 1 2 3 ... 98 99

从这里可以看出T1表中id为0的数据都集中在几个数据块上,而T2表中id为0的数据则分布在很多不同的块上,这样导致T1的查询只需要读取很少块就可以得到结果,因此使用了索引范围扫描,而T2上的查询则需要读取大部分块,因此优化器选择了全表扫描。

舍弃

需要注意的是,全扫描的效率不仅取决于读取的数据块个数,也取决于最终的结果集行数。从上面的例子中我们可以看到:当一个数据块被读取后,查询将根据过滤条件舍弃不符合条件的数据。而这个舍弃的过程是需要耗费资源的,由于这个操作在内存中,因此耗费的将是CPU资源,而舍弃的数据量越大,耗费的CPU资源就越多。
因此,读取的数据块的个数越多,舍弃的数据量越大,全扫描的成本(cost)就越高。
不难想象,当表的数据量不断增大,舍弃的行的数量不断增加,全扫描的成本不断增加,最终可能导致优化器放弃全扫描,转而选择索引扫描。

多块读取方式

多扫描使用的是多块读取,即一个单独的IO调用将会读取多个块,读取的块的数量是可变的,但有一个上限,通过db_file_multiblock_read_count参数指定,该参数通过下面的SQL查看:

select * from v$parameter where name = 'db_file_multiblock_read_count'

下面描述了Oracle在几种情况下读取的块的数量:

 1)Oracle不得不读取超过一定边界范围的数据块。
 在这种情况下,Oracle将会在一次调用中读取直到边界范围的数据块,然后发起另一次调用来读取剩下的块。
 2)存在块已经在内存中
 首先读取那么已经在内存中的块,然后发起调用读取剩下的块,这意味着多块读取可能一次仅读取一块。例如,假定多块读取的上限是16,该次读取的数据块编号为1-16,并且编号为偶数的块已经在内存中,那么在这里例子中,将会有8次的单块读取调用来读取奇数编号的块。
 3)多块读取大小超过了操作系统限制
 这时取决于你操作系统,因此是可变的。

高水位线

所谓高水位线,就是表中最后一块有数据写入的数据块。需要注意的是即使几乎所有数据行都被删除了,并且一些块实际上已经完全变为空的了,高水位线还是保持不变。看下面的例子,当表创建并插入数据后:

Oracle性能分析4:数据访问方法之全扫描

而随着后面数据的变化(删除和修改),表中的数据变化为:

Oracle性能分析4:数据访问方法之全扫描

虽然很多存储区域已经没有数据,但高水位线任然保持不变。
那么,高水位线对全扫描会造成什么影响呢?
执行全扫描时,Oracle将一直读取到位于表中高水位线的数据块,即使它们是空的,这就意味着许多实际上不需要读取的数据块也被读取了。

下面通过一个具体的实例来看,使用先前的表T2。

1)通过下面的语句判断表所包含的数据块数量:

select blocks from user_segments where segment_name = 'T2'
结果:24

2)确定表中有多少数据块包含数据;

select count(distinct(dbms_rowid.rowid_block_number(rowid))) block_ct from t2
结果:17

3)执行下面的查询,并查看trace信息(trace信息的获取方面见Oracle性能分析1)

alter system flush buffer_cache;--清理缓存
select * from t2 where id = 0

trace信息为:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.06         18         20          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.07         18         20          0         100

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  TABLE ACCESS FULL T2 (cr=20 pr=18 pw=0 time=35975 us)

查询出100行数据,物理读取的数据块数量(disk)为18,包括一个表头数据块的读取(只有17个数据块包含数据)。执行计划使用了全表扫描。

4)执行删除数据的操作

delete from  T2

5)重新获取表包含的数据块数量

select blocks from user_segments where segment_name = 'T2'
结果:24

6)获取包含数据的数据块数量

select count(distinct(dbms_rowid.rowid_block_number(rowid))) block_ct from t2
结果:0

7)执行查询并查看trace信息

alter system flush buffer_cache;--清理缓存
select * from t2 where id = 0

trace信息为:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.21         18         20          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.22         18         20          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL T2 (cr=20 pr=18 pw=0 time=214806 us)

我们可以看到查询出的行数是0,但任然物理读取了18个数据块,执行计划任然使用了全扫描。

修正高水位线

我们已经了解了高水位线给全扫描带来的性能问题,下面介绍了几种降低高水位线的方法。

使用truncate操作

truncate table_name

在删除数据时尽量使用truncate操作,降低高水位线。

move操作

alter table table_name move

注意move操作需要使用额外的表空间存储,会锁住表,这样其他并发的用户在表上执行的DML语句会产生等待。move操作会影响到表上的索引,因此索引需要rebuild。

shrink操作

shrink space操作,不需要任何额外的空间,但是速度要比move慢上很多。shrink命令分为下面两种:

1)只压缩空间不调整水位线,在业务繁忙时可以执行

alter table table_name shrink space compact

compact操作通过一系列insert、delete操作,将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁,即只在需要移动的行上加锁。但由于涉及到rowid的改变,因此需要enable row movement。

2)调整水位线  会产生锁,可以在业务比较少的时候执行,oracle 会记住1步骤中的操作,只调整水位线

alter table big_table shrink space

使用新表

复制要保留的数据到临时表t,drop原表,然后rename临时表t为原表。

Oracle性能分析4:数据访问方法之全扫描

上一篇:Oracle 11G单机 无网络环境静默安装


下一篇:docker下如何搭建MySQL+php-fpm+nginx容器环境,本文详解