Re: Two tables are quite the same, but size of theirs ORA datafiles are different!
Date: Tue, 28 Oct 2008 05:19:30 -0700 (PDT)
Message-ID: <47b89210-8b27-4ca6-af9c-3e8810e3278b@u75g2000hsf.googlegroups.com>
Comments embedded.
On Oct 27, 12:41 pm, Big George <jbet..._at_gmail.com> wrote:
> I've got two tables in Oracle 10.1g, Windows 2003.
>
> TableA
> 16,594,824 records
>
> CREATE TABLE TableA
> (
> NUMDLE NCHAR(8) NOT NULL,
> DIGVER NCHAR(1) NOT NULL,
> NUMLIB NCHAR(6) NOT NULL,
> CODDEP NCHAR(2) NOT NULL,
> CODPRO NCHAR(2) NOT NULL,
> CODDIS NCHAR(2) NOT NULL,
> APEPAT VARCHAR2(40 BYTE),
> APEMAT VARCHAR2(40 BYTE),
> NOMBRE VARCHAR2(35 BYTE),
> FECNAC NCHAR(8),
> CODSEX NCHAR(1),
> CODGRI NCHAR(2),
> CODSTR NCHAR(1),
> TIPDOC NCHAR(1),
> CONSTRAINT NUMDLETABLEA
> PRIMARY KEY
> (NUMDLE)
> )
> ORGANIZATION INDEX
> LOGGING
> TABLESPACE TableSpaceA
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> PCTINCREASE 0
> BUFFER_POOL DEFAULT
> )
> NOPARALLEL
> MONITORING;
>
> TableB
> 16,494,906 records
>
> CREATE TABLE TableB
> (
> NUMDLE NCHAR(8) NOT NULL,
> DIGVER NCHAR(1) NOT NULL,
> NUMLIB NCHAR(6) NOT NULL,
> CODDEP NCHAR(2) NOT NULL,
> CODPRO NCHAR(2) NOT NULL,
> CODDIS NCHAR(2) NOT NULL,
> APEPAT VARCHAR2(40 BYTE),
> APEMAT VARCHAR2(40 BYTE),
> NOMBRE VARCHAR2(35 BYTE),
> FECNAC NCHAR(8),
> CODSEX NCHAR(1),
> CODGRI NCHAR(2),
> CODSTR NCHAR(1),
> TIPDOC NCHAR(1),
> CONSTRAINT NUMDLETABLEB
> PRIMARY KEY
> (NUMDLE)
> )
> ORGANIZATION INDEX
> LOGGING
> TABLESPACE TableSpaceB
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> PCTINCREASE 0
> BUFFER_POOL DEFAULT
> )
> NOPARALLEL
> MONITORING;
>
> Both tables have quite the same number of records.
>
> TableA is set in TableSpaceA.
> Primary Key NUMDLETABLEA is set in TableSpaceA.
> TableSpaceA has only TableA and its PK.
>
> CREATE TABLESPACE TableSpaceA DATAFILE
> '...\ORACLE\PRODUCT\10.1.0\ORADATA\OPINT2\TableSpaceDataFileA.ORA'
> SIZE 1864M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
> LOGGING
> ONLINE
> PERMANENT
> EXTENT MANAGEMENT LOCAL AUTOALLOCATE
> BLOCKSIZE 8K
> SEGMENT SPACE MANAGEMENT AUTO
> FLASHBACK ON;
>
I am no fan of autoextend, however I realise it has its uses as long as a MAXSIZE is set, and NOT to UNLIMITED. You're simply asking for trouble with this.
> In O/S Windows: TableSpaceDataFileA.ORA = 1,908,744 KB
> Data Filename = TableSpaceDataFileA.ORA
> Used% = 99.57
> Size = 1864 MB
> Used = 1856.06 MB
> Free = 7.94 MB
> Blocks = 238592
> AutoExtend = On
> Next Extent = 1 MB
> Max Size = UNLIMITED
> Max Blocks = UNLIMITED
> Status = AVAILABLE
> Fragmentation Index = 78.97
>
> What intrigues me is why DataFileTableSpaceB size takes much more
> megabytes than DataFileTableSpaceA size.
>
You're not familiar with chained/migrated rows, then. Such a situation consumes data blocks as a single row occupies more than one as insufficient space is available in a single populated block to contain the updated row. A large migrated row population could possibly double the number of populated blocks a table consumes. You should check this:
SQL> @?/rdbms/admin/utlchain
SQL> analyze table TableB list chained rows;
SQL> select owner, table_name, count(*)
2 from chained_rows 3 group by owner, table_name;
You may return a surprisingly large number.
> TableB is set in TableSpaceB.
> Primary Key NUMDLETABLEB is set in TableSpaceB.
> TableSpaceB has only TableB and its PK.
>
> CREATE TABLESPACE TableSpaceB DATAFILE
> '...\ORACLE\PRODUCT\10.1.0\ORADATA\OPINT2\TableSpaceB.ORA' SIZE
> 2964M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
> LOGGING
> ONLINE
> PERMANENT
> EXTENT MANAGEMENT LOCAL AUTOALLOCATE
> BLOCKSIZE 8K
> SEGMENT SPACE MANAGEMENT AUTO
> FLASHBACK ON;
>
> In O/S Windows: TableSpaceDataFileB.ORA = 3,035,144 KB
> Data Filename = TableSpaceDataFileB.ORA
> Used% = 99.33
> Size = 2964 MB
> Used = 2944.06 MB
> Free = 19.94 MB
> Blocks = 379392
> AutoExtend = On
> Next Extent = 50 MB
> Max Size = UNLIMITED
> Max Blocks = UNLIMITED
> Status = AVAILABLE
> Fragmentation Index = 100
>
> Both tables are quite the same.
>
The tables may be; it's the data inside them that causes the differences.
> How can I determine why TableB size takes much more megabytes than
> TableA size ?
David Fitzjarrell Received on Tue Oct 28 2008 - 07:19:30 CDT