Getting datapump to follow orders?



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.....

0 comments:

Post a Comment