SELECT OWNER,
       TABLE_NAME,
       PARTITION_NAME,
       OBJECT_TYPE,
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS,
       AVG_SPACE,
       CHAIN_CNT,
       AVG_ROW_LEN,
       to_char(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED,
       USER_STATS
  FROM DBA_TAB_STATISTICS
 WHERE OWNER='CMXBUSI'
 AND TABLE_NAME = 'T02';

SELECT INDEX_NAME              AS NAME,
       BLEVEL,
       LEAF_BLOCKS             AS LEAF_BLKS,
       DISTINCT_KEYS           AS DST_KEYS,
       NUM_ROWS,
       CLUSTERING_FACTOR       AS CLUST_FACT,
       AVG_LEAF_BLOCKS_PER_KEY AS LEAF_PER_KEY,
       AVG_DATA_BLOCKS_PER_KEY AS DATA_PER_KEY,
       LAST_ANALYZED
  FROM DBA_IND_STATISTICS where
  TABLE_OWNER='CMXBUSI' AND
  TABLE_NAME='T02';

select table_name,index_name,leaf_blocks,blevel,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows from dba_indexes where owner = 'SCOTT' and table_name = 'T1';

方法一：
SELECT COLUMN_NAME,
      NUM_DISTINCT,
      LOW_VALUE,
      HIGH_VALUE,
      DENSITY ,
      NUM_NULLS ,
      AVG_COL_LEN ,
      HISTOGRAM,
      NUM_BUCKETS
  FROM DBA_TAB_COL_STATISTICS
 WHERE OWNER='CMXBUSI'
 AND TABLE_NAME = 'T02';


方法二：
a.owner ||'.'||a.table_name name ,
a.column_name,
b.num_rows,
a.num_distinct Cardinality,
a.num_distinct/b.num_rows selectivity,
num_nulls,density,
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=upper('CMXBUSI')
and a.table_name=upper('T02')
and a.column_name=upper('ID');

方法三：
select table_name,column_name,num_distinct,low_value,high_value,histogram,density from dba_tab_columns where owner = 'SCOTT' and table_name = 'T1';

1.性能數(shù)據(jù)的收集
這個(gè)包的下面四個(gè)存儲(chǔ)過(guò)程分別收集 index、table、schema、database 的統(tǒng)計(jì)信息:
dbms_stats.gather_table_stats 收集表、列和索引的統(tǒng)計(jì)信息(當(dāng) cascade 為true 時(shí)，分析表、列（索引）信息)；
dbms_stats.gather_schema_stats 收集 schema 下所有對(duì)象的統(tǒng)計(jì)信息；
dbms_stats.gather_index_stats 收集索引的統(tǒng)計(jì)信息；
dbms_stats.gather_system_stats 收集系統(tǒng)統(tǒng)計(jì)信息
dbms_stats.gather_dictionary_stats： 所有字典對(duì)象的統(tǒng)計(jì)；
dbms_stats.GATHER_DATABASE_STATS：分析數(shù)據(jù)庫(kù)信息
dbms_stats.create_stat_table 創(chuàng)建存放統(tǒng)計(jì)信息表
dbms_stats.auto_sample_size 采樣值

2.性能數(shù)據(jù)的設(shè)置
設(shè)置表統(tǒng)計(jì)信息：dbms_stats.set_table_stats
設(shè)置索引統(tǒng)計(jì)信息：dbms_stats.set_index_stats
設(shè)置列統(tǒng)計(jì)信息：dbms_stats.set_column_stats

3.性能數(shù)據(jù)的刪除
刪除數(shù)據(jù)庫(kù)統(tǒng)計(jì)信息：dbms_stats.delete_database_stats
刪除用戶方案統(tǒng)計(jì)信息：dbms_stats.delete_schema_stats
刪除表統(tǒng)計(jì)信息：dbms_stats.delete_table_stats
刪除索引統(tǒng)計(jì)信息：dbms_stats.delete_index_stats
刪除列統(tǒng)計(jì)信息：dbms_stats.delete_column_stats

4.性能數(shù)據(jù)的備份和恢復(fù)
dbms_stats.EXPORT_COLUMN_STATS：導(dǎo)出列的分析信息
dbms_stats.EXPORT_INDEX_STATS：導(dǎo)出索引分析信息
dbms_stats.EXPORT_SYSTEM_STATS：導(dǎo)出系統(tǒng)分析信息
dbms_stats.EXPORT_TABLE_STATS：導(dǎo)出表分析信息
dbms_stats.EXPORT_SCHEMA_STATS：導(dǎo)出方案分析信息
dbms_stats.EXPORT_DATABASE_STATS：導(dǎo)出數(shù)據(jù)庫(kù)分析信息
dbms_stats.IMPORT_COLUMN_STATS：導(dǎo)入列分析信息
dbms_stats.IMPORT_INDEX_STATS：導(dǎo)入索引分析信息
dbms_stats.IMPORT_SYSTEM_STATS：導(dǎo)入系統(tǒng)分析信息
dbms_stats.IMPORT_TABLE_STATS：導(dǎo)入表分析信息
dbms_stats.IMPORT_SCHEMA_STATS：導(dǎo)入方案分析信息
dbms_stats.IMPORT_DATABASE_STATS：導(dǎo)入數(shù)據(jù)庫(kù)分析信息

1.檢查統(tǒng)計(jì)信息
set linesize 300;
SELECT OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED  FROM dba_tables where  TABLE_NAME  in (
'T_S_TYPE',
'T_EDF_TASK'
);

2.針對(duì)運(yùn)行效率慢的SQL情況，按表收集統(tǒng)計(jì)信息：
sqlplus / as sysdba <<EOF
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '用戶名',TABNAME => '表名' ,ESTIMATE_PERCENT => 0.1 ,METHOD_OPT => 'FOR ALL INDEXED COLUMNS' ,CASCADE => TRUE,DEGREE => 30);
EOF

