Database reorg

[former_member759680]

Hello,

The customer wants to know whether it's time that we do a reorganization of our SAP Netweaver 7.0 database.

The thing is, the growth of teh database is not much, hardly 1 GB per month, but maybe internally the database is fragmented (windows term).

So how is it that we decide, if it is necessary to reorg the database? I know we need to look in Tx db02. But what exactly in Db02?

Kindly suggest.

Thanks.

[former_member227283]

Hi Gautam,

What is you current database size?

Thanks

Anil

[Former Member]

Gautam,

If you are facing poor db response time OR if you have deleted large amount of data from tables etc.. In these scenario you need db reorg. There is no specific time line about db reorg.

[Former Member]

Hello Gautum,

One of the ways to get this done is request SAP to have a look at your systems and ask them to give recommendations. I don't know if this is cost free but the advice given by them would be excellent. Due to constant updates and deletion database tends to get heavily fagmented and therefore occupies more space than it actually should. This process would involve creation of new tablespaces and relocation of existing tables and indexes in these Tbs from the old Tbs.

With Regards.

Ruchit.

[former_member759680]

As suggested in many posts, I checked the EWA reports for our systems.

But there is no mention of Database Reorganization anywhere.

I see -

9.1. Database Parameters

10.1. Database Growth

10.2. Freespace in Tablespaces

They all seem ok. And no mention of Database reorg anywhere.

So does taht mean that we do not need a reorg, since EWA reports dont mention it.

Thanks.

P.S.

In Note - 821678, I found this -

The percentage of space in a tablespace used by extents can be determined as follows:

SELECT TO_CHAR(

(1 - (SELECT SUM(BYTES) FROM DBA_FREE_SPACE

WHERE TABLESPACE_NAME = '<tablespace_name>') /

(SELECT SUM(BYTES) FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME = '<tablespace_name>'))

• 100,

999999999999.99) "% (USED)"

FROM DUAL;

I executed teh query and the value came to be 91%

Could tell me the significance of this?

Edited by: Gautam Poddar on Aug 24, 2009 8:55 AM

[Former Member]

Gautam,

EWA report never mention about need of re-org and all. EWA report just fetch all information from your SAP system and represent it to you in report format.

[anindya_bose]

>But there is no mention of Database Reorganization anywhere

Early-Watch Report does mention about bad storage qualities. both for tables and indexes.

It lists top 20 tables and indexes with bad storage qualities. I am not sure why your EWA report does not show that. but in our report ( Both for BW 3.5, and BI7 ) we get these information.

By the way, what is your ST-PI and ST-A/PI version? There is a bug in 2008_1_640 but after applying one note correction now again we get these information.

Some portion of my latest EWA is given below.

0.0.1 Auxiliary Storage Quality Information

The following table shows the top "regular" (not partitioned, empty, index organized) tables (max. 20) that have more than 1000 blocks with the lowest storage quality (based on the available CBO statistics).

TABLES WITH FRAGMENTATION

Table Name Rows Average Row Length Blocks Kb Used Kb Needed Kb Wasted

BALDAT 10050500 196 10873585 76235087 1933543 74301543

UCL2040 2707167 218 1286381 9018862 578974 8439888

DBTABLOG 56460000 271 2617504 18351413 14997188 3354225

...............................................................................................

.........................................................................

Action: Reorganization

The last column (Kb Wasted) shows how much disk space can be recovered through reorganization.

Caution:

The Oracle bug 5842686 (detailed in SAP Note 821687) may cause calculations to be incorrect for tables with long raw fields. This bug can be fixed as of Oracle Release 10.2.0.2.

The following table shows the top "type normal" indexes (max. 20) that have more than 1000 blocks with the lowest storage quality (based on the available CBO statistics).

Even if your EWA does not provide these information, in my last post there is one link. Click on that, you will get one SQL, by which you can get this information

Edited by: Anindya Bose on Aug 25, 2009 6:07 PM

[Former Member]

Hi

I am facing this very problem to have storage quality shown in the EWA.

However, after the delta-upgrade of ST-PI & ST-A/PI , I still failed to have them shown in the report.

May I know which correction note you have applied?

Advice please.

Thanks.

[anindya_bose]

Gautam

you could go by the post below. In the post you will find one SQL which will provide you some information to decide whether you should go for reorganization... More over, your EarlyWatch report will have the same information..

SAP note 821687 - FAQ: Space utilization and fragmentation in Oracle provides excellent information about fragmentation.

SAP Note 646681 - Reorganizing tables with BRSPACE provides ways to reorganize..

Hope this information will help

Anindya

[JPReyes]

Read,

SAP Note 541538 - FAQ: Reorganization

That should answer all you questions

Regards

Juan

[Former Member]

Hello Juan, Good Morning

I will use the DB02 transaction to REORG the table SOFFCONT1 , due to after of delete almost all entries in it,  the size is the same, then, I know , that is necessary execute the task REORG Table since DB02 transsaction, however, I have doubt about which  use, "offline" or "online"

¿could you give me your opinion please?

thanks

Regards