Poor performance after database reorganization (8.1.7.0)

[ncrespo]

Hi! I need help please!! Sorry for my poor english.
I created two new tablespaces (one for tables and one for indexes).
I used "alter table ... move tablespace " and "alter index ... rebuild tablespace " for some objects. I executed "alter table ... move tablespace " and "alter index ... rebuild" to reorganize the objects.
After that, the database shows a POOR performance. I found some ORA-0600 in alert.log, related to compressed index. Following Metalink recomendations, I rebuilt the indexes with nocompress option. The error disapears, but performance problems continues. I analyzed the objects (the optimizer_mode is CHOOSE), but slow problem continues.
Please let me know if anybody had a similar problem or what to do!!!!
Thanks!!!
Natalia

[marist89]

Could be any number of things; more extents, less extents, I/O not distrubuted, DMT vs. LMT, updated stats causing query plan change, etc.

[wjramsey]

Did you try just moving the tables and indexes back into their original locations to see if that helps?

Did you possibly have any histograms created?

Are the tablespace default storage parameters the same?

Are the table and index storage parameters the same?

Did you change the disk locations where the datafiles are stored?

It could be that the data was spread out more throughout the blocks and now the data is more compressed so that you're getting block contention going on.

[abhaysk]

possbily heavy redo generation after you rebuilt your indexes..

but it looks like after you did a move and may be you analyzed the tables/indexes/columns, have messed up the plan of you queries..

Abhay.

[julian]

Did you reanalyze the objects after the reorganization?

[ncrespo]

Thanks for all the replies.
Let me answer some of the questions you asked.

julian :
-- Did you reanalyze the objects after the reorganization?
Yes...

abhaysk:
I think like you. I believe that the plans have been changed. I'm not sure why ...

wjramsey:
-- Did you try just moving the tables and indexes back into their original locations to see if that helps?
I can`t do it now because it's a production database.

-- Did you possibly have any histograms created?
No

-- Are the tablespace default storage parameters the same?
Yes. Both are locally managed tablespaces with the same uniform size.

-- Are the table and index storage parameters the same?
No storage parameters. The tablespaces are locally managed.

-- Did you change the disk locations where the datafiles are stored?
Yes, the new tablespaces were created in new disk arrays, but it doesn't seem to be a problem because the same problem was reproduced when I copied the database to another server.

-- It could be that the data was spread out more throughout the blocks and now the data is more compressed so that you're getting block contention going on.
May be... But it's TOO SLOW...

marist89
-- Could be any number of things; more extents, less extents, I/O not distrubuted, DMT vs. LMT, updated stats causing query plan change, etc.
There are many queries resolved with full scans. Reorganizing the tables may help ....
The original idea was to distribute the objects using a new disk array, so the problem (I think) is not I/O distribution.
I think the problem is with plan changes... I don't undestand it, because previous stats was few days ago...


Thanks again
Natalia

[tamilselvan]

DO the following changes in init.ora.

Increase optimizer_index_caching value to 80 and reduce optimizer_index_cost_adj to 20.

If db_file_multiblock_read_count is not set to 8, then set it to 8.

Play around "_area_size" values so that the optimizer chooses INDEX SCAN rather than FULL TABLE SCAN.

Did you do count of all indexes before and after re-org?

Tamil

[marist89]

Originally posted by tamilselvan
DO the following changes in init.ora.

Increase optimizer_index_caching value to 80 and reduce optimizer_index_cost_adj to 20.

If db_file_multiblock_read_count is not set to 8, then set it to 8.

Play around "_area_size" values so that the optimizer chooses INDEX SCAN rather than FULL TABLE SCAN.

Did you do count of all indexes before and after re-org?

Tamil

Personally, I wouldn't blindly change init.ora parameters without fully understanding what they do and how my application would be affected.

For example, for plenty of OLTP systems 80/20 is a good mix for the optimizer_* variables. However, if your systems SHOULD prefer FTS over index hits, this would kill your performance.

If your dmrc is already 32 and you change to 8 and you're still doing FTS, then your performance will be worse.