| Expdp/Impdp performs slow in RAC oracle11gR2 [message #540470] |
Mon, 23 January 2012 23:18  |
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 #540501 is a reply to message #540470] |
Tue, 24 January 2012 02:34  |
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 #540512 is a reply to message #540470] |
Tue, 24 January 2012 03:43 |
 |
Mahesh Rajendran
Messages: 10708
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
