If you decided to do a logical migration of your Oracle Database with Zero Downtime Migration (ZDM), here is something important about optimizer statistics. You must manually take care of the optimizer statistics on the target database after the Data Pump import. Either by recreating the statistics or transporting the statistics from the source database.
Whenever I talk about migration of Oracle Database with Data Pump, I always mention that it is best practice to exclude optimizer statistics from the Data Pump export. Here is how you do it:
$ expdp ... exclude=statistics
Why is that recommended? Data Pump is not very good at extracting the optimizer statistics. There is nothing wrong with the statistics when they are imported. But the issue is that it can take a very long time to do the export of statistics.
ZDM and Data Pump
The development team behind ZDM wanted the tool to be easy to use. Also, they wanted ZDM to use all the best practices that come with the various other tools that ZDM uses. When they talked to us about Data Pump, we told them to exclude statistics.
BUT – this also leaves you in a situation where you have a database completely without optimizer statistics. It goes without saying that it is a disaster waiting to happen. You must ensure that optimizer statistics are present on the target database before allowing the users to connect to it.
One option is to regather optimizer statistics on the target database after the Data Pump import. When the Data Pump import completes, and before you proceed with the switchover, it is time to regather the statistics. Typically, you would start ZDM something like this:
$ zdmcli migrate database .... -pauseafter ZDM_MONITOR_GG_LAG
It will start by performing the initial load of the database with Data Pump. Then it will configure GoldenGate before it pauses – waiting for your signal to complete the migration. At this time, use DBMS_STATS to gather statistics:
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS;
This has some drawbacks:
- It requires time and resources – which might not be the biggest problem. The source database is still open for business. We haven’t performed the switchover yet.
- Column usage information (table
COL_USAGE$) is not populated, so in some cases, no histograms will be created. This will happen if the database is supposed to automatically determine whether histograms are needed (
size auto). In that case, you can merge the column usage information from another database via a database link, which can be a good idea if your application is depending on histograms.
- The table, schema, or database statistics preferences are not present. It could be
stale_pctor any other preferences that you can set with
DBMS_STATS.SET_TABLE_PREFS(or schema or database-wide preferences). These preferences can be transferred to the new system, which is what I will talk about next.
Another option is to transfer the statistics using
DBMS_STATS. We have covered this in detail in a webinar, so I suggest you watch Performance Stability, Tips and Tricks and Underscores for all the details.
- The optimizer statistics in the source database are extracted from the data dictionary and stored in a transportable format in a regular heap table (referred to as a staging table).
- Using Data Pump, you move that table to the target database.
- Then you put the statistics into the data dictionary of the target database so that optimizer can use them.
Pro tip: You should perform step #1 before you start ZDM and store the staging table in one of the schemas that you are migrating with ZDM. That way, you don’t have to move the table manually. It is moved by ZDM together with the real data.
One thing to be aware of is that the table, schema or database statistics preferences are not transferred when you use e.g. DBMS_STATS.EXPORT_TABLE_STATS. There are dedicated procedures for transferring the statistics preferences:
- And corresponding IMPORT procedures
You must figure out how to move the optimizer statistics into your target database when you use ZDM to perform logical migrations. If not, your target database will be without optimizer statistics which is a disaster waiting to happen.
You might now ask. If Data Pump is bad at exporting statistics, but there are already better ways available in the database, why don’t we change the Data Pump code? And you are right – but so far, other things have been prioritized. I would love to see this one day fully embedded in Data Pump.
Other Blog Posts in This Series
- Install And Configure ZDM
- Physical Online Migration
- Physical Online Migration to DBCS (this post)
- Physical Online Migration to ExaCS
- Physical Online Migration and Testing
- Physical Online Migration of Very Large Databases
- Logical Online Migration
- Logical Online Migration to DBCS
- Logical Offline Migration to Autonomous Database
- Logical Online Migration and Testing
- Logical Online Migration of Very Large Databases
- Logical Migration and Statistics
- Logical Migration and the Final Touches
- Create GoldenGate Hub
- Monitor GoldenGate Replication
- The Pro Tips