Deleted space is reused in indexes only when an entire block is empty.
The empty block goes to the freelist.
- Original Message -----
From: Kimberly Smith <kimberly.smith_at_gmd.fujitsu.com>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent: Monday, October 23, 2000 3:50 PM
Subject: RE: Clarification -- NUMERIC data type always takes up 22 bytes.
> Have you been deleting data? If I remember correctly, the space
> is not reused in indexes. Try rebuilding your index and see
> if the size changes to what you feel it should be.
>
> -----Original Message-----
> [mailto:Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com]
> Sent: Monday, October 23, 2000 12:20 PM
> To: Multiple recipients of list ORACLE-L
> bytes.
>
>
>
>
>
> Thank you for the script. Here is the output of the script for the table in
> question:
> Type Name Blocks Next Ext Unused
> Free
> --------------- ---------------------------- -------- -------- -------- ---
> -----
> TABLE AGYPKAR 31,360 640 200
> 3
> INDEX AGYPKAR_PK 5,120 640 153
> 0
>
> My DB_BLOCK_SIZE = 8K, thus it's telling me that I only have 1Mb unused (I
> can see that happenning) and
> it is also telling me that my index size approx. 40Mb (which matches my
> queries results) -- that I can not explain.
>
> I expect this index to be 14Mb given the data size stored in it and the
> number of redcords.
>
> Am I missing something?
>
> Thanks,
> Val Gamerman.
> To: Val Gamerman/Victoria Financial_at_VICTORIA FINANCIAL
> cc: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Date: 10/23/2000 05:26:39 PM GMT
> Alternatives?
>
>
>
>
> I have a script I run in sql*plus, stolen from Jonathan Lewis, which shows
> the
> actual amount of space taken up by objects and the amount of free space
> below the
> high water mark (in full blocks, of course). The parameter m_scan_limit
> determines how
> many many blocks on the free list Oracle walks. The larger the number, the
> more accurate
> if there are lots of blocks below the HWM. Of course, the higher the
> number, the load placed
> upon your database.
> If you have recently rebuilt the index which makes up your PK, the script
> will tell you just how
> big your PK index is.
> Make sure you replace MY_SCHEMA with the name of your schema and edit the
> in list for
> the segment types.
> rem this script show the number of blocks used an unused by each segment
> rem it shows the unused space under the high water mark as well
> rem must be run in sql*plus
> set serveroutput on size 100000
> declare
> cursor c1 is
> select owner, segment_name,segment_type,
> blocks, next_extent/8192 next_ext, partition_name
> from dba_segments
> where segment_type in ('INDEX','TABLE','CLUSTER', 'TABLE
> PARTITION')
> and owner in ('MY_SCHEMA')
> order by segment_name
> ;
> m_tot_blocks number;
> m_tot_bytes number;
> m_unused_blocks number;
> m_unused_bytes number;
> m_last_file_id number;
> m_last_block_id number;
> m_last_block number;
> m_on_free_list number;
> m_free_group number := 0;
> m_scan_limit number := 100;
> begin
> dbms_output.put_line(
> rpad('Type',15) || ' ' ||
> rpad('Name',28) || ' ' ||
> lpad('Blocks',8) || ' ' ||
> lpad('Next Ext',8) || ' ' ||
> lpad('Unused',8) || ' ' ||
> lpad('Free',8)
> );
> dbms_output.put_line(
> rpad('-',15,'-') || ' ' ||
> rpad('-',28,'-') || ' ' ||
> lpad('-',8,'-') || ' ' ||
> lpad('-',8,'-') || ' ' ||
> lpad('-',8,'-') || ' ' ||
> lpad('-',8,'-')
> );
> for r1 in c1 loop
> dbms_space.free_blocks(
> r1.owner,
> r1.segment_name,
> r1.segment_type,
> m_free_group,
> m_on_free_list,
> m_scan_limit, r1.partition_name
> );
> dbms_space.unused_space(
> r1.owner,
> r1.segment_name,
> r1.segment_type,
> m_tot_blocks,
> m_tot_bytes,
> m_unused_blocks,
> m_unused_bytes,
> m_last_file_id,
> m_last_block_id,
> m_last_block, r1.partition_name
> );
> dbms_output.put_line(
> rpad(substr(r1.segment_type,1,15),15) || ' ' ||
> rpad(substr(r1.segment_name,1,28),28) || ' ' ||
> lpad(to_char(m_tot_blocks,'999,990'),8) || ' ' ||
> lpad(to_char(r1.next_ext,'99,990'),8) || ' ' ||
> lpad(to_char(m_unused_blocks,'999,990'),8) || ' '
> ||
> lpad(to_char(m_on_free_list,'999,990'),8)
> );
> end loop;
> end;
> /
>
> ----- Original Message -----
> To: Tom Pall _ <tom_at_cdproc.com>
> Cc: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Monday, October 23, 2000 11:09 AM
> Alternatives?
>
> >
> >
> >
> > Alright,
> >
> > My bad - I should have selected vsize(field) to see how much is actully
> > allocated for the data in the column.
> >
> > However I still have something that I can not explain. I have a table
> that
> > has a PRIMARY KEY that is too big in my opinion:
> >
> > The recap:
> > 1. Actual size of the index is ~ 40Mb
> > 2. Actual size of the data that should be stored in the index is ~ 14Mb
> > 3. Index INIT_EXTENT = 10Mb, NEXT = 5Mb, PCT_FREE=0, MIN_EXTENTS = 1
> > 4. Index has just been rebuilt.
> >
> > Below you can find the results of queries that I ran trying to identify
> > what the problem might be.
> >
> > If anybody has enough time to spend to look at these I would be very
> > appreciative. It's just it's been driving me nuts ...
> >
> > Thanks,
> > Val Gamerman.
> >
> >
> >
> >
> > Here is the description of the table:
> >
> >
> ===========================================================================
> > ==============================
> > Name Null? Type
> > ------------------------------- -------- ----
> > D8POLN NOT NULL NUMBER(9)
> > D8SEQN NOT NULL NUMBER(3)
> > D8PACK VARCHAR2(2000)
> > D8DSTS VARCHAR2(1)
> > D8LCUS VARCHAR2(10)
> > D8LCDT DATE
> > D8LCTM VARCHAR2(8)
> >
> > Or more detailed one:
> >
> ===========================================================================
> > ==============================
> > 1 select SUBSTR(TABLE_NAME,1,8), substr(COLUMN_NAME,1,8), DATA_TYPE,
> > DATA_LENGTH, DATA_PRECISION,
> > 2 from all_tab_columns
> > 3* where table_name = 'AGYPKAR'
> > SQL> /
> >
> > SUBSTR(T SUBSTR(C DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
> COLUMN_ID
> > -------- -------- --------- ----------- -------------- ----------
> ---------
> > AGYPKAR D8POLN NUMBER 22 9 0
> 1
> > AGYPKAR D8SEQN NUMBER 22 3 0
> 2
> > AGYPKAR D8PACK VARCHAR2 2000
> 3
> > AGYPKAR D8DSTS VARCHAR2 1
> 4
> > AGYPKAR D8LCUS VARCHAR2 10
> 5
> > AGYPKAR D8LCDT DATE 7
> 6
> > AGYPKAR D8LCTM VARCHAR2 8
> 7
> >
> ===========================================================================
> > ==============================
> > Number of records:
> >
> > SQL> select count(*) from agypkar;
> >
> > COUNT(*)
> > ---------
> > 2092487
> >
> ===========================================================================
> > ==============================
> > The first two fields constitute the PRIMARY KEY:
> >
> > 1 select INDEX_NAME, COLUMN_NAME,COLUMN_POSITION, COLUMN_LENGTH
> > 2 from all_ind_columns
> > 3* where table_name = 'AGYPKAR'
> > SQL> /
> >
> > INDEX_NAME COLUMN_NAME
> > COLUMN_POSITION COLUMN_LENGTH
> > ------------------------------ ------------------------------
> -------------
> > -- -------------
> > AGYPKAR_PK D8POLN
> > 1 22
> > AGYPKAR_PK D8SEQN
> > 2 22
> >
> ===========================================================================
> > ==============================
> >
> ===========================================================================
> > ==============================
> >
> ===========================================================================
> > ==============================
> >
> ===========================================================================
> > ==============================
> >
> ===========================================================================
> > ==============================
> >
> > What I do NOT understand is why the size of the index is
> > 1 select sum(bytes) from dba_extents
> > 2* where segment_name = 'AGYPKAR_PK'
> > SQL> /
> >
> > SUM(BYTES)
> > ----------
> > 41943040
> > ------------------------------------------------------------- OR 7
> EXTENTS
> > ! ---------------------------------------------------------------
> > 1 select count(*) from dba_extents
> > 2* where segment_name = 'AGYPKAR_PK'
> > SQL> /
> >
> > COUNT(*)
> > ---------
> > 7
> >
> >
> > That's roughly 40Mb for 2 million records. I would think that it should
> be
> > approx. 14Mb according to the data below.
> >
> > 1* select sum(vsize(d8poln)+vsize(d8seqn)) from agypkar
> > SQL> /
> >
> > SUM(VSIZE(D8POLN)+VSIZE(D8SEQN))
> > --------------------------------
> > 14626070
> >
> >
> > The MIN extents are fine and PCT_FREE is set to 0.
> > 1 select index_name, initial_extent, min_extents, next_extent,
> pct_free
> > 2 from all_indexes
> > 3* where index_name = 'AGYPKAR_PK'
> > SQL> /
> >
> > INDEX_NAME INITIAL_EXTENT MIN_EXTENTS NEXT_EXTENT
> > PCT_FREE
> > ------------------------------ -------------- ----------- -----------
> -----
> > ----
> > AGYPKAR_PK 10485760 1 5242880
> > 0
> >
> > What am I missing?
> >
> >
> > The RECAP:
> > 1. Actual size of the index is ~ 40Mb
> > 2. Actual size of the data that should be stored in the index is ~ 14Mb
> > 3. Index INIT_EXTENT = 10Mb, NEXT = 5Mb, PCT_FREE=0, MIN_EXTENTS = 1
> > 4. Index has just been rebuilt.
> >
> >
> >
> > Thanks,
> > Val Gamerman.
> >
> >
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Kimberly Smith
> INET: kimberly.smith_at_gmd.fujitsu.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
Received on Mon Oct 23 2000 - 16:26:19 CDT
