Reorg Oracle Database for BW

[Former Member]

Hi,

We are about to start a project to reorganize our BW instance which runs on Oracle 10.2.0.4.

This operation is well know by our company.

Yet, we are not really expert on dedicted points regarding BW and reorganizing.

Does anyone has guidelines or best practices to reorganize huge BW tables ?

Is it a good idea to put huge BW tables in a dedicated tablespace (as we did on R3) or not ?

Is it a good idea to put PSA tables in a dedicated tablesspace ?

Thanks for your answer.

[Former Member · Accepted solution]

Issac,

Just to add check below links & sap notes

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70c8f19f-8010-2c10-ac89-8d828039...

SAP Note 541538 - FAQ: Reorganization.

Also note down that you cannot perform online reorganization for tables with LONG or LONG RAW fields but you can convert them to CLOB or BLOB online. After this conversion, you can reorganize all tables online. Check see SAP Note 646681 for more information,

See below link for how to do table reorg with BR*tools

http://help.sap.com/saphelp_nw70/helpdata/EN/58/6bec38c9fa7e44b7f2163905863575/frameset.htm

Hope it helps

Thanks

Sushil

[former_member204746 · Accepted solution]

no BW tables contain LONG RAW, only tables from the SAP NETWEAVER layer might contain LONG RAW.

[Former Member · Accepted solution]

Sushil,

We don't want to convert to lob because it leads to very porr performance.

tanks.

[former_member204746 · Accepted solution]

Poor performance? SAP says about a 30% decrease on these converted tables only. the rest of tables are not affected.

ability to reorg online might overweight the small performance loss. it's up to you, but as I stated, all BW tables do not use LONG RAW.

[Former Member · Accepted solution]

In note 835552, SAP says : "you should(for performance reasons) continue to process SAP tables using the data type LONG. Tables with lob columns result in an increase of the CPU consumption."

That's why, we don't convert LONG or LONG RAW to LOB.

[former_member204746 · Accepted solution]

Note that all new Netweaver 7.0 systems are installed using LOB and not LONG RAW. So, SAP is not "consistent" in their strategies(!).

But I do understand your concerns, I, myself, did not convert to LOB as I did not really need to reorg these tables often, and that online reorgs was not really needed. But if I reorg again in the future, I will convert them at that time as I also found out that this kind of reorg is much faster than a offline reorg.

[Former Member · Accepted solution]

Hi Eric,

Do you have a sapnote that explains your answer : "BW keeps on creating/deleting tables whenever needed and might move your table to another tablespace because of DDART." ?

Thanks a lot

[former_member204746 · Accepted solution]

you might want to read SAP Note 771191 - Copying BW objects to new tablespaces

also read SAP Note 666061 - FAQ: Database objects, segments and extents

section 13

When your BW functional creates a new cube or aggregate, this will create new tables. in some case, tables are dropped an re-created.

This is much different from an R/3 system which does not really change its table structures.

[former_member204746]

Does anyone has guidelines or best practices to reorganize huge BW tables ?

--> read SAP note 646681. I would reorg online whenever possible

Is it a good idea to put huge BW tables in a dedicated tablespace (as we did on R3) or not ?

--> No, not a good idea. BW keeps on creating/deleting tables whenever needed and might move your table to another tablespace because of DDART.

Is it a good idea to put PSA tables in a dedicated tablespace ?

--> This is already the case if you installed your BW system back in 2.1 version or below. this was placed in PSAPODSD and PSAPODSI. If you upgraded since then, the old tablespace layout is probably still in effect.

[Former Member]

Thanks for your answers Eric.