EXEC DBMS_STATS, gather_table_STATS (' &username ',' &partition_table ',' &part_name ',estimate_percent ',' &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 ',' &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); 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); -- Collect statistical information according to 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); EXEC DBMS_STATS. Gather_DATABASE_STATS (ESTIMATE_Percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL, EXEC DBMS_STATS COLUMNS SIZE 1',NO_INVALIDATE=>FALSE,DEGREE=>4,CASCADE=>TRUE); Oracle creates a histogram by specifying the DBMS_STATS method_opt parameter. There are three related options in the method_opt clause, namely skewonly, repeat, and auto. The 'skewonly' option, which is very time-sensitive because it checks the distribution of values for each column in each index. If DBMS_STATS finds an index with unevenly distributed columns, it creates a histogram for that index, 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, SELECT COLUMN_NAME, 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 C. Selectivity means SELECTIVITY above 10% is high; Histogram shows the type of HISTOGRAM; Frequecncy frequency histograms. When a column is few Distinct_keys (less than 254), Oracle automatically creates a Distinct_keys histograph without manually specifying a BUCKET number and Distinct_keys equal to Distinct_keys; Mysql > Distinct_keys for a column that is greater than 254; then Oracle will automatically create a Distinct_keys histograph if the number of BUCKET is not specified manually. None means histograms were not collected. NUM_BUCKETS represents the number of buckets; NDV: Numbers distinct of values NDV: Numbers distinct of values NDV: Numbers distinct of values NDV: Numbers distinct of values NDV: Numbers distinct of values NDV: Numbers distinct of values NDV: Numbers distinct of values NDV: Numbers distinct of values NDV: Numbers distinct of values Represents the percentage threshold of internal variables, (1 -- (1/n))* 100. For example, if n = 254 (1 -- (1/n)), Then P IS 99.6 -- Official description https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/histograms.html#GUID-EED37588-DEB3-4426-92D1-76A1B BEGIN dbms_stats. Delete_column_stats (ownName =>' &username ', tabName =>' &table_name ', 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'); 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 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'); 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');