Poor performance after database reorganization (8.1.7.0)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Poor performance after database reorganization (8.1.7.0)

  1. #1
    Join Date
    Jun 2004
    Location
    Buenos Aires - Argentina
    Posts
    2

    Exclamation Poor performance after database reorganization (8.1.7.0)

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Could be any number of things; more extents, less extents, I/O not distrubuted, DMT vs. LMT, updated stats causing query plan change, etc.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    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.
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Poor performance after database reorganization (8.1.7.0)

    Did you reanalyze the objects after the reorganization?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Jun 2004
    Location
    Buenos Aires - Argentina
    Posts
    2
    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

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width