RE: Is there a way to skip the creation of a temporal table when

From: Randolf Geist <info_at_www.sqltools-plusplus.org>
Date: Wed, 5 Oct 2016 01:17:48 +0200
Message-ID: <39add4cc-aff2-a033-1526-7a7bc43ec611_at_www.sqltools-plusplus.org>

Just for clarification, the last mentioned option (separate call with just the columns specified that should get histograms) can be combined into a single DBMS_STATS call, so the following is a perfectly valid METHOD_OPT expression:

"FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 COLA,COLB" This way COLA and COLB get a histogram (either a frequency or height-balanced in 11.2 depending on the number of distinct column values) but all other columns don't get one - and it uses the tiny sample size Jonathan mentioned (typically 5500 rows), and this is all done in a single call, avoiding the second call to DBMS_STATS that will collect table level stats again (avg row size, number of rows etc.)

To the OP: Don't use FOR ALL COLUMNS SIZE SKEWONLY, except you're sure this is a good idea, as it generates histograms on any column that qualifies for a histogram based on value distribution although it might not be used in any predicate so you accumulate potentially a huge number of useless histograms in your data dictionary, also Oracle has to generate a histogram on every column to find out whether it needs to keep it or not, so it also consumes more time and resources at gather stats time.

Don't use ESTIMATE_PERCENT => 2 unless you're sure this is a good idea, from 11.1 on you should use NULL as ESTIMATE_PERCENT which gives accurate statistics (100% sample) but takes only a fraction of the time of the old "100" percent ((not so) new approximate NDV algorithm avoiding sorts)

Of course, keep in mind with all these recommendations - if you change the way stats are gathered you potentially end up with plan changes. Even if the changed method of gathering statistics in principle should provide more accurate statistics you still might end up with plan regressions hence this needs to be tested and treated carefully.

Randolf

> If you then think these two columns need a histogram you either live with an expensive insert and histogram generation, or you write some SQL that creates the histograms from scratch (or, if you're amazingly lucky) use method_opt in a separate call to specify just the columns that you want to have histograms and live with the tiny sample that Oracle will use for generating a histogram with auto_sample_size.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 05 2016 - 01:17:48 CEST