Search code examples
oraclehistogramtable-statistics

Oracle 12c release 1 Histograms for skewed data

We are running Oracle Applications 12.2.4 on a 12.1.0.2.0 database. When I do the following query:

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

I get:

"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"

However when I run:

select distinct histogram
from   user_tab_col_statistics

I only get NONE

How is it possible that an Oracle Application instance can have no tables with skews that need histograms? Or am I not understanding the settings?

Also when you want a histogram on a column do you have to use method_opt => 'for all columns size skewonly'? How can you specify auto for all columns and skew for one column?

I would really like the potential huge speed increase that histograms can bring and I am surprised that Oracle Applications does not provide this by default. There is a Gather Schema Statistics process that runs every night, could it be that the code in that is very old and that it kills any dbms_stats calls? I specifically created the following index which should have a histogram.

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');
Solution

  • Oracle Applications uses its own mechanisms for statistics collection and you should be using dbms_stats directly.

    "Oracle E-Business Suite statistics should only be gathered using FND_STATS or the Gather Statistics concurrent request. Gathering statistics with DBMS_STATS or the desupported ANALYZE command may result in suboptimal executions plans for E-Business Suite"

    Please refer to the following whitepaper for recommendations:

    Best Practices for Gathering Statistics with Oracle E-Business Suite (MOS Note 1586374.1)