ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 10053事件

10053事件

原创 Oracle 作者：zzh_ds 时间：2016-12-20 19:51:17 0 删除 编辑

借助Oracle的10053事件event，我们可以监控到CBO对SQL进行成本计算和路径选择的过程和方法。

10053事件有两个级别：

Level 2：2级是1级的一个子集，它包含以下内容：

Column statistics

Single Access Paths

Join Costs

Table Joins Considered

Join Methods Considered (NL/MS/HA)

Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：

Parameters used by the optimizer

Index statistics

启用10053事件

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';

关闭10053事件：

ALTER SESSION SET EVENTS '10053 trace name context off';

说明：

1、sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。应该通过v$sql_plan查看SQL的真实的执行计划。

2、10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。

3、10053事件产生的trace文件不能用tkprof格式化。

---------通过10053事件分析一个SQL执行计划的产生过程，需要贴出trace中的相关信息和必要的文字说明
---创建两个测试表
  1. SQL> create table t1 as select * from all_objects;

  3. Table created.
  4.  
  5. SQL> select count(*) from t1;

  7.   COUNT(*)
  8. ----------
  9.      68323

  11. SQL> create table t2 as select * from all_objects where rownum<=100;

  13. Table created.

  15. SQL> select count(*) from t2;

  17.   COUNT(*)
  18. ----------
  19.        100

  21. SQL>
----对T1、T2表进行分析、不生成直方图

  1. SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1');

  3. PL/SQL procedure successfully completed.

  5. SQL> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1');

  7. PL/SQL procedure successfully completed.

  9. SQL>
----生成trace文件

  1. SQL> alter session set events '10053 trace name context forever ,level 1';

  3. Session altered.

  5. SQL> select count(*) from scott.t1,scott.t2 where t1.object_id=t2.object_id;

  7.   COUNT(*)
  8. ----------
  9.        100

  11. SQL>
  12. SQL> alter session set events '10053 trace name context off';

  14. Session altered.

  16. SQL>
  17. SQL> select value from v$diag_info where name like '%Default%';

  19. VALUE
  20. --------------------------------------------------------------------------------
  21. /u01/app/oracle/diag/rdbms/ora/ORA/trace/ORA_ora_12822.trc

  23. SQL>
