Home » Server Options » RAC & Failsafe » Expdp/Impdp performs slow in RAC oracle11gR2
Expdp/Impdp performs slow in RAC oracle11gR2 [message #540470] Mon, 23 January 2012 23:18 Go to next message
shipon_97
Messages: 17
Registered: March 2008
Junior Member
Dear Friends ,

i got a problem recenly in Oracle 11g R2 RAC database . normally When I export sample user 'SCOTT' , it takes hardly one minutes .But In our RAC environment this export runs with 20to40 minutes .

Here the output :
---------------------------------------------------------------
oracle@rac2 dump]$ expdp system/sys123 directory=test_dir dumpfile=scott1.dmp schemas=scott

Export: Release 11.2.0.1.0 - Production on Mon Jan 23 09:30:26 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=test_dir dumpfile=scott1.dmp schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.937 KB 4 rows
. . exported "SCOTT"."EMP" 8.570 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.867 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/dump/scott1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:52:05
----------------------------------------------------------
Here please look @ the time .
Import also have the same problem .

In another machine(where I configure RAC again in Linux) , I got the same problem . Now I dont understand is it a BUG or another issue ?

I also dont find any perfect documents in metalink .

My host information :

OS : AIX 6.1
Storage : IBM (using ASM)
Database : Oracle 11g R2

please give me a solution or an expert opinion .. ...
Re: Expdp/Impdp performs slow in RAC oracle11gR2 [message #540471 is a reply to message #540470] Mon, 23 January 2012 23:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Activate a trace on the data pump sessions to know on which it is waiting.

Regards
Michel
Re: Expdp/Impdp performs slow in RAC oracle11gR2 [message #540501 is a reply to message #540470] Tue, 24 January 2012 02:34 Go to previous messageGo to next message
shipon_97
Messages: 17
Registered: March 2008
Junior Member
I have taken the trace file using below ways :


1)

set serveroutput on size 1000000 for wra
declare
paramname varchar2(256);
integerval binary_integer;
stringval varchar2(256);
paramtype binary_integer;
begin
paramtype:=dbms_utility.get_parameter_value('user_dump_dest',integerval,stringval);
if paramtype=1 then
dbms_output.put_line(stringval);
else
dbms_output.put_line(integerval);
end if;
end;
/


2)

select name,value
from v$parameter
where name='user_dump_dest';

3)
set lines 1000 pages 1000

select s.sid,s.serial#,s.username,s.osuser
from v$session s,v$process p
where s.paddr=p.addr;

SYSTEM : SID: 61 SERIAL : 325


4)


exec dbms_system.set_sql_trace_in_session(61,325,true);


Now, Run the below process after completing expd:
exec dbms_system.set_sql_trace_in_session(61,325,false);






The followng trace files are created :

-rw-r----- 1 oracle oinstall 1018 Jan 24 17:00 RAC2_j001_12799.trc
-rw-r----- 1 oracle oinstall 1946 Jan 24 17:03 RAC2_lmd0_4111.trc
-rw-r----- 1 oracle oinstall 3053 Jan 24 17:04 RAC2_pz97_12966.trc
-rw-r----- 1 oracle oinstall 4801 Jan 24 17:07 RAC2_dw00_12972.trc
-rw-r----- 1 oracle oinstall 12187 Jan 24 17:07 RAC2_pz98_12958.trc
-rw-r----- 1 oracle oinstall 5049 Jan 24 17:07 RAC2_dm00_12893.trc
-rw-r----- 1 oracle oinstall 81029 Jan 24 17:07 RAC2_pz99_12750.trc
-rw-r----- 1 oracle oinstall 649892 Jan 24 17:07 RAC2_ora_12789.trc

I will give you the last one which is increasing frequently .. ...

[Updated on: Tue, 24 January 2012 02:46]

Report message to a moderator

Re: Expdp/Impdp performs slow in RAC oracle11gR2 [message #540505 is a reply to message #540501] Tue, 24 January 2012 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which session is session 65?

You have to take trace of datapump master and datapump workers processes.
And you have to activate a trace with waits, use dbms_monitor.session_trace_enable procedure for this.

Regards
Michel
Re: Expdp/Impdp performs slow in RAC oracle11gR2 [message #540512 is a reply to message #540470] Tue, 24 January 2012 03:43 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Another option is to use inbuilt trace mechanisms.
Something like this.
expdp dbadmin/xxxx directory=expdpdir dumpfile=dabdmin.dmp schemas=dbadmin trace=1FF0300 metrics=Y

Trace will produce the trace files and metrics will show the output broken down with time taken taken. Could be useful sometimes.
As Michel said,
Datapump shadow/worker/master trace files are the key here.
Here is a sample output.

Export: Release 11.2.0.1.0 - Production on Tue Jan 24 04:31:18 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Data Mining and Real Application Testing options
Starting "DBADMIN"."SYS_EXPORT_SCHEMA_01":  dbadmin/******** directory=expdpdir dumpfile=dabdmin.dmp 
schemas=dbadmin TRACE=1FF0300 metrics=Y 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11.43 MB
Processing object type SCHEMA_EXPORT/USER
     Completed 1 USER objects in 1 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
     Completed 2 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
     Completed 9 ROLE_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
     Completed 1 DEFAULT_ROLE objects in 6 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
     Completed 1 PROCACT_SCHEMA objects in 8 seconds
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
     Completed 4 SYNONYM objects in 2 seconds
Processing object type SCHEMA_EXPORT/DB_LINK
     Completed 1 DB_LINK objects in 0 seconds
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
     Completed 1 SEQUENCE objects in 34 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 6 TABLE objects in 39 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 2 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 2 INDEX_STATISTICS objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
     Completed 2 FUNCTION objects in 20 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
     Completed 5 PROCEDURE objects in 10 seconds
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
     Completed 2 ALTER_FUNCTION objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
     Completed 5 ALTER_PROCEDURE objects in 6 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
     Completed 3 VIEW objects in 12 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 1 REF_CONSTRAINT objects in 10 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 2 TABLE_STATISTICS objects in 58 seconds
. . exported "DBADMIN"."EMDISTMAST"                      8.949 MB   57362 rows
. . exported "DBADMIN"."DEPT"                            5.945 KB       4 rows
. . exported "DBADMIN"."DEPTREE_SHADOW"                  7.117 KB       2 rows





And please check metalink note 453895.1

[Updated on: Tue, 24 January 2012 03:55]

Report message to a moderator

Previous Topic: ORACLE Server Failover Parameters in Windows Server machine.config file
Next Topic: CRS-0215: Could not start resource
Goto Forum:
  


Current Time: Thu Mar 28 11:55:40 CDT 2024