本人测试源于itpub的一个帖子，测试版本是10.2.0.5，欢迎大家一起讨论。
SQL> drop table killdb;
Table dropped.
SQL> create table killdb (id number,name varchar2(10));
Table created.
SQL> create index killdb_idx on killdb(id);
Index created.
SQL> insert into killdb values(1,'killdb.com');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> begin
2 for i in 1..5 loop
3 insert into killdb select * from killdb;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from killdb;
COUNT(*)
----------
192
SQL> begin
2 for i in 1..100 loop
3 insert into killdb select * from killdb;
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at line 3
SQL> select count(*) from killdb;
COUNT(*)
----------
196608
SQL> insert into killdb values (2,'baidu.com');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> begin
2 for i in 1..5 loop
3 insert into killdb values(2,'baidu.com');
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select id, count(*) from killdb group by id;
ID COUNT(*)
---------- ----------
1 196608
2 13
SQL> begin
2 dbms_stats.gather_table_stats( ownname => 'ROGER',
3 tabname => 'KILLDB',
4 estimate_percent => dbms_stats.auto_sample_size,
5 method_opt => 'for all columns size 254',
6 cascade => true,
7 degree => 2 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> set lines 140
SQL> set autot trace exp
SQL> select * from killdb where id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2601906965
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 442 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| KILLDB | 34 | 442 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | KILLDB_IDX | 34 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
SQL> set autot off
SQL> insert into killdb select * from killdb where id=2;
13 rows created.
SQL> commit;
Commit complete.
SQL> set autot trace exp
SQL> select * from killdb where id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2601906965
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 442 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| KILLDB | 34 | 442 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | KILLDB_IDX | 34 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
SQL> set autot off
SQL> begin
2 dbms_stats.gather_table_stats( ownname => 'ROGER',
3 tabname => 'KILLDB',
4 cascade => false,
5 degree => 2 );
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> set autot trace exp
SQL> select * from killdb where id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2601906965
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| KILLDB | 1 | 14 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | KILLDB_IDX | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
SQL> begin
2 dbms_stats.gather_table_stats( ownname => 'ROGER',
3 tabname => 'KILLDB',
4 estimate_percent => dbms_stats.auto_sample_size,
5 method_opt => 'for all columns size 254',
6 cascade => false,
7 degree => 2 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select * from killdb where id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2601906965
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| KILLDB | 1 | 14 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | KILLDB_IDX | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
SQL> begin
2 dbms_stats.gather_table_stats( ownname => 'ROGER',
3 tabname => 'KILLDB',
4 estimate_percent => dbms_stats.auto_sample_size,
5 method_opt => 'for all columns size 254',
6 cascade => true,
7 degree => 2 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> set autot trace exp
SQL> select * from killdb where id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2601906965
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 676 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| KILLDB | 52 | 676 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | KILLDB_IDX | 53 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
SQL> set autot off
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> select * from killdb where id=2;
ID NAME
---------- ----------
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
2 baidu.com
26 rows selected.
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL> select sql_id, sql_text
2 from v$sqlarea
3 where sql_text like '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}select * from killdb{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';
SQL_ID SQL_TEXT
------------- -----------------------------------------------------------------------------------
1tjf3zusd7h0k select sql_id,sql_text from v$sqlarea where sql_text like '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}select * from killdb{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}'
4yt3uzwprgscs select * from killdb where id=2 <== 格式化10053 trace
SQL> start sqltrcasplit.sql /home/ora10g/admin/roger/udump/roger_ora_9740.trc
PL/SQL procedure successfully completed.
Parameter 1:
Trace Filename (required)
Value passed to sqltrcasplit.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: /home/ora10g/admin/roger/udump/roger_ora_9740.trc
Splitting /home/ora10g/admin/roger/udump/roger_ora_9740.trc
NOTE:
If you get one of these errors it means SQLTXPLAIN is not installed:
PLS-00201: identifier 'SQLTXPLAIN.SQLT$A' must be declared
ORA-00904: "SQLTXPLAIN"."SQLT$A"."VALIDATE_USER": invalid identifier
Review NN_*.log files created during installation and fix errors reported.
PL/SQL procedure successfully completed.
... please wait ...
To monitor progress, login as SQLTXPLAIN into another session and execute:
SQL> SELECT * FROM trca$_log_v;
... splitting trace(s) ...
Execution ID: 57658 started at 2012-02-18 06:36:03
In case of premature termination, read trcanlzr_error.log located in SQL*Plus default directory
/*************************************************************************************/
06:36:03 => trcanlzr
06:36:03 directory path was ignored from "/home/ora10g/admin/roger/udump/roger_ora_9740.trc"
06:36:03 file_name:"roger_ora_9740.trc"
06:36:03 analyze:"NO"
06:36:03 split:"YES"
06:36:03 tool_execution_id:"57658"
06:36:03 directory_alias_in:"SQLT$STAGE"
06:36:03 file_name_log:""
06:36:03 file_name_html:""
06:36:03 file_name_txt:""
06:36:03 file_name_10046:""
06:36:03 file_name_10053:""
06:36:03 out_file_identifier:""
06:36:03 calling trca$p.parse_main
06:36:03 => parse_main
06:36:03 analyzing input file roger_ora_9740.trc in /home/ora10g/admin/roger/udump
06:36:03 -> parse_file
06:36:03 parsing file roger_ora_9740.trc in /home/ora10g/admin/roger/udump
06:36:03 parsed roger_ora_9740.trc (input 46354 bytes, parsed as 46354 bytes)
06:36:03 <- parse_file
06:36:03 parsed 1 file(s) (input 46354 bytes)
06:36:03 first trace: /home/ora10g/admin/roger/udump/roger_ora_9740.trc
06:36:03 <= parse_main
06:36:03
++++++ 如下是10053 trace格式化以后的关键部分 ++++++
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: KILLDB Alias: KILLDB
#Rows: 195119 #Blks: 622 AvgRowLen: 13.00
Index Stats::
Index: KILLDB_IDX Col#: 1
LVLS: 2 #LB: 604 #DK: 2 LB/K: 302.00 DB/K: 258.00 CLUF: 516.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): ID(NUMBER)
AvgLen: 3.00 NDV: 2 Nulls: 0 Density: 1.7934e-04 Min: 1 Max: 2
Histogram: Freq #Bkts: 2 UncompBkts: 5576 EndPtVals: 2
Table: KILLDB Alias: KILLDB
Card: Original: 195119 Rounded: 52 Computed: 52.49 Non Adjusted: 52.49
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 171.51 Resp: 171.51 Degree: 0
Cost_io: 170.00 Cost_cpu: 43454376
Resp_io: 170.00 Resp_cpu: 43454376
Access Path: index (AllEqRange)
Index: KILLDB_IDX
resc_io: 4.00 resc_cpu: 48096
ix_sel: 2.6901e-04 ix_sel_with_filters: 2.6901e-04
Cost: 4.00 Resp: 4.00 Degree: 1
Best:: AccessPath: IndexRange Index: KILLDB_IDX
Cost: 4.00 Degree: 1 Resp: 4.00 Card: 52.49 Bytes: 0 <== IndexRange计算出来的card为52.49，也就是plan中显示的53
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]: KILLDB[KILLDB]#0
***********************
Best so far: Table#: 0 cost: 4.0017 card: 52.4890 bytes: 676
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan: Best join order: 1
Cost: 4.0017 Degree: 1 Card: 52.0000 Bytes: 676 <== 这里计算出来为52
Resc: 4.0017 Resc_io: 4.0000 Resc_cpu: 48096
Resp: 4.0017 Resp_io: 4.0000 Resc_cpu: 48096
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "KILLDB"."ID" "ID","KILLDB"."NAME" "NAME" FROM "ROGER"."KILLDB" "KILLDB" WHERE "KILLDB"."ID"=2
kkoqbc-subheap (delete addr=0xb72ebdd8, in-use=9964, alloc=11176)
kkoqbc-end
: call(in-use=11616, alloc=32736), compile(in-use=32992, alloc=33876)
apadrv-end: call(in-use=11616, alloc=32736), compile(in-use=33528, alloc=33876)
sql_id=53zmp1j4dsgy4.
Current SQL statement for this session:
select * from killdb where id=2
============
Plan Table
============
-------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | TABLE ACCESS BY INDEX ROWID | KILLDB | 52 | 676 | 4 | 00:00:01 | ==> 52
| 2 | INDEX RANGE SCAN | KILLDB_IDX| 53 | | 3 | 00:00:01 | ==> 53
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("ID"=2)
虽然前面收集统计信息是size 254，实际上buckets只有2个，如下：
SQL> set lines 150
SQL> col owner for a15
SQL> col table_name for a15
SQL> select owner,
2 table_name,
3 COLUMN_NAME,
4 NUM_DISTINCT,
5 DENSITY,
6 NUM_BUCKETS,
7 HISTOGRAM
8 from dba_tab_col_statistics
9 where table_name = 'KILLDB'
10 and owner = 'ROGER';
OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
-------- --------------- -------------- ------------ ---------- ----------- ---------------
ROGER KILLDB ID 2 2.5625E-06 2 FREQUENCY
ROGER KILLDB NAME 2 2.5625E-06 2 FREQUENCY
上面的过滤因子2.6901e-04 是什么计算出来的，还不太清楚，特别是直方图存在的情况下，
对于无直方图的情况比较简单，过滤因子即为1/NDV，所以card计算公式就为如下：
Cardinality = 行数*过滤因子
计算结果如下：
SQL> select 195119 * (2.6901e-04) from dual;
195119*(2.6901E-04)
-------------------
52.4889622
总的来说，还有些地方没有搞明白，还需要继续研究！
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
