SAP Migration exporting large cluster table with RAW field

[Former Member]

Running OSDB migration - We are having performance issues on export - database is ORACLE 11g on UNIX.

There is one key problem table. It has a very large number of rows and is a Cluster table with a RAW field.

The rest of the DB exports and this table continues on for another 24 hours+

Currently it's split into 20 pieces, but still we're getting VERY slow speeds.

The PGA and DB Cache are sized according to SAP recs.

NB of course we cannot split on ORACLE ROWID as this is a Cluster table...

I have some thoughts:

*could it be slow mainly because of the read cost scanning a VERY large table for records? In this case are more or less splits better?
*all blocks read from disk touch the DB cache right? So the table is 10x bigger than the DB cache can be - is there some advice based on this? Should for example the number of parallel running pieces always add up to less than the DB cache size for example? I'm not certain how key this may or may not be.

*I'm not sure why SAP recommend so much RAM for PGA_AGGREGATE_TARGET - it looks to me like that should be scaled back and the db_cache_size further increased

*Sorted - this export of this table must be sorted as it's clustered

Any thoughts on this interesting export issue welcome and I will follow up here.

NB I'm looking at setting up dsitribution monitor but I'm not sure it'll help much if the DB Cache is the bottleneck (still just an assumption at this stage, but CPU/RAM aren't maxed out; though disk i/o very busy)

[ACE-SAP · Accepted solution]

Hello,

Large number of row in a cluster table, is it RFBLG ?

As you are in 11g you can reorg that table to convert that long raw to lob / securefile (better performance)

http://help.sap.com/saphelp_nw73/helpdata/en/46/adf8c2c5b462d1e10000000a1553f7/content.htm

646681  - Reorganizing tables with BRSPACE

835552  - Oracle Database 10g: LONG-LOB migration

1431296  - LOB conversion and table compression with BRSPACE 7.20

Did you use the Oracle optimized splitting script ?

1043380 - Efficient Table Splitting for Oracle Databases

It is then possible to split RFBLG that way:

exec table_splitter.ranges ('RFBLG', 'BELNR',<number of chunk>);

I know you already moved you message once but you might get better answers if posted in the Oracle area...

Best regards

[Former Member · Accepted solution]

Good guess - no it's CDCLS - a common one to grow big and no time to archive.

Re splitting - it's split into 20 pieces (as already stated) and I'm looking to break it down further and then use Distribution Monitor to split load across extra servers that are available.

It seems to me that in addition the key here will be PGA configuration to allow sorting in memory.

Re reorg of the table - sounds like this can be done online and that would be good if the benefits are worthwhile and I will read up on this - thank you for the tip! Have you any experience with (a) how much performance benefit this gives, or (b) how long it takes (this is 1 billion+ rows)

[ACE-SAP · Accepted solution]

Secure files are supposed to be up to 5  time faster than Long raw according to Oracle,

according to SAP enhancement is around 20% depending on row size.

source : oracle-oug-ireland-11g-new-features-upgrades.

source : SAP on Oracle - Development Update June 2013

The bad point is that for CDCLS as for RFBLG (and all cluster tables) the data inside the raw field is compressed thus the Lob data is under 4Kb and is stored inline inside the table and not in the Securefiles/lob segment...

So I'm not really sure that the conversion to long raw will really improve the export time.

Did you use the Oracle specific splitting script from note  1043380 ?

It really speed up the export.

I cannot tell you how much it will improve the export speed as I did always used it for all the exports I've done.

Distribution monitor will help if the source system get overloaded by the export (CPU - I/O) allowing to spread the load among other servers.

As you are reading huge amount of data from DB in 11g it should not get into the cache, so DB_CACHE_SIZE should not be an issue.

Have a look to that note, there are some change documents that are not related to business data that can be archived alone.

1257133  - CD: Archiving options for CDCLS

The table cluster CDCLS contains a very large amount of data. You should reduce this amount before a Unicode conversion, for example.

Best regards

[Former Member · Accepted solution]

Hey Yves - yes I looked a archiving but it is not an option in this case unforutnately.

I do indeed always use the SAP ORACLE SQL table splitter (note 1043380) but in this case the split pieces of thie particuar table run a LOT slower on export than any other table (i.e. MB/s export rate drops off a cliff).

My analysis so far suggest this is due to the overhead sorting a table with 1 billion rows in PSAPTEMP (i.e. on disk). My plan is that with Distribution Monitor I can split the table into many smaller pieces and increase the _pga_max_size setting (PGA per process) to allow sorting in memory, which should really speed things up.

Your point re a large read in 11g avoiding the DB cache is very interesting. In this case I wonder if it would in fact be a lot quicker to try and export the whole table at once, and make sure I have a 500GB+ TEMP TS to sort it all in one go it..... Give the time I will try this. I think currently as the table is in pieces it will hit the cache, which of course really is for re-readability and not required for an export...... in fact following that line of thinking is there no way to turn off the DB caching during an export?

The controlled tests continue - I will post updates for the community. Any more experiences/thoughts/advise is always very welcome

Message was edited by: Jamie Neilan

[ACE-SAP · Accepted solution]

Hi Jamie,

I did not pay attention to the number of lines, 1 billion / 500 Gb, that's tough...

With such a big table you should go far beyond 20 chunks, I would recommend at least 200, but you can go up to 500.

Distribution monitor can also help to unload your DB server (mainly on CPU & RAM but also on I/O).

As a last option or for testing performance, you can try to export CDCLS with datapump putting a high level of parallelism.

I do not think DB caching can be an issue, your reads will go through direct path. Check that thread on that point

Possible I/O Performance Problem - 'direct path read'

http://scn.sap.com/message/14438201

Best regards

[Former Member · Accepted solution]

Hi Yves - good conversation - thanks for engaging. Good to speak with someone on the same wavelength.

The DB cache I wonder about - OK I was trying to sort 500GB it would skip DB cache; but if each piece of the table is only 3-5GB then perhaps it will cache it by default I think?

Currently I'm running several controlled tests with more PGA and less SGA (to encourage sorts in memory). After that I will try more SGA and less PGA - see which yields better results.

Then at the end I intend to get Distribution Monitoring (DM) going and really ramp up the power applied to this issue - though I'm intrigued about how to tune memory for DM - i.e. PGA memory will still be local to the DB so how do you fully leverage the extra servers RAM/CPU for these exports?  Interesting...

[ACE-SAP · Accepted solution]

Hi Jamie,

You're welcome, that the pleasure of SCN, learning by sharing knowledge, experience.

Oracle parameters should not be an issue, you've already done all what's needed to be done.

Reducing DB_CACHE_SIZE, increasing PGA, do not forget to also increase sessions & processes.

936441  - Oracle settings for R3load based system copy

DM will help because all the SAP process (R3load) launched for extracting / importing data does consume CPU, RAM and I/O. With DM you can transfer that load from DB instance to external servers.

You should define many more chunks for CDCLS, go up to 400, it should have a greater impact than Oracle memory tuning.

How many parallel process have you defined in SWPM, how many CPU core on you server (CPU_COUNT on Oracle) ?

By the way is your DB target an Oracle one ?

Are you performing Unicode migration ?

Best regards.

[Former Member · Accepted solution]

Hi Yves - ORACLE 11g. Running just an osdb migration. I have a few more tests to run today/tomorrow. I will update back.

NB Yves - my concern re 400 pieces etc. I do wonder if this is really adding a large cost scanning the table (even an index takes a while for over 1 billion rows). Do you find there is a tipping point where splitting too much starts to adversely affect export performance?

Message was edited by: Jamie Neilan

[ACE-SAP · Accepted solution]

Hi

I never came to a point where splitting too much did slow down the process... but I never had to process such gigantic tables.

It seems you have an opportunity to test this

Too many chunks could cause some overhead (negative effect) especially if you are note using DM.

Best regards

[Former Member · Accepted solution]

100 pieces is actually working quite well - currently planning is one of two options

1) distribution monitor - export all CDCLS packages on 2nd meaty temp "app" server; while all other packges export on the DBCI

2) export 90%+ of CDCLS ahead of migratoin during uptime - this seems safe as entries in CDCLS are written (inc timestamp) and never updated

Actually I like both options - though 1 allows for more offline testing.

[Former Member · Accepted solution]

**Update

well with the table in 100 pieces a full export of a copy of the db on a powerful server is down to a reasonable time frame (circa 15 hours).

I'll be testing this across two servers with Dist.Mon soon to see just how short we can get this export time.

[ACE-SAP · Accepted solution]

Hi Jamie,

Good news, thank for keeping us updated.

I'm curious, what is the whole DB size, on which U*x are your servers running ?

Best regards

[Former Member · Accepted solution]

Over 2TB - running export on HPUX

[Former Member]

**UPDATE.

To further try and improve performance we're looking at a new index on CDCLS that only includes the split field "CHANGENR". Despite including COMPUTE STATISTICS on index creation R3load is still reading the table using the standard 5-field index.

Can anyone advise best way to convince R3load to use the new index on export?

Note this is a test clone DB with no SAP layer installed so the index ONLY exists in the DB, not in the Data Dictionary..

Regards, Jamie

[Former Member]

Fascinating.

All my table split files (WHR files) are now updated so that the hint in them suggests to ORACLE to use the new index (only CHANGENR in it).

However an ORACLE snapshot report shows that for some it's using this new one now, but for others it's using the other, original, index despite the hint.

Could this be ORACLE "being clever" and using the alternate due to contention on the new index?

I'd like to think so.

Anyway we will see if this has a decent impact on export speed.

This is becoming more a blog

Regards, Jamie

[Former Member]

Hi Jamie,

I am facing same issue with table CDCLS, In my case table is 400GB , splitted  into 22 pieces using SQL splitter, it took more than 100 hrs to export CDCLS.  as you already faced this situation couple of year before , appreciate if you can share the details on how to reduced export run time of this table.

Regards

Pat

[Former Member]

Hi Pat,

Funny I was just re-reading this the other day. Simplest things to do:

1) Re-org the table - impact can be enormous

2) Be sure to try and do this offline and include a reorg of the index you need to use (PK possibly) - maybe use an EXPLAIN plan to check what you're using

3) You can create a 1-field index against the tables matched to the WHERE clause in your WHR file, add HINTS into the WHR statement and re-run stats - then it should read the single-field index uber-fast

If you take your time (always an issue) and do all three (I would suggest the first 2 to start with) then you should be very nicely sped up.

Broader scenario?

Regards, Jamie

[Former Member]

Thanks Jamie for quick response.

Meanwhile I tried 1-filed index and able to reduce runtime significantly with 200 pieces.

I will try other recommendation.

Thanks,

Pat

[Former Member]

UPDATE:  As this table must be sorted for export I'm reading into how the PGA areas are used as these are tried to sorting before TEMP TS (which of course is disk and a LOT slower). It's a 500GB table so I'm hoping if we can split it into 100 pieces (roughly 5GB per piece) then we may be able to size the PGA large enough to run sorts in memory. However this is a tricky business to manage the PGA in such a way that sorts can be done. Take a 5GB set of data - what settings need to be made to allow the sorting of this data in memory.

For example is it enough for _pga_max_size to be set to 6GB...... from some forums I read it may need to be 10GB to allow sorting using that size/2.