Rebuilding indexes with Bad Storage Quality

[Former Member]

Hi All,

I need to Know how you can find out indexes with Bad storage quality and then i have to prepare a List of all these indexes and go for an Online Reorginzation, can some one help me in getting this data and the procedure as to how you do it.. I know rebulding Particular index using Brtools, but not Pretty sure about the Online Reorganization

Regards,

Ershad Ahmed.

[Former Member]

This message was moderated.

[Former Member]

Hi Ahmed,

please run report RSORAISQ or RSORAISQN --> check notes 712098 and 1026796

kind regards

Davy

[Former Member]

Hi Ershad,

It is a good practise to rebuild all indexes as they improve performance and release unused storage (due to delete operations). It will be time consuming activity to identify which indexes are currupt, just run rebuild for all the indexes in SAP schema.

Run a SQL script to generate list of all the indexes and its rebuild script . The index rebuild operation that I run is:

alter index SAPR3E."<object name>" rebuild parallel 20 online nologging;

I leave this running during the weekend (part of cron) and this does wonders to the database performance. The rebuild is fast as there is no logging done and it uses parallel servers to build your indexes.

Cheers,

Nisch

[benjamin_mehlhorn]

Hi Nisch,

you've written, that you are using an sql script to list all indexes and than build one to rebuild all the indexes. How do you build the scripts? What sql*plus commands are you using? Can you please provide your script in here?

We've tried to rebuild the indexes with brtools but failed in selecting the tables to rebuild. The wildcard is not recognized by BRSPACE 6.40 (43):

BR1107E Table(s) '*' not found
BR0182E Checking parameter/option '-t|-table' failed

Any hints or tips?

Thanks in advance

[lbreddemann]

Dear Nisch,

it's true: (re)building indexes with the NOLOGGING option is faster than the normal way. Using the parallel option is also a speed up.

But both options do come for a price:

- Recover a database over the time the NOLOGGING indexes were built and you've to rebuild them again. These index blocks cannot be recovered - so you'll loose precious time in case you need to do a recovery of the database.

- Parallel Queries do make a highly scalable multi-user database system a single-user system. Many - usually shared resources - are bound for the time the index build takes to this session. This might be OK when nobody is working on the system on the weekends. But, wait a minute: which businesses do not operate on weekends these days?

Both options are there for good reason, yes. But I would be carefull when I use them.

By the way: it's <b>not</b> sensible to rebuild the indexes regularly. There are only very few situations where a index rebuild is a good idea. Otherwise - and sadly that are the most cases - it's just a wast of time, computing power and hot air from the harddisks server racks.

More about this can (and should) be read here:

<a href="http://service.sap.com/sap/support/notes/771929">#771929 - FAQ: Index fragmentation[/url

