探索索引的奥秘 – 10053事件

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 探索索引的奥秘 – 10053事件

探索索引的奥秘 - 10053事件

之前我们了解了索引的属性,以及一些对于是否能用索引似是而非的场景,相应的说明和结论可以参考,

《》

《》

对于一条SQL,是否可以用索引,在CBO下,是依赖于Oracle对于不同执行计划成本值预估的判断,下面这张图是Concept描述的优化器,

探索索引的奥秘 - 10053事件

优化器的输入,是通过解析器处理的语句,在优化器中,会执行以下操作,

  1. 基于可用的访问路径,以及hints,优化器会创建一组SQL执行计划。
  1. 基于数据字典中的统计信息,优化器会预估计算每一个执行计划对应的成本值。当然这是根据需要消耗的资源得到的一个预估值。

  2. 优化器比较执行计划成本,然后选择一个成本最低的,作为query plan,这是优化器的输出,进入下一个阶段。

这么一看,一条SQL是否高效,换句话说,这条SQL的执行计划,成本值是否是最低的,就和优化器,息息相关了。

对于关系型数据库来说,优化器这个组件的优劣,一定意义上,决定了这款产品的优劣,优化器的实现,算是Oracle的商业机密,但Oracle可以说是最“开源”的“闭源”,因为他提供了一些方法,可以让我们了解为了创建一条执行计划而使用的一些信息,这个方法就是10053事件,他会详细描述CBO模式的优化器在评估执行计划并进行选择时的信息。

通过10053事件的trace,可以看见所有执行计划,以及相应的成本,进而可以知道为什么Oracle选择了这个索引或者为什么没用这索引。因此当我们碰见SQL性能问题,或者怀疑执行计划是否正确,可以执行一下10053,了解选择的背后,也许可以得到一些提示。

要得到10053的trace,针对不同的场景,有两种方法,

场景一:
在当前的session,创建10053事件trace,可以使用
alter session set events ‘10053 trace name context forever[, level {1|2}]’
关闭10053事件trace,可以使用
alter session set events ‘10053 trace name context off’

场景二:

