Thursday, December 19, 2013

Poor export performance of SAP database and how to speed it up.

During an export (regular exp not datapump) of SAP database with CONSISTENT=YES I've found that performance was suboptimal. There was 100% CPU consumption, and even empty tables were exported one per 2-3 sec!
The export took so long time that finally caused it crash with "Snapshoot too old".

Investigation showed that the exp called "SYS"."DBA_CAPTURE_PREPARED_TABLES" view for each table. And, you may be know, there can be 80-90 thousands of tables in SAP schema.
In my DB there were 3 SAP schemas so total number of tables was quite big.

I've got an execution plan of the offensive query that exp called:

SELECT NVL(MAX(CO.SCN), 0) FROM ALL_CAPTURE_PREPARED_TABLES CO WHERE CO.TABLE_OWNER = :B2 AND CO.TABLE_NAME = :B1;

And the plan shows full scan of SYS.CDEF$ table.

ALL_CAPTURE_PREPARED_TABLES calls DBA_CAPTURE_PREPARED_TABLES.
The query of DBA_CAPTURE_PREPARED_TABLES view in my Oracle 10g is:

 select u.name, o.name, co.ignore_scn, co.timestamp,
       decode(bitand(cd.flags, 1), 1,
              decode(bitand(co.flags, 1), 1, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(cd.flags, 2), 2,
              decode(bitand(co.flags, 2), 2, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(cd.flags, 4), 4,
              decode(bitand(co.flags, 4), 4, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(cd.flags, 8), 8,
              decode(bitand(co.flags, 8), 8, 'IMPLICIT', 'EXPLICIT'), 'NO')
  from obj$ o, user$ u, streams$_prepare_object co,
       (select obj#, sum(DECODE(type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)) flags
          from sys.cdef$ group by obj#) cd

  where o.obj# = co.obj# and o.owner# = u.user# and co.obj# = cd.obj#(+);


Please note highlighted text. The combination of aggregating inline view cd and its left outer join caused Oracle to full scan whole CDEF$ table, aggregate it to calculate "FLAGS" for each table and then hash join it to just one row from streams$_prepare_object to get cd.FLAGS.
Not good.

To speed up the query I tried all legitimate ways - gathered stats, set performance hints like FIRST_ROWS(1) PUSH_PRED(cd) USE_NL_WITH_INDEX(cd I_CDEF2) and so on.
But nothing convinced the optimizer to push OBJ#= predicate into the cd inline view.

So I've just ended up with total rewrite the view's query as:

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_CAPTURE_PREPARED_TABLES" ("TABLE_OWNER", "TABLE_NAME", "SCN", "TIMESTAMP", "SUPPLEMENTAL_LOG_DATA_PK", "SUPPLEMENTAL_LOG_DATA_UI", "SUPPLEMENTAL_LOG_DATA_FK", "SUPPLEMENTAL_LOG_DATA_ALL") AS
  /* made by MM to speedup export */

  select
       u.name, o.name, co.ignore_scn, co.timestamp,
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 1), 1,
              decode(bitand(co.flags, 1), 1, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 2), 2,
              decode(bitand(co.flags, 2), 2, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 4), 4,
              decode(bitand(co.flags, 4), 4, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 8), 8,
              decode(bitand(co.flags, 8), 8, 'IMPLICIT', 'EXPLICIT'), 'NO')
  from streams$_prepare_object co
  join obj$ o on o.obj# = co.obj# 
  join user$ u on o.owner# = u.user# 
  left outer join sys.cdef$ cd on co.obj# = cd.obj#
  group by u.name, o.name, co.ignore_scn, co.timestamp, co.flags;


that produces same results, but 5000 times faster.
I know, it is not good to modify dictionary objects. But what else to do when you need the thing done fast?
Export of empty tables become lightning fast, and full export has been finally taken.
If you are going to do this trick too, do not forget to restore the original view's SQL, that is provided by Oracle.