ps：ESTIMATE_PERCENT => 0.1    為采樣比例

3.針對(duì)運(yùn)行效率慢的SQL情況，按用戶收集統(tǒng)計(jì)信息：
sqlplus / as sysdba <<EOF
exec DBMS_STATS.GATHER_schema_STATS(OWNNAME => 'CCICJY' ,ESTIMATE_PERCENT => 0.1 ,METHOD_OPT => 'FOR ALL INDEXED COLUMNS' ,CASCADE => TRUE,DEGREE => 30);
EOF

4.查看統(tǒng)計(jì)信息是否執(zhí)行
select  * from dba_scheduler_jobs;
select * from dba_scheduler_jobs  WHERE JOB_NAME='GATHER_STATS_JOB';

DBMS_STATS.GATHER_TABLE_STATS的語(yǔ)法如下:

DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname  VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);

參數(shù)說(shuō)明:

1.ownname:要分析表的擁有者

2.tabname:要分析的表名.

3.partname:分區(qū)的名字,只對(duì)分區(qū)表或分區(qū)索引有用.

4.estimate_percent:采樣行的百分比,取值范圍[0.000001,100],null為全部分析,不采樣. 
  常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默認(rèn)值,由oracle決定最佳取采樣值.

5.block_sapmple:是否用塊采樣代替行采樣.

6.method_opt:決定histograms信息是怎樣被統(tǒng)計(jì)的（抽樣方法）
method_opt的取值如下(默認(rèn)值為FOR ALL COLUMNS SIZE AUTO):
  for table：只統(tǒng)計(jì)表
  for all columns：分析所有的列
  for all indexes：只分析統(tǒng)計(jì)相關(guān)索引
  for all indexed columns：只統(tǒng)計(jì)有索引的表列
  for all hidden columns:分析所有隱藏列（函數(shù)索引等
  for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:統(tǒng)計(jì)指定列的histograms.
      N的取值范圍[1,254]; 
      REPEAT：上次統(tǒng)計(jì)過(guò)的histograms;
      AUTO：由oracle決定N的大小;
      skewonly：Oracle 確定需要收集檢查每個(gè)索引中每列值的分布
  例如：
  	method_opt=>'for all columns size skewonly'
  	method_opt=>'for all columns size repeat'
  	method_opt=>'for all columns size auto'
    
7.degree:決定并行度.默認(rèn)值為null.
  分析索引速度加快,根據(jù) cpu 數(shù)量來(lái)設(shè)置，一般在業(yè)務(wù)空閑的時(shí)候 degree 可設(shè)為 cpu 數(shù)量－1，繁忙的時(shí)候就再小點(diǎn)。

