Home » RDBMS Server » Server Administration » How to deal with statistics during migration
How to deal with statistics during migration [message #682059] Fri, 02 October 2020 07:43 Go to next message
burkinaone
Messages: 11
Registered: October 2020
Junior Member
Hello.
I am carrying a migration on from Oracle 11.2.0.4 to 19.3.
Source server is AIX 7.1. Destination is RHEL 7.
I am doing a schema export/import with datapump.

I am looking for the best way to deal with statistics:
-Systems statistics
-Dictionnary statistics
-Fixed objects statistics
-Databse statistics
Should I export/import them with datapump or dbms_stats or should I gather them on the new server?
Has anybody gone through this kind of migration?
Thanks.
Re: How to deal with statistics during migration [message #682064 is a reply to message #682059] Fri, 02 October 2020 08:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

None of the current statistics will be valid after the import, you need to gather them all on the new server.
Be sure when gathering object statistics that you do not specify the ESTIMATE_PERCENT parameter, as setting that will disable the new histograms. In fact, it is usually best in 19 not to specify any parameters at all, just run gather_database_Stats with none.
Ideally, wait until you have had time to run a representative workload so that you will get the histograms you need.

Lastly, why 19.3? You really should have patched it up to at least 19.7.1. If you wait a week or two the 19.9.0 RU will be out.
Re: How to deal with statistics during migration [message #682098 is a reply to message #682064] Mon, 05 October 2020 09:03 Go to previous messageGo to next message
burkinaone
Messages: 11
Registered: October 2020
Junior Member
Thanks John for the response. So even the user objects statistics will not be valid after import?
Re: How to deal with statistics during migration [message #682170 is a reply to message #682098] Wed, 07 October 2020 09:35 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I suppose the number of rows in each table would be the same, but everything else would be wrong. For example, number of blocks above and below high water marks; index clustering factor; and most importantly, you would not have the new histograms.
Previous Topic: Oracle12c Blocking Session Information
Next Topic: SGA used
Goto Forum:
  


Current Time: Thu Mar 28 20:47:23 CDT 2024