| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Upgrade from 8.1.6 to 8.1.7 really slow
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3cdfda5d$0$234$ed9e5944_at_reading.news.pipex.net>...
> "Mark D Powell" <mark.powell_at_eds.com> wrote in message
> news:178d2795.0205130540.108a493c_at_posting.google.com...
> > And
> > how were the statistics created? I have not personally verified this
> > but I have read that the statistics created by dbms_stats cannot be
> > replaced by an analyze, but either have to be deleted first or
> > replaced by a new run of dbms_stats.
> >
> > HTH -- Mark D Powell --
>
> Not true. (or not universally true)
>
> SQL*Plus: Release 9.0.1.0.1 - Production on Mon May 13 16:17:59 2002
>
> (c) Copyright 2001 Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Oracle8i Release 8.1.7.3.0 - Production
> JServer Release 8.1.7.3.0 - Production
>
> SQL> desc rm_agltransact;
> ERROR:
> ORA-04043: object rm_agltransact does not exist
>
>
> SQL> select table_name from user_tables
> 2 where table_name like 'RM%';
>
> TABLE_NAME
> ------------------------------
> RM_ACRTRANS
> RM_ATSINVTRANS
> RM_ATSINVTRANS2
>
> SQL> begin
> 2 dbms_stats.gather_table_stats('AGRPROD','RM_ATSINVTRANS');
> 3 END;
> 4 /
>
> PL/SQL procedure successfully completed.
>
> SQL> ANALYSE TABLE RM_ATSINVTRANS ESTIMATE STATISTICS;
> SP2-0734: unknown command beginning "ANALYSE TA..." - rest of line ignored.
> SQL> ANALYZE TABLE RM_ATSINVTRANS ESTIMATE STATISTICS;
>
> Table analyzed.
>
> SQL>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
Niall, since dbms_stats does not calculate chained rows and analyze may not calculate some statistics that analyze does calculate your example does not actually prove anything since you omitted displaying the statistics generated. I will do that and prove (I think) your point that analyze does replace statistics generated by dbms_stats.
I have read that statement that analyze does not replace statistics generated by dbms_stats more than once, but only remember one source:
From Oracle8 to 8i Upgrade Exam Cram by Freemand and Pack, chapter 9
section DBMS_STATS:
"If you plan to use the CBO, use DBMS_STATS instead of the ANALYZE
command. The statistics are more accurate and run automatically in
parallel, and ANALYZE cannot replace statistics generated by
DBMS_STATS." P. 172 in my copy. This is not the only error in the
book, but it is still a pretty decent book for preparing for the OCP
upgrade examination.
Oracle version is 8.1.7.2 -- After dbms_stats
UT1> select num_rows, blocks, empty_blocks, avg_space, chain_cnt,
avg_row_len
2 from dba_tables
3 where table_name = 'MARKTEST'
4 /
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ------------ ---------- ---------- -----------
33 1 0 0 0 13
Delete from marktest where rownum < 10;
commit;
UT1> @mark
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ------------ ---------- ---------- -----------
24 1 2 3529 0 17
Drop/Recreate/load
UT1> select count(*) from marktest;
COUNT(*)
32
UT1> execute dbms_stats.gather_table_stats('MPOWEL01','MARKTEST');
PL/SQL procedure successfully completed.
UT1> @ora/obj/tbl_col
Enter value for tbl_name: marktest
COLUMN_NAME
9 rows deleted.
UT1> commit;
Commit complete.
UT1> analyze table marktest compute statistics;
Table analyzed.
UT1> @ora/obj/tbl_col
Enter value for tbl_name: marktest
COLUMN_NAME
Niall, thanks for replying and making me get off my lazy butt and at least partially checking this out.
 |
 |