8.granularity:要收集的統(tǒng)計(jì)信息的粒度（僅在表已分區(qū)時(shí)才相關(guān)）
  根據(jù)將優(yōu)化的 SQL語(yǔ)句，優(yōu)化器可以選擇使用分區(qū)統(tǒng)計(jì)或全局統(tǒng)計(jì)，對(duì)于大多數(shù)系統(tǒng)這兩種統(tǒng)計(jì)都是很
重要的，Oracle 推薦將 GRANULARITY 設(shè)置為 AUTO 同時(shí)收集全部信息。  
			①'AUTO'-根據(jù)分區(qū)類型確定粒度。這是默認(rèn)值。
			②'ALL' -收集所有（子分區(qū)，分區(qū)和全局）統(tǒng)計(jì)信息
			③'GLOBAL' -收集全球統(tǒng)計(jì)數(shù)據(jù)
			④'GLOBAL AND PARTITION'-收集全局和分區(qū)級(jí)別的統(tǒng)計(jì)信息。即使它是一個(gè)復(fù)合分區(qū)對(duì)象，也不會(huì)收集任何子分區(qū)級(jí)別統(tǒng)計(jì)信息。
			⑤'PARTITION '-收集分區(qū)級(jí)別的統(tǒng)計(jì)信息
			⑥'SUBPARTITION' -收集子分區(qū)級(jí)別的統(tǒng)計(jì)信息
			⑦'DEFAULT'-收集全局和分區(qū)級(jí)別的統(tǒng)計(jì)信息。該選項(xiàng)已過(guò)時(shí)，并且當(dāng)前受支持，但僅出于遺留原因才包含在文檔中。您應(yīng)該使用“ GLOBAL AND PARTITION”來(lái)實(shí)現(xiàn)此功能。請(qǐng)注意，默認(rèn)值現(xiàn)在是'AUTAUTO'。

9.cascade:是收集索引的信息.默認(rèn)為FALSE.

10.stattab:用戶統(tǒng)計(jì)信息表標(biāo)識(shí)符，用于描述將當(dāng)前統(tǒng)計(jì)信息保存在何處
  statid如果多個(gè)表的統(tǒng)計(jì)信息存儲(chǔ)在同一個(gè)stattab中用于進(jìn)行區(qū)分，標(biāo)識(shí)符
  statown存儲(chǔ)統(tǒng)計(jì)信息表的擁有者.包含的架構(gòu)stattab（如果不同于ownname）
以上三個(gè)參數(shù)若不指定,統(tǒng)計(jì)信息會(huì)直接更新到數(shù)據(jù)字典.

11.no_invalidate: 通過(guò)不同的參數(shù)配置，可以實(shí)現(xiàn)對(duì) Oracle 失效共享游標(biāo)行為的控制TRUE，F(xiàn)ALSE
  如果取值為 true，表示不進(jìn)行游標(biāo)失效動(dòng)作，原有的 shared cursor 保持原有狀態(tài)。
  如果取值為 false，表示將統(tǒng)計(jì)量對(duì)象相關(guān)的所有 cursor 全部失效。
  如果設(shè)置為auto_invalidate，根據(jù)官方文檔，Oracle 自己決定 shared cursor 失效動(dòng)作。
  從 10G 開(kāi)始，Oracle 就將 auto_invalidate 作為默認(rèn)的統(tǒng)計(jì)量收集行為。
  select dbms_stats.get_param(pname => 'no_invalidate') from dual;

12.force:即使表鎖住了也收集統(tǒng)計(jì)信息.

13.options 分析模式
使用 4 個(gè)預(yù)設(shè)的法子之一，這個(gè)選項(xiàng)能把握 Oracle 統(tǒng)計(jì)的刷新方法：
  gather——重新分析整個(gè)（Schema）。
  gather empty——只分析目前還沒(méi)有統(tǒng)計(jì)的表。
  gather stale——只重新分析修改量超過(guò) 10%的表（這些修改包含插入、更新和刪除）。
  gather auto——重新分析以前沒(méi)有統(tǒng)計(jì)的對(duì)象，以及統(tǒng)計(jì)數(shù)據(jù)過(guò)期（變臟）的對(duì)象。
