Hi,
I'm upgrading my database from 12.1 to 19c using the datapump export/import ( cause I'm want to do a reorg of the DB as well) and it seems I have a problem with the way I gather statistic after the import operation.
Since importing statistics takes a lot of time, I'm excluding the stats from being imported into the new oracle 19 database and I do gather DB stats manually after import is finished!
Here is how I gather the statistics after the import:
EXEC DBMS_STATS.gather_database_stats( method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE,degree => 16);
I have to also mention that my DB is pretty large, 1,7 TB which is reduced to 1,3 TB after being imported. I have large tables which some of them have like couple of hundred milion rows.
The reason It seems that I have a problem is, one of our sql statements is now taking for ever and filling like 1,1 TB of temp tablespace.
The same query is not filling the temp TBS att all.
Now I'm thinking of exporting/importing the stats to (dbms_stats.export_schema_stats) the new databas but I think it's weird not to use the oracle widely encouraged way of gathering stats?!
Do you think so? or what do you think is the most reliable way to do a fresh stats gathering?!
Thanks for all your inputs!
Regards
/Hesipesi