2011-11-27

expdp slow after upgrade from 11.1.0.6 to 11.1.0.7

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.

Symptoms

DataPump Export (EXPDP)/Import (IMPDP) runs very slow after upgrade from 11.1.0.6 to 11.1.0.7. 

Changes

Upgrade to 11.1.0.7 was successful. Database registry shows all components are valid and no invalid objects. No changes in the database.

 

Cause

The 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