RE: Manipulate DATE histograms
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: "'Wolfgang Breitling'" <breitliw@xxxxxxxxxxxxx>
- Date: Thu, 14 Dec 2006 23:24:51 +0100
Hi again Wolfgang,
Accordinf to the docs
(http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2143.htm#1302694)
:
GLOBAL_STATS -
For partitioned tables, indicates whether column statistics were collected for
the table as a whole (YES) or were
estimated from statistics on underlying partitions and subpartitions (NO).
USER_STATS-
Were the statistics entered directly by the user?
So, if flag is 2 then we have GLOBAL_STATS=YES and USER_STATS = NO
(values derived from the source of DBA_TAB_COLS) :
....
decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
...
From
sys.hist_head$ h
....
What I is still not clear to me is:
1) When USER_STATS is set to YES. I have tried to use
DBMS_STATS.SET_COLUMN_STATS, then
I have played with exec dbms_stats.export_table_stats +
dbms_stats.import_table_stats, but
USER_STATS is still set to NO.
2) When/under which circumstances CBO is using/checking whether USER_STATS =
YES and what is the influence of USER_STATS
column on the CBO , if any ...
Best Regadrs. Milen
-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx]
Sent: Thursday, December 14, 2006 10:30 PM
To: Milen Kulev
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: *****SPAM***** RE: Manipulate DATE histograms
You're right. avgclen should be 7. As I said I just took the example
>from my paper. I changed the density (so some fictitious value) but
forgot to change avgclen.
The flags value I think I took from dbms_stats.set_column_stats calls
created by export. I suppose you could just let it default. The
flag(s) correspond to GLOBAL_STATS and USER_STATS of dba_tab_columns
and thus can take on the values 0, 1, 2, and 3. (binary 00, 01, 10, 11).
At 02:11 PM 12/14/2006, Milen Kulev wrote:
>Hi Wolfgang,
>Thank you very much for your reply/Christmas present ;)
>I have noticed that you are always using (..., 2,2) for the last
>two paramemers of DBMS_STATS.SET_COLUMN_STATS
>(avgclen and flags )
>
>Shouldn't in this case avgclen to be set to 7 (for DATE type) ?
>avgclen should play a role somewhere in the CBO calculations, I think
>...
>
>And can "flags" get any other value other than 2 (asking just of
>curiosity) ?
>
>Best Regards. Milen
>
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System. For more
information please visit
http://www.messagelabs.com/email
______________________________________________________________________
--
//www.freelists.org/webpage/oracle-l
Other related posts:
