R3load and Advanced Compression during installation

[former_member182313]

Hello,

I reviewed note 1436352: Oracle 11g Advanced Compression for SAP Systems and Uwe Specht's white paper: http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f0774cd8-03dd-2d10-0897-8ca1ffc38....

Our R3ldctl and R3load both compiled in Oct 2010. Prerequisites as per Uwe's whitepaper met.

We used COMPRESS_FOR_OLTP option for tablespaces in SAPINST and installed on Solaris system (source also Solaris).

I used "select compress_for, count(*) from all_tables group by compress_for" to verify the number of tables with Compress_for OLTP on. And it showed correctly for almost all tables.

I compared the sizes of Source and Target. I didn't see significant reduction in size. The reduction was something like 7%. So I suspected COMPRESS_FOR OLTP didn't really work. I decided to test by creating another table from an existing table of target system using CTAS method. I saw significant reduction in size.

In order to confirm large first_extent was not the problem, I created two tables from an existing table. One with compress for OLtp option and another table without that option. Both tables used 64K extent size; Compress for oltp table showed significant reduction(<20% of original table) in size whereas new table without that option showed the same size as original table.

Note 1436352 states "OCI Direct path (used by R3load for example)" is supported. However I don't see it working except the flag set correctly for COMPRESS_FOR OLTP for future inserts.

Did anyone observe this behavior?

Thanks for your time,

Bala

[stefan_koehler · Accepted solution]

Hello Bala,

a compression factor of round about 7% sounds some kind of weird indeed.

Have you already checked / compared the block usage with the PL/SQL procedure DBMS_SPACE.SPACE_USAGE for that specific table on both systems (source and target): http://download.oracle.com/docs/cd/E11882_01/appdev.112/e25788/d_space.htm#CACBDJIH

Could you also please run this query for that specific table on both systems:

SQL> select TABLE_NAME, COMPRESS_FOR, PCT_FREE from DBA_TABLES where TABLE_NAME = '<TAB>';

You could also do a block dump (on the target system with OLTP compression) to verify if all possible data is compressed/deduplicated - maybe you don't have many duplicates in each block.

As Direct Path Inserts should work with OLTP compression - you could also "simulate" it by using the APPEND hint:

http://download.oracle.com/docs/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF50901

Regards

Stefan

[former_member182313 · Accepted solution]

Hello Stefan,

7% reduction is not due to compression but probably due to elimination of fragmentation I guess.

To your suggestion that there were not many duplicates:

As I explained before, I performed the following:

1) On target system, I created two tables from an existing table using CREATE TABLE AS SELECT *

2) I used COMPRESS FOR OLTP for one table; and NOCOMPRESS for another table. Both table used initial extent size of 64K.

3) New table that was created with COMPRESS FOR OLTP showed significant reduction in size.

4) New table that was created with NOCOMPRESS showed same size as original table.

IMO this clearly demonstrates that there are duplicates in the table.

I see this behavior in both Oracle/Solaris to Oracle/Solaris; we also tried this from Oracle/Solaris to Oracle/AIX.

I hope this is clear.

Best regards,

Bala

[stefan_koehler · Accepted solution]

Hello Bala,

1) On target system, I created two tables from an existing table using CREATE TABLE AS SELECT *

Ok - as far as i understood the existing table in the target system was loaded by R3load and created with OLTP compression.

2) I used COMPRESS FOR OLTP for one table; and NOCOMPRESS for another table. Both table used initial extent size of 64K.

Ok - i guess that the table needs more than one extent and in LMTs the extent allocation algorithm differs - but that should not the topic in here.

3) New table that was created with COMPRESS FOR OLTP showed significant reduction in size.

Ok - the table was created by "CREATE TABLE AS SELECT *" and that's a huge difference from Direct Path Inserts. That is the reason why is suggested to check the block usage with DBMS_SPACE.SPACE_USAGE and doing a block dump or simulate that scenario again with APPEND hint.

As we don't have system access we can only make suggestions how to analyze this issue and get closer to the root cause. As i also mentioned this seems to be pretty strange and is not the "normal" behavior.

Regards

Stefan

[former_member182313 · Accepted solution]

Hello Stefan,

I've screenshots for the table names and size; but don't know how to attach them. We're also doing one more import so I'll document and paste sql statements and results here in a day or two if this message has not been answered.

Thanks,

Bala

[former_member182313 · Accepted solution]

Hello Stefan,

Ok. I'll check what you suggested and let you know soon.

Best regards,

Bala

[former_member182313 · Accepted solution]

Hello Stefan,

I'm working on checking your suggestion. Meanwhile I've a few questions:

Hello Bala,
> 

1) On target system, I created two tables from an existing table using CREATE TABLE AS SELECT *

> Ok - as far as i understood the existing table in the target system was loaded by R3load and created with OLTP compression.
     YES
> 

3) New table that was created with COMPRESS FOR OLTP showed significant reduction in size.

> Ok - the table was created by "CREATE TABLE AS SELECT *" and that's a huge difference from Direct Path Inserts. That is the reason why is suggested to check the block usage with DBMS_SPACE.SPACE_USAGE and doing a block dump or simulate that scenario again with APPEND hint.

I agree Direct path versus CTAS is a huge difference; however I don't see the compression would be handled differently between CTAS and Direct Path in the note 1436352:

