Thanks for the question, John.

Asked: September 04, 2007 - 5:13 pm UTC

Last updated: July 28, 2011 - 7:41 pm UTC

Version: 10.2.2

Viewed 10K+ times! This question is

You Asked

How do you actually create statistics without creating histograms? I know you can supply the auto or the actual number of buckets using the command in dbms_stats.

Is a bucket of 1 the same as no histograms? There is a disagreement (with me and another DBA) on the way to do this.

thanks.

and Tom said...

ops$tkyte%ORA10GR2> create table t as select * from all_users;

Table created.

ops$tkyte%ORA10GR2> column column_name format a15
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select column_name, count(*) from user_tab_histograms where table_name = 'T' group by column_name;

COLUMN_NAME       COUNT(*)
--------------- ----------
USER_ID                  2
CREATED                  2
USERNAME                 2

ops$tkyte%ORA10GR2> select * from t where username = 'x';

no rows selected

ops$tkyte%ORA10GR2> select * from t where user_id = -1;

no rows selected

ops$tkyte%ORA10GR2> select * from t where created = sysdate;

no rows selected

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select column_name, count(*) from user_tab_histograms where table_name = 'T' group by column_name;

COLUMN_NAME       COUNT(*)
--------------- ----------
USER_ID                  2
CREATED                 30
USERNAME                 2

Ok, so by default in 10g, Oracle uses size auto to gather column stats - that is, it looks at predicates you've used and the data in the columns and figures out what histograms to gather

If you don't want that, you can just get endpoints (one bucket - high and low values and number of values - this is useful stuff generally) by doing this:

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size 1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select column_name, count(*) from user_tab_histograms where table_name = 'T' group by column_name;

COLUMN_NAME       COUNT(*)
--------------- ----------
USER_ID                  2
CREATED                  2
USERNAME                 2

Now, if you really really didn't want ANY column information, you could do this:

ops$tkyte%ORA10GR2> exec dbms_stats.delete_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for columns ' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select column_name, count(*) from user_tab_histograms where table_name = 'T' group by column_name;

no rows selected

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Histogram generation in run time

Vikas Atrey, September 07, 2007 - 8:19 am UTC

1. It means first time query execution may include histogram generation as well ?

2. Does it mean that in addition to work required to process the query oracle will have to do one FTS to generate the histogram ?

3. How does oracle decide that current histograms are not enough ?

September 12, 2007 - 10:03 am UTC

1) no idea what you mean by that question - not clear. I ran no query to generate histograms, I ran dbms_stats (see example), the query - the fact the query was executed - that causes dbms_stats (in 10g) to change how it automatically gathers stats.

2) does what mean? If you are thinking the query did the histogram generation, that is wrong, see example, dbms_stats was invoked to do that.

3) by sampling the data, dbms_stats defaults to AUTO, it looks at the data to figure out what it wants to gather histograms on or not.

Changing "Analyze Table..." to "dbms_stats.gather..."

C.C., September 14, 2007 - 1:55 pm UTC

Can I rewrite

analyze table PARTITIONED_FACT_TABLE PARTITION (P200705) estimate statistics for table for all local indexes for all indexed columns;

to

exec dbms_stats.gather_table_stats(ownname=>'USER',tabname=>'PARTITIONED_FACT_TABLE', partname=>'P200705', estimate_percent=>dbms_stats.auto_sample_size, cascade=>TRUE);

or

exec dbms_stats.gather_table_stats(ownname=>'USER',tabname=>'PARTITIONED_FACT_TABLE', partname=>'P200705', estimate_percent=>dbms_stats.auto_sample_size, cascade=>TRUE, 'for all
indexed columns size 1');

?

September 15, 2007 - 9:58 pm UTC

well, the second dbms_stats won't work...
you cannot do positional after named notation :)

they are not identical, but the intent seems the same - you will estimate stats on that partition and hit all of the indexes on same, using a defaultish sample size.

previous post + correction

C.C, September 17, 2007 - 9:29 am UTC

Sorry for the typo!

If I corrected the syntax to

