通过10046 event来获取真实的执行计划
获取SQL执行计划的方式有很多,但是某些时候获取的SQL执行计划并不是准确的,只有在SQL真实执行之后获取到的SQL PLAN才是真实准确的,其他方式(如,explain plan)获取到的执行计划都有可能因为绑定绑定变量和当时SQL执行环境的影响而导致获取到的执行计划可能不准确。对于AUTOTRACE开关,当执行SET AUTOT ON
和SET AUTOT TRACE
命令此时的SQL是实际执行过的,所以此时获取到的执行计划是准确的。而SET AUTOT TRACE EXP
命令时稍有不同,当执行的是SELECT语句时SQL语句并不会实际执行,此时获取到的执行计划和直接使用explain plan
命令获取的结果是一致的,但是对于DML命令则会实际执行该SQL。 下面看一个例子,说明SET AUTOT TRACE EXP
并不会真实的执行SELECT语句,以此为例来介绍如何确定SQL是否真实执行。 首先,我们直接执行SQL,可以在V$SQLAREA
里看到EXECUTIONS的值为1。
SQL> SELECT PCT_USED,PCT_FREE FROM TEST_ENV.TB_TABLE_LIST WHERE TABLE_NAME='DBMS_SQLPATCH_STATE'; PCT_USED PCT_FREE ---------- ---------- 40 10 SQL> SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT PCT_USED,PCT_FREE FROM TEST_%'; EXECUTIONS ---------- 1 SQL> ALTER SYSTEM FLUSH SHARED_POOL;--该操作为了方便后面查询,生产环境勿执行 系统已更改。
打开SET AUTOT TRACE EXP
,后重复上述SQL,可以看到EXECUTIONS的值为0,表明SQL并没有真实执行。
SQL> SET AUTOT TRACE EXP SQL> SELECT PCT_USED,PCT_FREE FROM TEST_ENV.TB_TABLE_LIST WHERE TABLE_NAME='DBMS_SQLPATCH_STATE'; 执行计划 ---------------------------------------------------------- Plan hash value: 3473397811 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 23 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST | 1 | 23 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TB_TABLE_LIST_TBNAME | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TABLE_NAME"='DBMS_SQLPATCH_STATE') SQL> SET AUTOTRACE OFF SQL> SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT PCT_USED,PCT_FREE FROM TEST_%'; EXECUTIONS ---------- 0
另一种获取SQL真实执行计划的方式是通过10046事件,通过这种方式我们可以看到更细粒度的结果展示,如每一个步骤所执行的逻辑读、物理读和花费的时间等,这对于某些复杂SQL诊断时会比较有用。
什么是10046事件?
10046 event是ORACLE提供的一个性能分析工具,可以用来跟踪某一个session所执行的SQL语句或者或者PL/SQL语句各个阶段中遇到的等待事件、消耗的逻辑读、物理读、消耗的时间、执行计划等。 10046 event有以下9个有效跟踪级别:
- level 1 启用标准SQL_TRACE的功能,包含时间消耗、处理的行数,物理读和写的数、执行计划等
- level 4 在level 1 的基础上多了变量绑定
- level 8 在level 1 的基础上多了等待事件
- level 12 即level 4+level 8
- level 16 level 1的基础上为每一次执行生成执行计划信息(11.1之后的版本才有)
- level 28 level 4+level 8+level 16
- level 32 比level 1少了执行计划信息(11.1之后的版本才有)
- level 64 在level 1的基础上,如果某游标比上次执行的时间多消耗了1min以上则还会生成执行计划信息(11.2之后的版本才有)
- level 68 即leve4 + level 64
如何使用?
通过以下三个步骤我们可以通过10046 event来获取执行计划:
- 在当前session激活10046 event,有以下两种方式:
alter session set events '10046 trace name context forever,level 12' oradebug setmypid oradebug event 10046 trace name context forever,level 12
- 执行目标SQL
- 在当前session关闭10046 event:
alter session set events '10046 trace name context off' oradebug event 10046 trace name context off
推荐使用oradebug的方式,这样可以通过oradebug tracefile_name来获取tracefile的绝对路径。对于直接获取到的tracefile不直观,看起来非常吃力,可以通过ORACLE提供的tkprof来对得到的trace文件进行翻译
案例操作
SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10046 trace name context forever,level 12 已处理的语句 SQL> select a.table_name,a.pct_used,b.column_name from test_env.tb_table_list a,test_env.tb_column_list b where a.owner=b.owner and a.table_name=b.table_name and b.table_name='DBMS_SQLPATCH_STATE'; . . . . DBMS_SQLPATCH_STATE 40 S_CACHED_LSINVENTORY 已选择 27 行。 SQL> oradebug tracefile_name C:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3016.trc SQL> oradebug event 10046 trace name context off 已处理的语句 SQL> C:\Users\Administrator>tkprof C:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2548.trc C:\explain_orcl_ora_2548.txt TKPROF: Release 18.0.0.0.0 - Development on 星期五 4月 5 16:05:18 2019 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
翻译后的结果如下:
******************************************************************************** select a.table_name,a.pct_used,b.column_name from test_env.tb_table_list a,test_env.tb_column_list b where a.owner=b.owner and a.table_name=b.table_name and b.table_name='DBMS_SQLPATCH_STATE' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.01 0.07 856 863 0 27 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.03 0.08 856 863 0 27 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 27 27 27 HASH JOIN (cr=863 pr=856 pw=0 time=78538 us starts=1 cost=246 size=63 card=1) 1 1 1 TABLE ACCESS FULL TB_TABLE_LIST (cr=101 pr=99 pw=0 time=23646 us starts=1 cost=31 size=26 card=1) 27 27 27 TABLE ACCESS FULL TB_COLUMN_LIST (cr=762 pr=757 pw=0 time=42700 us starts=1 cost=214 size=444 card=12) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ Disk file operations I/O 2 0.00 0.00 SQL*Net message to client 3 0.00 0.00 db file sequential read 2 0.01 0.01 db file scattered read 36 0.01 0.04 SQL*Net message from client 3 6.98 7.06 PGA memory operation 1 0.00 0.00 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.01 0.07 856 863 0 27 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.03 0.08 856 863 0 27 Misses in library cache during parse: 1 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 6 0.00 0.00 SQL*Net message from client 6 80.00 104.95 db file sequential read 2 0.01 0.01 PGA memory operation 1 0.00 0.00 Disk file operations I/O 5 0.00 0.00 db file scattered read 36 0.01 0.04 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.01 0.00 0 0 0 0 Execute 124 0.01 0.04 0 0 0 0 Fetch 233 0.01 0.04 10 399 0 422 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 359 0.04 0.08 10 399 0 422 Misses in library cache during parse: 2 Misses in library cache during execute: 15 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 10 0.00 0.04 PGA memory operation 7 0.00 0.00 Disk file operations I/O 1 0.00 0.00 1 user SQL statements in session. 19 internal SQL statements in session. 20 SQL statements in session. ******************************************************************************** Trace file: C:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2548.trc Trace file compatibility: 12.2.0.0 Sort options: default 1 session in tracefile. 1 user SQL statements in trace file. 19 internal SQL statements in trace file. 20 SQL statements in trace file. 20 unique SQL statements in trace file. 2176 lines in trace file. 0 elapsed seconds in trace file.
从结果中可以看到SQL的执行计划、每个阶段(Parse、Execute、Fetch)cpu时间返回的行数、所涉及等待事件、row source操作的具体情况等详细的信息。当我们通过AUTOTRACE
以及DBMS_XPLAN
方式无法确定SQL具体性能问题时,10046可以让我们获取到更多详细信息来定位相关性能问题。

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
死磕 java集合之ConcurrentLinkedQueue源码分析
问题 (1)ConcurrentLinkedQueue是阻塞队列吗? (2)ConcurrentLinkedQueue如何保证并发安全? (3)ConcurrentLinkedQueue能用于线程池吗? 简介 ConcurrentLinkedQueue只实现了Queue接口,并没有实现BlockingQueue接口,所以它不是阻塞队列,也不能用于线程池中,但是它是线程安全的,可用于多线程环境中。 那么,它的线程安全又是如何实现的呢?让我们一起来瞧一瞧。 源码分析 主要属性 // 链表头节点 private transient volatile Node<E> head; // 链表尾节点 private transient volatile Node<E> tail; 就这两个主要属性,一个头节点,一个尾节点。 主要内部类 private static class Node<E> { volatile E item; volatile Node<E> next; } 典型的单链表结构,非常纯粹。 主要构造方法 public Concurr...
- 下一篇
Fabric Kafka入门
Hyperledger Fabric推荐Kafa用于生产环境。Kafa是一个分布式、具有水平伸缩能力、崩溃容错能力的日志系统。在Hyperledger Fabric区块链中可以有多个Kafka节点,使用zookeeper进行同步管理。本文将介绍Kfaka的基本工作原理,以及在HyperledgerFabric中使用Kafka和zookeeper实现共识的原理,并通过一个实例剖析Hyperledger Farbic中Kafka共识的达成过程。 如果希望快速掌握Fabric区块链的链码及应用开发,建议访问汇智网的在线互动课程: Fabric区块链Java开发详解 Fabric区块链NodeJs开发详解 一、Kafka工作原理 Kafka本质上是一个消息处理系统,它使用的是经典的发布-订阅模型。消息的消费者订阅特定的主题,以便收到新消息的通知,生产者则负责消息的发布。 当主题的数据规模变得越来越大时,可以拆分为多个分区,Kafka保障在一个分区内的消息是按顺序排列的。 Kafka并不跟踪消费者读取了哪些消息,也不会自动删除已经读取的消息。Kafka会保存消息一段时间,例如一天,或者直到数据规...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- CentOS8安装MyCat,轻松搞定数据库的读写分离、垂直分库、水平分库
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长
- SpringBoot2全家桶,快速入门学习开发网站教程
- CentOS7设置SWAP分区,小内存服务器的救世主
- CentOS7安装Docker,走上虚拟化容器引擎之路
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- CentOS8安装Docker,最新的服务器搭配容器使用
- SpringBoot2初体验,简单认识spring boot2并且搭建基础工程
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题