创建另一个session的10053事件trace,可以使用 sys.dbms_system.set_ev (sid, serial#, 10053, {1|2}, '') 关闭10053事件trace,可以使用 sys.dbms_system.set_ev (sid, serial#, 10053, 0, '')

针对场景二,session的sid和serial#可以使用如下SQL,

SQL select sid, serial# from v$session where username=USER and status='ACTIVE'; SID    SERIAL# ------ ---------- 30      201

sid和serial#含义如下,

SID:

Session identifier

SERIAL#: Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.

还有一些关于10053事件的细节,

(1) 10053事件有两个级别,1和2,1比2要详细。
(2) 10053事件的trace会写入user_dump_dest定义的路径。
(3) 只有使用CBO优化器的时候,才可以产生10053的trace,否则(使用RBO)trace文件只会包含SQL语句。

(4) SQL使用RBO解析的三个原因
(a) optimizer_mode或者optimizer_goal设置为rule。其中optimizer_goal是11g废弃的参数,另外PLSQL Developer的界面中,会有这参数。

探索索引的奥秘 - 10053事件

(b) 语句使用了rule hint。
(c) 语句中任何表没被分析过,并且语句中不包含任何hint。

创建10053事件,

session A执行:

SQL select sid, serial# from v$session where username=USER and status='ACTIVE'; SID    SERIAL#

------ ---------- 30      201

session B执行:

SQL exec sys.dbms_system.set_ev(30, 201, 10053, 1, ''); PL/SQL procedure successfully completed.

session A执行:

SQL explain plan for select * from test where id = 1; Explained.

session B执行:

SQL exec sys.dbms_system.set_ev(30, 201, 10053, 0, ''); PL/SQL procedure successfully completed.

进入user_dump_dest定义的路径,可以看见,10053的trace文件,

BISAL_ora_74755.trc

打开文件,我们来看一些主要的信息,

第一部分,描述了可能影响执行计划选择的一系列参数值,简单讲会影响CBO的参数,有一些是默认值,有一些是改了的,

*************************************** PARAMETERS USED BY THE OPTIMIZER ********************************************************************* PARAMETERS WITH ALTERED VALUES ****************************** Compilation Environment Dump _smm_min_size                       = 176 KB optimizer_use_invisible_indexes     = true Bug Fix Control Environment ************************************* PARAMETERS WITH DEFAULT VALUES ****************************** Compilation Environment Dump optimizer_mode_hinted               = false optimizer_features_hinted           = 0.0.0 parallel_execution_enabled          = true ...

这部分显示了,经过transformation的语句,来作为优化器的输入,可以看出,将原语句的select *解析为了,具体的字段信息,并且表名前,增加了属主信息,

Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "TEST"."ID" "ID","TEST"."NAME" "NAME" FROM "BISAL"."TEST" "TEST" WHERE "TEST"."ID"=1 kkoqbc: optimizing query block SEL$1 (#0)         :     call(in-use=5456, alloc=16344), compile(in-use=64880, alloc=68488), execution(in-use=93848, alloc=97560) kkoqbc-subheap (create addr=0x7fb7c28fea40) **************** QUERY BLOCK TEXT **************** select * from test where id = 1

接下来是基本的统计信息,包括表以及所有的索引,

探索索引的奥秘 - 10053事件

其中的字段,可以和视图dba_tables进行对应,例如表行数为10000,有20个数据块,平均行长为8字节,

trace中的名词dba_tables中的字段解释 #Rows/CDNNUM_ROWSThe cardinality = number of rows of the table #Blks/NBLKSBLOCKSThe number of blocks below the high water mark AvgRowLen/AVG_ROW_LENAVG_ROW_LENThe average length of a row

还有些字段,可以和视图dba_indexes进行对应,例如索引IDX_TEST_01的层级为1,叶子块数量为21,索引distinct值为10000,每个键值的平均叶子块数为1,每个键值的平均数据块数为1,索引聚簇因子为16,

trace中的名词dba_indexes中的字段解释 Index#, col#The object# of the index and the column_id of the columns. Oracle 9 brings an improvement by using the index name rather than index# LVLSBLEVELThe height of the index b-tree #LBLEAF_BLOCKSThe number of leaf blocks #DKDISTINCT_KEYSThe number of distinct keys of the index LB/KAVG_LEAF_BLOCKS_PER_KEYThe average number of leaf blocks per key DB/KAVG_DATA_BLOCKS_PER_KEYThe average number of data blocks per key CLUFCLUSTERING_FACTORThe clustering factor of the index

SINGLE TABLE ACCESS PATH部分,是优化器评估如何访问TEST表,注意这只会显示谓词中出现的列对应的统计信息,

探索索引的奥秘 - 10053事件

其中一些字段,可以和视图dba_tab_columns进行对应,

trace中的名词dba_tab_columns中的字段解释 NDVNUM_DISTINCTNumber of distinct values for the column NULLSNUM_NULLSNumber of rows with a null “value” for the column DENSITYDENSITY“Density” of the column. Without histogram this is = 1/NDV

MIN

LOW_VALUEThe lowest value for the column (only for numeric columns)

MAX

HIGH_VALUEThe highest value for the column (only for numeric columns)

首先给出了表的基本信息,

Table: TEST  Alias: TEST     Card: Original: 10000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00

全表扫描的成本如下,

Access Path: TableScan     Cost:  7.17  Resp: 7.17  Degree: 0       Cost_io: 7.00  Cost_cpu: 2142450       Resp_io: 7.00  Resp_cpu: 2142450

Resp表示使用并行查询的成本。

索引扫描的成本如下,

Access Path: index (AllEqRange)     Index: IDX_TEST_01     resc_io: 2.00  resc_cpu: 15463     ix_sel: 0.000100  ix_sel_with_filters: 0.000100     Cost: 2.00  Resp: 2.00  Degree: 1

根据成本值,得出使用IndexRange的执行计划,是最佳选择,

Best:: AccessPath: IndexRange   Index: IDX_TEST_01          Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0

下面就会显示执行计划,其中Cost就是之前Oracle计算出来的,

探索索引的奥秘 - 10053事件

还会有一些辅助信息,包括Outline数据,

探索索引的奥秘 - 10053事件

问题来了,我们有了这个10053的trace,对于排查SQL问题,有什么帮助?

10053的trace中会显示CBO计算每一种执行计划需要用到的各种参数信息,例如表数据量、索引聚簇因子、是否开启并行、甚至使用的一些系统参数,就为我们排查SQL性能问题,提供了线索,比如原表应有100万条记录,虽然检索字段有索引,但trace中显示表记录数只有1000条,就有可能不会用索引,我们看一下Oracle计算的执行计划成本值有可能就会发现些问题,再比如两表连接,两张大表关联应该用Hash Join,但实际选择的是Nest Loop,为何选择了前者,通过10053事件,会给我们些提示。

总结:

  1. 我们可以通过10053事件的trace,了解CBO模式下执行计划的选择,进而辅助我们来了解,SQL执行计划的选择正确与否。

  2. 无论当前session,还是另一个session,都可以创建10053事件的trace文件,只是语法不同。

参考文献:

《A LOOK UNDER THE HOOD OF CBO: THE 10053 EVENT》

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:63445044804318》

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

探索索引的奥秘 - 10053事件 探索索引的奥秘 - 10053事件
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 探索索引的奥秘 – 10053事件


 上一篇
filebeat占用Linux空间未释放的问题解决 filebeat占用Linux空间未释放的问题解决
我们的一台应用服务器,操作系统是Red Hat Linux,监控报警,/opt/applog文件系统使用率超阈值,整体容量为50G,但发现实际文件容量20G,剩下的30G空间是什么? 我们知道,Linux环境下,任何事物,都是以文
下一篇 
Windows环境安装MySQL ZIP Archive Windows环境安装MySQL ZIP Archive
这是之前写的几篇关于Linux环境下安装MySQL数据库的文章,包括5.7.19以及5.6这两个版本, 《》 《》 《》 今儿碰巧需要部署一个Windows版本,于是先下载了最新的,5.7.20版本安装包,选择了Windows平台