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