借助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中的相关信息和必要的文字说明---创建两个测试表
- SQL> create table t1 as select * from all_objects;
-
- Table created.
-
- SQL> select count(*) from t1;
-
- COUNT(*)
- ----------
- 68323
-
- SQL> create table t2 as select * from all_objects where rownum<=100;
-
- Table created.
-
- SQL> select count(*) from t2;
-
- COUNT(*)
- ----------
- 100
-
- SQL>
- SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1');
-
- PL/SQL procedure successfully completed.
-
- SQL> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1');
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> alter session set events '10053 trace name context forever ,level 1';
-
- Session altered.
-
- SQL> select count(*) from scott.t1,scott.t2 where t1.object_id=t2.object_id;
-
- COUNT(*)
- ----------
- 100
-
- SQL>
- SQL> alter session set events '10053 trace name context off';
-
- Session altered.
-
- SQL>
- SQL> select value from v$diag_info where name like '%Default%';
-
- VALUE
- --------------------------------------------------------------------------------
- /u01/app/oracle/diag/rdbms/ora/ORA/trace/ORA_ora_12822.trc
-
- SQL>
- [oracle@ds ~]$ more /u01/app/oracle/diag/rdbms/ora/ORA/trace/ORA_ora_12822.trc
- Trace file /u01/app/oracle/diag/rdbms/ora/ORA/trace/ORA_ora_12822.trc
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
- ·············································································
- Peeked values of the binds in SQL statement
- *******************************************
-
- Final query after transformations:******* UNPARSED QUERY IS *******
- SELECT COUNT(*) "COUNT(*)" FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2" WHERE "T1"."OB
- JECT_ID"="T2"."OBJECT_ID"
- kkoqbc: optimizing query block SEL$1 (#0)
-
- :
- call(in-use=1928, alloc=16344), compile(in-use=58824, alloc=62776), execution(
- in-use=3296, alloc=4032)
-
- kkoqbc-subheap (create addr=0x7f11c504fa10)
- ****************
- QUERY BLOCK TEXT
- ****************
- select count(*) from scott.t1,scott.t2 where t1.object_id=t2.object_id
- ---------------------
- QUERY BLOCK SIGNATURE
- ---------------------
- signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
- fro(0): flg=0 objn=88661 hint_alias="T1"@"SEL$1"
- fro(1): flg=0 objn=88662 hint_alias="T2"@"SEL$1"
-
- -----------------------------
- SYSTEM STATISTICS INFORMATION
- -----------------------------
- Using NOWORKLOAD Stats
- CPUSPEEDNW: 3074 millions instructions/sec (default is 100)
- IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
- IOSEEKTIM: 10 milliseconds (default is 10)
- MBRC: NO VALUE blocks (default is 8)
-
- ***************************************
- BASE STATISTICAL INFORMATION
- ***********************
- Table Stats::
- Table: T2 Alias: T2
- #Rows: 100 #Blks: 5 AvgRowLen: 89.00 ChainCnt: 0.00
- Column (#4): OBJECT_ID(
- AvgLen: 4 NDV: 100 Nulls: 0 Density: 0.010000 Min: 100 Max: 1495
- ***********************
- Table Stats::
- Table: T1 Alias: T1
- #Rows: 68323 #Blks: 1011 AvgRowLen: 98.00 ChainCnt: 0.00
- Column (#4): OBJECT_ID(
- AvgLen: 5 NDV: 68323 Nulls: 0 Density: 0.000015 Min: 100 Max: 88661
- Access path analysis for T1
- ***************************************
- SINGLE TABLE ACCESS PATH
- Single Table Cardinality Estimation for T1[T1]
- Table: T1 Alias: T1
- Card: Original: 68323.000000 Rounded: 68323 Computed: 68323.00 Non Adjusted
- : 68323.00
- Access Path: TableScan
- Cost: 276.58 Resp: 276.58 Degree: 0
- Cost_io: 276.00 Cost_cpu: 21547606
- Resp_io: 276.00 Resp_cpu: 21547606
- Best:: AccessPath: TableScan
- Cost: 276.58 Degree: 1 Resp: 276.58 Card: 68323.00 Bytes: 0
-
- Access path analysis for T2
- ***************************************
- SINGLE TABLE ACCESS PATH
- Single Table Cardinality Estimation for T2[T2]
- Table: T2 Alias: T2
- Card: Original: 100.000000 Rounded: 100 Computed: 100.00 Non Adjusted: 100.
- 00
- Access Path: TableScan
- Cost: 3.00 Resp: 3.00 Degree: 0
- Cost_io: 3.00 Cost_cpu: 56607
- Resp_io: 3.00 Resp_cpu: 56607
- Best:: AccessPath: TableScan
- Cost: 3.00 Degree: 1 Resp: 3.00 Card: 100.00 Bytes: 0
-
- ***************************************
-
-
- OPTIMIZER STATISTICS AND COMPUTATIONS
- ***************************************
- GENERAL PLANS
- ***************************************
- Considering cardinality-based initial join order.
- Permutations for Starting Table :0
- Join order[1]: T2[T2]#0 T1[T1]#1
-
- ***************
- Now joining: T1[T1]#1
- ***************
- NL Join
- Outer table: Card: 100.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 4
- Access path analysis for T1
- Inner table: T1 Alias: T1
- Access Path: TableScan
- NL Join: Cost: 27444.41 Resp: 27444.41 Degree: 1
- Cost_io: 27386.00 Cost_cpu: 2154817191
- Resp_io: 27386.00 Resp_cpu: 2154817191
-
- Best NL cost: 27444.41
- resc: 27444.41 resc_io: 27386.00 resc_cpu: 2154817191
- resp: 27444.41 resp_io: 27386.00 resc_cpu: 2154817191
- Join Card: 100.000000 = outer (100.000000) * inner (68323.000000) * sel (0.000015
- )
- Join Card - Rounded: 100 Computed: 100.00
- Outer table: T2 Alias: T2
- resc: 3.00 card 100.00 bytes: 4 deg: 1 resp: 3.00
- Inner table: T1 Alias: T1
- resc: 276.58 card: 68323.00 bytes: 5 deg: 1 resp: 276.58
- using dmeth: 2 #groups: 1
- SORT ressource Sort statistics
- Sort width: 430 Area size: 352256 Max Area size: 75497472
- Degree: 1
- Blocks to Sort: 1 Row size: 15 Total Rows: 100
- Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
- Total IO sort cost: 0 Total CPU sort cost: 36918822
- Total Temp space used: 0
- SORT ressource Sort statistics
- Sort width: 430 Area size: 352256 Max Area size: 75497472
- Degree: 1
- Blocks to Sort: 134 Row size: 16 Total Rows: 68323
- Initial runs: 2 Merge passes: 1 IO Cost / pass: 74
- Total IO sort cost: 208 Total CPU sort cost: 89625937
- Total Temp space used: 1663000
- SM join: Resc: 491.02 Resp: 491.02 [multiMatchCost=0.00]
- SM Join
- SM cost: 491.02
- resc: 491.02 resc_io: 487.00 resc_cpu: 148148972
- resp: 491.02 resp_io: 487.00 resp_cpu: 148148972
- Outer table: T2 Alias: T2
- resc: 3.00 card 100.00 bytes: 4 deg: 1 resp: 3.00
- Inner table: T1 Alias: T1
- resc: 276.58 card: 68323.00 bytes: 5 deg: 1 resp: 276.58
- using dmeth: 2 #groups: 1
- Cost per ptn: 0.20 #ptns: 1
- hash_area: 124 (max=18432) buildfrag: 1 probefrag: 142 ppasses: 1
- Hash join: Resc: 279.79 Resp: 279.79 [multiMatchCost=0.00]
- HA Join
- HA cost: 279.79
- resc: 279.79 resc_io: 279.00 resc_cpu: 29051513
- resp: 279.79 resp_io: 279.00 resp_cpu: 29051513
- Best:: JoinMethod: Hash
- Cost: 279.79 Degree: 1 Resp: 279.79 Card: 100.00 Bytes: 9
- ***********************
- Best so far: Table#: 0 cost: 3.0015 card: 100.0000 bytes: 400
- Table#: 1 cost: 279.7875 card: 100.0000 bytes: 900
- ***********************
- Join order[2]: T1[T1]#1 T2[T2]#0
-
- ***************
- Now joining: T2[T2]#0
- ***************
- NL Join
- Outer table: Card: 68323.00 Cost: 276.58 Resp: 276.58 Degree: 1 Bytes: 5
- Access path analysis for T2
- Inner table: T2 Alias: T2
- Access Path: TableScan
- NL Join: Cost: 92903.43 Resp: 92903.43 Degree: 1
- Cost_io: 92798.00 Cost_cpu: 3889121331
- Resp_io: 92798.00 Resp_cpu: 3889121331
-
- Best NL cost: 92903.43
- resc: 92903.43 resc_io: 92798.00 resc_cpu: 3889121331
- resp: 92903.43 resp_io: 92798.00 resc_cpu: 3889121331
- Join Card: 100.000000 = outer (68323.000000) * inner (100.000000) * sel (0.000015
- )
- Join Card - Rounded: 100 Computed: 100.00
- Outer table: T1 Alias: T1
- resc: 276.58 card 68323.00 bytes: 5 deg: 1 resp: 276.58
- Inner table: T2 Alias: T2
- resc: 3.00 card: 100.00 bytes: 4 deg: 1 resp: 3.00
- using dmeth: 2 #groups: 1
- SORT ressource Sort statistics
- Sort width: 430 Area size: 352256 Max Area size: 75497472
- Degree: 1
- Blocks to Sort: 134 Row size: 16 Total Rows: 68323
- Initial runs: 2 Merge passes: 1 IO Cost / pass: 74
- Total IO sort cost: 208 Total CPU sort cost: 89625937
- Total Temp space used: 1663000
- SORT ressource Sort statistics
- Sort width: 430 Area size: 352256 Max Area size: 75497472
- Degree: 1
- Blocks to Sort: 1 Row size: 15 Total Rows: 100
- Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
- Total IO sort cost: 0 Total CPU sort cost: 36918822
- Total Temp space used: 0
- SM join: Resc: 491.02 Resp: 491.02 [multiMatchCost=0.00]
- SM Join
- SM cost: 491.02
- resc: 491.02 resc_io: 487.00 resc_cpu: 148148972
- resp: 491.02 resp_io: 487.00 resp_cpu: 148148972
- Outer table: T1 Alias: T1
- resc: 276.58 card 68323.00 bytes: 5 deg: 1 resp: 276.58
- Inner table: T2 Alias: T2
- resc: 3.00 card: 100.00 bytes: 4 deg: 1 resp: 3.00
- using dmeth: 2 #groups: 1
- Cost per ptn: 56.31 #ptns: 1
- hash_area: 124 (max=18432) buildfrag: 142 probefrag: 1 ppasses: 1
- Hash join: Resc: 335.90 Resp: 335.90 [multiMatchCost=0.00]
- Outer table: T2 Alias: T2
- resc: 3.00 card 100.00 bytes: 4 deg: 1 resp: 3.00
- Inner table: T1 Alias: T1
- resc: 276.58 card: 68323.00 bytes: 5 deg: 1 resp: 276.58
- using dmeth: 2 #groups: 1
- Cost per ptn: 0.20 #ptns: 1
- hash_area: 124 (max=18432) buildfrag: 1 probefrag: 142 ppasses: 1
- Hash join: Resc: 279.79 Resp: 279.79 [multiMatchCost=0.00]
- HA Join
- HA cost: 279.79 swapped
- resc: 279.79 resc_io: 279.00 resc_cpu: 29051513
- resp: 279.79 resp_io: 279.00 resp_cpu: 29051513
- Join order aborted: cost > best plan cost
- ***********************
- (newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
-
- *********************************
- Number of join permutations tried: 2
- *********************************
- Consider using bloom filter between T2[T2] and T1[T1] with ??
- kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because n
- o single-tables predicates
- Enumerating distribution method (advanced)
- --- Distribution method for
- join between T2[T2](serial) and T1[T1](serial); jm = 1; right side access path = T
- ableScan
- ---- serial Hash-Join -> NONE
-
- (newjo-save) [0 1 ]
- Trying or-Expansion on query block SEL$1 (#0)
- Transfer Optimizer annotations for query block SEL$1 (#0)
- id=0 frofkksm[i] (sort-merge/hash) predicate="T1"."OBJECT_ID"="T2"."OBJECT_ID"
- id=0 frosand (sort-merge/hash) predicate="T1"."OBJECT_ID"="T2"."OBJECT_ID"
- Final cost for query block SEL$1 (#0) - All Rows Plan:
- Best join order: 1
- Cost: 279.7875 Degree: 1 Card: 100.0000 Bytes: 900
- Resc: 279.7875 Resc_io: 279.0000 Resc_cpu: 29051513
- Resp: 279.7875 Resp_io: 279.0000 Resc_cpu: 29051513
- kkoqbc-subheap (delete addr=0x7f11c504fa10, in-use=36144, alloc=49272)
- kkoqbc-end:
- :
- call(in-use=10704, alloc=82128), compile(in-use=62376, alloc=62776), execution
- (in-use=3296, alloc=4032)
-
- kkoqbc: finish optimizing query block SEL$1 (#0)
- apadrv-end
- :
- call(in-use=10704, alloc=82128), compile(in-use=63376, alloc=66920), execution
- (in-use=3296, alloc=4032)
-
-
- Starting SQL statement dump
-
- user_id=0 user_name=SYS module=sqlplus@ds (TNS V1-V3) action=
- sql_id=aqkx0jysz4fr9 plan_hash_value=-20910549 problem_type=3
- ----- Current SQL Statement for this session (sql_id=aqkx0jysz4fr9) -----
- select count(*) from scott.t1,scott.t2 where t1.object_id=t2.object_id
- sql_text_length=71
- sql=select count(*) from scott.t1,scott.t2 where t1.object_id=t2.object_id
- ----- Explain Plan Dump -----
- ----- Plan Table -----
-
- ============
- Plan Table
- ============
- ---------------------------------------+-----------------------------------+
- | Id | Operation | Name | Rows | Bytes | Cost | Time |
- ---------------------------------------+-----------------------------------+
- | 0 | SELECT STATEMENT | | | | 280 | |
- | 1 | SORT AGGREGATE | | 1 | 9 | | |
- | 2 | HASH JOIN | | 100 | 900 | 280 | 00:00:04 |
- | 3 | TABLE ACCESS FULL | T2 | 100 | 400 | 3 | 00:00:01 |
- | 4 | TABLE ACCESS FULL | T1 | 67K | 334K | 277 | 00:00:04 |
- ---------------------------------------+-----------------------------------+
- Predicate Information:
- ----------------------
- 2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-
- Content of other_xml column
- ===========================
- db_version : 11.2.0.4
- parse_schema : SYS
- plan_hash : 4274056747
- --More--(60%)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31399171/viewspace-2131066/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31399171/viewspace-2131066/