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.
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.
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.
What you tell us is very similar to use swap space on disk. This makes everything very slow.
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
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=129496729 6
and
sga_max_size=5905580032
Do you really need so much cache size.
Big SGA works slowe.
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=129496729
and
sga_max_size=5905580032
Do you really need so much cache size.
Big SGA works slowe.
ASKER
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.
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
session_max_open_files=20
Seee also the sessions parameter:
SQL> SELECT count(*) FROM v$session;
Check sessions parameter:
SQL> show parameter session
ASKER
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 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-d b3a-44f6-a 687-8e2dda c4038d
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
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/?
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
Also
http://docs.oracle.com/cd/B19306_01/server.102/b15658/appa_aix.htm
Administering Oracle Database on AIX
http://docs.oracle.com/cd/B19306_01/server.102/b15658/appa_aix.htm
Administering Oracle Database on AIX
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check this doc for more info
http://youyus.com/wp-content/uploads/html/Checklist%20for%20Slow%20Performance%20of%20Export%20Data%20Pump.htm