注意，使用 gather auto 相似于組合使用 gather stale 和 gather empty。注意，不論 gather stale 仍是 gather auto，都請(qǐng)求進(jìn)行監(jiān)視。假如你施行一個(gè)alter table xxx monitoring 命令，Oracle 會(huì)用 dba_tab_modifications 視圖來(lái)跟蹤發(fā)生變動(dòng)的表。這樣一來(lái)，你就確實(shí)地知道，自從上一次剖析統(tǒng)計(jì)數(shù)據(jù)以來(lái)，發(fā)生了多少次插入、更新和刪除操作。

14.objlist: 指定對(duì)象列表
15.obj_filter_list：A list of object filters
16.gather_sys：只收集 sys 的對(duì)象。
17.stattype：統(tǒng)計(jì)信息類型。允許的唯一值是DATA。

直方圖收集方法
1. 語(yǔ)法格式
  對(duì)于dbms_stats包，是通過(guò)指定method_opt參數(shù)實(shí)現(xiàn)的，該參數(shù)可接受值如下：
1.1統(tǒng)計(jì)所有列的histograms(直方圖).
for all [indexed | hidden] columns [size_clause]

1.2統(tǒng)計(jì)指定列的histograms(直方圖).
for columns [size_clause] column | attribute [size_clause] [,column | attribute [size_clause]...]
  例如： 
    for columns column  size 1
    for columns column  size auto
  其中size_clause必須符合以下格式:size [整數(shù)值 | repeat | auto | skewonly]

      整數(shù)值：直方圖的Bucket數(shù)，范圍為[1,254]，為1表示刪除列上直方圖信息
      repeat：只對(duì)已有直方圖的列收集直方圖信息
      auto：由oracle決定是否對(duì)列收集直方圖，以及使用哪類直方圖。oracle默認(rèn)只對(duì)用過(guò)的列（where條件中出現(xiàn)過(guò)的列）自動(dòng)收集直方圖統(tǒng)計(jì)信息，oracle會(huì)在sys.col_usage$基表中記錄各列的使用情況。在自動(dòng)收集直方圖統(tǒng)計(jì)信息時(shí)先查該表，如果列未被使用過(guò)，則不會(huì)收集。
      skewonly：只對(duì)傾斜列收集直方圖 

2.距離
2.1 對(duì)表所有列以auto方式收集直方圖
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',method_opt=> 'for all columns size auto');

2.2 對(duì)表所有有索引的列以auto方式收集直方圖
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',method_opt=> 'for all indexed columns size auto');

2.3 對(duì)表的empno和deptno列以auto方式收集直方圖
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',method_opt=> 'for columns size auto empno deptno');

2.4 對(duì)表的empno和deptno列收集直方圖，并指定Bucket數(shù)為10
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',method_opt=> 'for columns size 10 empno deptno');

2.5 對(duì)表的empno和deptno列收集直方圖，指定empno列Bucket數(shù)為10，deptno列Bucket數(shù)為5
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',method_opt=> 'for columns empno size 10 deptno size 5')
 
3.查詢直方圖
SELECT COLUMN_NAME,NOTES,HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'SALES2'; 

select * from user_histograms;

DBMS_STATS.GATHER_DATABASE_STATS (
  estimate_percent NUMBER DEFAULT to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
  block_sample BOOLEAN DEFAULT FALSE,
  method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
  degree NUMBER DEFAULTto_degree_type(get_param('DEGREE')),
  granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
  cascade BOOLEAN DEFAULT
  to_cascade_type(get_param('CASCADE')),
  stattab VARCHAR2 DEFAULT NULL,
  statid VARCHAR2 DEFAULT NULL,
  options VARCHAR2 DEFAULT 'GATHER',
  objlist OUT ObjectTab,
  statown VARCHAR2 DEFAULT NULL,
  gather_sys BOOLEAN DEFAULT TRUE,
  no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
    get_param('NO_INVALIDATE')),
  obj_filter_list ObjectTab DEFAULT NULL);

SQL>exec DBMS_STATS.gather_database_stats;
SQL>exec DBMS_STATS.gather_database_stats(estimate_percent => 15);
SQL>exec DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent=>10,Degree=>8, Cascade=>TRUE, Granularity=>'ALL');

SQL> exec DBMS_STATS.gather_database_stats;
PL/SQL procedure successfully completed

SQL> select distinct sid, serial# from v$session_longops where opname like'%Gather%';
       SID    SERIAL#
---------- ----------
       459      20726

       select distinct sid, serial# from v$session_longops where opname like'%gather_database_stats%';

SQL> alter system kill session '459,20726';
System altered

