SQL调优之十:访问路径(Access Path)二
一,Direct Path Reads
直接路径读,这种情况下,数据库会绕过SGA,从磁盘把缓存读进PGA。
下面的图显示了scattered和sequential读的不同之处,包括从SGA读缓存以及直接路径读。
可以看到:
scattered read是多块读入SGA
sequential read是单块读入SGA
Direct Path Read包含了单块和多块读,读入PGA,Session Memory中
在以下几种情况,数据库可能会使用直接路径读:
- CREATE TABLE AS SELECT语句
- ALTER REBUILD或者ALTER MOVE语句
- 从临时表空间读取数据
- 并行查询
- 从LOB段读取数据
二,Full Table Scans
全表扫描,顾名思义会读取表里的所有行,然后再给其他步骤过滤掉不符合条件的结果。
优化器什么时候会考虑使用全表扫描?
一是优化器没办法使用一个不同的访问路径,比如说索引失效;
二是其他访问路径成本过高。
Reason | Explanation |
没有索引 | 如果没有索引的话,优化器只能选择全表扫描 |
查询谓词里对索引列使用了函数 | 除非索引本身是基于函数的索引,否则数据库不会使用该索引。 因为数据库是对索引列的值进行索引,而不是对应用了函数的值进行索引。 一个典型的应用错误就是,对一个数据类型为字符的列进行索引,比如说char_col列, 结果在查询的时候,使用的语句是char_col=1。 这种情况下,数据库会在这个谓词上隐式应用to_number函数,也就是会变成 to_number(char_col)=1。这种情况下,数据库不会使用原本的字符类型索引。 |
运行select count(*)语句, 有索引,但是索引列包含 null值 |
优化器在这种情况下无法使用索引,因为索引不会去索引null值。 |
查询谓词没有使用B树索引的前缘列 | 比如说,一个对employee(first_name,last_name)进行索引的索引。 如果一条SQL的谓词条件是WHERE last_name='KING',那么优化器可能不会使用该索引, 因为first_name不在谓词条件里面。 但是,这种情况下,优化器可能会选择index skip scan |
查询本身的选择性很差 | 如果优化器认为这次查询需要读取表里面的大多数数据块,那么即便索引是有效的,它也会 选择使用全表扫描。全表扫描会使用大的I/O请求。 使用比较大的I/O请求要优于很多次的小I/O请求。 |
统计信息过旧 | 比如一个小表增长成大表,统计信息错误的以为它还是小表,这种情况下,优化器可能觉得 索引的效率没有全表扫描的效率高。 |
表很小 | 如果一个表在高水位以下的数据块少于DB_FILE_MULTIBLOCK_READ_COUNT参数设置 的值n,那么一次全表扫描的成本可能会比索引范围扫描还要低。 扫描的成本可能会比读取其中的碎片或者索引更低。 |
表设置高并行 | 一个高并行的表会使优化器倾向于使用全表扫描而不是范围扫描。表的并行度可以查询 ALL_TABLES.DEGREE列 |
使用了hint来指定使用全表扫描 | FULL(表别名) |
全表扫描的工作机制
在一次全表扫描里,数据库会顺序读取高水位以下的每一个格式化了的数据块。每个数据块只读一次。
下图显示了数据库是怎么扫描的,以及怎么跳过非格式化的数据块的:
因为数据块是相连的,因此数据库可以使用更大的I/O请求来替代单块读来加快扫描速度,也就是多块读。
一次读多少个数据块是由参数DB_FILE_MULTIBLOCK_READ_COUNT控制。比如说,但这个参数值为4的时候,单次读请求里面,数据库就会去读4个数据块。
全表扫描对数据块进行缓存的算法很复杂,它会因为表的大小不同而不同。
全表扫描例子,没有索引:
三,Table Access By Rowid
rowid是数据存储位置的内部体现。它代表了行在哪个数据文件的哪个数据块,以及该行在数据块里面的位置。
使用rowid来定位某一行的位置是获取单行数据最快速的方式。
优化器什么时候会考虑使用rowid来访问表?
在大多数情况下,数据库会在使用索引扫描后使用rowid来访问表。
但是,并不是每一次索引扫描后都需要接着使用rowid来访问表。
如果索引包含了所有需要的列,那么通过rowid来访问表可能不会发生(Index Fast Full Scan)。
通过rowid访问表的工作机制
1,获取rowid,可能是where条件,或者是从索引扫描返回的结果
2,基于rowid来定位数据
例子:
四,Sample Table Scans
表取样扫描会从数据集中获取随机的采样数据。
优化器什么时候会考虑使用表取样扫描?
当from后面跟了SAMPLE关键字的时候数据库就会使用采样扫描:
1,SAMEPLE(sample percent)
数据库从表读取指定的百分比的行数
2,SAMPLE(sample block)
数据库读取指定的百分比的表数据块
取值范围从.000001到100(不包括100),实际执行只是大概值,不是准确值。
数据块采样只有在全表扫描或者索引快速全扫描的时候才能使用。如果存在一个更高效的执行计划的话,数据库是不会采取数据块采样的。
如果要确保使用数据块采样,则需要加hint来指定使用全扫或索引快速全扫。
例子:
SELECT * FROM hr.employees SAMPLE BLOCK (1);
五,In-Memory Table Scans
从Oracle 12c(12.1.0.2)开始,In-Memory scan会从In-Memory Column Store(内存列式存储)那里获取数据。
IM column store是在SGA区域中以特殊的列式方式存储表或者分区的副本,用以快速扫描使用。它和普通的缓存不冲突,可以作为补充并存。
优化器什么时候会考虑使用内存表扫描?
初始化参数影响In-Memory特性:
1,INMEMORY_QUERY
该参数可以在会话层面或者系统层面启用或者禁用in-memory查询。这个参数在测试内存列式存储的负载和性能的时候很有用。
2,OPTIMIZER_INMEMORY_AWARE
配置这个参数可以选择优化器在计算成本的时候是不是考虑in-memory对性能的提升。
3,OPTIMIZER_FEATURE_ENABLE
当把这个值设成低于12.1.0.2的版本的时候,就相当于把OPTIMIZER_INMEMORY_AWARE设置成false。
启用或者禁用in-memory查询也可以通过添加hint:INMEMORY或者NO_INMEMORY,这种方式和设置INMEMORY_QUERY参数是等同的。
假如在使用INMEMORY提示来指定使用内存表查询的时候,该对象还没有加载在内存列式存储里面的话,数据库不会等待加载该对象。
但是,在访问该对象的同时就会触发加载该对象的操作。
例子:
SELECT *
FROM oe.product_information
WHERE list_price > 10
ORDER BY product_id;
六,B-Tree Index Access Paths
索引是一个和表/表聚簇相关的可选结构,在获取表里随机分布的行的时候,索引一般情况下可以减少磁盘I/O。
B树索引是Oracle数据库里面最常见的索引类型,它会对值进行排列,并且按照范围进行划分。
因此,对于范围扫描,以及准确匹配的查询,B树索引能够提供很好的性能。
B树索引的结构
一个B树索引有两种类型的数据块:用于搜索的枝干块和用于存储数值的叶子块。
枝干块包含了能够分辨叶子块存储值的最短前缀,叶子块则包含了索引的数值以及指向行实际位置的rowid。
每个索引记录都是按(键值,rowid)排序。
索引的存储是怎么影响索引扫描的
位图索引块可以出现在索引段的任何一个位置。
上图中显示叶子块和其他块是连续的。比如说,存储了键值0到10的叶子块排在了11到19的前面。但是这个显示的是索引记录的链接列表。
实际上,索引段里面的索引数据块不需要按顺序存储。也就是说246到250的数据块在存储上可能排在了0到10的索引块的前面。
这就引出了一个结果,排序了的索引扫描必须或者说只能是单块I/O,因为数据库必须读上一个索引块之后,才能知道接着读哪个索引块。
索引块的主体部分会以堆的形式存放记录,跟表存放行数据是一样的。
比如说,如果值10最先插入到表里面,那么10的索引记录就会插入到索引数据块的底部。接着数值0被插入表中,那么在索引里面,0的索引记录会放在10的索引记录上面,依此类推,一个接着一个往上堆。
因此,在索引块内部,数据并不是排序的。但是,行头里的记录则是按序排列的,比如说第一个记录会指向键值0,最后的记录指向键值10。
所以索引扫描的过程中可以从行头判断从哪个记录开始读,以及读到哪里结束,能够避免读所有的记录。
唯一和非唯一索引
在非唯一索引里,数据库把rowid作为一个额外的列附加到键值后面。每一条记录会增加一个长度比特来让键值唯一,然后数据库会按键值排序,再按rowid排序。
比如说:
0,AAAPvCAAFAAAAFaAAa
0,AAAPvCAAFAAAAFaAAg
0,AAAPvCAAFAAAAFaAAl
2,AAAPvCAAFAAAAFaAAm
在唯一索引里,键值不会包含rowid。数据库直接按键值排序。
B树索引和空值
B树索引不会存储空值,这个特性也会影响优化器对访问路径的选择。
但是,如果是复合索引,只要不是所有列都是空值,B树索引就还会存储该键值。
例子:
假设employee表在employy_id这一列有个主键,也就是唯一加非空,然后再department_id这一列有个唯一索引,也就是允许空值。
1,select department_id from employees;这个语句要返回所有的department_id,因为这一列有空值,所以走索引是不会返回所有值的,因此优化器只能走全表扫描。
2, select department_id from employees where department_id=10; 这个语句指定了过滤条件为10,也就是非空值,可以走索引。
3,SELECT department_id FROM employees WHERE department_id IS NOT NULL; 返回所有非空值,所以直接全扫整个索引即可。