Link to home
Start Free TrialLog in
Avatar of oraclescsa
oraclescsa

asked on

Database Hang when taking datapump Export

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.



Avatar of Praveen Kumar Chandrashekatr
Praveen Kumar Chandrashekatr
Flag of India image

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
Avatar of schwertner
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.
Avatar of oraclescsa
oraclescsa

ASKER

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

session_max_open_files=20

Seee also the sessions parameter:

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

Check sessions parameter:

SQL> show parameter session
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 ?
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




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
ASKER CERTIFIED SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial