通过10053事件生成trace文件,分析一个SQL的执行计划。
1、 启用10053事件与关闭10053事件:
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
Session altered.
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';
Session altered.
关闭:
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';
Session altered.
2、 通过10053事件分析一个SQL执行计划的产生过程,需要贴出trace中的相关信息和必要的文字说:
1》创建表t1:
SQL> create table t1 as select * from all_objects;
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
68327
2》 创建表t2:
SQL> create table t2 as select * from all_objects where rownum<=100;
Table created.
SQL> select count(*) from t2;
COUNT(*)
----------
100
3、 对T1,T2表进行分析,不包含直方图:
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.
4、sys用户打开10053事件 :
SQL> alter session set events '10053 trace name context forever ,level 1';
Session altered.
5、在 scott:用户建立以下查询:
SQL> select count(*) from t1,t2 where t1.object_id = t2.object_id;
COUNT(*)
----------
100
6、sys用户关闭10053事件 :
SQL> alter session set events '10053 trace name context off';
Session altered.
7、查看生成的trace文件的位置:
SQL> select value from v$diag_info where name like '%Default%';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11gr2/OCMU/trace/OCMU_ora_14201.trc
8、读取并 查看trace文件的内容:
more /u01/app/oracle/diag/rdbms/ora11gr2/OCMU/trace/OCMU_ora_14201.trc
--trace文件路径、数据库版本、数据库基本信息
Trace file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_16663.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: SX
Release: 2.6.18-238.el5
Version: #1 SMP Tue Jan 4 15:24:05 EST 2011
Machine: i686
Instance name: ORA11GR2
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 16663, image: oracle@SX (TNS V1-V3)
*** 2013-09-27 14:27:36.337
*** SESSION ID:(17.408) 2013-09-27 14:27:36.337
*** CLIENT ID:() 2013-09-27 14:27:36.337
*** SERVICE NAME:(SYS$USERS) 2013-09-27 14:27:36.337
*** MODULE NAME:(SQL*Plus) 2013-09-27 14:27:36.337
*** ACTION NAME:() 2013-09-27 14:27:36.337
Registered qb: SEL$1 0x5e3604 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=4 objn=76317 hint_alias="T1"@"SEL$1"
fro(1): flg=4 objn=76318 hint_alias="T2"@"SEL$1"
SPM: statement not found in SMB
... ...
Consider using bloom filter between T2[T2] and T1[T1]
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because no single-tables predicates
(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: 289.4172 Degree: 1 Card: 100.0000 Bytes: 900
Resc: 289.4172 Resc_io: 288.0000 Resc_cpu: 45848965
Resp: 289.4172 Resp_io: 288.0000 Resc_cpu: 45848965
kkoqbc-subheap (delete addr=0x5efb0c, in-use=19568, alloc=29032)
kkoqbc-end:
:
call(in-use=8048, alloc=49168), compile(in-use=52416, alloc=54448), execution(in-use=2040, alloc=4060)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=8048, alloc=49168), compile(in-use=53052, alloc=54448), execution(in-use=2040, alloc=4060)
Starting SQL statement dump
user_id=111 user_name=SCOTT module=SQL*Plus action=
sql_id=dzvtvzv73q45u plan_hash_value=-20910549 problem_type=3
----- Current SQL Statement for this session (sql_id=dzvtvzv73q45u) -----
select count(*) from t1,t2 where t1.object_id = t2.object_id
sql_text_length=61
sql=select count(*) from t1,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 | | | | 289 | |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | HASH JOIN | | 100 | 900 | 289 | 00:00:04 |
| 3 | TABLE ACCESS FULL | T2 | 100 | 400 | 3 | 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 70K | 349K | 286 | 00:00:04 |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Content of other_xml column
===========================
db_version : 11.2.0.1
parse_schema : SCOTT
plan_hash : 4274056747
plan_hash_2 : 655528263
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_HASH(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 11.2.0.1
_optimizer_search_limit = 5
cpu_count = 1
... ...
由于生成的trace文件内容太多,以上只是截取了部分粘贴在这里供参考。
1、 启用10053事件与关闭10053事件:
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
Session altered.
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';
Session altered.
关闭:
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';
Session altered.
2、 通过10053事件分析一个SQL执行计划的产生过程,需要贴出trace中的相关信息和必要的文字说:
1》创建表t1:
SQL> create table t1 as select * from all_objects;
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
68327
2》 创建表t2:
SQL> create table t2 as select * from all_objects where rownum<=100;
Table created.
SQL> select count(*) from t2;
COUNT(*)
----------
100
3、 对T1,T2表进行分析,不包含直方图:
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.
4、sys用户打开10053事件 :
SQL> alter session set events '10053 trace name context forever ,level 1';
Session altered.
5、在 scott:用户建立以下查询:
SQL> select count(*) from t1,t2 where t1.object_id = t2.object_id;
COUNT(*)
----------
100
6、sys用户关闭10053事件 :
SQL> alter session set events '10053 trace name context off';
Session altered.
7、查看生成的trace文件的位置:
SQL> select value from v$diag_info where name like '%Default%';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11gr2/OCMU/trace/OCMU_ora_14201.trc
8、读取并 查看trace文件的内容:
more /u01/app/oracle/diag/rdbms/ora11gr2/OCMU/trace/OCMU_ora_14201.trc
--trace文件路径、数据库版本、数据库基本信息
Trace file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_16663.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: SX
Release: 2.6.18-238.el5
Version: #1 SMP Tue Jan 4 15:24:05 EST 2011
Machine: i686
Instance name: ORA11GR2
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 16663, image: oracle@SX (TNS V1-V3)
*** 2013-09-27 14:27:36.337
*** SESSION ID:(17.408) 2013-09-27 14:27:36.337
*** CLIENT ID:() 2013-09-27 14:27:36.337
*** SERVICE NAME:(SYS$USERS) 2013-09-27 14:27:36.337
*** MODULE NAME:(SQL*Plus) 2013-09-27 14:27:36.337
*** ACTION NAME:() 2013-09-27 14:27:36.337
Registered qb: SEL$1 0x5e3604 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=4 objn=76317 hint_alias="T1"@"SEL$1"
fro(1): flg=4 objn=76318 hint_alias="T2"@"SEL$1"
SPM: statement not found in SMB
... ...
Consider using bloom filter between T2[T2] and T1[T1]
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because no single-tables predicates
(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: 289.4172 Degree: 1 Card: 100.0000 Bytes: 900
Resc: 289.4172 Resc_io: 288.0000 Resc_cpu: 45848965
Resp: 289.4172 Resp_io: 288.0000 Resc_cpu: 45848965
kkoqbc-subheap (delete addr=0x5efb0c, in-use=19568, alloc=29032)
kkoqbc-end:
:
call(in-use=8048, alloc=49168), compile(in-use=52416, alloc=54448), execution(in-use=2040, alloc=4060)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=8048, alloc=49168), compile(in-use=53052, alloc=54448), execution(in-use=2040, alloc=4060)
Starting SQL statement dump
user_id=111 user_name=SCOTT module=SQL*Plus action=
sql_id=dzvtvzv73q45u plan_hash_value=-20910549 problem_type=3
----- Current SQL Statement for this session (sql_id=dzvtvzv73q45u) -----
select count(*) from t1,t2 where t1.object_id = t2.object_id
sql_text_length=61
sql=select count(*) from t1,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 | | | | 289 | |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | HASH JOIN | | 100 | 900 | 289 | 00:00:04 |
| 3 | TABLE ACCESS FULL | T2 | 100 | 400 | 3 | 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 70K | 349K | 286 | 00:00:04 |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Content of other_xml column
===========================
db_version : 11.2.0.1
parse_schema : SCOTT
plan_hash : 4274056747
plan_hash_2 : 655528263
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_HASH(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 11.2.0.1
_optimizer_search_limit = 5
cpu_count = 1
... ...
由于生成的trace文件内容太多,以上只是截取了部分粘贴在这里供参考。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2126480/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2126480/