Hello,
I have oracle 12.1.0.2(upgraded from 12.1.0.1) with bundle patch. 22310559 (12.1.0.2.160119)
I noticed export schema is slowest then before.
I have 2.66GHz, 32gb ram, windows server 2012r2
My memory parameters:
SQL> show parameter target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_big_table_cache_percent_target string 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 21056M
memory_target big integer 21056M
parallel_servers_target integer 384
pga_aggregate_target big integer 5000M
sga_target big integer 15040M
SQL>
all steps before SCHEMA_EXPORT/VIEW/COMMENT are really fast. but view/comment and later steps taking too much time.
step SCHEMA_EXPORT/VIEW/COMMENT is take about 20 minutes but statistics return 0 seconds....
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Completed 45 COMMENT objects in 0 seconds
Below command
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@server/orcl compress
ion=all schemas=PLAFMO12W122 dumpfile=PLAFMO12W122_20160408.dmp reuse_dumpfiles=
y EXCLUDE=OBJECT_GRANT METRICS=Y
Startup took 17 seconds
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimated 1312 TABLE_DATA objects in 48 seconds
Total estimation using BLOCKS method: 1012. MB
Processing object type SCHEMA_EXPORT/USER
Completed 1 USER objects in 0 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Completed 8 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Completed 3 ROLE_GRANT objects in 1 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Completed 1 DEFAULT_ROLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Completed 1 TABLESPACE_QUOTA objects in 0 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Completed 1 PROCACT_SCHEMA objects in 43 seconds
Processing object type SCHEMA_EXPORT/LIBRARY/LIBRARY
Completed 1 LIBRARY objects in 142 seconds
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Completed 14 TYPE objects in 7 seconds
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Completed 725 SEQUENCE objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Completed 1316 TABLE objects in 73 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Completed 34104 COMMENT objects in 138 seconds
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Completed 1454 PACKAGE objects in 35 seconds
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Completed 9 FUNCTION objects in 4 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Completed 12 PROCEDURE objects in 5 seconds
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTE
PACKAGE_SPEC
Completed 1454 ALTER_PACKAGE_SPEC objects in 8 seconds
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Completed 9 ALTER_FUNCTION objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Completed 12 ALTER_PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Completed 124 VIEW objects in 4 seconds
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Completed 45 COMMENT objects in 0 seconds
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
How can i improve export performance or find what caused that issue?
Best regards,
Mateusz