On a couple of occasions now I've had cause to want to artificially change the order in which oracle extracts the tables when doing an export. This tends to be when I have a table which is randomly truncated by the application processing which then means when the table data comes to be extracted it throws an error as 'ddl' has happened to the table. The other cause may be when you have a table which (for whatever reason) is more likely than others to throw a snapshot too old error.
So is this possible or not, how does oracle decide on the ordering?
From a lot of expereince with datapump it's apparent that the size of the table is the governing factor - big tables are done first ( the logic being that these are more difficult to get a consistent image of). If tables are the 'same' size then it seems to just be alphabetical.
So how can i use this snippet of information to help me?
Lets do some quick test to prove what i think i already know - first up we create a new schema and 3 'empty' tables.
[oracle@server]:DEMO:[/oracle/admin/DBNAME/diag/rdbms/DBNAME/DBNAME/trace]# s
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 26 16:49:18 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create user dptest identified by dptest;
User created.
SQL> grant dba to dptest;
Grant succeeded.
SQL> conn dptest/dptest
ERROR:
ORA-00942: table or view does not exist
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL>
SQL> create table A (col1 number);
Table created.
SQL> create table B (col1 number);
Table created.
SQL> create table C (col1 number);
Table created.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Now we have those - lets try an export as is
[oracle@server]:DEMO:[/oracle/admin/DBNAME/diag/rdbms/DBNAME/DBNAME/trace]# expdp dptest/dptest
Export: Release 12.1.0.1.0 - Production on Thu Jun 26 16:50:55 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DPTEST"."SYS_EXPORT_SCHEMA_01": dptest/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "DPTEST"."A" 0 KB 0 rows
. . exported "DPTEST"."B" 0 KB 0 rows
. . exported "DPTEST"."C" 0 KB 0 rows
Master table "DPTEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPTEST.SYS_EXPORT_SCHEMA_01 is:
/oracle/12.0.0.1/rdbms/log/expdat.dmp
Job "DPTEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 26 16:51:29 2014 elapsed 0 00:00:33
So the export finished fine ( in alphabetic order - or it could be creation date order i guess - i'm not too concerned about that part)
Now lets create some data, and make table C bigger than the others
sqlplus dptest/dptest
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 26 16:52:33 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-00942: table or view does not exist
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Last Successful login time: Thu Jun 26 2014 16:50:55 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> insert into c values (1);
1 row created.
SQL> insert into c select * from c;
1 row created.
SQL> /
2 rows created.
SQL> /
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> /
32 rows created.
SQL> /
64 rows created.
SQL> /
128 rows created.
SQL> /
256 rows created.
SQL> /
512 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(USER,'C');
PL/SQL procedure successfully completed.
Now we try the export again
expdp dptest/dptest
Export: Release 12.1.0.1.0 - Production on Thu Jun 26 16:54:17 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DPTEST"."SYS_EXPORT_SCHEMA_01": dptest/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "DPTEST"."C" 12.01 KB 1024 rows
. . exported "DPTEST"."A" 0 KB 0 rows
. . exported "DPTEST"."B" 0 KB 0 rows
Master table "DPTEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPTEST.SYS_EXPORT_SCHEMA_01 is:
/oracle/12.0.0.1/rdbms/log/expdat.dmp
Job "DPTEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 26 16:54:42 2014 elapsed 0 00:00:25
And indeed we see - table C is done first, now lets artificially make oracle think that table B is bigger than it is
[oracle@server]:DEMO:[/oracle/admin/DBNAME/diag/rdbms/DBNAME/DBNAME/trace]# sqlplus dptest/dptest
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 26 16:55:22 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-00942: table or view does not exist
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Last Successful login time: Thu Jun 26 2014 16:54:17 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> exec dbms_stats.set_table_stats(USER,'B',numblks=>100000);
PL/SQL procedure successfully completed.
SQL>
Ok - so now B in stats terms is the biggest table - lets try and export again
expdp dptest/dptest
Export: Release 12.1.0.1.0 - Production on Thu Jun 26 16:56:34 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DPTEST"."SYS_EXPORT_SCHEMA_01": dptest/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "DPTEST"."C" 12.01 KB 1024 rows
. . exported "DPTEST"."A" 0 KB 0 rows
. . exported "DPTEST"."B" 0 KB 0 rows
Master table "DPTEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPTEST.SYS_EXPORT_SCHEMA_01 is:
/oracle/12.0.0.1/rdbms/log/expdat.dmp
Job "DPTEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 26 16:56:47 2014 elapsed 0 00:00:12
Hmm - no difference - maybe deferred segment creatuon is somehow messing things up - lets create a real row to ensure a segment exists.
sqlplus dptest/dptest
SQL> insert into b values (1);
1 row created.
SQL> commit;
Commit complete.
Double check the stats so B is still shown to be the biggest.
SQL> select table_name,NUM_ROWS,BLOCKS from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
NUM_ROWS BLOCKS
---------- ----------
A
B
2000 100000
C
1024 3
SQL>
expdp dptest/dptest reuse_dumpfiles=y
Export: Release 12.1.0.1.0 - Production on Thu Jun 26 17:00:10 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DPTEST"."SYS_EXPORT_SCHEMA_01": dptest/******** reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 56 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "DPTEST"."C" 12.01 KB 1024 rows
. . exported "DPTEST"."B" 5.023 KB 1 rows
. . exported "DPTEST"."A" 0 KB 0 rows
Master table "DPTEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPTEST.SYS_EXPORT_SCHEMA_01 is:
/oracle/12.0.0.1/rdbms/log/expdat.dmp
Job "DPTEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 26 17:00:22 2014 elapsed 0 00:00:12
Hmm - still didn't work - lets trace datapump and see what its doing to decide the 'size'. I activated full trace and got the following extract from the tracefile
META:12:04:33.402: get_xml_inputs SCHEMA_EXPORT/TABLE/TABLE_DATA:
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_DATA_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,'TABLE' ,to_char(KU$.BYTES_ALLOC) ,KU$.DATAOBJ_NUM ,to_char(KU$.ET_PARALLEL) ,KU$.FGAC ,KU$.LONGCOL ,KU$.NONSCOPED_REF ,KU$.TSTZ_COLS ,KU$.XMLSCHEMACOLS ,KU$.NAME ,KU$.NAME ,'TABLE_DATA' ,KU$.PART_NAME ,KU$.PARTTYPE ,KU$.PROPERTY ,KU$.REFPAR_LEVEL ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.SCHEMA_OBJ.NAME ,KU$.TRIGFLAG ,decode(KU$.SCHEMA_OBJ.TYPE_NUM,2, decode(bitand(KU$.PROPERTY,8224),8224,'NESTED PARTITION',8192,'NESTED TABLE','TABLE'),19, decode(bitand(KU$.PROPERTY,8224),8224,'NESTED PARTITION','PARTITION'),20,'PARTITION','SUBPARTITION') ,to_char(KU$.UNLOAD_METHOD) ,KU$.XMLTYPE_FMTS FROM SYS.KU$_TABLE_DATA_VIEW KU$ WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND NOT (BITAND (KU$.BASE_OBJ.FLAGS,16)=16) AND NOT XML_OUTOFLINE='Y' AND KU$.BASE_OBJ.OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS
So the driving view seems to be SYS.KU$_TABLE_DATA_VIEW - where does that get its data from? (here I'll just show the series of linked views as displaying the full text just gets messy - it's like descending into dante's version of hell trying to get to the source of this data.....
So in summary the path goes:
ku$_table_data_view -> ku$_htable_data_view -> KU$_HTABLE_DATA_VIEW -> objid from tab$ -> dbms_metadata_util.block_estimate -> ku$_htable_bytes_alloc_view -> KU$_BYTES_ALLOC_VIEW -> KU$_STORAGE_VIEW -> seg$
So essentially the driver of what datapump thinks how big things are is the blocks column in seg$ - this is the actual physical size allocated - nothing to do with stats - so we can't trick datapump.
or can we............
There is another flag to datapump which is called 'estimate' - i don't think is widely used but actually allows us to do exactly what we want. This parameter is basically saying don't look at how big things actually are - look at how big the statistics tell you they are.
So what happens if we do this?
[oracle@server]:DEMO:[/oracle/admin/DBNAME/diag/rdbms/DBNAME/DBNAME/trace]# expdp dptest/dptest reuse_dumpfiles=y estimate=statistics
Export: Release 12.1.0.1.0 - Production on Thu Jun 26 17:06:59 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DPTEST"."SYS_EXPORT_SCHEMA_01": dptest/******** reuse_dumpfiles=y estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "DPTEST"."B" 200.3 KB
. estimated "DPTEST"."C" 8.058 KB
. estimated "DPTEST"."A" 0 KB
Total estimation using STATISTICS method: 208.4 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "DPTEST"."B" 5.023 KB 1 rows
. . exported "DPTEST"."C" 12.01 KB 1024 rows
. . exported "DPTEST"."A" 0 KB 0 rows
Master table "DPTEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPTEST.SYS_EXPORT_SCHEMA_01 is:
/oracle/12.0.0.1/rdbms/log/expdat.dmp
Job "DPTEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 26 17:07:11 2014 elapsed 0 00:00:12
As you can see - this did exactly what we wanted - we just had to use this extra flag. By doing this we changed the extract order. You also see you get an extra section in the log at the top - how big the stats said the extract would be - followed by the actual extract which shows how big it actually was.
Very useful in certain cases i think.
It could may be combined with pending statistics so that only datapump sees the 'fake' object size and the normal activity in the system is unaffacted while this bodge is in place.
It would be a nice enhancement to be able to list 'priority/problem' objects to be done first in datapump to avoid these occasional issues.
Hope someone finds this useful.....
Comments
Post a Comment