透過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檔案內容太多，以上只是擷取了部分貼上在這裡供參考。

透過10053事件分析一個SQL執行計劃

相關文章