| 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: Imported DB runs SLOWER on faster machine -Apology
"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message
news:ahlq3r$955$1_at_babylon.agtel.net...
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3d3d6c5a$0$237$ed9e5944_at_reading.news.pipex.net...
>
>
> > I agree that Daniel is right. However by default you *do* get stats on
> > import - just estimated stats. I know this because moving an 816 export
to
> > an 817 instance *always* gives a dbms_stats error on import. Ignoring
this
> > and reanalyzing works just fine.
>
>
> Just looked at a couple of dumps made using EXP 8.1.7.4 from 8.1.7.4 db
> (I know it's buggy, I have a workaround in place). One dump is a full user
> export, and another is a single table export. Guess what: single table
export
> contains
> ANALYZE TABLE x ESTIMATE STATISTICS
> just before enabling constraints, and full user dump does not have any
> analyze statements or references to dbms_stats and I didn't find this name
> in IMP.EXE strings, so it doesn't seem to be calling it once for the whole
> schema after import... Also, there is RECALCULATE_STATISTICS parameter
> to IMP, which defaults to N. Don't know what to think. :)
The lesson is Don't trust what I say. The estimated statistics are created by EXP not IMP if you use the defaults. Then the default behaviour of IMP is (as you say above) not to recalculate stats. The rationale being that it is faster to use the precomputed values placed by exp into the dump file. So the net effect is that if you use defaults for *both* exp and imp you get the precomputed stats from the exported db. These may well not be appropriate.
The full detail is at
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a76955/ch01.htm#17173
and
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a76955/ch02.htm#40631
which amongst other things contains the statement
"If statistics are requested at export time and analyzer statistics are available for a table, Export will place the ANALYZE command to recalculate the statistics for the table into the dump file"
My reading of this is that if you specify statistics=estimate in the export command you should still get no stats if the table hadn't already been analysed. IMO This is counterintuitive.
My import error BTW ends
(bunch of my data snipped)
DBMS_STATS."
"SET_COLUMN_STATS(NULL,'ACUHEADER','APAR_NAME',NULL,NULL,NULL,12139,.0000836
"
"696097327825,0,srec,35,0); END;"
IMP-00003: ORACLE error 6550 encountered ORA-06550: line 1, column 2160: PLS-00103: Encountered the symbol "S" when expecting one of the following:......
In other words it *is* a call to the dbms_stats package that fails.
The executive summary should probably be to think about and actually specify the relevant stats parameters rather than accepting defaults.
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ******************************************Received on Wed Jul 24 2002 - 04:18:46 CDT
 |
 |