| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: analyze problems
Not that he needs it, but I can confirm Jonathan's claim that the
method_opt clause you are using does not collect column statistics:
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: breitliw_at_centrexcc.com 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: ListGuru_at_fatcity.com (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).Received on Thu Dec 11 2003 - 09:04:41 CST
 |
 |