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: aaaa wwwwww <krisibm_at_lycos.com>
Date: Mon, 23 Oct 2000 17:37:02 -0700
Message-Id: <10658.120013@fatcity.com>


What about rowid column ? It also occupies some space

What about pctused It cann't be 100%

Index keeps some space in every block for expansion

Rao

--

On Mon, 23 Oct 2000 12:50:27  
 Kimberly Smith wrote:

>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
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
Received on Mon Oct 23 2000 - 19:37:02 CDT

Original text of this message

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