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.



oraclescsaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
0
schwertnerCommented:
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.
0
oraclescsaAuthor 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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

schwertnerCommented:
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.
0
oraclescsaAuthor 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.
0
schwertnerCommented:
Increase

session_max_open_files=20

Seee also the sessions parameter:

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

Check sessions parameter:

SQL> show parameter session
0
oraclescsaAuthor 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 ?
0
schwertnerCommented:
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




0
schwertnerCommented:
Also

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

Administering Oracle Database on AIX
0
oraclescsaAuthor 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
0
schwertnerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.