Poor performance after database reorganization (18.104.22.168)
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 for all the replies.
Let me answer some of the questions you asked.
-- Did you reanalyze the objects after the reorganization?
I think like you. I believe that the plans have been changed. I'm not sure why ...
-- 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?
-- 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...
-- 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...