Questioning whether or not to reorg the table RFBLG.

[Former Member]

I'm questioning whether or not to reorg the RFBLG table.

When I ran the show table in BRTOOLS, I got the following information:

10 - Allocated space in KB (space) ....... 7836576

11 - Used space in KB / % (used) ......... 7836568 / 100.00

12 - Pure data in KB / % (data) .......... 390542 / 4.98

So does that mean I should reorg because there is quite a bit of unused space?

Any guidance would be greatly appreciated.

Pauline

[jim_ma]

If the table has a long raw column, the import process will be super long. i remember my co-worker took about 20 hours for 20G table.

The new Oracle datadump tools sounds very promising for the import performance. Have you tried this? It is integrated with the brspace.

[Former Member]

Hello Stefan,

I work in the SAP Active Global Support. My focus is the support of large and critical SAP customers in terms of the Oracle database.

Kind regards

Martin

[stefan_koehler]

Hello Martin,

thanks for your answer.

It is great, that SAP is giving you the time to investigate internals/functions of oracle and publish your results.

Is this a special-team of the "SAP Active Global Support" that you are working in?

Regards

Stefan

[Former Member]

Hello Stefan,

well, it's the "Applications Platform" team in the Solution Operations Support. The team takes care for the different databases and technical aspects with a particular focus on performance. It is always of an advantage to have strong knowledge about external software like the Oracle database also on SAP side. Otherwise we would have to rely too much on our partners.

Regards

Martin

[stefan_koehler]

Hello Martin,

> The team takes care for the different databases and technical aspects with a particular focus on performance

That sounds really great

One last question:

Is the "Applications Platform" team (Solution Operations Support) with the focus on oracle performance spread all over the world or is it located in Walldorf only?

Regards

Stefan

[Former Member]

Hello Stefan,

we have also colleagues in foreign countries like China or US.

Kind regards

Martin

[Former Member]

Hello Pauline,

if the BRSPACE information is right a reorganization would definitly make sense in order to reduce fragmentation and decrease the size. Nevertheless you should ask yourself how it can be that 95 % of the table is empty at the moment. Was there a big archiving activity or client deletion in the past? If not you always have to take into account that the data displayed by BRSPACE is not 100 % right. Then you should e.g. check SAP note 821687 in order to understand better if and to which extent the table is fragmented.

Regards

Martin

[stefan_koehler]

Hello Martin,

my question is off-topic and relating to you

I have read some papers from you and also have seen that you do workshops on DOAG.

I am wondering: In which branch of SAP do you work or better said what is your job at SAP?

Regards

Stefan

[Former Member]

Thank-you for your response.

I have since started the reorg process, but this is like my 4th try. With 8 million + records it is taking forever. As of yet, I have not regained any space. In fact I have had to extend psapclud 6G, so it doesn't run out of space.

So does that mean the brspace results is misleading?

the more notes I read, it points me to use DB02 (detailed analysis) to determine whether to reorg or not. could it be because there is a long raw field?

Still confused.

thanks in advance for your responses.

Pauline

[Former Member]

In case of import performance problems with LONG RAW please see SAP note 1028099.

As I told you I have doubts that the BRTOOLS output is right here. Perhaps it suffers from the fact that statistics created with DBMS_STATS don't calculate proper AVG_ROW_LEN and AVG_COL_LEN values if LONG RAW columns exist. What you could do is the following:

Create statistics with ANALYZE:

analyze table rfblg estimate statistics sample 1 percent;

Check the AVG_SPACE value for RFBLG in DBA_TABLES:

select AVG_SPACE from dba_tables where table_name = 'RFBLG';

If it is smaller than 2000 (bytes free per block), there is no significant fragmentation.

Kind regards

Martin

[Former Member]

Thank-you. It seems that on one of the other systems with similar data is is not fragmented when I ran the command:

SQL> select AVG_SPACE from dba_tables where table_name = 'RFBLG';

AVG_SPACE

-

---

1079

Maybe that is why it taking so long to import. I will use that to determine fragmentation.

thank-you so very much!

Pauline