Skip to main content

Statistics, Histograms, Baselines, OH MY (in Oracle 12c)

Janis Griffin (SolarWinds/CONFIO)
@DoBoutAnything

Cost Based Optimizer

  • first released in Oracle 7.3 (allowed for things like HASH Joins, partition, etc
  • 12c -- Adaptive Plans, Stats and Auto re-optimized
    • sql plan directives

12c DBMS_STATS package

  • DBMS_STATS rewritten in 11g release
    • use only these parameters
      • schema_name
      • table_name
      • Partition_name
      • Degree (of parallelism)
    • DON'T USE extimate_percent (default is auto_samples_size) 
    • DON'T USE analyze <- for the love of Jebus!
      • analyze will prevent histograms from being calculated...at all
  • REPORT_GATHER_*_STATS to show what will happen
    • won't change stats
    • report on status before they are enabled
  • Global Temp tables can have Stats on a per/session

Adaptive Stats

  • Complex queries require more info than base table stats
  • Dynamic Stats (dynamic sampling)
    • enabled by default in 12c
    • happens automatically for tables with no stats (ie after CTAS)
    • gathered during parsing of SQL
    • ALTER SESSION SET optimizer_dynamic_sampling = 11;
      • or add to init.ora
    • parse time will take longer, but results will be persisted
  • automatic re-optimization 
    • stats feedback
      • identifies missing stats or unusable stats
      • or complex predicates when stats can't be used
    • new columns in V$SQL called 
        • IS_REOPTIMIZABLE
        • IS_RESOLVED_ADAPTIVE_PLAN
    • New columns in V$SQL_SHARED_CURSOR
      • will show reasons for optimizations 
      • PK = SQL_ID
    • performance feedback (auto parallism)
      • PARALLEL_DEGREE_POLICY=ADAPTIVE
        • adaptive new in 12c, parameter available in 11g
      • PARALLEL_MIN_TIME_THRESHOLD
        • used to govern parallelism
      • automatic decisions by database to use parallelism
      • Accurate system stats seem to be important here


New Histograms

  • Histograms tell database of data skew
  • Histogram tables
    • [DBA|ALL|USER]_TAB_HISTOGRAMS
    • [DBA|ALL|USER]_HISTOGRAMS
  • ONLY CREATED WHEN DBMS_STATS is run with AUTO_SAMPLE_SIZE
  • New Types of Histograms in 12c
    • Top Frequency
    • Hybrid


SQL Plan Management

  • SQL Plan Directives
    • additional instructions for missing column group stats or histograms
    • not tied to specific SQL statement
    • defined on a query expression
    • Create in SHARED_POOL and written to SYSAUX
      • DBA_SQL_PLAN_DIRECTIVES
      • DBA_SQL_PLAN_DIR_OBJECTIVES
    • Can be a performance hit, as reparsing. 
    • Use directive to identify, and then add column group stats
    • DBMS_SPD
      • can't manually create directives



select /*+ gather _plan_statistics */ blah from dual;
DBMS_XPLAN.display_cursor(SQL_ID,2,format=>'ALLSTATS LAST')

  • will display actual cardinality vs Estimated cardinality
  • can help to identify 'non-representative stats'
dbms_stats.create_extended_stats('schema','table',('col1','col2',etc));
dbms_stats.gather_table_stats('schema','table); -- to populate new extended stats


Labels:

Comments

Post a Comment