[url=http://service.sap.com/sap/support/notes/912620]#912620 - FAQ: Oracle indexes</a>

KR Lars

[Former Member]

Hi Lars,

Thank you for your advice on rebuilding indexes. Please find my response below:

- Recover a database over the time the NOLOGGING indexes were built and you've to rebuild them again. These index blocks cannot be recovered - so you'll loose precious time in case you need to do a recovery of the database.

-> I agree there will be additional time to perform recovery, but recovery of production systems is not a regular activity and management needs to be made aware of it. Its matter of comfort level that is established between the teams.

- Parallel Queries do make a highly scalable multi-user database system a single-user system. Many - usually shared resources - are bound for the time the index build takes to this session. This might be OK when nobody is working on the system on the weekends. But, wait a minute: which businesses do not operate on weekends these days?

-> We run SAP in a retail environment and are given a fixed window and using the above options, we can complete reorganizations. Using the 'rebuild online' option, the system is not unavailable during the index rebuilds. We continue to run the business processes normally and have never experienced any failures. We just run them during the low system activity period.

Both options are there for good reason, yes. But I would be carefull when I use them.

By the way: it's not sensible to rebuild the indexes regularly. There are only very few situations where a index rebuild is a good idea. Otherwise - and sadly that are the most cases - it's just a wast of time, computing power and hot air from the harddisks server racks.

-> I do not agree that rebuilding indexes is a waste of time and resources. From the stats that I have, we reclaim 5 to 10 gigs of space every week, it depends on the load on the system during the week and we also have archiving enabled, our response times are on the agreed SLA of 500ms and excellent DB hit ratios on a 1.6 TB database.

Reorganization option will change depending on the environment the system is running on and every site will defer. There cannot be a generalized statement for adminisration task as there are several ways to perform similar activities.

Regards,

Nisch

[lbreddemann]

Hi again Nisch,

> -> I do not agree that rebuilding indexes is a waste

> of time and resources. From the stats that I have, we

> reclaim 5 to 10 gigs of space every week, it depends

> on the load on the system during the week and we also

> have archiving enabled, our response times are on the

> agreed SLA of 500ms and excellent DB hit ratios on a

> 1.6 TB database.

Ok, there may be certain situations where due to heavy insert, update, delete scenarios indexes get fragmented quite fast.

But what you describe sounds as if you make the indexes small by rebuilding them and within a week the index structures grow again. They do this because the rebuild index is optimal for a current state of data. Once you begin to change the data it becomes inefficient to keep this "narrow" B*Tree stucture. So it expands again.

Basically the indexes do grow to a efficient size for your regular data actions again.

> Reorganization option will change depending on the

> environment the system is running on and every site

> will defer. There cannot be a generalized statement

> for adminisration task as there are several ways to

> perform similar activities.

General statements are "dangerous" quite often. But rebuilding indexes regularly should be really justified by facts. Just looking at the number of allocated blocks right after the index rebuild is not enough for that.

How does the Storage Quality of the index change after it is back to it's "normal" size?

Is it really that bad? If so: what is the reason for it?

I would really be interested how your I/O stats develop if you stop rebuilding indexes (and get a living ).

A good discussion of this can be found here:

<a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112">AskTom: Rebuilding Indexes</a>

Best regards,

Lars

[Former Member]

Just as an example, this is how I've been rebuilding indexes with NOLOGGING...

ALTER INDEX "SAPPRD"."ALGRPCUSPF~0" REBUILD ONLINE NOLOGGING COMPUTE STATISTICS;

ALTER INDEX "SAPPRD"."ALGRPCUSPF~0" LOGGING;

COMMIT;

I'd like to use the BRSPACE command, so it'll record and display the results of the operation via Transaction DB14. Is there something I can add to this BRSPACE command to rebuild an index without logging?

brspace -p initPRD.sap -confirm force -s 20 -l E -f idrebuild -a rebuild -i "ALGRPCUSPF~0"

Regards,

Scott

[lbreddemann]

Hi Scott,

no there is no such switch.

The reason for this is, that indexes that have been created with NOLOGGING will lead to corrupt blocks after a recovery.

Since we strive to minimize downtime even in case of a recovery it's not wanted to have the need for index rebuilds after a recovery just before the system can go online again.

Especially for the UNIQUE indexes that are used to make sure the primary keys are unique this is a rather dangerous situation when the data is changed while the index is not usable.

BTW: Why do you perform a ALTER INDEX LOGGING after you've rebuild the index? Be aware that the NOLOGGING flag applies for the index (re)build ONLY! All other changes afterwards are logged.

There is the Option to use the NOLOGGING flag for indexes in BW systems - but this is also not recommended for HA BW systems. It's a performance feature with a heavy drawback once you've to recover. In some cases dbv will still report corrupt blocks even after all indexes have been rebuild.

So this should not be some to be used in your productive OLTP system.

KR Lars

[Former Member]

Refer to SAP note 332677

Regards

Manohar

[Former Member]

Index can be checked using: ANALYZE INDEX <ix> VALIDATE STRUCTURE;

But also check whether the index is used:

alter index <index_name> monitoring usage;

and check view v$object_usage

e.g.

desc index_stats

set linesize 120

SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space

FROM index_stats

WHERE name = 'IX_TEST';

ANALYZE INDEX ix_test VALIDATE STRUCTURE;

SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space

FROM index_stats

WHERE name = 'IX_TEST';

SELECT opt_cmpr_count, opt_cmpr_pctsave

FROM index_stats;

DROP INDEX ix_test;

CREATE INDEX ix_test

ON test (object_name, object_type)

PCTFREE 0

COMPRESS 1;

ANALYZE INDEX ix_test VALIDATE STRUCTURE;

SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space

FROM index_stats

WHERE name = 'IX_TEST';

SELECT opt_cmpr_count, opt_cmpr_pctsave

FROM index_stats;