RE: Help! Db table reorg problem...
- From: "Ben Wittmeier" <Ben.Wittmeier@xxxxxxxxx>
- To: <mark.powell@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 8 Jan 2008 14:44:27 -0700
Mark - thanks for the reply:
>> What type of space management was used in the old tablespaces verse
the new ones?
>> Dictionary vs auto-allocate? ASSM?
As per the details section, the original tablespace was created as
'NOLOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128M
BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON'
and the new tablespace was created the same way with the poor storage
results. A 2nd attempt with tablespace setup as
'NOLOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON' resulted in a
bit better storage usage but still not close to the original.
>>What do you mean by did not change anything important?
I changed the name of the tablespaces, added new partitions (which
should not have increased the size of old partitions) and changed the
names of the partitions - none of these should have had any bearing on
space usage that I can see. The create table and tablespace statements
are basically what the originals were.
Thanks,
Ben
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark D
Sent: Tuesday, January 08, 2008 2:26 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Help! Db table reorg problem...
What type of space management was used in the old tablespaces verse the
new ones? Dictionary vs auto-allocate? ASSM? What do you mean by did
not change anything important?
-- Mark D Powell --
Phone (313) 592-5148
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ben Wittmeier
Sent: Tuesday, January 08, 2008 2:53 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Help! Db table reorg problem...
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
