DBMS_STATS and frag prob
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: DBMS_STATS and frag prob

  1. #1
    Join Date
    Sep 2006
    Posts
    19

    DBMS_STATS and frag prob

    Hi

    We are having 9idb and optimizer_mode is CHOOSE,

    Question:1
    -----------
    To analyze the objects whether i have to uses analyze table/index ... compute statistics or DBMS_STATS. What is the difference.

    Question 2
    -----------

    I have read some article, LMT tablespaces doesnt have fragmentation.However when i check in dba_free_space
    using qry
    SELECT tablespace_name,COUNT(*) ,SUM(bytes) as total,max(bytes) as largest
    FROM dba_free_space
    GROUP BY tablespace_name;

    I am getting the following output.

    Tablespace count sum(bytes) max(bytes)
    INDEXM1 10 545193984 427753472
    INDEXM2 3 2732982272 2174746624
    INDM3 100 1779171328 144703488
    INDM4 4 1255342080 973012992

    can any one clear my doubts.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    use dbms_stats
    you dont have fragmentation if you use LMT's

  3. #3
    Join Date
    Sep 2006
    Posts
    19
    Hi

    I have analyzed the all the index and tables thru dbms_stats by giving
    EXEC DBMS_STATS.gather_index_stats and
    EXEC DBMS_STATS.gather_table_stats.

    As I just analyzed the index I wanted to know what is criteria for rebuild the index.

    THanks

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    dont rebuild them, thats a good criteria

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by davey23uk
    dont rebuild them, thats a good criteria
    Agreed
    BUT
    If you have bitmap indexes on heavily updated tables you want to keep an eye on them. If they start growing out-of-control then rebuild them.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    PAVB, I am not very experienced with the bitmap indexes, can you pls. explain why they starts growing?

    Thanks

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Not rebuilding as a blanket criteria is not a good idea. On paper it looks good but the reality, as PAVB pointed out is that heavily updated tables will see indexes grow out of control. I have 3 prod boxes that have this issue and when rebuilding saves 50% of the tablespace, well, it is hard to argue that you should not rebuild cause Tom says so.
    I remember when this place was cool.

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Bore
    PAVB, I am not very experienced with the bitmap indexes, can you pls. explain why they starts growing?
    Let me quote Jonathan Lewis (http://jonathanlewis.wordpress.com/2...ing-in-action/) on this one.

    "it is fairly common for bitmap indexes to grow dramatically when the underlying tables are subject to DML. A single bitmap index “chunk” can be as large as 3,900 bytes (in an 8K block), and changing the value of the indexed column for just one row of the table makes Oracle clone two such chunks - which can cause two leaf block splits, which sometimes causes two branch block splits."

    Imagine this scenario, taken for a dw prod environment.

    -- You have a partioned bitmap index bitmap_idx on table_a.
    -- Before your daily ETL process you check the size of bitmap_idx:Mar2007 partition, it could be something like 15 Megs.
    -- After your daily ETL process which is heavily hitting Mar2007 partitions you go back to check the size of bitmap_idx:Mar2007... don't be surprised if now is as big as 1.5 Gig
    -- After rebuilding it goes back to 15 Megs.

    We actually have a process after ETL looking for bitmap index partitions larger than 1 Gig and, rebuilding them as they are found.

    Why you want to use bitmap indexes if they are such a pain? 'cause they are great for Oracle's query-rewrite functionality which gives you better performance in a dw environment. They are like antibiotics, use them just when actually needed.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by dba_45
    Hi

    I have analyzed the all the index and tables thru dbms_stats by giving
    EXEC DBMS_STATS.gather_index_stats and
    EXEC DBMS_STATS.gather_table_stats.

    As I just analyzed the index I wanted to know what is criteria for rebuild the index.

    THanks
    Please read my article:
    http://www.oracleact.com/papers/inde...lock_frag.html

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