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

Thread: Index statistics

  1. #1
    Join Date
    Apr 2001
    Posts
    103

    Question Index statistics

    Hi Gurus,
    Is there a way I can find out the efficiency of an Index.
    That is I want to know the following details

    Allocated blocks
    Distinct keys
    Times most repeated key is repeated
    B-tree space (total allocated space)
    Used space
    Percent used
    Average rows per distinct key
    Average blocks gets per access
    B-tree height
    Leaf rows
    Leaf blocks In Be
    Size of all leaf rows
    Size of all leaf blocks in B-tree
    Deleted leaf rows
    Size of all deleted leaf rows
    Branch rows
    Branch blocks in B-tree
    Size of all branch rows
    Size of all branch blocks in B-tree

    Is there a view gives me the above details.

    Thanks
    Naeem

  2. #2
    Join Date
    Jul 2002
    Location
    California
    Posts
    128
    Trick question?

    Analyze the indexes or the underlying tables and check DBA_INDEXES.

    P.S. Study...
    alapps

    Fast, Cheap, Reliable... Pick Two(2)

  3. #3
    Join Date
    Apr 2001
    Posts
    103
    Yes your right, i have found out many values in DBA_INDEXES
    But the following values I am unable to find out in that view!!!

    Leaf rows
    Leaf blocks In Be
    Size of all leaf rows
    Size of all leaf blocks in B-tree
    Deleted leaf rows
    Size of all deleted leaf rows
    Branch rows
    Branch blocks in B-tree
    Size of all branch rows
    Size of all branch blocks in B-tree
    Naeem

  4. #4
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148

    Index Statistics

    There is a table called INDEX_STATS that contains
    what you are looking for; however it only contains
    a single row of data at a time and you must populate it.

    To populate it: validate index YOUR_INDEX_NAME;

    To View: select * from INDEX_STATS;

    Output:

    -- Column Name Description
    -- ----------------------- ----------------------------------------- HEIGHT height of the b-tree
    -- BLOCKS blocks allocated to the segment
    -- NAME name of the index
    -- LF_ROWS number of leaf rows
    -- LF_BLKS number of leaf blocks in the b-tree
    -- LF_ROWS_LEN sum of the lengths of all the leaf rows
    -- LF_BLK_LEN useable space in a leaf block
    -- BR_ROWS number of branch rows
    -- BR_BLKS number of branch blocks in the b-tree
    -- BR_ROWS_LEN sum of the lengths of all the branch block rows
    -- BR_BLK_LEN useable space in a branch block
    -- DEL_LF_ROWS number of deleted leaf rows in the index
    -- DEL_LF_ROWS_LEN total length of all deleted rows in the index
    -- DISTINCT_KEYS number of distinct keys in the index
    -- MOST_REPEATED_KEY how many times the most repeated key is repeated
    -- BTREE_SPACE total space currently allocated in the b-tree
    -- USED_SPACE total space that is currently being used
    -- PCT_USED percent of space allocated that is being used
    -- ROWS_PER_KEY average number of rows per distinct key
    -- BLKS_GETS_PER_ACCESS Expected number of consistent mode block
    -- gets per row. This assumes that a row
    -- chosen at random from the table is being
    -- searched for using the index

  5. #5
    Join Date
    Apr 2001
    Posts
    103
    Thanks for your reply.
    Can you give me the syntax to validate index.
    Because when I do
    validate index INTCH1 ;
    i have ORA-01418: Index does not exist

    when i do
    validate index 'INTCH1' ;

    ORA-00953: Index name absent

    Thanks
    Naeem

  6. #6
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    you can:

    analyze index OWNER.INDEX_NAME validate structure;

    OR

    validate index OWNER.INTCH1 ;

    select * from INDEX_STATS;


    I usually look at the height, distinct_keys,
    BLKS_GETS_PER_ACCESS, ROWS_PER_KEY etc...

  7. #7
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    Here is a little script I also like that will tell me
    the density of columns within a table as well as a look
    at the indexes... This is only usefull if you are using
    the cost based optimizer. Save the script and run it
    as :

    sql> @c:\...\script_name

    /********************************************************************/

    set echo off
    set scan on
    set lines 132
    set pages 66
    set verify off
    set feedback off
    set termout off
    set termout on
    column uservar new_value Table_Owner noprint
    column TABLE_NAME heading "Tables owned by &Table_Owner" format a30
    select user uservar from dual;
    undefine table_name
    undefine owner
    prompt
    accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
    select table_name from DBA_tables WHERE OWNER = '&OWNER' order by 1
    /
    accept table_name prompt 'Please enter Table Name to show Statistics for: '
    column TABLE_NAME heading "Table|Name" format a15
    column NUM_ROWS heading "Number|of Rows" format 9,999,990
    column BLOCKS heading "Blocks" format 999,990
    column EMPTY_BLOCKS heading "Empty|Blocks" format 999,990

    column AVG_SPACE heading "Average|Space" format 9,990
    column CHAIN_CNT heading "Chain|Count" format 999,990
    column AVG_ROW_LEN heading "Average|Row Len" format 990
    column COLUMN_NAME heading "Column|Name" format a25
    column NULLABLE heading Null|able format a4
    column NUM_DISTINCT heading "Distinct|Values" format 9,999,990
    column DENSITY heading "Density" format 990
    column INDEX_NAME heading "Index|Name" format a15
    column UNIQUENESS heading "Unique" format a9
    column BLEV heading "B|Tree|Level" format 90
    column LEAF_BLOCKS heading "Leaf|Blks" format 9,999,990
    column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,990
    column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990
    column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990
    column CLUSTERING_FACTOR heading "Cluster|Factor" format 9,999,990
    column COLUMN_POSITION heading "Col|Pos" format 990
    column col heading "Column|Details" format a24
    column COLUMN_LENGTH heading "Col|Len" format 990
    select TABLE_NAME,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN
    from dba_tables
    where owner = upper(nvl('&&Owner',user))
    and table_name = upper('&&Table_name')
    /
    select
    COLUMN_NAME,
    decode(t.DATA_TYPE,
    'NUMBER',t.DATA_TYPE||'('||
    decode(t.DATA_PRECISION,
    null,t.DATA_LENGTH||')',
    t.DATA_PRECISION||','||t.DATA_SCALE||')'),
    'DATE',t.DATA_TYPE,
    'LONG',t.DATA_TYPE,
    'LONG RAW',t.DATA_TYPE,
    'ROWID',t.DATA_TYPE,
    'MLSLABEL',t.DATA_TYPE,
    t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
    decode(t.nullable,
    'N','NOT NULL',
    'n','NOT NULL',
    NULL) col,
    NUM_DISTINCT,
    DENSITY
    from dba_tab_columns t
    where table_name = upper('&Table_name')
    and owner = upper(nvl('&Owner',user))
    ORDER BY COLUMN_ID
    /
    select INDEX_NAME,
    UNIQUENESS,
    BLEVEL BLev,
    LEAF_BLOCKS,
    DISTINCT_KEYS,
    AVG_LEAF_BLOCKS_PER_KEY,
    AVG_DATA_BLOCKS_PER_KEY,
    CLUSTERING_FACTOR
    from dba_indexes
    where table_name = upper('&Table_name')
    and table_owner = upper(nvl('&Owner',user))
    /
    break on index_name
    select
    i.INDEX_NAME,
    i.COLUMN_NAME,
    i.COLUMN_POSITION,
    decode(t.DATA_TYPE,
    'NUMBER',t.DATA_TYPE||'('||
    decode(t.DATA_PRECISION,
    null,t.DATA_LENGTH||')',
    t.DATA_PRECISION||','||t.DATA_SCALE||')'),
    'DATE',t.DATA_TYPE,
    'LONG',t.DATA_TYPE,
    'LONG RAW',t.DATA_TYPE,
    'ROWID',t.DATA_TYPE,
    'MLSLABEL',t.DATA_TYPE,
    t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
    decode(t.nullable,
    'N','NOT NULL',
    'n','NOT NULL',
    NULL) col
    from dba_ind_columns i,dba_tab_columns t
    where i.table_name = upper('&Table_name')
    and owner = upper(nvl('&Owner',user))
    and i.table_name = t.table_name
    and t.column_name = i.column_name
    order by index_name,column_position
    /
    clear breaks
    set echo on

  8. #8
    Join Date
    Apr 2001
    Posts
    103
    Thanks very much.
    It is excellent your script.
    I realy appriciate your help.
    Thanks once again.
    Naeem

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