statistics on import

[davo]

hi,
if you do an import, is it better to set the statistics option on the import itself, or should we gather_schema_stats on the schema after the import, or is it not necessary at all?

[davey23uk]

depends what you want

do you want :

1) no statistics
2) new ones
3) ones from the export

[davo]

what's the best? - presumably generating new ones is, doesn't the import do something that can affect the stats that will be gathered?

[davey23uk]

only you can tell what the best is as only you know what it being exported

gathering new statistics isnt the best thing if you want to keep the ones from the other system is it?

[davo]

All I am trying to do is refresh the database on the target machine with the data from the source machine, using export full=y and then importing fromuser for just the one user.
I thought (possibly wrongly) that you should gather new statistics based on how the data looks now after the import.
Are you saying that if I let the import do the stats that they will basically be exactly the same on the new instance as they were on the old one - which in itself relies upon how often stats have been gathered on the source machine in the first place I presume, and therefore they could be rubbish if not done lately?
If this is acceptable, then I will do it that way.

I was trying to find out the most efficient way to get stats associated with the data on the new instance, so that the optimizer will have the latest set of stats to go on, so was thinking along the lines of doing the gather stats afterwards.

Presumably if I did no stats at all then performance would be very slow as the optimizer would hav nothing to go on to build execution plans?

[davey23uk]

im saying only you know what you want do

either you want to keep the imported statistics / or you dont - simple as that

and no, the performance wouldnt definitely be slow if their were no stats - it could be lightning fast

[tamilselvan]

You can also consider dbms_stats.export_schema_stats and import_schema_stats. This is the fastest way you get the stats on the target DB.

Tamil