Help needed with BRtools/sapdba!!!

[Former Member]

Hi SAP gurus!!

We have to perform reorganization as part of archiving post processing activity.

pls refer the following link:

http://help.sap.com/saphelp_nw70/helpdata/EN/8d/3e4e81462a11d189000000e8323d3a/frameset.htm

It says" If data has been archived or simply deleted and the associated tables were accessed via an index, then index should be reorganized"

1) How we come to know that data is accessed via index?

2)Which is the less time consuming among the below ?

1- table space reorg ; 2- index reorg; 3- table reorg

3) After performing the delete action in archiving will the space gain shows automatically in DB02 or do we need to do a "refresh action" .I heard about update statistics is this is that refresh action (assumed). and how to do this from SAP level( Any Tcode)?

4)We have 4.7kernel rel 640 and oracle 10.2 on AIX , Which is the best applicable? A)BRTOOLS B)sapdba.

5)Which one can be used online(Avoiding downtime) i mean SAPDBA/Brtools

and if downtime is necessary . How to roughly estimate the needed downtime?

6)Can this(reorg) be done through any Tcode and is it(doing with Tcode) good than using SAPDBA/Brtools?

Your responses are highly appreciated

Points would be liberally rewarded.

Thanks a lot in advance!!

[lbreddemann]

> We have to perform reorganization as part of archiving post processing activity.

Ok, can be a good idea.

> It says" If data has been archived or simply deleted and the associated tables were accessed via an index, then index should be reorganized"

Well, that's by far not all "it says".

Basically when you delete/archive data the storage structures inside the oracle database (tables and indexes) won't release allocated space automatically.

Therefore, depending on what you do with these tables/indexes it's a good idea to release this space - this is called reorganisation.

> 1) How we come to know that data is accessed via index?

Pretty simple - check the explain plan of the SQL that access the table.

How you can do this? There are many ways: via shared cursor cache (ST05), via SQL Trace (ST05) ...

> 2)Which is the less time consuming among the below ?

> 1- table space reorg ; 2- index reorg; 3- table reorg

Think about it, yourself!

Tablespace reorg means: take all segments (tables and indexes) in a specific tablespace and reorganize them.

Sound like a lot of work to do and usually it is.

Index reorg means: take one or more indexes and rebuild them. This is usually pretty fast, but of course depends on your data.

Table reorg means: you guess it - yes! Take one or more tables and reorganize it.

As it is necessary to rebuild all indexes on the reorganized tables, the amount of work to do here it bigger than for index reorg but usually still far smaller than for the tablespace reorg.

> 3) After performing the delete action in archiving will the space gain shows automatically in DB02 or do we need to do a "refresh action" .I heard about update statistics is this is that refresh action (assumed). and how to do this from SAP level( Any Tcode)?

As already written: the space won't be released until you reorganize. You don't have to run update statistics to update these information. Just reorganize.

> 4)We have 4.7kernel rel 640 and oracle 10.2 on AIX , Which is the best applicable? A)BRTOOLS B)sapdba.

There is no choice. SAPDBA is not supported for 10g and is deprecated a very loong time now!

BRTOOLS are the tools for the SAP Oracle DBA.

> 5)Which one can be used online(Avoiding downtime) i mean SAPDBA/Brtools

> and if downtime is necessary . How to roughly estimate the needed downtime?

BRTOOLS can reorganize all tables that do not contain LONG RAW fields online.

To estimate the reorganization time for a offline reorg you can export the table and import it into another schema.

> 6)Can this(reorg) be done through any Tcode and is it(doing with Tcode) good than using SAPDBA/Brtools?

No, there is no such transaction. Use the BRTOOLS.

Please test out the procedure on a test system before you try it out on the production instance.

Check [SAP Note 646681 Reorganizing tables with BRSPACE|https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes/sdn_oss_bc_db/~form/handler%7b5f4150503d3030323030363832353030303030303031393732265f4556454e543d444953504c4159265f4e4e554d3d363436363831%7d]

before doing anything...

And perhaps you may want to visit the ADM505 SAP course. It covers reorganization as well as the usage of the BRTOOLS.

regards,

Lars

[Former Member]

Hi

Thank you very much for your replies!!

1) Can yo say the space (rough estimate) for starting the BRtools

2) After completion of archive activity completion , How to get the fair estimate about a)how much space was consumed to write the archiving file b)How much size the data deleted after delete activity of archiving ? I am referring SARA->management -->statistics of an archived session.

But the fields are confusing there and difficult to get a fair conclusion--I short i wanted to know " how much size archive file size consumed and how much data (size) is deleted after the delete activity" is what i wanted to know

Pls look

http://help.sap.com/saphelp_di471/helpdata/EN/6a/83bf3ab169cb17e10000000a11402f/frameset.htm

Here it says for one field

DB Space (Write)= "Virtual" storage space in MB that is occupied by an incomplete archiving session in the database (whats is the actual space then?)

Please give me the clarification for the above.

Thank you.

[fidel_vales]

too many questions

> 1) How we come to know that data is accessed via index?

You could activate the monitoring of the table indexes and then check if they are being used

You could check how the table s accessed looking at the execution plans on the oracle history, but this does not contain absolutely all information.

In General, I'd say that, if you have archived/deleted a lot of records and you do not expect the table to grow a lot, then a reorganization could be interesting.

Take a look at the Note 541538 - FAQ: Reorganization

> 2)Which is the less time consuming among the below ?

> 1- table space reorg ; 2- index reorg; 3- table reorg

Each reorganization has its own objective. The fastest is the index reorganization. But that is useless if you need a table reorganization You have to choose the one you need.

Take a look at the note Note 541538 - FAQ: Reorganization

> 3) After performing the delete action in archiving will the space gain shows automatically in DB02

After delete/archive the space is not returned to the tablespace, to return it you need to reorganize the table

Take a look at the note Note 541538 - FAQ: Reorganization

> 4)We have 4.7kernel rel 640 and oracle 10.2 on AIX , Which is the best applicable? A)BRTOOLS B)sapdba.

Forget about SAPDBA, it is old, not maintained and not supported. It may work with Oracle 10g but it was never released for that database version.

> 5)Which one can be used online(Avoiding downtime) i mean SAPDBA/Brtools

> and if downtime is necessary . How to roughly estimate the needed downtime?

Do not use SAPDBA. If the table has a field "LONG RAW" then you may need downtime. Otherwise you can use online reorganization.

Take a look at the note Note 541538 - FAQ: Reorganization

The time needed it is difficult to calculate as it depends on your hardware, size of the table ....

> 6)Can this(reorg) be done through any Tcode and is it(doing with Tcode) good than using SAPDBA/Brtools?

NO

Recommend you to read the note Note 541538 - FAQ: Reorganization and related notes like:

821687 - FAQ: Space utilization and fragmentation in Oracle

646681 - Reorganizing tables with BRSPACE

910389 - FAQ: Oracle Segment Shrinking

1080376 - Enhancements for reorganization and rebuild