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.

6 Comments:

Blogger Suseela Susiee said...




Great and useful article. Creating content regularly is very tough. Your points are motivated me to move on.


SEO Company in Chennai

7:14 AM  
Blogger Aasha said...

Wow amazing i saw the article with execution models you had posted. It was such informative. Really its a wonderful article. Thank you for sharing

Email Marketing Chennai

12:48 AM  
Blogger Giri Mani 2 said...

Thanks for this blog. All screenshots can explain the full blogs easily. This is very easy to understand. Using this screen shots know the details becomes easy.
Digital Marketing Company in Chennai

5:56 AM  
Blogger Luckperson said...

For this kind of back end process we can learn java training for front end process.You have shared very informative news with us. Your way of explanation is easy to understand SAP.

10:23 PM  
Blogger subuvenni said...

i really like this blog.And i got more information's from this blog.thanks for sharing!!!!
Digital Marketing Company in Chennai

10:50 PM  
Blogger lakshmi deepa said...

Thank you for your sharing information..Its very interesting and useful.. awesome article
SAP Training in Chennai
SAP Basis Training in Chennai
SAP SD Training in Chennai
SAP FICO Training in Chennai

11:06 PM  

Post a Comment

<< Home