impdp very very long ...
Giganews
I am the happy heir of a server running Red Hat Enterprise 3 with Oracle
10.2g. This server is used for integration purposes. Every night, the prod
db is exported to a dump file using expdp, and then we use impdp to import
the schemas, one after the other. The export (5GB) is done in about 30
minutes, the import takes about 7-8 hours ! There are very few people using
this server/this db, the machine is not overloaded at all. I tried to import
on Sunday, when really nobody works on it, same behaviour. It would be nice
if somebody could give me some advice where to start searching. Of course I
already rebooted the server, no change.
pher
joel garry
There are several bugs, depending on which features you are using.
search metalink.oracle.com, see the faq Note:556636.1, don't calculate
statistics as part of the import.
In the future, remember to post your exact version (ie, 10.2.0.4), in
case someone happens to know about a particular issue.
jg
--
@home.com is bogus.
http://catless.ncl.ac.uk/Risks/25.22.html#subj2
sybr...@hccnet.nl
wrote:
The first question with any performance problem is
WHAT IS IT WAITING FOR?
--
Sybrand Bakker
Senior Oracle DBA
Giganews
at some point in the alert log, I get several messages
MMNL absent for 1205 secs; Foregrounds taking over
MMNL absent for 2856 secs; Foregrounds taking over
etc.
I found on Metalink that this might be a problem with the Undo tablespace
datafile definition, when max size < initial size. This was probably the
case in our system: we had set Initial Size to 1024000 KB, and max size to
1000 MB. I changed max size to 2 GB and the problem seems to be fixed.
Yesterday, the procedure took about 90 minutes, which is a normal duration.
pher
<sybr...@hccnet.nl> wrote in message
news:024n745ngt6u5dv4u...@4ax.com...
Mladen Gogala
> I don't know WHAT it is waiting for, but I know WHO is waiting: at some
> point in the alert log, I get several messages MMNL absent for 1205
> secs; Foregrounds taking over MMNL absent for 2856 secs; Foregrounds
> taking over etc.
> I found on Metalink that this might be a problem with the Undo
> tablespace datafile definition, when max size < initial size. This was
> probably the case in our system: we had set Initial Size to 1024000 KB,
> and max size to 1000 MB. I changed max size to 2 GB and the problem
> seems to be fixed. Yesterday, the procedure took about 90 minutes, which
> is a normal duration.
>
> pher
I would be most interested in the ML note that lead you to the conclusion
about "MMNL absent" notes being caused by the undo tablespace. I found the
following note: Note:567562.1
I will quote the most important part:
Cause
The Memory Monitor Light (MMNL) process is a new process in 10g which
works with the Automatic Workload Repository new features (AWR) to write
out full statistics buffers to disk as needed. These messages can be
generated if you have the database in restricted mode.
Sat May 10 02:28:03 2008
Starting ORACLE instance (restrict)
...
Sat May 10 03:37:39 2008
MMNL absent for 4159 secs; Foregrounds taking over
Sat May 10 06:13:28 2008
MMNL absent for 13509 secs; Foregrounds taking over
Sat May 10 06:42:35 2008
MMNL absent for 15256 secs; Foregrounds taking over
Sat May 10 07:26:42 2008
MMNL absent for 17902 secs; Foregrounds taking over
Sat May 10 10:32:08 2008
MMNL absent for 29029 secs; Foregrounds taking over
Sat May 10 10:37:12 2008
MMNL absent for 29334 secs; Foregrounds taking over
Sat May 10 11:38:47 2008
ALTER SYSTEM disable restricted session;
Solution
Messages are informational only. They will stop once the database is no
longer in restricted mode.
As for the "I don't know what is it waiting for", I believe that 10g
might have
tables like V$SESSION_WAIT and V$SESSION_EVENT for that purpose.
--
Mladen Gogala
http://mgogala.freehostia.com
joel garry
You should have clicked on the links at the bottom of that. They lead
to Note:462402.1 which leads to bug 5470031 .
Search MMNL in the bug database. Looks like there are a lot of not-a-
bug type hits there, like 5978008 for example - you look at the
related bugs and they are closed due to lack of data, or non-
accessible. I get the feeling from poking around there that these are
the sort of things that cause lots of minor curiousnesses from a new
feature, but little is ever really told to us out in the real world,
and they go away on upgrading or "empiricist tuning." Then most
people sort of forget about them. The "sweep it under the carpet"
method of software quality control.
Personally, I find the concept of a tuning process that trips over its
own genitals very humorous.
(While I may be making fun of "empiricist tuning," I think the fact
that it here solved the problem shows why so many people think it
works - a magick incantation fixed the problem, which may or may not
have reflected the actual problem. I think the Rapid Visibility stuff
is good, though dangerous in the wrong hands, which could be the
majority.)
I think it is very odd that the bug as stated has not been fixed yet.
I take that as an indicator that it must be hard, and therefore must
extend over a lot of code, perhaps because there are now so many new
features like ASSM and AWR tacked on top of very old dictionary
objects.
jg
--
@home.com is bogus.
"Something we are generating is causing the db to puke. " - Metalink
not-a-bug 6775598