关于oracle:Oracle-统计信息

-- 按分区收集统计信息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 优化器决定是应用索引还是全表扫描拜访begindbms_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就是3n： 代表直方图的桶，默认是254p： 代表外部变量百分比阈值， (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--删除列上的直方图信息BEGINdbms_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间接图的桶数为1BEGINdbms_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_statisticscol 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');