Inside Automating Histogram Sampling!!

Page: 1 2

Oracle 11g extended optimizer statistics is provided as an alternative to dynamic_sampling for estimating result set sizes. One exciting feature of dbms_stats is the ability to automatically look for columns that should have histograms, and create the histograms.  Multi-bucket histograms add a huge parsing overhead to SQL statements, and histograms should only be used when the SQL will choose a different execution plan based upon the column value.

Histogram Generation:

Oracle uses the method_opt parameter of dbms_stats to aid in intelligent histogram generation.  Other important options within the method_opt clause include

method_opt= “skewonly”:

The skewonly option is very time-intensive because it examines the distribution of values for every column within every index. 

If dbms_stats discovers an index whose columns are unevenly distributed, it will create histograms for that index to aid the cost-based SQL optimizer in making a decision about index vs. full-table scan access.  For example, if an index has one column that is in 50% of the rows, a full-table scan is faster than and index scan to retrieve these rows.

Histograms are also used with SQL that has bind variables and SQL with cursor_sharing enabled.  In these cases, the CBO determines if the column value could affect the execution plan, and if so, replaced the bind variable with a literal and performs a hard parse.

--**********************************************************
-- SKEWONLY option – Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--**********************************************************

begin
   dbms_stats.gather_schema_stats(
      ownname          => 'SCOTT',
      estimate_percent => dbms_stats.auto_sample_size,
      method_opt       => 'for all columns size skewonly',
      degree           => 7
   );
end;
/

method_opt= “ repeat”:

If one-time detailed analysis is done then the re-analyze task will be less resource intensive with the REPEAT option. 

Using the repeat option will only re-analyze indexes with existing histograms, and will not search for other histograms opportunities.  This is the way that you will re-analyze you statistics on a regular basis.

--************************************************************
-- REPEAT OPTION - Only re-analyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
job will use the “repeat” option.  The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only re-analyze histograms for
-- indexes that have histograms.
--***********************************************************
begin
   dbms_stats.gather_schema_stats(
      ownname          => 'SCOTT',
      estimate_percent => dbms_stats.auto_sample_size,
      method_opt       => 'for all columns size repeat',
      degree           => 7
   );
end;
/  

Page: 1 2

 More Database Articles
   Database Security: Step by step guideline
   Oracle – SQL Guide for Parallel Stored Procedures
   Performance Impact of Low Cardinality Leading Columns in an Index!!
   DBA Tips when Oracle Account is Locked!!
   Oracle Tips for Monitoring Oracle Import Speed!!