SIZING INDEXES
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: SIZING INDEXES

  1. #1
    Join Date
    May 2002
    Posts
    19
    I need approximate sizes of the indexes on our production system to enable me to schedule rebuilds over a series of batch overnight processes. Does anyone have a formula that will enable me to work out the size simply by knowing the INITIAL EXTENT, NEXT EXTENT, NUM OF EXTENTS and PCT INCREASE VALUES for the index or is there more to this than meets the eyes ?

    Help would be very much appreciated

    Thanks in Advance
    Eddy

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    The current size of you indexes can be worked out like this:

    SQL> select sum(bytes)
    2 from dba_segments
    3 where segment_name = 'MY_INDEX_IDX';

    SUM(BYTES)
    ----------
    41943040

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    May 2002
    Posts
    19

    Smile

    I'll re phrase my question a little here.

    What I was after was a script of method to run over all 650 indexes that I need to Analyse and not just each index on an ad-hoc basis.

    Thanks for the info - it is helpful :-)

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Eddy, do you want to analyze those indexes or rebuild them? If the latter, then would you like to rebuild all indexes or just the ones that need to be rebuilded?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    I don't understand the question. First you said rebuilding your indexes. Next you said analyzing them. I don't see what the size of the index has to do with either.

    If you want the size of multiple indexes you can do something liek this:

    select segment_name, sum(bytes)
    from dba_segments
    where segment_type = 'INDEX'
    and owner = 'MY_SCHEMA'
    group by segment_name;

    If you want to rebuild several indexes you can do something like this:

    DECLARE
    CURSOR c_indexes IS
    SELECT owner || '.' || index_name as name
    FROM dba_indexes
    WHERE owner = 'MY_SCHEMA';
    BEGIN
    FOR cur_idx IN c_indexes LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || cur_idx.name || ' REBUILD ONLINE;';
    END LOOP;
    END;
    /

    If you want to analyze several indexes you can do something like this:

    DECLARE
    CURSOR c_indexes IS
    SELECT owner, index_name
    FROM dba_indexes
    WHERE owner = 'MY_SCHEMA';
    BEGIN
    FOR cur_idx IN c_indexes LOOP
    DBMS_STATS.GATHER_INDEX_STATS(cur_idx.owner, cur_idx.index_name);
    END LOOP;
    END;
    /

    Perhaps you want to do a combination of these things???
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  6. #6
    Join Date
    May 2002
    Posts
    19
    I've obviously been as Clear as mud to date.

    In essence I am rebuilding the indexes on the Oracle Apps system.

    But before I do the rebuild I have been told I need to find out an approximate size for each of the indexes. This is so that I can estimate the time it will take to rebuild the indexes by testing some of the rebuilds against the UAT system. I have been told that we can't REBUILD ONLINE as my boss doesn't like the idea of this. Therefore we are going to rebuild them during the nightly batch jobs.

    Out of interest does anyone have any real time experience of what kind of impact rebuilding the indexes online has on system performance ? Is the degredation such that this shouldn't be considered a viable option. Obviously this is relative to normal system performance.

    Thanks for all the help so far

    Eddy

    PS:- I want to rebuild some indexes in a schema where an Oracle Patch has added some 5,000,000 records to some indexes when there should be about 5 records, hence causing capacity management problems.

    [Edited by Eddy99 on 05-23-2002 at 08:50 AM]

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by Eddy99

    Out of interest does anyone have any real time experience of what kind of impact rebuilding the indexes online has on system performance ?
    If you rebuild one index, performance will not suffer that much but you better rebuild them at hight when there is no DB activity. Do you drop and rebuild, or just rebuild?

    I just rebuilt 5 indexes in a small production detabase (by dropping them first), nobody complained (it took me 2-3 minutes or so).
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Does the information above answer your question or do you need more clarification?

    I'm assuming this was the answer to your question:

    select segment_name, sum(bytes)
    from dba_segments
    where segment_type = 'INDEX'
    and owner = 'MY_SCHEMA'
    group by segment_name;

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  9. #9
    Join Date
    May 2002
    Posts
    19
    Thankyou Tim that is just what I wanted !

    Thankyou Julian - I am rebuilding 650 indexes on the production system so I think an overnight job seems the best bet in light of your comments. Is it a lot faster to just drop and then recreate than rebuilding - I would guess if your rebuilding an index from 5,000,000 to 5 entries it would be ? Would the rebuild option mean reading all the 5,000,000 blocks in the index to find the 5 that havn't been deleted ?

    Thanks again all
    Eddy

    [Edited by Eddy99 on 05-23-2002 at 09:18 AM]

  10. #10
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Cool. It took some time but we got there in the end
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

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