Help! Db table reorg problem...
Date: Tue, 8 Jan 2008 12:52:58 -0700
Message-ID: <2BC7419BF42B0146A7BB8C52A236313D02F1C721@E03-GOA-EXCH-66.goa.ds.gov.ab.ca>
Hello List,
Problem Summary: Partitioned table "original_table" did not have partitions to properly deal with the last 2 years and future years. My table reorg objective was to create a new set of tablespaces similar to the old ones and a new table similar to the old one with the exception that it would have the correct number of partitions in it; thus when the data would be re-inserted into the new table, that it would be allocated to the correct partition. The old table and tablespaces would then be dropped. The end result was that after the data was inserted into the "new_table" (say 250million rows) that the new table consumed 3 (almost 4) times the number of data blocks (Example: Where one partition used to use up 1Gb of space, the new one used up 3Gb). As this would result in 3 times the number of data blocks being read from disk, there would likely be a performance degradation, so I had to abort the reorg. I did not change anything of importance in the ddl script generated from TOAD except for the tablespace names, table name and addition of more partitions, so why was there such a large discrepancy in the space used for the data? The original table was in an Oracle 9.2.0.4 database that was upgraded to 10.2.0.2; the new table is being created in the 10.2.0.2 database. The indexes/index tablespaces likewise grew in size.
Naturally we assumed the issue was with the table or tablespace being created with pctfree/pctused parameters incorrectly. However, since we're using automatic allocation, this should be done automatically. The data was inserted from the old table with a straight "insert into table_new () select () from table_old;" type of command.
Details:
Following is the ddl script from the original table that was used to
create the new table. I didn't feel that the 128Mb next extent size was
appropriate, but I did not want to change anything from the original
without an appropriate test cycle to ensure performance was not
degraded:
CREATE TABLE SCOTT.ORIGINAL_TABLE (
ID NUMBER(12), PROG_PROGRAM_CD VARCHAR2(2 BYTE), ACPE_END_DATE DATE,
...)
TABLESPACE ORIGINAL_TABLESPACE6
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY RANGE (PROG_PROGRAM_CD, ACPE_END_DATE) ( PARTITION ORIG_P01_89 VALUES LESS THAN ('01', TO_DATE(' 1990-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOLOGGING
COMPRESS
TABLESPACE ORIGINAL_TABLESPACE1
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128M
NEXT 128M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION ORIG_P01_9091 VALUES LESS THAN ('01', TO_DATE(' 1992-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOLOGGING
COMPRESS
TABLESPACE ORIGINAL_TABLESPACE1
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128M
NEXT 128M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION RTLI_P01_9293 VALUES LESS THAN ('01', TO_DATE(' 1994-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ...<editted out due to email length issues> PARTITION RTLI_P01_0203 VALUES LESS THAN ('01', TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOLOGGING
COMPRESS
TABLESPACE ORIGINAL_TABLESPACE5
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128M
NEXT 128M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION RTLI_P01_0405 VALUES LESS THAN ('01', MAXVALUE) NOLOGGING
NOCOMPRESS
TABLESPACE ORIGINAL_TABLESPACE6
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128M
NEXT 128M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION RTLI_PXX VALUES LESS THAN (maxvalue, maxvalue) NOLOGGING
NOCOMPRESS
TABLESPACE ORIGINAL_TABLESPACE7
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128M
NEXT 128M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT; The new table script looked similar except there were new partitions added in (not much changed).
The original tablespaces for the original table looked similar to the following:
CREATE TABLESPACE ORIGINAL_TABLESPACE1 DATAFILE
'/u10/oradata/DB/data_original101.dbf' SIZE 1200M AUTOEXTEND ON NEXT
150M MAXSIZE 2000M
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
For the first iteration, I used similarly defined tablespaces, but ran
into the space issue. Then Itried the automatic allocation options so
that my tablespaces were now defined as:
CREATE TABLESPACE NEW_TABLESPACE1 DATAFILE
'/u03/oradata/DB/data_new101.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M
MAXSIZE 4000M,
'/u13/oradata/DB/data_new102.dbf' SIZE 3000M AUTOEXTEND OFF,
'/u16/oradata/DB/data_new103.dbf' SIZE 4000M AUTOEXTEND OFF
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
The above still caused a big difference in space and blocks used. To
demonstrate, I compare values for tablespace # 2:
Original:
Partition Year 1992-93 Blocks 50,944 Empty Blocks 15,360 Avg Spc 73 Rows 11,514,503 Partition Year 1994-95 Blocks 65,280 Empty Blocks 1,280 Avg Spc 61 Rows 14,522,500 New: Partition Year 1992-93 Blocks 191,853 Empty Blocks null Avg Spc 0 Rows (table not analyzed; but rowcounts in table are the same) Partition Year 1994-95 Blocks 217,429 Empty Blocks null Avg Spc 0 Rows (table not analyzed; but rowcounts in table are the same)
For the original table, tablespace 2 uses up 1,144 GB of space; for the new table, 3,264.125 GB of space are used up (this is with the autoallocate parameters on the tablespace).
I tried changing the pctused/pctfree values to 80% used, 10% free on the 'create table' statement, but these are supposed to be disregarded anyway when the tablespace uses the autoallocate functions.
Does anyone have an explanation for these differences in space allocated? I've been thinking it's a TOAD ddl script creation bug where the ddl generated was not what was really there originally. Or else that since the table/tablespaces were originally created in 9i, that the upgrade to 10g changed things and now I can't hope to get the same disk usage under the new 10g methods. Or else I'm somewhere out in left field...
TIA,
Ben
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 08 2008 - 13:52:58 CST