We help IT Professionals succeed at work.

Database Hang when taking datapump Export

oraclescsa
oraclescsa asked
on
Hi  ,

Our Enviroment
AIX 5.3
Oracle 10g Release 2
2 Database Running on Server

I have 2 Database on our development Server and we recently upgraded from 9i to 10g.

Whenever i am taking datapump export of 1st database , the queries and transactions

for both the database gets very very slow.

Even when i log in to sys user using sqlplus it takes 40 secs to login.  I dont know

why Datapump is driving my both the database crazy.



Comment
Watch Question

Praveen Kumar ChandrashekatrDatabase Analysist Senior

Commented:
You can check v$session_wait for details or run a 10046 trace to identify what is creating the problem. Also you can check the alert_log file for any space issuses.

check this doc for more info

http://youyus.com/wp-content/uploads/html/Checklist%20for%20Slow%20Performance%20of%20Export%20Data%20Pump.htm
Top Expert 2008

Commented:
I gues that the disk drives are busy and/or your RAM is too small to meet the requirements.

What you tell us is very similar to use swap space on disk. This makes everything very slow.

Author

Commented:
I have checked the alert.log file but there is no such suspicious thing to look.

One thing i noticed is that there is lot of paging for oracle process even though i am not using

datapump , i dont know why but it is after upgrading from 9i to 10g.

I am attaching topas screenshot with init.ora files for 2 databases.
initdev.txt
inittesting.txt
paging-in-test-database.bmp
Top Expert 2008

Commented:
You will not see the swap/paging alerts in the alert.log.

You have to investigate swap space usage and also the RAM used. Check SGA caches parameters of both instances.


Also you have
db_cache_size=2147483648
shared_pool_size=1294967296
and
sga_max_size=5905580032

Do you really need so much cache size.
Big SGA works slowe.

Author

Commented:
I have changed the settings and reduced but still the problem exists.

When ever datapump exports starts then everything comes to stand still.

I am not able to find the exact solution to this cause.

I have checked the swap space 1% is used and 99% is free.

Tell me which parameters would effect the datapump export performance.
Top Expert 2008

Commented:
Increase

session_max_open_files=20

Seee also the sessions parameter:

SQL> SELECT count(*) FROM v$session;

Check sessions parameter:

SQL> show parameter session

Author

Commented:
Hi schwetner,

I have tried to change the parameters which is mentioned above but nothing
 
changes.

Maybe there is some bug in the aix level itself because there is heavy paging

when i close the oracle instance. MY AIX version is 5.3 TL4 and i am planning to upgrade to AIX TL8

and also oracle 10g 10.2.0.1 to 10.2.0.4 . This is my last hope.

What do you suggest ?
Top Expert 2008

Commented:
I am also surprised.
Heavy paging means that the RAM usage is not planned good.

Please refer this:

http://www.oracle.com/technetwork/database/clusterware/overview/rac-aix-system-stability-131022.pdf

Pay attention to:

1. Implement AIX tuning recommendations for Oracle

There are some other articles in Google.

Looking at the Oracle parameters definitelly this parameter is not relevant:

compatible=9.2.0.0.0

Change it so:
SQL>create pfile='/tmp/pfile.ora' from spfile;
This is to have backup of the spfile.

SQL> alter system set compatible = '10.2.0.1.0' scope=spfile;
Shutdown and restart the database:
SQL> shutdown immediate
SQL> startup

See here
library.blackboard.com/d/?dcd60ae4-db3a-44f6-a687-8e2ddac4038d


Given http://database.itags.org/oracle/140998/

my question is have you run

SQL>startup upgrade

and after that the upgrade script in order to upgrade the Dictionary of the DB?


Next advise is to run AWR report (choose HTML version):


select snap_interval, retention   from dba_hist_wr_control;

SELECT  *  FROM  DBA_HIST_SNAPSHOT ORDER BY BEGIN_INTERVAL_TIME;

SELECT SNAP_ID, STARTUP_TIME ,BEGIN_INTERVAL_TIME,  END_INTERVAL_TIME FROM  DBA_HIST_SNAPSHOT ORDER BY BEGIN_INTERVAL_TIME;

 

spool c:\AWR_P646.txt

@ ?/rdbms/admin/awrrpt.sql




Top Expert 2008

Commented:
Also

http://docs.oracle.com/cd/B19306_01/server.102/b15658/appa_aix.htm

Administering Oracle Database on AIX

Author

Commented:
I have read all the documents and implemented the VMM settings on AIX version.

Regarding the Startup Upgrade Script . I did the Oracle 9i to 10g upgrade using the DBUA (upgrade Assistance ) so there will be no need to give the startup upgrade at startup.

Anyhow i am attaching 2 database AWR report when the datapump was running so we can have the idea about the problem.

After all the vmm settings and reboot of server the problem still persists.

Does this parameter gives you the problem "db_block_size"=32768 . I am having this parameter in the
database which i am taking Datapump Export.
awrrpt-1-554-555.html
awrrpt-1-581-582.html
Top Expert 2008
Commented:
No, db_block_size is set to values typical for big datawarehouses.
But this can not harm the datapump.
If you have more cores then try to use paralel option of the pump (using also the % option to place the output in many files, but limit to size of the data files).

Have you tried do change "compatible" to 10.2.0.1  ?

Where your Pump logical directory is physically placed? The best choice will be to place it on separate disk drive far away from the datafiles. so you will icrease the concurent execution of the jobs.

I have read the AWR report. Haven't encountered something wrong.

Stil the paging is bad symptom. See thoroughly the SGA parameters and the sum of the size of all SGAs (I mean of all instances). It is possible that tthe total amount ist bigger as your physical RAM. Try to involve a memory investigating tool that graphically shows the memory consuption. Be aware that Unix uses as disk data cache the free RAM, so try to distinguesh the size of this cache from the real RAM consumption.