Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Clarification -- NUMERIC data type always takes up 22 bytes.

Re: Clarification -- NUMERIC data type always takes up 22 bytes.

From: <Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com>
Date: Mon, 23 Oct 2000 14:14:54 -0400
Message-Id: <10658.119990@fatcity.com>


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>
From:     "Tom Pall" <tom_at_cdproc.com> @ NOTES NET
Date:     10/23/2000 05:26:39 PM GMT
Subject:  Re: Clarification -- NUMERIC data type always takes up 22 bytes.
          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;
/
>
>
>
> 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?
Received on Mon Oct 23 2000 - 13:14:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US