Oracle 12c release 1倾斜数据柱状图

我们正在12.1.0.2.0数据库上运行Oracle Applications 12.2.4。执行以下查询时:

select DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET') as autostats_target,
       DBMS_STATS.GET_PREFS('CASCADE') as cascade,
       DBMS_STATS.GET_PREFS('DEGREE') as degree,
       DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT') as estimate_percent,
       DBMS_STATS.GET_PREFS('METHOD_OPT') as method_opt,
       DBMS_STATS.GET_PREFS('NO_INVALIDATE') as no_invalidate,
       DBMS_STATS.GET_PREFS('GRANULARITY') as granularity,
       DBMS_STATS.GET_PREFS('PUBLISH') as publish,
       DBMS_STATS.GET_PREFS('INCREMENTAL') as incremental,
       DBMS_STATS.GET_PREFS('STALE_PERCENT') as stale_percent
from   dual

我得到:

"AUTOSTATS_TARGET","CASCADE","DEGREE","ESTIMATE_PERCENT","METHOD_OPT","NO_INVALIDATE","GRANULARITY","PUBLISH","INCREMENTAL","STALE_PERCENT"
"AUTO","DBMS_STATS.AUTO_CASCADE","NULL","DBMS_STATS.AUTO_SAMPLE_SIZE","FOR ALL COLUMNS SIZE AUTO","DBMS_STATS.AUTO_INVALIDATE","AUTO","TRUE","FALSE","10"

但是,当我跑步时:

select distinct histogram
from   user_tab_col_statistics

我只得到 NONE

Oracle应用程序实例怎么可能没有需要直方图的倾斜表?还是我不了解设置?

此外,当您需要列上的柱状图时,是否必须使用 method_opt => 'for all columns size skewonly' ? 如何为所有列指定auto,为一列指定skew?

我非常希望直方图能够带来潜在的巨大速度提升,我很惊讶Oracle应用程序在默认情况下并没有提供这一点。每天晚上都会运行一个收集模式统计信息的过程,这可能是因为其中的代码非常旧,并且它会终止任何dbms\U stats调用吗?我专门创建了下面的索引,它应该有一个柱状图。

create index xxpqh_ss_trans_history_idx1 on hr.pqh_ss_transaction_history (process_name, nvl(selected_person_id, -1)) compress 1 tablespace apps_ts_tx_idx;

exec dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'PQH_SS_TRANSACTION_HISTORY', cascade => true, method_opt => 'for all columns size skewonly');