DBMS_STATS.GATHER_SCHEMA_STATS (
  ownname VARCHAR2,
  estimate_percent NUMBER DEFAULT to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
  block_sample BOOLEAN DEFAULT FALSE,
  method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
  degree NUMBER DEFAULTto_degree_type(get_param('DEGREE')),
  granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
  cascade BOOLEAN DEFAULTto_cascade_type(get_param('CASCADE')),
  stattab VARCHAR2 DEFAULT NULL,
  statid VARCHAR2 DEFAULT NULL,
  options VARCHAR2 DEFAULT 'GATHER',
  objlist OUT ObjectTab,
  statown VARCHAR2 DEFAULT NULL,
  no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
    get_param('NO_INVALIDATE')),
  force BOOLEAN DEFAULT FALSE,
  obj_filter_list ObjectTab DEFAULT NULL);	
 GATHER_SCHEMA_STATS

begin
dbms_stats.gather_schema_stats(ownname => 'CMXBUSI',
                                options => 'gather',
                                estimate_percent => 100,
                                method_opt => 'for all indexed columns',
                                degree => 2);
end;
/

begin
dbms_stats.gather_schema_stats(ownname => 'CMXBUSI',
                                options => 'gather auto',
                                estimate_percent =>dbms_stats.AUTO_SAMPLE_SIZE,
                                method_opt => 'for all columns size auto',
                                cascade => true,
                                degree => 2);
end;
/

begin
dbms_stats.gather_schema_stats(ownname => 'CMXBUSI',
                                estimate_percent => 10,
                                cascade => true,
                                granularity => 'all',
                                degree => 2);
end;
/

dbms_stats.gather_table_stats (
  ownname varchar2,
  tabname varchar2,
  partname varchar2 default null,
  estimate_percent number default to_estimate_percent_type
  (get_param('estimate_percent')),
  block_sample boolean default false,
  method_opt varchar2 default get_param('method_opt'),
  degree number default to_degree_type(get_param('degree')),
  granularity varchar2 default get_param('granularity'),
  cascade boolean defaultto_cascade_type(get_param('cascade')),
  stattab varchar2 default null,
  statid varchar2 default null,
  statown varchar2 default null,
  no_invalidate boolean default to_no_invalidate_type (
    get_param('no_invalidate')),
  force boolean default false);

SQL>select table_name, blocks, empty_blocks, num_rows from SQL>dba_tables where owner = 'SCOTT' and table_name = 'T1';
SQL>select table_name, blocks, empty_blocks, num_rows,LAST_ANALYZED fromdba_tables where owner = 'SCOTT' and table_name = 'T1';
SQL>select table_name,sample_size,num_rows,round(sample_size/num_rows*100,1)"%" from dba_tables where owner = 'SCOTT' and table_name = 'T1';
SQL>select table_name,sample_size,num_rows,round(sample_size/num_rows*100,1)"%" from dba_tables where owner = 'SCOTT' order by table_name;

SQL>select table_name,column_name,last_analyzed,num_distinct,low_value,high_value,histogram,density from dba_tab_columns where owner = 'SCOTT' and table_name ='T1';

SQL>select table_name,index_name,last_analyzed,leaf_blocks,blevel,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows fromdba_indexes where owner = 'SCOTT' and table_name = 'T1';

SQL>analyze table ITPUX_M10 delete statistics;
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
                               >tabname => 'T1',
                               >estimate_percent =>dbms_stats.AUTO_SAMPLE_SIZE,
                               >method_opt => 'for all indexed columns',
                               >cascade => true,
                               >degree => 2);
>end;
>/

SQL>analyze table ITPUX_M10 delete statistics;
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
                               >tabname => 'T1',
                               >estimate_percent => 15,
                               >method_opt => 'for table',
                               >cascade => false,
                               >degree => 2);
>end;
>/

SQL>analyze table T1 delete statistics;
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
                               >tabname => 'T1',
                               >estimate_percent => 100,
                               >method_opt => 'for columns size 1 object_name status',
                               >cascade => false,
                               >degree => 2);
>end;
>/

SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
                               >tabname => 'T1',
                               >estimate_percent => 100,
                               >cascade => true,
                               >degree => 2);
>end;
>/

SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
                               >tabname => 'T1',
                               >estimate_percent => 18,
                               >cascade => true,
                               >granularity => 'ALL',    --全局的表，分區(qū)表，子分區(qū)表
                               >degree => 2);
