</pre><pre name="code" class="sql">SQL> select count(distinct(msgid)),count(*) from messagein ;
COUNT(DISTINCT(MSGID)) COUNT(*)
---------------------- ----------
978619 978619
说明msgid列时唯一的
搜集直方图信息:
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'MBFE',
tabname => 'MESSAGEIN',
estimate_percent => 100,
method_opt => 'for columns MSGID size skewonly',
no_invalidate => FALSE,
degree => 16,
cascade => TRUE);
END;
2 3 4 5 6 7 8 9 10 /
SQL> set linesize 200
SQL> select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'MBFE'
and a.table_name = upper('messagein'); 2 3 4 5 6 7 8 9 10 11
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
ID 978619 978619 100 NONE 1
MSGID 978619 978619 100 FREQUENCY 2
STATUS 978619 1 0 NONE 1
PROCCNT 978619 2 0 NONE 1
ACCEPTTIME 978619 653888 66.82 NONE 1
MSGBODY 978619 0 0 NONE 0
QUEUE 978619 4 0 NONE 1
7 rows selected.
SQL> select msgid from messagein where rownum<10;
MSGID
----------------------------------------------------------------------------------------------------
ID:414d5120514d454d42464520202020204c24b44120263403
ID:414d5120514d453333313020202020204c224a3c200c1de7
ID:414d5120514d454d42464520202020204c24b44120263404
ID:414d5120514d453333313020202020204c224a3c200c49cb
ID:414d5120514d454d42464520202020204c24b44120263703
ID:414d5120514d453333313020202020204c224a3c200fa81d
ID:414d5120514d453333313020202020204c224a3c200e15d0
ID:414d5120514d453333313020202020204c224a3c200ff871
ID:414d5120514d453333313020202020204c224a3c200cd011
9 rows selected.
SQL> select messagein0_.proccnt AS col_0_0_
FROM mbfe.messagein messagein0_
WHERE messagein0_.msgID ='ID:414d5120514d454d42464520202020204c24b44120263403' 2 3 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2678161237
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 454K| 23M| 43978 (1)| 00:08:48 |
|* 1 | TABLE ACCESS FULL| MESSAGEIN | 454K| 23M| 43978 (1)| 00:08:48 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MESSAGEIN0_"."MSGID"='ID:414d5120514d454d4246452020202020
4c24b44120263403')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
161610 consistent gets
161607 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
强制走索引呢？
SQL> select /*+ index(messagein0_,INDEXMSGIN_1)*/ messagein0_.proccnt AS col_0_0_
FROM mbfe.messagein messagein0_
WHERE messagein0_.msgID ='ID:414d5120514d454d42464520202020204c24b44120263403' 2 3 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4204216909
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 454K| 23M| 426K (1)| 01:25:20 |
| 1 | TABLE ACCESS BY INDEX ROWID| MESSAGEIN | 454K| 23M| 426K (1)| 01:25:20 |
|* 2 | INDEX RANGE SCAN | INDEXMSGIN_1 | 454K| | 4029 (1)| 00:00:49 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MESSAGEIN0_"."MSGID"='ID:414d5120514d454d42464520202020204c24b4412026
3403')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
1 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到Rows都是454K，看来是CBO出现问题了
里面10053进行跟踪：
[oracle@devrac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 5 08:05:23 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> oradebug setmypid;
Statement processed.
SQL> alter session set events='10053 trace name context forever,level 1';
Session altered.
SQL> select messagein0_.proccnt AS col_0_0_
FROM mbfe.messagein messagein0_
WHERE messagein0_.msgID ='ID:414d5120514d454d42464520202020204c24b44120263403'; 2 3
COL_0_0_
----------
1
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL> oradebug close_trace;
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/diag/rdbms/devrac/devrac1/trace/devrac1_ora_467.trc
---------------------------------------------------------------------------------------------
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: MESSAGEIN Alias: MESSAGEIN0_
#Rows: 978619 #Blks: 162129 AvgRowLen: 1077.00 ChainCnt: 0.00
Index Stats::
Index: INDEXMSGIN_1 Col#: 2
LVLS: 2 #LB: 8661 #DK: 978619 LB/K: 1.00 DB/K: 1.00 CLUF: 910071.00
Index: MESSAGEIN_PK Col#: 1
LVLS: 2 #LB: 2045 #DK: 978619 LB/K: 1.00 DB/K: 1.00 CLUF: 888635.00
Index: SYS_IL0000209895C00006$$ Col#: (NOT ANALYZED)
LVLS: 1 #LB: 25 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 800.00
Access path analysis for MESSAGEIN
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for MESSAGEIN[MESSAGEIN0_]
Column (#2):
NewDensity:0.232079, OldDensity:0.000001 BktCnt:978619, PopBktCnt:978619, PopValCnt:2, NDV:978619
Column (#2): MSGID(
AvgLen: 52 NDV: 978619 Nulls: 0 Density: 0.232079
Histogram: Freq #Bkts: 2 UncompBkts: 978619 EndPtVals: 2
Table: MESSAGEIN Alias: MESSAGEIN0_
Card: Original: 978619.000000 Rounded: 454234 Computed: 454233.50 Non Adjusted: 454233.50
Access Path: TableScan
Cost: 43978.03 Resp: 43978.03 Degree: 0
Cost_io: 43912.00 Cost_cpu: 1388057486
Resp_io: 43912.00 Resp_cpu: 1388057486
Access Path: index (AllEqRange)
Index: INDEXMSGIN_1
resc_io: 426440.00 resc_cpu: 3223102814
ix_sel: 0.464158 ix_sel_with_filters: 0.464158
Cost: 426593.33 Resp: 426593.33 Degree: 1
Best:: AccessPath: TableScan
Cost: 43978.03 Degree: 1 Resp: 43978.03 Card: 454233.50 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: MESSAGEIN[MESSAGEIN0_]#0
***********************
Best so far: Table#: 0 cost: 43978.0316 card: 454233.5000 bytes: 24982870
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofand predicate="MESSAGEIN0_"."MSGID"='ID:414d5120514d454d42464520202020204c24b44120263403'
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 43978.0316 Degree: 1 Card: 454234.0000 Bytes: 24982870
Resc: 43978.0316 Resc_io: 43912.0000 Resc_cpu: 1388057486
Resp: 43978.0316 Resp_io: 43912.0000 Resc_cpu: 1388057486
kkoqbc-subheap (delete addr=0x7f346a6efaa0, in-use=14096, alloc=16408)
kkoqbc-end:
:
call(in-use=12912, alloc=49184), compile(in-use=59680, alloc=63568), execution(in-use=2616, alloc=4032)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=12912, alloc=49184), compile(in-use=60592, alloc=63568), execution(in-use=2616, alloc=4032)
Starting SQL statement dump
user_id=0 user_name=SYS module=sqlplus@devrac1 (TNS V1-V3) action=
sql_id=81u8ngrax001k plan_hash_value=-1616806059 problem_type=3
----- Current SQL Statement for this session (sql_id=81u8ngrax001k) -----
select messagein0_.proccnt AS col_0_0_
FROM mbfe.messagein messagein0_
WHERE messagein0_.msgID ='ID:414d5120514d454d42464520202020204c24b44120263403'
sql_text_length=154
sql= select messagein0_.proccnt AS col_0_0_
FROM mbfe.messagein messagein0_
WHERE messagein0_.msgID ='ID:414d5120514d454d42464520202020204c24b44120263403'
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 43K | |
| 1 | TABLE ACCESS FULL | MESSAGEIN| 444K | 24M | 43K | 00:09:48 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("MESSAGEIN0_"."MSGID"='ID:414d5120514d454d42464520202020204c24b44120263403')
Content of other_xml column
===========================
db_version : 11.2.0.3
parse_schema : SYS
plan_hash : 2678161237
plan_hash_2 : 999486991
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "MESSAGEIN0_"@"SEL$1")
END_OUTLINE_DATA
*/
Column (#2): MSGID(
AvgLen: 52 NDV: 978619 Nulls: 0 Density: 0.232079
Histogram: Freq #Bkts: 2 UncompBkts: 978619 EndPtVals: 2
其中NDV 表示 number of distinct的值,选择性是0.232079 这里选择性明显是100
不清楚这个Density: 0.232079是怎么计算的？
