Re: Real size of an index?

From: Oracleguru <oracleguru_at_mailcity.com>
Date: Thu, 27 Aug 1998 14:26:10 GMT
Message-ID: <01bdd1d6$60d4b080$a504fa80@mndnet>

Hi,

Here is the PL/SQL that I wrote that gives the information that you seek (for certain tables and indexes). You may need to modify it to suit your needs.

This version does not create a procedure but executes the PL/SQL block in SQL*Plus. Some of the HOST commands are for UNIX.

I have snipped it from one of my SQL script. I hope I have not missed anyting.

Good luck !!!

Here we go:

set pause off termout off verify off wrap on serveroutput on size 1000000 set newpage 0 pagesize 58 linesize 80

clear breaks
break on station skip 1  

column today            new_value     today     noprint
column time             new_value     time      noprint
 

/*

   Remove report file if exists, otherwise in case of problem, you may    receive a print out of an old file.
*/  

host rm -f $HOME/rep/analyze_tablespace_usage.lst  

spool $HOME/rep/analyze_tablespace_usage.lst  

set feedback off  

ttitle today center 'HIST TABLE AND INDEX SPACE USAGE' -

   right 'Page ' format 990 sql.pno skip 1 -    time -
   skip 2 -
   analyze_tablespace_usage.sql skip 1  

select to_char(sysdate, 'dd-MON-yyyy') today,

         to_char(sysdate, 'HH:MI:SS AM') time   from dual
/  

set feedback on  

/*

   Compute HIST and HIST_IDX total and unused disk space. */  

DECLARE

   total_blocks                 number;
   total_bytes                  number;
   unused_blocks                number;
   unused_bytes                 number;/* Above High water mark */
   last_used_extent_file_id     number;
   last_used_extent_block_id    number;
   last_used_block              number;
   free_space                   number;/* Below High water mark */
   user_id                      all_tables.owner%type;
   object_name                  all_tables.table_name%type;
   object_type                  varchar2 ( 5);

--

   cursor c1

       is
   select owner, table_name, 'TABLE'
     from all_tables
    where ( table_name like 'HIST%' )
   union
   select owner, index_name, 'INDEX'
     from all_indexes
    where ( table_name like 'HIST%' );
BEGIN
   open c1;
   LOOP

      fetch   c1
       into   user_id, object_name, object_type;
      exit when c1%notfound;
   dbms_space.unused_space


      ( user_id,


        object_name,
        object_type,
        total_blocks,
        total_bytes,
        unused_blocks,
        unused_bytes,
        last_used_extent_file_id,
        last_used_extent_block_id,
        last_used_block
      );
   dbms_space.free_blocks


      ( user_id,


        object_name,
        object_type,
        0,
        free_space);

--

   dbms_output.put_line

( object_type||' Name = '||object_name );
   dbms_output.put_line

( '**********' );
   dbms_output.put_line

( 'Total Bytes = '||to_char(total_bytes, '999,999,990')||

        '     Free Bytes above Water Mark    = '||
        to_char(unused_bytes,'999,999,990') );
   dbms_output.put_line


      ( 'Bytes to W.Mark= '||to_char(total_bytes -


unused_bytes,'999,999,990')||
        '     Free Bytes below Water Mark    = '||
         to_char(free_space*8192, '999,999,990') );
   dbms_output.put_line
      (
'-------------------------------------------------------------------------

-------');

   END LOOP;
END;
..
/

Arkady Moreynis <webmaster_at_price.ru> wrote in article <webmaster-2408982057270001_at_wmaster.price.ru>...

> Hi all,
> 
> Can anyone suggest how to calculate the REAL disk space occupied by an

index?

> 
> I doesn't mean just to sum up the sizes of extents occupied by the index.
> I'm interested in the space occupied INSIDE the extent (because as a
> performance-oriented person I'm trying to keep only 1 large extent per
> object).
> 
> The only idea I came up with is a multiplying number of index_leaf_blocks
> (from dba_indexes) by db_block_size which could be a reasonable
> approximation.
> 
> But maybe someone knows a better way?
> 
> Regards,
> Arkady
> 

Received on Thu Aug 27 1998 - 09:26:10 CDT