Oracle数据库全表扫描详解(一)

作者简介:惠星星,现就职于北京海天起点,持有OCP 10g、OCP 11g、OCM 11g证书,并有长达8年电力行业业务维护、数据库维护服务经验,擅长Oracle数据库性能优化、故障处理及数据可视化技术研究。

概念介绍

Oracle全表扫描操作会读取表中的所有行,并过滤掉不符合查询条件的行。全表扫描只查询高水位以下的数据块。全表扫描操作Oracle使用多块读提高查询效率,多块读的效率由DB_FILE_MULTIBLOCK_READ_COUNT参数决定,数据库在以下情况使用全表扫描:

  • 缺乏索引:无索引、查询键使用函数。
  • 大量数据:查询表中大部分列
  • 小表查询:块小于单次多块读
  • Hint提示:使用全表扫描提示
  • 统计信息:统计信息不准时

疑惑不解

问题1: Oracle进行全表扫描时会进行哪些操作,具体有哪些IO(物理IO、逻辑IO)?

问题2:全表扫描查询,有哪些优化方法

拨开云雾

环境准备

–创建表空间

–创建用户

–创建测试表

–插入数据

–收集统计信息

–查询extent信息

–查询段头块

–修改多块读参数

执行计划

说明:

操作TABLE ACCESS FULL说明该事件为全表扫描,物理读15次,逻辑读38次,autotrace方式获取执行计划是根据数据字典估算值,我们能否通过操作系统工具监测实际物理IO?

查看物理IO

计划使用strace工具和Oracle10046事件进行分析,strace主要分析物理IO次数和性能,10046主要分析具体数据库内部操作,

说明:

返回值:成功,返回成功读取数据的字节数;失败,返回-1;

参数:

(1) fd:要读取数据的文件描述符

(2) buf:数据缓存区指针,存放读取出来的数据

(3) count:读取数据的字节数

(4) offset:读取的起始地址的偏移量,读取地址=文件开始+offset。注意,执行后,文件偏移指针不变

查看文件描述符对象

小结:

1、256指向system表空间、258指向数据表空间,步骤回溯如下,

步骤1-9、system表空间物理读取9次,获取8192字节,1个数据库块

步骤10、数据表空间物理读取1次,获取8192字节,1个数据库块

步骤11、数据表空间物理读取1次,获取32768字节,4个数据库块,一次多块读,db_file_multiblock_read_count设置为4。

步骤12、table表空间物理读取1次,获取8192字节,1个数据库块

2、autot和strace监测值一致

查看操作

使用10046事件分析具体操作,

窗口一:

窗口二:

云开月明

详细分析10046中的每步操作,

步骤1:obj$查询(物理IO:1,逻辑IO:4)

步骤2:tab$查询(物理IO:2,逻辑IO:4)

步骤3:ind$查询(物理IO:0,逻辑IO:3)

步骤4:col$查询(物理IO:0,逻辑IO:4)

步骤5:seg$查询(物理IO:2,逻辑IO:3)

步骤6:objauth$查询(物理IO:1,逻辑IO:2)

步骤7:objauth$查询(物理IO:0,逻辑IO:2)

步骤8:cdef$查询(物理IO:1,逻辑IO:2)

步骤9:cdef$查询(物理IO:1,逻辑IO:2)

步骤10:hist_head$查询(物理IO:1,逻辑IO:6)

步骤11:数据查询(物理IO:6,逻辑IO:7)

经验小结

根据数据库SQL执行过程(Parse/ Execute/ Fetch),以IO访问为监测方式,将数据库全表扫描可以分为以下12步,

步骤1:obj$查询(物理IO:1,逻辑IO:4) 执行阶段:parse

步骤2:tab$查询(物理IO:2,逻辑IO:4) 执行阶段:parse

步骤3:ind$查询(物理IO:0,逻辑IO:3) 执行阶段:parse

步骤4:col$查询(物理IO:0,逻辑IO:4) 执行阶段:parse

步骤5:seg$查询(物理IO:2,逻辑IO:3) 执行阶段:parse

步骤6:objauth$查询(物理IO:1,逻辑IO:2) 执行阶段:parse

步骤7:objauth$查询(物理IO:0,逻辑IO:2) 执行阶段:parse

步骤8:cdef$查询(物理IO:1,逻辑IO:2) 执行阶段:parse

步骤9:cdef$查询(物理IO:1,逻辑IO:2) 执行阶段:parse

步骤10:hist_head$查询(物理IO:1,逻辑IO:6) 执行阶段:parse

步骤11:Execute 执行阶段:Execute

步骤12:数据查询(物理IO:6,逻辑IO:7) 执行阶段:Fetch

从以下分析可以得出以下结论:

    1. 绑定变量软解释的重要性,可以节省前10步parse过程。
    2. 大表查询时Fetch的耗时占比会较大,因此大表查询全表扫描时,尽量使用多块读,提高查询效率。

问题来了

问题1: Oracle进行全表扫描时会进行哪些操作,具体有哪些IO(物理IO、逻辑IO)?(已解决)

问题2:、10046 中数据查询(物理IO:6), strace监测中物理IO为3,为啥是6次或3次物理IO,每步做了哪些操作?

问题3:单块读和多块读性能差别多少?如果单块读耗时1us,单次多块(16数据块)读耗时16us,多块读是否能提升性能?

问题4:全表扫描查询,有哪些优化方法?

以上问题待下节分析。

未经允许不得转载:Oracle一体机用户组 » Oracle数据库全表扫描详解(一)

相关推荐