Performance issue in table

[Former Member]

Hi All,

From last some days we are facing perofmance issue in accessing one table.

Before issue we have inserted and overwrite large no of rows in table.

My analysis

1 - Update statisctis are up to date.

2 - Storage Quality of index is 55%

Now my queries are :

a ) What is the impact of storage quality on performance.

b) Does rebuilding index will help to improve the issue.

Shivam Mittal

[Former Member]

As said rebuilding the index is worth trying. Did you check the execution plan of the statement? If the index rebuild does not solve your problem, you should post the sql statement together with the execution plan here.

Good luck, Michael

[Former Member]

This message was moderated.

[Former Member]

Read Note 771929 - FAQ: Index fragmentation

[volker_borowski2]

Hi,

as the keys in an index are sorted, it happens upon inserts, that oracle needs to fill in a

key into an existing block to kep the sorted sequence consistent.

If the block is full, oracle perfromes a split of this block in to two blocks which are both filled ~ 50% then.

After this, the key can be inserted as needed.

If a table undergoes a massive number of inserts, AND the keys for this index are not added at the

upper end of the index, you will rather soon have the situation you face right now.

Rebuilding might help, coalesce might be cheaper

(it costs less rollback, only redo and has less chances to disturbe you in terms of locks).

But both may be contraproductive for additional inserts, as new blocksplits will occur to serve future inserts.

This is extremly costy in terms of performance for these inserts and for resources (undo AND redo AND cache).

If you have a selection that gets only a small number of records, it will rarely help to do anything about this.

It will cost you storage, ok, but as long as we are not talking 100eds of GB, who cares.

If you have a selection that uses this index, and it suffers from this storage quality, keep in mind that you can

only earn GETs on the index leafblock side in that statement. It will do nothing about the ratio to the table!

This would be the clustering factor you need to work on, andf that might require a sorted reorg.

So if your Query gets 150.000 rows and your index block can hold i.e. 150 keys at best storage, we talke about

substituing 2000 gets (of 50% filled blocks) in a range scan to 1000 gets (of 100% filled blocks) after a coalesce.

Yes that is 50% and yes that is a lot, if you do it all the time, but if you do it just once or 5 times, a 1000 gets are nothing.

Modern I/O Systems will get you 1000 blocks really fast, and I would favor the inserts in that case.

If you need to retrieve more rows, a FTS might be a better approach anyway (depends on blocks:rows ratio).

If you are somewhere in between, and you have a significant number of calls, that do suffer, you have to decide,

if you favor your select for i.e. reporting, by coalescing the index regulary, or if you favor the inserts to keep operational

tasks as inserting new orders (or whatever it is) happy. It is your decision.

Volker

[Former Member]

You should definitely rebuild your index because changing a large number of table entries will result in a bad access performance which can degrade to a full table scan.

Kind regards,