10053事件

借助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;
   
2. 
   
3. Table created.
   
4.  
   
5. SQL> select count(*) from t1;
   
6. 
   
7.   COUNT(*)
   
8. ----------
   
9.      68323
   
10. 
    
11. SQL> create table t2 as select * from all_objects where rownum<=100;
    
12. 
    
13. Table created.
    
14. 
    
15. SQL> select count(*) from t2;
    
16. 
    
17.   COUNT(*)
    
18. ----------
    
19.        100
    
20. 
    
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');
   
2. 
   
3. PL/SQL procedure successfully completed.
   
4. 
   
5. SQL> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1');
   
6. 
   
7. PL/SQL procedure successfully completed.
   
8. 
   
9. SQL>

----生成trace文件

1. SQL> alter session set events '10053 trace name context forever ,level 1';
   
2. 
   
3. Session altered.
   
4. 
   
5. SQL> select count(*) from scott.t1,scott.t2 where t1.object_id=t2.object_id;
   
6. 
   
7.   COUNT(*)
   
8. ----------
   
9.        100
   
10. 
    
11. SQL>
    
12. SQL> alter session set events '10053 trace name context off';
    
13. 
    
14. Session altered.
    
15. 
    
16. SQL>
    
17. SQL> select value from v$diag_info where name like '%Default%';
    
18. 
    
19. VALUE
    
20. --------------------------------------------------------------------------------
    
21. /u01/app/oracle/diag/rdbms/ora/ORA/trace/ORA_ora_12822.trc
    
22. 
    
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. *******************************************
   
9. 
   
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)
    
18. 
    
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"
    
30. 
    
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)
    
39. 
    
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
    
67. 
    
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
    
81. 
    
82. ***************************************
    
83. 
    
84. 
    
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
    
92. 
    
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
     
104. 
     
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
     
154. 
     
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
     
166. 
     
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
     
220. 
     
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
     
232. 
     
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)
     
248. 
     
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)
     
254. 
     
255. 
     
256. Starting SQL statement dump
     
257. 
     
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博客 ” ，链接：http://blog.itpub.net/31399171/viewspace-2131066/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31399171/viewspace-2131066/