Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Posts
    6

    Unanswered: Reorg and Prefetch in Oracle

    Hi experts,

    I am a DB2 DBA and new to Oracle. Have 2 questions:

    1. Does Oracle 9i has reorg & reorgchk like in db2? Basicly, a command to check if the tables & indexes need to be reorgonized?

    2. Db2 has prefetching which basicly fetches the rows to the buffer cache before they are needed to reduce I/O later? Does Oracle has a similar concept?

    Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    1. this is not applicable to Oracle, if I understand your question. Tables in Oracle don't need to be reorged like they do in DB2.
    2. yes, see the init.ora parameter db_file_multiblock_read_count. You set this to the number of blocks Oracle should read per I/O. Remember that the amount it fetches is really this number times the db_block_size.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Nov 2002
    Posts
    6
    Thanks Mark.

    But regarding 1, how come it's not needed? Say the table or index has become very fragmented, don't DBAs need to reorg the table? If so, how? Using SQL script to check?

  4. #4
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    It is possible to have lots of chains and migrated rows. Look at script utlchain.sql for a package to monitor chained rows. Also look at the "analyze table validate structure" commands. If you database block size is set correctly, and especially if you are using Oracle's locally managed tablespaces, you won't have any worries. It is very rare to need to reorg a table in Oracle8i and higher. Rows chain and migrate naturally, and only occurs to extremes if the database was not designed correctly.

    That said, check out the move table commands. You can move the table to its current location, causing it to be reorged without actually moving it. Neat, but is it really necessary? Tables are heap organized by default, and you should access data via indexes so you don't care where in the heap a rows sits.

    Rebuilding indexes is a much debated practice. Look at web site http://asktom.oracle.com/. Tom Kyte is the VP of Oracle Core Technologies, and author of two of the best known Oracle books. He states clearly that rebuilding Oracle's indexes is generally a waste of time, despite what all the overpaid consultants tell you about leaf node splits and tree height. Granted Oracle7 and below probably needed DBA attention, but I've been using 9i for years and never ever needed to rebuild an index (except for bitmap indexes on index organized tables.)
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One minor point about rebuilding indexes. Tom doesnt say its a complete waste of time it just depends on how your application works. For example due to the way the app works at my present employer simply rebuilding the indexes reduces the size of them by 30-50%!

    Alan

  6. #6
    Join Date
    Nov 2002
    Posts
    6
    So I guess there is no simple sql command to check if index reorg is needed.

    Thanks Mark & Alan.

  7. #7
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    sorry, there is no such command.

    I do not rebuild indexes to save space. The indexes will try to regain their "bad" shape. This can be (but is not always) more of a drain on your database performance than having a "bad" shaped index.

    Another thing to consider about regaining space is primary key indexes where the key is a simple one-up number fed by a sequence generator: data is deleted from the left, and added to the right so the blocks to the left can be reused without a reorg; if the index is not sequential (whether number or alpha) and data is deleted from random blocks, then the odds of an index block becoming completely empty and put on the free list is very small, and so rebuilds are useful to regain that "wasted" space.

    With tables I always use Locally Managed Tablespaces so there is no need to coalesce free space; Oracle auto-manages the job. And if tables are designed correctly (with all not null columns first and the remaining columns added according to the likelyhood of being populated with data) then the odds of row migration are very low indeed. Row chaining is mitigated by proper sizing of the db_block_size and the use of locally managed tablespaces.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  8. #8
    Join Date
    Nov 2002
    Posts
    6
    Thanks Mark.

    So how do you know WHEN to reorg the indexes? In Db2, the reorgchk command will calculate 6-7 different formula to check overflow, clusterratio etc to see if reorg of indexes are needed based on table/index statistics. Don't we need to do a similar analysis before we know if reorg is needed in oracle?

    It's cool that table needs not be reorged because of locally managed tables.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I have a question about the DB2 metric "CLUSTERRATIO"
    "CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary for indexes that are not in the same sequence as the base table. When multiple indexes are defined on a table, one or more indexes may be flagged as needing REORG. Specify the most important index for REORG sequencing."

    At least in the Oracle corner of the universe, the conventional wisdom is that
    the order of rows in a table is "indeterminate". I infer from "same sequence" (from above) that some order of rows in the TABLE exists for DB2.
    Is this correct?

    At least in the Oracle corner of the universe, indexes are ALWAYS ordered data structures. I completely fail to understand how or why rebuilding an
    index (& not the corresponding table) can change the "clusterratio",
    because AFAIK the order of the index will be identical before & after the reorg.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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