乐趣区

关于oracle:Oracle-统计信息

jiezi 
-- 按分区收集统计信息
exec dbms_stats.gather_table_stats('&USERNAME','&PARTITION_TABLE','&PART_NAME',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE,GRANULARITY =>'PARTITION',NO_INVALIDATE=>false,degree=>4);

-- 指定表收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('&USERNAME','&TABLE_NAME',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>false,cascade=>TRUE,degree=>4);

EXEC DBMS_STATS.GATHER_TABLE_STATS('IMES_X_DG','FOX_MODULE_DATA',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>false,cascade=>TRUE,degree=>1);

-- auto 形式
EXEC DBMS_STATS.GATHER_TABLE_STATS('GALILEO','WIP_PN_INPUT_TRACKING',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'for columns WO_NO size auto',NO_INVALIDATE=>false,cascade=>TRUE,degree=>2);
EXEC DBMS_STATS.GATHER_TABLE_STATS('VERA','WIP_WO_PICK_LIST',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'for columns WO_NO size auto',NO_INVALIDATE=>false,cascade=>TRUE,degree=>2);


-- 指定索引收集统计信息
exec dbms_stats.gather_index_stats(ownname =>'&USERNAME',indname=>'&INDEX_NAME',estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,NO_INVALIDATE=>false,degree =>4);

-- 依照 Schema 收集统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('&SCHEMA_NAME',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>FALSE,DEGREE=>4,CASCADE=>TRUE);

-- 按 DB 收集统计信息
EXEC DBMS_STATS.gather_database_stats(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>FALSE,DEGREE=>4,CASCADE=>TRUE);

-- 直方图
Oracle 通过指定 dbms_stats 的 method_opt 参数，来创立直方图。在 method_opt 子句中有三个相干选项，即 skewonly、repeat 和 auto。“skewonly”选项，它的时间性很强，因为它查看每个索引中每列值的散布。如果 dbms_stats 发现一个索引中具备不均匀分布的列，它将为该索引创立直方图，以帮忙基于老本的 SQL 优化器决定是应用索引还是全表扫描拜访
begin
dbms_stats. gather_table_stats(wnname           => '',
tabname          => '',
estimate_percent => dbms_stats.auto_sample_size,
method_opt       => 'for all columns size skewonly',
cascade          => true,
degree           => 4);
end;
/

-- 查看直方图
SELECT COLUMN_NAME, NUM_DISTINCT,NUM_BUCKETS,LAST_ANALYZED, HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = '&TABLE_NAME';

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 = '&USERNAME'
   and a.table_name = '&TABLE_NAME';

/*
NUM_ROWS 示意总行数;
CARDINALITY 示意基数;
SELECTIVITY 示意选择性 选择性在 10% 以上都比拟高了;
HISTOGRAM 示意直方图的类型;
FREQUECNCY 频率直方图、当列中 Distinct_keys 较少 (小于 254)，如果不手工指定直方图桶数(BUCKET)，Oracle 就会主动的创立频率直方图，并且桶数(BUCKET) 等于 Distinct_Keys;
HEIGHT BALANCED 高度均衡直方图 当列中 Distinct_keys 大于 254，如果不手工指定直方图桶数(BUCKET)，Oracle 就会主动的创立高度均衡直方图;
NONE 示意未收集直方图;
NUM_BUCKETS 示意桶数;
*/
oracle 个别查问数据行数在 5% 以下心愿走索引

-- 直方图的一些名词
NDV：number distinct of values , 比方一个列蕴含 10,200,300，则 NDV 就是 3
n：代表直方图的桶，默认是 254
p：代表外部变量百分比阈值，(1–(1/n))* 100. For example, if n = 254（11g 及以下版本限度,12c 调整为 2048）, then p is 99.6
-- 官网形容
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/histograms.html#GUID-EED37588-DEB3-4426-92D1-76A1B73888B4

-- 删除列上的直方图信息
BEGIN
dbms_stats.delete_column_stats(ownname=>'&USERNAME', tabname=>'&TABLE_NAME', colname=>'&COLUMN_NAME', col_stat_type=>'HISTOGRAM');
END;
/


EXECUTE dbms_stats.delete_column_stats(ownname => 'GALILEO',tabname => 'WIP_PN_INPUT_TRACKING',colname => 'PN_CODE',col_stat_type => 'HISTOGRAM');


-- 手动指定 prod_id 间接图的桶数为 1
BEGIN
dbms_stats.set_table_prefs('&USERNAME','&TABLE_NAME',
'METHOD_OPT',
'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 PROD_ID');
END;
/

-- 查看分区统计信息
col TB_NAME for a30;
col HIGH_VALUE for a300;
set lines 250;
select TABLE_OWNER||'.'||TABLE_NAME as TB_NAME,PARTITION_NAME,LAST_ANALYZED,HIGH_VALUE from DBA_TAB_PARTITIONS where TABLE_NAME like '&PARTITION_TABLE_NAME';


-- 查看一般表统计信息
-- dba_tab_statistics
col full_name for a35;
col PARTITION_NAME for a15;
select owner||'.'||TABLE_NAME full_name,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_tab_statistics where table_name = upper('&TABLE_NAME');

-- dba_tables 统计信息
col full_name for a35;
col TABLESPACE_NAME for a15;
col DEGREE for a10;
select owner||'.'||TABLE_NAME full_name,TABLESPACE_NAME,DEGREE,PARTITIONED,NUM_ROWS,BLOCKS,LAST_ANALYZED from dba_tables where table_name = upper('&TABLE_NAME');

-- 查看索引统计信息
col full_name for a35;
col TABLESPACE_NAME for a15;
col TABLE_NAME for a20;
col DEGREE for a10;
select owner||'.'||INDEX_NAME full_name,TABLE_NAME,TABLESPACE_NAME,DEGREE,PARTITIONED,NUM_ROWS,LAST_ANALYZED from dba_indexes where index_name = upper('&INDEX_NAME');
退出移动版