exec dbms_stats.gather_table_stats(ownname=>'USER',tabname=>'PARTITIONED_FACT_TABLE',
partname=>'P200705', estimate_percent=>dbms_stats.auto_sample_size, cascade=>TRUE, method_opt=>'for all
indexed columns size 1');

wouldn't it do the trick now as in "analyze table..." from above?

for table -> ownname + tabname
for all indexes -> cascade=>true
for all indexed columns -> method_opt=>'for all
indexed columns size 1'

Thanks,

C.C, September 17, 2007 - 11:09 am UTC

Never mind! Tom.

I got it. I know what you meant in your last comment. Even though I tried best to reconstruct "analyze table..." with "dbms_stats.gather...".

They are still fundamentally different as dbms_stats.gather... generates more statistics for the CBO. At least, Oracle has recommended people to use dmbs_stats.

Thanks again.

Setting METHOD_OPT w/ dbms_stats.set_%_prefs

Craig, July 28, 2011 - 2:14 pm UTC

Tom,

You originally demonstrated how to gather table stats with no histograms with the below example:

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for columns ' );

Is there a way to make that the default method_opt when collecting database stats with something like:

dbms_stats.set_global_prefs('method_opt', 'FOR COLUMNS ');

The goal being we could have histograms created during stats collection only on those tables we decide need them by setting it on a table-by-table basis with something like:

dbms_stats.set_table_prefs('SH', 'SALES','METHOD_OPT', 'FOR COLUMNS SIZE 1 PROD_ID');

Oracle Database 11.2.0.2

Best Regards.

July 28, 2011 - 7:41 pm UTC

dbms-stats.set_param is used to change the defaults globally.

however, it is expecting a method opt that uses "for all" - not one with specific columns - since it is used "globally" - so you cannot set it to "for columns"

so, this approach will not work - you'd need to set up a set table prefs to have it not gather this information on a table by table basis.

dbms_stats.set_param depreciated...

Craig, July 29, 2011 - 11:45 am UTC

At least in 11.2, from: Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 (11.2) http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_stats.htm#ARPLS68668

"Note: This subprogram has been replaced by improved technology and is maintained only for purposes of backward compatibility. In this case, use the SET_GLOBAL_PREFS Procedure."

Decided to use set_global_prefs to modify method_opt to "FOR ALL COLUMN SIZE REPEAT", clean out histograms, then when we find tables where histograms are needed we can collect them as such and possibly do a set_table_prefs to set method_opt appropriately for the table(s).

statistics

thanaa, July 06, 2012 - 8:07 pm UTC

Shigella
Sonnei Plasmid transfer by conjugation MIC µ/ml Gene transfer by conjugation
CTX CRO CAZ CIP N A
Sh. TC Sh. TC Sh. TC Sh.TC Sh. TC
S1/TC1 1.8,2.5 256 256 256 256 128 16 32 16 512 512 IV , qnrA
S2/TC2 10,1.8 128 128 64 64 32 16 16 4 256 256 I , qnrB
S3/TC3 10,2.5 128 128 128 64 128 16 64 16 512 64 II ,qnrA
S5/TC4 1.8,2.5 512 256 256 128 256 16 8 2 32 16 IV, qnrA
S11/TC5 >10(50),10 256 256 64 64 - - 64 8 256 32 III, qnrS
S12/TC6 10,2.5 256 256 64 64 - - 64 8 256 32 I, qnrA

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Histogram generation in run time

Changing "Analyze Table..." to "dbms_stats.gather..."

previous post + correction

Setting METHOD_OPT w/ dbms_stats.set_%_prefs

dbms_stats.set_param depreciated...

statistics

More to Explore

PL/SQL demos

PL/SQL docs

Create stats without histograms?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Histogram generation in run time

Changing "Analyze Table..." to "dbms_stats.gather..."

previous post + correction

Setting METHOD_OPT w/ dbms_stats.set_%_prefs

dbms_stats.set_param depreciated...

statistics

More to Explore

PL/SQL demos

PL/SQL docs