" OLTP Table Compression allows data to be compressed during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE.

These operations include:

1. OCI Direct path (used by R3load for example)

2. CREATE TABLE and AS SELECT statements (used by all SAP applications)

3. Parallel DML statements (used by SAP BW for example)

4. Single-row or array inserts (used by all SAP applications)

5. Single-row or array updates (used by all SAP applications)

"

Our objective of using compress for OLTP to reduce the disk space usage. Are you suggesting we wouldn't see the same level of compression ratio with SAPINST as CTAS or REORG? Should we plan on reorging after import is completed to get a better compression ratio?

Thanks,

Bala

[former_member182313 · Accepted solution]

Hello Stefan,

Ok. Yes I see space usage by data is much less in the target system than original table in the source system. However how do I release that unused space? Reorg? The question then is what do I benefit from using compress for OLTP in SAPINST if my objective is to reduce the disk space requirements.

It seems we would see reduction in DB size after importing regardless of whether we use compress_for_oltp or not. That reduction is due to the elimination of fragmentation.

Here is the output from DBMS_SPACE:

Case 1: Original table in target system - imported by R3load

FS1 Blocks = 0 Bytes = 0

FS2 Blocks = 156 Bytes = 1277952

FS3 Blocks = 70 Bytes = 573440

FS4 Blocks = 386 Bytes = 3162112

Full Blocks = 5498 Bytes = 45039616

PL/SQL procedure successfully completed.

SQL> select sum(bytes) from dba_segments

2 where segment_name = 'SMWT_TRC';

SUM(BYTES)

-

---

92274688

Case 2: New table created by CTAS using COMPRESS FOR OLTP option*

FS1 Blocks = 0 Bytes = 0

FS2 Blocks = 0 Bytes = 0

FS3 Blocks = 0 Bytes = 0

FS4 Blocks = 0 Bytes = 0

Full Blocks = 5591 Bytes = 45801472

PL/SQL procedure successfully completed.

SQL> select sum(bytes) from dba_segments

2 where segment_name = 'SMWT_TRC_COMP';

SUM(BYTES)

-

---

47185920

Case 3: Original table in the Source System

FS1 Blocks = 0 Bytes = 0

FS2 Blocks = 1 Bytes = 8192

FS3 Blocks = 0 Bytes = 0

FS4 Blocks = 188 Bytes = 1540096

Full Blocks = 10517 Bytes = 86155264

PL/SQL procedure successfully completed.

SQL> select sum(bytes) from dba_segments

2 where segment_name = 'SMWT_TRC';

SUM(BYTES)

-

---

92274688

Best regards,

Bala

[stefan_koehler · Accepted solution]

Hello Bala,

However I don't see the compression would be handled differently between CTAS and Direct Path in the note 1436352 .... are you suggesting we wouldn't see the same level of compression ratio with SAPINST as CTAS or REORG?

No - please don't get me wrong. Table compression was supported for Direct Load operations much earlier (9i R2) and it is also included in the advanced compression for OLTP. As the OLTP compression (which is deduplication in reality) is an a-sync process which is triggered at round about PCTFREE (my internal tests varied around that value so far) and as Direct Path Inserts works slightly different as "normal" Inserts it can make a difference. This is also documented by the APPEND hint:

http://download.oracle.com/docs/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF50901

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.

The output of the PL/SQL procedure DBMS_SPACE.SPACE_USAGE confirms my guess. To be absolutely sure you need to do some block dump.

Now the question is: Why you bother about that free space (which seems to be caused by the a-sync implemented design) in that table when it can be used in near future. So your system maybe does not become "smaller" but it won't grow in the next time rapidly.

If you care about the currently allocated space only - we need that SQL that is performed by R3load to check the HINTs (afaik there are several HINTs used) to check its impact on OLTP compression.

Regards

Stefan

[former_member182313 · Accepted solution]

Hello Stefan,

Thank you very much for your response. You explained it very well.

And to your question

"Why you bother about that free space ":

Three reasons (possibly more):

1) Backup(and restore) time is going to remain the same.

2) System copy would require much larger disk space requirements even though DB is much smaller.

3) Isn't the system fragmented? There may be performance impact. It may not be worse than before.

At any rate, thanks once again for your response. I'll consider your recommendation on HINT. I'll keep this message open for a day and then close.

Best regards,

Bala

[volker_borowski2 · Accepted solution]

I have another likely idea that I just faced in simple compress reorg.

Doing a compress of D010TAB and D010INC resulted in the table and indexes

having exactly the sames size as before compression.

Shrinking the segments afterwards gave about the expected ~50% space back.

This proves the space being preallocated and not used.

Doing a simple reorg again blew the segments up to the old size, allthough they have been still compressed.

Digging further revealed that the segements seem to have been reorged in earlier times and

may be sapdba recreated them then with a very high initial extent size. This is still valid and

will be kept by brspace if not overruled with option "-l".

The sapnote about compression mentions this behavior allthough I never observed this before.

I mind to remember, that R3load exp/imps do something similar with the size categroy, so if your

system was ever R3load copied before, you might have adjusted size categoryes, that do

now simply allocate a too big initial extent.

You can run a tbalter / idalter "shrink" for the entire TS to find out if space from too big initial extents can be released.

Do not worry about "non-existing segments" that will not been shrinked, This are the ones which are

created with the "defered" option of the compression feature.

Volker