OraFAQ Forum: Server Administration » How to deal with statistics during migration

Home » RDBMS Server » Server Administration » How to deal with statistics during migration

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to deal with statistics during migration [message #682059]

Fri, 02 October 2020 07:43

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.

Report message to a moderator

Re: How to deal with statistics during migration [message #682064 is a reply to message #682059]

Fri, 02 October 2020 08:51

John Watson
Messages: 8976
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.

Report message to a moderator

Re: How to deal with statistics during migration [message #682098 is a reply to message #682064]

Mon, 05 October 2020 09:03

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?

Report message to a moderator

Re: How to deal with statistics during migration [message #682170 is a reply to message #682098]

Wed, 07 October 2020 09:35

John Watson
Messages: 8976
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.

Report message to a moderator

Previous Topic:	Oracle12c Blocking Session Information
Next Topic:	SGA used

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 04:48:39 CDT 2025