This was first published on https://blog.dbi-services.com/impdp-contentmetadata_only-locks-the-stats (2017-09-06)
Republishing here for new followers. The content is related to the the versions available at the publication date
With Oracle you can learn something every day. Today, preparing a migration to 12.2, I found all tables had locked statistics. I learned that it is the expected behavior since 10.2 when importing metadata_only including statistics, to avoid that the automatic job gathering comes and replaces the stats by ‘0 rows’. It is documented in DataPump Import Without Data Locks Table Statistics (Doc ID 415081.1) but as I was really surprised about that (and also frustrated to learn a 10.2 thing when migrating to 12.2) that I wanted to test myself.
I create a table DEMO with statistics:
SQL> connect sys/oracle@//localhost/pdb1 as sysdba Connected. SQL> create table demo.demo as select * from dual; Table created. SQL> create index demo.demo on demo.demo(dummy); Index created. SQL> exec dbms_stats.gather_table_stats('DEMO','DEMO'); PL/SQL procedure successfully completed. SQL> create or replace directory TMP as '/tmp'; Directory created. SQL> select count(*) from DEMO.DEMO; COUNT(*) ---------- 1 SQL> select object_type from dba_objects where owner='DEMO' and object_name='DEMO'; OBJECT_TYPE ----------------------- TABLE INDEX SQL> select owner,table_name,last_analyzed,stattype_locked,num_rows from dba_tab_statistics where owner='DEMO' and table_name='DEMO'; OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED NUM_ROWS ---------- -------------------- -------------------- -------------------- ---------- DEMO DEMO 06-SEP-17 1
I export it:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SYS"."SYS_EXPORT_TABLE_01": "sys/********@//localhost/pdb1 AS SYSDBA" tables=DEMO.DEMO directory=TMP Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX . . exported "DEMO"."DEMO" 5.054 KB 1 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /tmp/expdat.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Sep 6 19:14:44 2017 elapsed 0 00:00:09
And drop it:
SQL> connect sys/oracle@//localhost/pdb1 as sysdba Connected. SQL> drop table demo.demo; Table dropped.
Now import metadata only (for example because I want to change NLS semantics before importing the data)
Import: Release 12.2.0.1.0 - Production on Wed Sep 6 19:21:28 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/********@//localhost/pdb1 AS SYSDBA" tables=DEMO.DEMO directory=TMP content=metadata_only exclude=index Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Sep 6 19:21:39 2017 elapsed 0 00:00:11
If I check the statistics:
SQL> select owner,table_name,last_analyzed,stattype_locked,num_rows from dba_tab_statistics where owner='DEMO' and table_name='DEMO'; OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED NUM_ROWS ---------- -------------------- -------------------- -------------------- ---------- DEMO DEMO 06-SEP-17 ALL 1
Stats are locked. I suppose that the idea is that you have the tables with same statistics as production for example, and you can load them with a subset of data but expect the same execution plans as in production. But this is not what I want for a migration.
One possibility is to unlock the stats once you have imported the data.
The other possibility is to import metadata without the statistics:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/********@//localhost/pdb1 AS SYSDBA" tables=DEMO.DEMO directory=TMP content=metadata_only exclude=index exclude=table_statistics Processing object type TABLE_EXPORT/TABLE/TABLE Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Sep 6 21:11:03 2017 elapsed 0 00:00:03
Then the table statistics are not locked:
OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED NUM_ROWS ---------- -------------------- -------------------- -------------------- ---------- DEMO DEMO
Once you have changed what you want on the tables, you import the data (table_exists_action=truncate) and then you import the remaining: indexes, ref_constraints, triggers. This is where you can also add include=table_statistics:
Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/********@//localhost/pdb1 AS SYSDBA" tables=DEMO.DEMO directory=TMP table_exists_action=truncate include=index include=table_statistics Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
So that you have the statistics from the source, unlocked.
OWNER TABLE_NAME LAST_ANALYZED STATTYPE_LOCKED NUM_ROWS ---------- -------------------- -------------------- -------------------- ---------- DEMO DEMO 06-SEP-17 1