8i下分区表统计柱状图问题。十分郁闷......

adamyang

DBMS_STATS.GATHER_TABLE_STATS
各参数的排列组合都几乎都用过了，
一模一样的结构和数据非分区表就很正常。
哎，难道是8ICBO计算柱状图的BUG？

adamyang

最初由 biti_rainy 发布
[B]http://download-west.oracle.com/ ... nts_46a.htm#2058583


参考语法，你可以分别对 分区进行分析看看?

PARTITION | SUBPARTITION
Specify the partition or subpartition on which you want statistics to be gathered. You cannot use this clause when analyzing clusters.

If you specify PARTITION and table is composite-partitioned, then Oracle analyzes all the subpartitions within the specified partition. [/B]

biti_rainy大哥，我也试过了
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESTUSER', TABNAME => 'TEST', PARTNAME => 'PAR_1',CASCADE => TRUE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS');

--我只有一个分区。复合索引的中的前列是分区列（建立的不是分区索引）

依然不行.......

biti_rainy

dbms_stats 是 816 推出的，在8i是存在一些问题，也许你这个也是问题之一呢

所以一条路走不通干吗不换条路呢，撞了墙回个头不行吗？
dbms_stats 不行换 analyze 看看

adamyang

谢谢 biti_rainy 大哥。
偶已经analyze table test compute statistics
for table
for all indexes
for all indexed columns;
可是分析结果是一样的。

不是偶不回头，只是回头还是没找到岸........

biti_rainy

让你参考 analyze  语法啊，里面有指定 * partition  *    啊,不是URL都给你给出来了么！

adamyang

收到，正在测试。

另外我在8I里面跟踪了线程的SQL，发现我在执行
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESTUSER', TABNAME => 'TEST',CASCADE => TRUE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS');
时后台的SQL竟然是
analyze table "TESTUSER"."TEST"  compute statistics
for table
for all indexes
for all indexed columns;

怎么DBMS_STATS.GATHER_TABLE_STATS就是在调用analyze 么......

adamyang

analyze Table TEST Partition (PAR_1) compute statistics
for Table
for all indexed columns;

analyze Table TEST compute Statistics
for all Indexes;

结果还是一样.....

adamyang

其实一直柱状图算是分析出来的，
在User_Histograms中只能看到最大最小值，
在user_part_histograms中可以看到每个索引列的75个buckets.

只是CBO计算复合索引成本有些问题。

select * from A='~'
会走全表扫描。

select * from A='~' and b=20041230
却去走A的索引了。

是不是CBO计算的问题呢？

请大侠指教一下

biti_rainy

那你可以搜索一下 event  10053 ,trace 一下

adamyang

连接到:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> Alter Session Set sql_trace=True;

会话已更改。

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

会话已更改。

SQL> Select * From testyk2 Where sn='~' And cdate=20041230;

未选定行

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

会话已更改。


TRACE:

*** SESSION ID34.202) 2004-12-27 15:48:07.437
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=99 oct=42 lid=99 tim=0 hv=2319219176 ad='586d9d0'
Alter Session Set sql_trace=True
END OF STMT
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=99 oct=42 lid=99 tim=0 hv=122530083 ad='2e73c7c'
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
QUERY
Select * From testyk2 Where sn='~' And cdate=20041230
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 8.1.7
OPTIMIZER_MODE/GOAL = Choose
OPTIMIZER_PERCENT_PARALLEL = 0
HASH_AREA_SIZE = 131072
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 5
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = FALSE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = FALSE
_PUSH_JOIN_PREDICATE = FALSE
PARALLEL_BROADCAST_ENABLED = FALSE
OPTIMIZER_MAX_PERMUTATIONS = 80000
OPTIMIZER_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = FALSE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = FALSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = FALSE
_PUSH_JOIN_UNION_VIEW = FALSE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = FALSE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = FALSE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
DB_FILE_MULTIBLOCK_READ_COUNT = 8
SORT_AREA_SIZE = 131072
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: TESTYK2   Alias: TESTYK2
  (Using composite stats)
  TOTAL ::  CDN: 3750091  NBLKS:  21221  TABLE_SCAN_CST: 3221  AVG_ROW_LEN:  39
-- Index stats
  INDEX#: 49681  COL#: 3 4
    USING COMPOSITE STATS
    TOTAL ::  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
  INDEX#: 49680  COL#: 1
    TOTAL ::  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
***************************************
SINGLE TABLE ACCESS PATH
Column:         SN  Col#: 1      Part#: 0     Table: TESTYK2   Alias: TESTYK2
    NDV: 544900    NULLS: 0         DENS: 1.4804e-006
Column:         SN  Col#: 1      Table: TESTYK2   Alias: TESTYK2
    NDV: 544900    NULLS: 0         DENS: 1.8352e-006
Column:      CDATE  Col#: 3      Part#: 0     Table: TESTYK2   Alias: TESTYK2
    NDV: 181       NULLS: 0         DENS: 6.6667e-003
Column:      CDATE  Col#: 3      Table: TESTYK2   Alias: TESTYK2
    NDV: 181       NULLS: 0         DENS: 5.5249e-003 LO:  20040427  HI: 20041216
  TABLE: TESTYK2     ORIG CDN: 3750091  CMPTD CDN: 1
  Access path: tsc  Resc:  3221  Resp:  3221
  Access path: index (scan)
      INDEX#: 49681  TABLE: TESTYK2
      CST: 7  IXSEL:  5.5249e-003  TBSEL:  5.5249e-003
  Access path: index (equal)
      INDEX#: 49680  TABLE: TESTYK2
      CST: 2  IXSEL:  0.0000e+000  TBSEL:  1.8352e-006
  BEST_CST: 2.00  PATH: 4  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: TESTYK2 [TESTYK2]
Best so far: TABLE#: 0  CST:          2  CDN:          1  BYTES:         39
Final:
  CST: 2  CDN: 1  RSC: 2  RSP: 2  BYTES: 39
=====================
PARSING IN CURSOR #1 len=53 dep=0 uid=99 oct=3 lid=99 tim=0 hv=4141192163 ad='58956b8'
Select * From testyk2 Where sn='~' And cdate=20041230
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
*** 2004-12-27 15:48:22.328
FETCH #1:c=0,e=0,p=22782,cr=22782,cu=0,mis=0,r=0,dep=0,og=4,tim=0
STAT #1 id=1 cnt=0 pid=0 pos=0 obj=49677 op='TABLE ACCESS BY GLOBAL INDEX ROWID TESTYK2 PARTITION: ROW LOCATION ROW LOCATION '
STAT #1 id=2 cnt=848182 pid=1 pos=1 obj=49680 op='INDEX RANGE SCAN '
QUERY
ALTER SESSION SET EVENTS '10053 trace name context off'
=====================
PARSING IN CURSOR #1 len=55 dep=0 uid=99 oct=42 lid=99 tim=0 hv=3008402716 ad='5890dd8'
ALTER SESSION SET EVENTS '10053 trace name context off'
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0


大哥帮忙瞧瞧，啥问题？