| DataPump Export (EXPDP) Runs Very Slow After Upgrade From 11.1.0.6 to 11.1.0.7 (Doc ID 1075468.1)Applies to:Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.7 - Release: 11.1 to 11.1 Information in this document applies to any platform.
SymptomsDataPump Export (EXPDP)/Import (IMPDP) runs very slow after upgrade from 11.1.0.6 to 11.1.0.7.
ChangesUpgrade to 11.1.0.7 was successful. Database registry shows all components are valid and no invalid objects. No changes in the database.
CauseThe import log shows waits for very long time on SYS.SCHEDULER$_EVENT_LOG.
From Import.log:
Objekttyp DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ wird verarbeitet
Wait
Current Wait Event db file scattered read
Current Wait Class User I/O
Wait Duration 0 (s)
P1 file# E:\ORACLE\ORADATA\DWHPROD...SYSAUX01.DBF
P2 block# 1212177
P3 blocks 128
Object SYS.SCHEDULER$_EVENT_LOG Further investigation narrowed down to table SCHEDULER$_EVENT_LOG that allocated huge space in tablespace SYSAUX..
Solution
SYS.SCHEDULER$_EVENT_LOG is a system table contains logging information. Here are the options to clear all entries from both the job and window logs:
1) Run in SQL*Plus:
execute DBMS_SCHEDULER.PURGE_LOG();
Or:
2) Run in SQL*Plus:
alter table SCHEDULER$_EVENT_LOG shrink space cascade;
then monitor with:
select *
from (select substr (owner, 1, 10) owner,
substr (segment_name, 1, 30) seg_name,
substr (segment_type, 1, 10) type,
bytes,
extents,
rank() over (order by bytes desc) position
from dba_segments
where tablespace_name = 'SYSAUX'
order by bytes, extents desc)
where position < 10
order by position;
After cleared and purged all scheduler event log entries, DataPump export/import completes in short time. |
Niciun comentariu:
Trimiteți un comentariu