Re: Two tables are quite the same, but size of theirs ORA datafiles are different!

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