SQL> @delete_table_stats tp1
PL/SQL procedure successfully completed.
SQL> @tblstats tp1
avg
TABLE_NAME free used fl log rows blks empty row px LAST_ANAL pool G U
------------------------------ ---- ---- --- --- ------------ ---------- ------- ------ ---------- --------- -------- - -
TP1 1 DEFAULT N N
TP1.P1 (1) 10 40 1 YES DEFAULT N N
TP1.P2 (2) 10 40 1 YES DEFAULT N N
TP1.P3 (3) 10 40 1 YES DEFAULT N N
4 rows selected.
SQL> @colstats tp1
table column NDV density nulls lo hi bkts
------------------------ --------------------- ---------- ------------ ------- -------------- -------------- -----
TP1 N1
TP1 N2
TP1 N3
TP1 C1
TP1 C2
TP1 C3
TP1 D1
TP1 D2
TP1 D3
TP1 L
10 rows selected.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SCOTT', tabname => 'TP1', method_opt => 'FOR COLUMNS', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> @tblstats tp1
avg
TABLE_NAME free used fl log rows blks empty row px LAST_ANAL pool G U
------------------------------ ---- ---- --- --- ------------ ---------- ------- ------ ---------- --------- -------- - -
TP1 25,000 8,402 0 100 1 11-DEC-03 DEFAULT Y N
TP1.P1 (1) 10 40 1 YES 2,490 836 0 100 11-DEC-03 DEFAULT Y N
TP1.P2 (2) 10 40 1 YES 2,489 852 0 100 11-DEC-03 DEFAULT Y N
TP1.P3 (3) 10 40 1 YES 20,021 6,714 0 100 11-DEC-03 DEFAULT Y N
4 rows selected.
SQL> @colstats tp1
table column NDV density nulls lo hi bkts
------------------------ --------------------- ---------- ------------ ------- -------------- -------------- -----
TP1 N1
TP1 N2
TP1 N3
TP1 C1
TP1 C2
TP1 C3
TP1 D1
TP1 D2
TP1 D3
TP1 L
10 rows selected.
SQL>
If you are seeing column statistics then they are old.
As to your original questions
"Are there any known do and don'ts concerning dbms_stats which might explain this?"
Nothing specific to dbms_stats, just the general advice: DON'T believe everything a self-proclaimed Oracle-Guru or consultant tells you. DO your own homework and due diligence. TEST what you are doing or planning to do. If you insist in painting all tables in your schemas with the same brush then at least just gather basic column statistics (num_distinct, min, max, nulls) by leaving the default method_opt alone. Afterwards you can collect histograms on select columns. In my opinion "for all indexed columns" is both too broad and too narrow - not all indexed column need or even should have histograms and some non-indexed columns could benefit from a histogram.
Is it better to stay on analyze table ? No
Can I expect lot's of problems in execute plans when migrating? Yes
At 03:44 AM 12/11/2003, you wrote:
Hi Jonathan,
Can you please elaborate on this 75 buckets issue. I had an advice from an oracle consultant to implement analyzing Like this. BTW column statistics are there but it makes no difference In plans. I also added optimizer_index_caching=90 and Optimizer_index_cost_adj=40 both also without effect on my testcase
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
