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

Thread: INDEX_STATS

  1. #1
    Hi there,
    in the near future, one of our tables is gettinga datadump of 2million rows added. I have been asked to give a report on the effect of this. Its as general a request as that....For the table, I can guestimate the new space required from the avg row length. I can even go and find the SQL in v$sqlarea that accesses the table and mat be affected by higher number of records.

    The bit im stuck on is indexes. I know that you can analyse the indexes and get info from INDEX_STATS. What I dont know how to do is how to interpret this, and if there is anything there that is appropriate for this report (how uill 2M extra entries affect the indexes).

    Any ideas?

    -Bob

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    You may want to rebuild an index when index entries are removed as a result of many rows being deleted from the table.

    After using the command "analyze index index_name validate structure" you can view the information stored in the session-specific INDEX_STATS dictionary view to decide if you need to rebuild the index.

    If the length of all deleted values (stored in INDEX_STATS.DEL_LF_ROWS_LEN) is more than 20% of the the sum in bytes of the length of all values (stored in INDEX_STATS.LF_ROWS_LEN), then it might be good idea to rebuild the index.

    The following script I made, accepts as parameters the schema name(s) and index name(s) and rebuilds all indexes which need to be rebuilt.

    accept owner prompt "Schema name for Indexes_to_rebuild (Enter for all): "
    accept index prompt "Index name to validate (Enter for all indexes owned by &owner): "
    --
    set serveroutput on
    set verify off
    prompt
    --
    DECLARE
    --
    w_owner varchar2(30); w_index varchar2(30);
    sql_text varchar2(256); sql_text2 varchar2(256);
    Dynamic_Cursor integer; Dynamic_Cursor2 integer;
    dummy integer; dummy2 integer;
    index_usage pls_integer;
    w_lf_rows_len index_stats.lf_rows_len%TYPE;
    w_broi pls_integer := 0;
    w_red varchar2(300);
    --
    cursor validate is
    select
    OWNER, OBJECT_NAME
    from
    SYS.DBA_OBJECTS
    where
    OWNER like upper('%&owner%') and
    OBJECT_NAME like upper('%&index%') and
    OBJECT_TYPE = 'INDEX'
    order by
    OBJECT_NAME;
    --
    BEGIN
    --
    dbms_output.enable(60000);
    open validate;
    fetch validate into w_owner, w_index;
    while validate%FOUND loop
    Dynamic_Cursor := dbms_sql.open_cursor;
    sql_text := 'analyze index '||w_owner||'.'||w_index||' validate structure';
    dbms_sql.parse(Dynamic_Cursor, sql_text, dbms_sql.native);
    dummy := dbms_sql.execute(Dynamic_Cursor);
    dbms_sql.close_cursor(Dynamic_Cursor);
    -- let us see now if we have to rebuild this index:
    begin
    select LF_ROWS_LEN into W_LF_ROWS_LEN from index_stats;
    select decode(W_LF_ROWS_LEN,0,19,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100)
    into index_usage
    from index_stats;
    select name||' '||to_char(ceil((blocks*8)/1024)) into w_red from index_stats;
    -- if index_usage > 20, then we have to rebuild the index
    -- you may replace 20 with 30 if you wish
    if index_usage >= 20 then
    begin
    Dynamic_Cursor2 := dbms_sql.open_cursor;
    sql_text2 := 'alter index '||w_owner||'.'||w_index||' rebuild';
    dbms_sql.parse(Dynamic_Cursor2, sql_text2, dbms_sql.native);
    dummy2 := dbms_sql.execute(Dynamic_Cursor2);
    dbms_sql.close_cursor(Dynamic_Cursor2);
    end;
    -- message to the DBA:
    dbms_output.put_line(w_owner||'.'||w_index||' rebuilt for index usage being '||index_usage);
    w_broi := w_broi+1;
    end if;
    end;
    fetch validate into w_owner, w_index;
    end loop;
    close validate;
    if w_broi = 0
    then dbms_output.put_line(CHR(10)||'No indexes need not to be rebuilt (perhaps)'||CHR(10));
    elsif w_broi = 1
    then dbms_output.put_line(CHR(10)||'Only 1 index had to be rebuilt');
    else
    dbms_output.put_line(CHR(10)||w_broi||' indexes had to be rebuilt');
    end if;
    --
    END;
    /

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