Import job is slow

Sometimes import becomes very slow and to improve performance it's better to clean purge sysaux table space.

Below are the simple sql's those can be executed to achieve this.

connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql

Also check max size and autoextend of sysaux related datafiles. Those might be full and need to extend. But default setting of datafiles needs to change if above method does not work.

This helps when AWR performance is slow.I can safely remove the AWR metadata tables and recreate them.

There is a good link that will give more information for some of the basic impdp operations.

http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/comment-page-1/

alternatively we can run impdp -help to find out all the options.

For export slow, follow below link
http://dbaharrison.blogspot.in/2014/07/datapump-export-is-slow-because-i-did.html

ORA-08102: index key not found, obj# 255898, file 15, block 758743 (2)

We encountered this issue couple of times during database migration or in client environment. This issue can be due to block corruption of index(or may be table as well)

I noticed that rebuild index online or dropping or re-creating the index maynot help in some of the cases.

We did following operations and it did not work.

1. Drop FCST_SELECT and HIST_SELECT indexes
2. Gather stats on HIST and FCST tables
3. Re-create FCST_SELECT and HIST_SELECT indexes

Below was the underlying query which was resulting the error:

DELETE /* HistPurgeDAODeleteHint */

 /* HistPurgeDAODeleteHint */

FROM TAB1 h

WHERE (h.dmdunit, h.dmdgroup, h.loc, h.histstream) IN

 (SELECT /* HistPurgeDAOSelectHint */

 /* HistPurgeDAOSelectHint */

 dm.todmdunit,

 dm.todmdgroup,

 dm.todfuloc,

 m.histstream

 FROM XXXX dm, YYYYY pr, ZZZZ m

 WHERE pr.map = :1

 AND pr.action_number = :2

 AND pr.action_group = :3

 AND pr.action_group_set_id = :4

 AND pr.map = dm.map

 AND pr.dmdunit = dm.todmdunit

 AND pr.dmdgroup = dm.todmdgroup

 AND pr.loc = dm.todfuloc

 AND pr.model = dm.tomodel

 AND dm.tomodel = m.model

 AND dm.histtype = 1)

 AND h.TYPE <> 3;

Note that FCST_SELECT is function based index but function always results into deterministic value 

CREATE INDEX HIST_SELECT ON TAB1

(DMDGROUP, DMDUNIT, LOC, HISTSTREAM, STARTDATE, 

"STARTDATE"+"DUR"/1440)

To resolve this issue we tried below approach and it worked.

1. Create a back up table with same structure and data

2. Drop existing table. 

3. Rename back up table to existing table name. 

As it was dev environment above mentioned approach is good but I'm still looking for actual root cause of this issue.

Note: If we remove function from the index it was working fine. 

Also we noticed that in dev environment index was enabled for parallel. Also there is another PK index defined that is overlapping most of the columns with this index.

Oracle Advanced Compression Option for LOB

Good news is that with oracle 12C oracle by default enabled SECUREFILE  for LOB objects. But it is not enabled by default for Oracle internal storage like system table spaces.

Here is a link that will give more information.

http://blog.dbi-services.com/oracle-12c-securefiles-enhancements/

Sample code to convert a column from Basicfile lob to securefile lob or to change any other component.

DECLARE
   v_needschange   NUMBER := 0;
BEGIN
   SELECT COUNT (1)
     INTO v_needschange
     FROM user_lobs
    WHERE     table_name = 'TAB1'
          AND column_name = 'COL1'
          AND NOT (    cache = 'YES'
                   AND securefile = 'YES'
                   AND deduplication = 'NO'
                   AND compression = 'NO');

   IF v_needschange > 0
   THEN
      DBMS_OUTPUT.put_line (
         'Column COL1 of table TAB1 is not defined as either secure file or keep duplicates, or no compress or cache as yes');

      EXECUTE IMMEDIATE
         'ALTER TABLE TAB1 ADD (COL11 BLOB) LOB(COL11) STORE AS SECUREFILE (KEEP_DUPLICATES CACHE)';

      EXECUTE IMMEDIATE
         'UPDATE TAB1 SET COL11 = COL1';

      EXECUTE IMMEDIATE 'ALTER TABLE TAB1 DROP (COL1)';

      EXECUTE IMMEDIATE
         'ALTER TABLE TAB1 RENAME COLUMN COL11 TO COL1';
   END IF;
END;
/