>end;
>/

SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
                               >tabname => 'T1',
                               >partname => '分區(qū)名',
                               >estimate_percent => 5,
                               >cascade => true,
                               >granularity => 'PARTITION',
                               >method_opt => 'for all indexes columns',
                               >degree => 2);
>end;
>/

SQL>begin
>dbms_stats.delete_table_stats (ownname => 'SCOTT',tabname >=>'T1');
>end;
>/

DBMS_STATS.GATHER_INDEX_STATS (
  ownname VARCHAR2,
  indname VARCHAR2,
  partname VARCHAR2 DEFAULT NULL,
  estimate_percent NUMBER DEFAULT to_estimate_percent_type (GET_PARAM('ESTIMATE_PERCENT')),
  stattab VARCHAR2 DEFAULT NULL,
  statid VARCHAR2 DEFAULT NULL,
  statown VARCHAR2 DEFAULT NULL,
  degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
  granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
  no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')),
  force BOOLEAN DEFAULT FALSE);

GATHER_DICTIONARY_STATS：
DBMS_STATS.GATHER_DICTIONARY_STATS (
  comp_id VARCHAR2 DEFAULT NULL,
  estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
  block_sample BOOLEAN DEFAULT FALSE,
  method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
  degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
  granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
  cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
  stattab VARCHAR2 DEFAULT NULL,
  statid VARCHAR2 DEFAULT NULL,
  options VARCHAR2 DEFAULT 'GATHER AUTO',
  objlist OUT ObjectTab,
  statown VARCHAR2 DEFAULT NULL,
  no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
  obj_filter_list ObjectTab DEFAULT NULL);

SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS
(estimate_percent=>100, Degree=>8, Cascade=>TRUE, Granularity=>'ALL');

動(dòng)態(tài)性能表統(tǒng)計(jì)信息采集GATHER_FIXED_OBJECTS_STATS
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (
  stattab VARCHAR2 DEFAULT NULL,
  statid VARCHAR2 DEFAULT NULL,
  statown VARCHAR2 DEFAULT NULL,
  no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')));

SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

SQL>exec dbms_stats.gather_fixed_objects_stats;

SQL>exec DBMS_STATS.GATHER_TABLE_STATS ('SYS', 'X$KSUSE');
SQL>exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'RECYCLEBIN$');

SQL>select count(*) from sys.tab_stats$;
SQL>select num_rows, last_analyzed from user_tab_statistics where table_name ='X$KSUSE';

SQL>select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$LOCK';
or:
SQL>select dbms_metadata.get_ddl('VIEW', 'DBA_EXTENTS') from dual;

select table_name, blocks, empty_blocks, num_rows
from dba_tables
where owner = 'CMXBUSI'
and table_name = 'T02';

select table_name,
column_name,
num_distinct,
low_value,
high_value,
histogram,
density
from dba_tab_columns
where owner = 'CMXBUSI'
and table_name = 'T02';

select * from dba_indexes
leaf_blocks,              -- 索引中葉子塊數(shù)據(jù)
blevel,                   -- B 樹(shù)索引等級(jí)
distinct_keys,            -- 不同索引值的數(shù)量
avg_leaf_blocks_per_key,  -- 索引中的每個(gè)值平均在多少個(gè)葉子塊中，如果是主鍵/唯一就是 1.
avg_data_blocks_per_key,  -- 通過(guò)索引中的一個(gè)值指向表中的數(shù)據(jù)塊，應(yīng)數(shù)據(jù)塊數(shù)量的平均值。
clustering_factor,        -- 聚集因子，越小越好，越大越不好。num_rows --索引的行數(shù)量。

SELECT  TABLE_NAME,INDEX_NAME,LEAF_BLOCKS,blevel,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR
from dba_indexes
WHERE TABLE_OWNER='CMXBUSI'
and table_name = 'T02';

analyze table t1 compute statistics for table;

analyze table T1 compute statistics for all columns;

analyze table T1 compute statistics for all indexed columns;

analyze table T1 compute statistics;

analyze table T1 compute statistics for all indexes;

analyze table T1 compute statistics for table for all indexes for all columns;

analyze table t1 delete statistics;

analyze table T1 validate structure;

analyze table CMXBUSI.T01 validate structure;