DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Dml

  1. #1
    Join Date
    May 2001
    Posts
    73

    Dml

    I need your inputs on this.

    I have lot of DML activity (deletes,inserts,updates) on the tables.
    Does just rebuilding indexes would be sufficient to aviod performance problems?
    Does reorganising the tables will be more benefitial? (I don't want to reorganise the tables unless there is chaining or migration)

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    rebuilding indexes probably wont increase your performace - why do you think it will?

    Are your tables / tablespaces fragmented?

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Good design and implementation practices would avid performance problems. if you don't have performance problems, then don't worry about it.

    If you do, then get a measurement of exactly what is causing the problem -- it is always a wait event, and you have to identify what event is responsible for the problem.

    It could be a different event for each type of DML operation, and even each individual SQL, so take your poorest performing operation and start with that.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    May 2001
    Posts
    73
    In my understanding the high DML activity may leave the indexes disorganised. Over a period of time they could be larger than base table and optimizer pefroms a full table scans.
    So I thought rebulding the indexes would reorganize them which in turn result in performance benefits.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Very unlikely, in fact you make make performance worse.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    May 2001
    Posts
    73
    Slimdave - Can you please elaborate.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    May 2001
    Posts
    73
    Slimdave - Thanks much. Thats a valuable information. It does make sense in OLTP systems where the DML activity is normal.But I am little ambiguous.What if the large number of records (millions) being deleted/updated/inserted everday in the OLTP system. It does makes the indexes disorganised.Rebuilding these indexes would be benefitial rather than leaving them the way they are.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Read that link again. And again. And again. And then read it again from start to end. And you'll discovered that you don't need to rebuild your indexes at all (unless you have reeeeeally very peculiar and specific situation, which in this case you should desscribe it in more detail). So what if you delete milions of rows a day? Most of the leaf blocks that those deleted rows belonged to will be totaly empty after the delete, won't they? If they won't be empty today, they might be tomorrow, or next week. But they will all eventualy find their way to free list, when they will be reused. So the index is (almost) never disorganised, it very soon reaches the state of "equilibrium" as Tom says, and it than stays in this stable state. If you rebuild it regulary you are only disturing this "equilibrium".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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