-----查看trace文件

  1. [oracle@ds ~]$ more /u01/app/oracle/diag/rdbms/ora/ORA/trace/ORA_ora_12822.trc
  2. Trace file /u01/app/oracle/diag/rdbms/ora/ORA/trace/ORA_ora_12822.trc
  3. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  4. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  5. ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
  6. ·············································································
  7. Peeked values of the binds in SQL statement
  8. *******************************************

  10. Final query after transformations:******* UNPARSED QUERY IS *******
  11. SELECT COUNT(*) "COUNT(*)" FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2" WHERE "T1"."OB
  12. JECT_ID"="T2"."OBJECT_ID"
  13. kkoqbc: optimizing query block SEL$1 (#0)
  14.         
  15.         :
  16.     call(in-use=1928, alloc=16344), compile(in-use=58824, alloc=62776), execution(
  17. in-use=3296, alloc=4032)

  19. kkoqbc-subheap (create addr=0x7f11c504fa10)
  20. ****************
  21. QUERY BLOCK TEXT
  22. ****************
  23. select count(*) from scott.t1,scott.t2 where t1.object_id=t2.object_id
  24. ---------------------
  25. QUERY BLOCK SIGNATURE
  26. ---------------------
  27. signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
  28.   fro(0): flg=0 objn=88661 hint_alias="T1"@"SEL$1"
  29.   fro(1): flg=0 objn=88662 hint_alias="T2"@"SEL$1"

  31. -----------------------------
  32. SYSTEM STATISTICS INFORMATION
  33. -----------------------------
  34.   Using NOWORKLOAD Stats
  35.   CPUSPEEDNW: 3074 millions instructions/sec (default is 100)
  36.   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  37.   IOSEEKTIM: 10 milliseconds (default is 10)
  38.   MBRC: NO VALUE blocks (default is 8)

  40. ***************************************
  41. BASE STATISTICAL INFORMATION
  42. ***********************
  43. Table Stats::
  44.   Table: T2 Alias: T2
  45.     #Rows: 100 #Blks: 5 AvgRowLen: 89.00 ChainCnt: 0.00
  46.   Column (#4): OBJECT_ID(
  47.     AvgLen: 4 NDV: 100 Nulls: 0 Density: 0.010000 Min: 100 Max: 1495
  48. ***********************
  49. Table Stats::
  50.   Table: T1 Alias: T1
  51.     #Rows: 68323 #Blks: 1011 AvgRowLen: 98.00 ChainCnt: 0.00
  52.   Column (#4): OBJECT_ID(
  53.     AvgLen: 5 NDV: 68323 Nulls: 0 Density: 0.000015 Min: 100 Max: 88661
  54. Access path analysis for T1
  55. ***************************************
  56. SINGLE TABLE ACCESS PATH
  57.   Single Table Cardinality Estimation for T1[T1]
  58.   Table: T1 Alias: T1
  59.     Card: Original: 68323.000000 Rounded: 68323 Computed: 68323.00 Non Adjusted
  60. : 68323.00
  61.   Access Path: TableScan
  62.     Cost: 276.58 Resp: 276.58 Degree: 0
  63.       Cost_io: 276.00 Cost_cpu: 21547606
  64.       Resp_io: 276.00 Resp_cpu: 21547606
  65.   Best:: AccessPath: TableScan
  66.          Cost: 276.58 Degree: 1 Resp: 276.58 Card: 68323.00 Bytes: 0

  68. Access path analysis for T2
  69. ***************************************
  70. SINGLE TABLE ACCESS PATH
  71.   Single Table Cardinality Estimation for T2[T2]
  72.   Table: T2 Alias: T2
  73.     Card: Original: 100.000000 Rounded: 100 Computed: 100.00 Non Adjusted: 100.
  74. 00
  75.   Access Path: TableScan
  76.     Cost: 3.00 Resp: 3.00 Degree: 0
  77.       Cost_io: 3.00 Cost_cpu: 56607
  78.       Resp_io: 3.00 Resp_cpu: 56607
  79.   Best:: AccessPath: TableScan
  80.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 100.00 Bytes: 0

  82. ***************************************


  85. OPTIMIZER STATISTICS AND COMPUTATIONS
  86. ***************************************
  87. GENERAL PLANS
  88. ***************************************
  89. Considering cardinality-based initial join order.
  90. Permutations for Starting Table :0
  91. Join order[1]: T2[T2]#0 T1[T1]#1

  93. ***************
  94. Now joining: T1[T1]#1
  95. ***************
  96. NL Join
  97.   Outer table: Card: 100.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 4
  98. Access path analysis for T1
  99.   Inner table: T1 Alias: T1
  100.   Access Path: TableScan
  101.     NL Join: Cost: 27444.41 Resp: 27444.41 Degree: 1
  102.       Cost_io: 27386.00 Cost_cpu: 2154817191
  103.       Resp_io: 27386.00 Resp_cpu: 2154817191

  105.   Best NL cost: 27444.41
  106.           resc: 27444.41 resc_io: 27386.00 resc_cpu: 2154817191
  107.           resp: 27444.41 resp_io: 27386.00 resc_cpu: 2154817191
  108. Join Card: 100.000000 = outer (100.000000) * inner (68323.000000) * sel (0.000015
  109. )
  110. Join Card - Rounded: 100 Computed: 100.00
  111.   Outer table: T2 Alias: T2
  112.     resc: 3.00 card 100.00 bytes: 4 deg: 1 resp: 3.00
  113.   Inner table: T1 Alias: T1
  114.     resc: 276.58 card: 68323.00 bytes: 5 deg: 1 resp: 276.58
  115.     using dmeth: 2 #groups: 1
  116.     SORT ressource Sort statistics
  117.       Sort width: 430 Area size: 352256 Max Area size: 75497472
  118.       Degree: 1
  119.       Blocks to Sort: 1 Row size: 15 Total Rows: 100
  120.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  121.       Total IO sort cost: 0 Total CPU sort cost: 36918822
  122.       Total Temp space used: 0
  123.     SORT ressource Sort statistics
  124.       Sort width: 430 Area size: 352256 Max Area size: 75497472
  125.       Degree: 1
  126.       Blocks to Sort: 134 Row size: 16 Total Rows: 68323
  127.       Initial runs: 2 Merge passes: 1 IO Cost / pass: 74
  128.       Total IO sort cost: 208 Total CPU sort cost: 89625937
  129.       Total Temp space used: 1663000
  130.   SM join: Resc: 491.02 Resp: 491.02 [multiMatchCost=0.00]
  131. SM Join
  132.   SM cost: 491.02
  133.      resc: 491.02 resc_io: 487.00 resc_cpu: 148148972
  134.      resp: 491.02 resp_io: 487.00 resp_cpu: 148148972
  135.   Outer table: T2 Alias: T2
  136.     resc: 3.00 card 100.00 bytes: 4 deg: 1 resp: 3.00
  137.   Inner table: T1 Alias: T1
  138.     resc: 276.58 card: 68323.00 bytes: 5 deg: 1 resp: 276.58
  139.     using dmeth: 2 #groups: 1
  140.     Cost per ptn: 0.20 #ptns: 1
  141.     hash_area: 124 (max=18432) buildfrag: 1 probefrag: 142 ppasses: 1
  142.   Hash join: Resc: 279.79 Resp: 279.79 [multiMatchCost=0.00]
  143. HA Join
  144.   HA cost: 279.79
  145.      resc: 279.79 resc_io: 279.00 resc_cpu: 29051513
  146.      resp: 279.79 resp_io: 279.00 resp_cpu: 29051513
  147. Best:: JoinMethod: Hash
  148.        Cost: 279.79 Degree: 1 Resp: 279.79 Card: 100.00 Bytes: 9
  149. ***********************
  150. Best so far: Table#: 0 cost: 3.0015 card: 100.0000 bytes: 400
  151.               Table#: 1 cost: 279.7875 card: 100.0000 bytes: 900
  152. ***********************
  153. Join order[2]: T1[T1]#1 T2[T2]#0

  155. ***************
  156. Now joining: T2[T2]#0
  157. ***************
  158. NL Join
  159.   Outer table: Card: 68323.00 Cost: 276.58 Resp: 276.58 Degree: 1 Bytes: 5
  160. Access path analysis for T2
  161.   Inner table: T2 Alias: T2
  162.   Access Path: TableScan
  163.     NL Join: Cost: 92903.43 Resp: 92903.43 Degree: 1
  164.       Cost_io: 92798.00 Cost_cpu: 3889121331
  165.       Resp_io: 92798.00 Resp_cpu: 3889121331

  167.   Best NL cost: 92903.43
  168.           resc: 92903.43 resc_io: 92798.00 resc_cpu: 3889121331
  169.           resp: 92903.43 resp_io: 92798.00 resc_cpu: 3889121331
  170. Join Card: 100.000000 = outer (68323.000000) * inner (100.000000) * sel (0.000015
  171. )
  172. Join Card - Rounded: 100 Computed: 100.00
  173.   Outer table: T1 Alias: T1
  174.     resc: 276.58 card 68323.00 bytes: 5 deg: 1 resp: 276.58
  175.   Inner table: T2 Alias: T2
  176.     resc: 3.00 card: 100.00 bytes: 4 deg: 1 resp: 3.00
  177.     using dmeth: 2 #groups: 1
  178.     SORT ressource Sort statistics
  179.       Sort width: 430 Area size: 352256 Max Area size: 75497472
  180.       Degree: 1
  181.       Blocks to Sort: 134 Row size: 16 Total Rows: 68323
  182.       Initial runs: 2 Merge passes: 1 IO Cost / pass: 74
  183.       Total IO sort cost: 208 Total CPU sort cost: 89625937
  184.       Total Temp space used: 1663000
  185.     SORT ressource Sort statistics
  186.       Sort width: 430 Area size: 352256 Max Area size: 75497472
  187.       Degree: 1
  188.       Blocks to Sort: 1 Row size: 15 Total Rows: 100
  189.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  190.       Total IO sort cost: 0 Total CPU sort cost: 36918822
  191.       Total Temp space used: 0
  192.   SM join: Resc: 491.02 Resp: 491.02 [multiMatchCost=0.00]
  193. SM Join
  194.   SM cost: 491.02
  195.      resc: 491.02 resc_io: 487.00 resc_cpu: 148148972
  196.      resp: 491.02 resp_io: 487.00 resp_cpu: 148148972
  197.   Outer table: T1 Alias: T1
  198.     resc: 276.58 card 68323.00 bytes: 5 deg: 1 resp: 276.58
  199.   Inner table: T2 Alias: T2
  200.     resc: 3.00 card: 100.00 bytes: 4 deg: 1 resp: 3.00
  201.     using dmeth: 2 #groups: 1
  202.     Cost per ptn: 56.31 #ptns: 1
  203.     hash_area: 124 (max=18432) buildfrag: 142 probefrag: 1 ppasses: 1
  204.   Hash join: Resc: 335.90 Resp: 335.90 [multiMatchCost=0.00]
  205.   Outer table: T2 Alias: T2
  206.     resc: 3.00 card 100.00 bytes: 4 deg: 1 resp: 3.00
  207.   Inner table: T1 Alias: T1
  208.     resc: 276.58 card: 68323.00 bytes: 5 deg: 1 resp: 276.58
  209.     using dmeth: 2 #groups: 1
  210.     Cost per ptn: 0.20 #ptns: 1
  211.     hash_area: 124 (max=18432) buildfrag: 1 probefrag: 142 ppasses: 1
  212.   Hash join: Resc: 279.79 Resp: 279.79 [multiMatchCost=0.00]
  213. HA Join
  214.   HA cost: 279.79 swapped
  215.      resc: 279.79 resc_io: 279.00 resc_cpu: 29051513
  216.      resp: 279.79 resp_io: 279.00 resp_cpu: 29051513
  217. Join order aborted: cost > best plan cost
  218. ***********************
  219. (newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000

  221. *********************************
  222. Number of join permutations tried: 2
  223. *********************************
  224. Consider using bloom filter between T2[T2] and T1[T1] with ??
  225. kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because n
  226. o single-tables predicates
  227. Enumerating distribution method (advanced)
  228. --- Distribution method for
  229. join between T2[T2](serial) and T1[T1](serial); jm = 1; right side access path = T
  230. ableScan
  231. ---- serial Hash-Join -> NONE

  233. (newjo-save) [0 1 ]
  234. Trying or-Expansion on query block SEL$1 (#0)
  235. Transfer Optimizer annotations for query block SEL$1 (#0)
  236. id=0 frofkksm[i] (sort-merge/hash) predicate="T1"."OBJECT_ID"="T2"."OBJECT_ID"
  237. id=0 frosand (sort-merge/hash) predicate="T1"."OBJECT_ID"="T2"."OBJECT_ID"
  238. Final cost for query block SEL$1 (#0) - All Rows Plan:
  239.   Best join order: 1
  240.   Cost: 279.7875 Degree: 1 Card: 100.0000 Bytes: 900
  241.   Resc: 279.7875 Resc_io: 279.0000 Resc_cpu: 29051513
  242.   Resp: 279.7875 Resp_io: 279.0000 Resc_cpu: 29051513
  243. kkoqbc-subheap (delete addr=0x7f11c504fa10, in-use=36144, alloc=49272)
  244. kkoqbc-end:
  245.         :
  246.     call(in-use=10704, alloc=82128), compile(in-use=62376, alloc=62776), execution
  247. (in-use=3296, alloc=4032)

  249. kkoqbc: finish optimizing query block SEL$1 (#0)
  250. apadrv-end
  251.           :
  252.     call(in-use=10704, alloc=82128), compile(in-use=63376, alloc=66920), execution
  253. (in-use=3296, alloc=4032)


  256. Starting SQL statement dump

  258. user_id=0 user_name=SYS module=sqlplus@ds (TNS V1-V3) action=
  259. sql_id=aqkx0jysz4fr9 plan_hash_value=-20910549 problem_type=3
  260. ----- Current SQL Statement for this session (sql_id=aqkx0jysz4fr9) -----
  261. select count(*) from scott.t1,scott.t2 where t1.object_id=t2.object_id
  262. sql_text_length=71
  263. sql=select count(*) from scott.t1,scott.t2 where t1.object_id=t2.object_id
  264. ----- Explain Plan Dump -----
  265. ----- Plan Table -----
  266.  
  267. ============
  268. Plan Table
  269. ============
  270. ---------------------------------------+-----------------------------------+
  271. | Id | Operation | Name | Rows | Bytes | Cost | Time |
  272. ---------------------------------------+-----------------------------------+
  273. | 0 | SELECT STATEMENT | | | | 280 | |
  274. | 1 | SORT AGGREGATE | | 1 | 9 | | |
  275. | 2 | HASH JOIN | | 100 | 900 | 280 | 00:00:04 |
  276. | 3 | TABLE ACCESS FULL | T2 | 100 | 400 | 3 | 00:00:01 |
  277. | 4 | TABLE ACCESS FULL | T1 | 67K | 334K | 277 | 00:00:04 |
  278. ---------------------------------------+-----------------------------------+
  279. Predicate Information:
  280. ----------------------
  281. 2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  282.  
  283. Content of other_xml column
  284. ===========================
  285.   db_version : 11.2.0.4
  286.   parse_schema : SYS
  287.   plan_hash : 4274056747
  288. --More--(60%)






来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31399171/viewspace-2131066/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： RMAN的完全备份
下一篇： SQL_TRACE文件
请登录后发表评论 登录
全部评论
zzh_ds

注册时间：2016-09-29

  • 博文量
    61
  • 访问量
    47810

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593