RE: Strange index sizing after import

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Thu, 21 Dec 2000 22:46:00 -0000
Message-Id: <10717.125192@fatcity.com>

Hi,

Thanks for the input so far and it appears that the difference is due to the number of free blocks.

Why would the number of free blocks differ after an import?

To provide some information that was requested:

Both databases have the same block size (8k)

Using DBMS_SPACE.UNUSED_SPACE and DBMS_SPACE.FREE_BLOCKS showed the following.

Prod

Index CHSE_ELMN_FK_I, total blks = 17850, total bytes = 146227200 unused blks = 1360, unused bytes = 11141120 free blocks = 2

Test

Index CHSE_ELMN_FK_I, total blks = 31219, total bytes = 255746048 unused blks = 0, unused bytes = 0
free blocks = 15803

PCT_FREE in both cases is 40.

Info from index stats:

test

    HEIGHT BLOCKS LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS
---------- -------------------- ---------- ----------- ---------- ----------

         3 31219 5172976 15376 71891345 7952 15375

prod

    HEIGHT BLOCKS LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS
---------- -------------------- ---------- ----------- ---------- ----------

         3 16475 5521137 16336 76732616 7952 16335

test
BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
------- ----------- ---------- ----------- --------------- -------------

     34 240663 8032 0 0 27

prod
BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
------- ----------- ---------- ----------- --------------- -------------

     55 252190 8032 0 0 27

test
MOST_RPTD_KEY BTREE_SPC USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------- --------- ---------- -------- ------------

---

       218056 22543040 72132008 59 191591.704 95799.3519

prod
MOST_RPTD_KEY BTREE_SPC USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------- --------- ---------- -------- ------------

---

       232956 130345632 76984806 60 204486.556 102246.778

test

   PRE_ROWS PRE_ROWS_LEN

• ------------ 0 0

prod

   PRE_ROWS PRE_ROWS_LEN

• ------------ 0 0

Thanks,
Bruce

-----Original Message-----

From: Joseph S. Testa [mailto:teci_at_oracle-dba.com] Sent: Monday, 18 December 2000 23:11

Bruce, take a look at the dbms_space package(its hiding under $ORACLE_HOME/rdbms/admin), just grep for it to see which .sql it lives in dbmsutil.sql more than likely), anyways what you're looking at is allocated space versus used space.

in that package is a proc you can use to see truly used space, that info is not available with any views that exists today.

hth, joe
"Reardon, Bruce (CALBBAY)" wrote:

> 
> Hi,
> 
> I am running Oracle 81511 on NT 4.
> 
> I exported our production database and imported into a freshly created

test

> instance.
> 
> In doing so, I found that 1 index took up twice as much space in the test
> instance as it did in production.
> 
> The query I used to look at the occupied space was:
> 
> select
>   2     segment_name , segment_type , bytes , blocks , extents ,
> tablespace_name
>   3  from
>   4     dba_segments
>   5  where
>   6     segment_name ='CHSE_ELMN_FK_I'
>  10  order by
>  11     tablespace_name , bytes
>  12  ;
> 
> In production:
> 
> SEGMENT_NAME         SEGMENT_TYPE                  BYTES     BLOCKS
> EXTENTS TABLESPACE_NAME
> -------------------- ------------------ ---------------- ----------
> ---------- --------------------
> CHSE_ELMN_FK_I       INDEX                   134,963,200      16475
> 1 INDEXD
> 
> whereas in test:
> 
> SEGMENT_NAME         SEGMENT_TYPE                  BYTES     BLOCKS
> EXTENTS TABLESPACE_NAME
> -------------------- ------------------ ---------------- ----------
> ---------- --------------------
> CHSE_ELMN_FK_I       INDEX                   261,144,576      31878
> 7 INDEXD
> 
> The export was taken with compress=no (and anyway I got more extents after
> the import compared to in production.
> 
> In both cases, the degree of the index is set to default.
> 
> I checked the base table in both instances and it contained basically the
> same number of rows (production has been in use since the export and now

has

> a few more rows) - this is around 5,172,976.
> 
> Initially I had analyzed the test schema with an estimate of 30% and
> dba_indexes showed the number of rows 10,363,732 whereas after I did a
> compute analyze the number of rows showed correctly in dba_indexes.

Received on Thu Dec 21 2000 - 16:46:00 CST