INDX tablespace in increasing
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34

Thread: INDX tablespace in increasing

  1. #1
    Join Date
    Jun 2006
    Posts
    40

    INDX tablespace in increasing

    Hi,
    In our production database, we've two index tablespaces. These are continuously increasing (about 300 mb per day). Some days before i've added some more datafiles to these tabelspace. But it is not possible to add space everytime. So what should be the reason behind it?How can I analyze this? How do I proceed to solve this issue?

    Thanks in advance.

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    Enable index usage monitoring and drop the unused indexes to recover the space.

    I dont think you can do anything else as index consumes data as your loading the data into base tables. Either not to load the data or drop the indexes to avoid space usage

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    have you cheked which indexes are growing most?
    have you cheked how the tables realted to these indexes are growing?
    are these table growing as exepcted?
    did you make any estimation how the tables and indexes are going to grow over the time?

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Quote Originally Posted by minal_yawale
    Hi,
    In our production database, we've two index tablespaces. These are continuously increasing (about 300 mb per day). Some days before i've added some more datafiles to these tabelspace. But it is not possible to add space everytime. So what should be the reason behind it?How can I analyze this? How do I proceed to solve this issue?

    Thanks in advance.
    You'll just have to stop those pesky users from using the database...
    Assistance is Futile...

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Take tree dump of the index that you suspected growing fast and post the same here.

    Tamil

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    the first thing shoud be checking if the table is growing. taking dumps is rather severe for a beginner like him

  7. #7
    Join Date
    Jun 2006
    Posts
    40
    Thanks for ur suggetions.

    I've started analyzing the indexes.
    Then fired this query:
    SELECT name,height,lf_rows,del_lf_rows, USED_SPACE
    FROM INDEX_STATS;
    It shows following output (for one indx)

    NAME HEIGHT LF_ROWS DEL_LF_ROWS USED_SPACE
    ---------------------------------------------
    PK_ORDER_ID 1 22 0 570 and total space of that indx is 10 MB and used space =570, pct_used=8,LF_BLK_LEN=8000,BTREE_SPACE=8000
    What does it mean ? Used space is in bytes right ?

    Also I've tried this query
    alter index PK_ORDER_ID monitoring usage

    select index_name,used from v$object_usage where index_name='PK_ORDER_ID'

    But it shows nothing.

    I am not able to understand what to do from this result. Can u pls guide me about this and tell steps how to proceed further ?

    Thanks for your valuable time.

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Can you answer the following question?
    Does the table data grow with respect to the index growth?

    Tamil

  9. #9
    Join Date
    Jun 2006
    Posts
    40
    Yes, that table data also growing with respect to indexes.

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    so whats the problem, data id going into your tables and the indexes are growing?

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