RE: Help! Db table reorg problem...
- From: "Ben Wittmeier" <Ben.Wittmeier@xxxxxxxxx>
- To: "Bradd Piontek" <piontekdd@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Jan 2008 16:17:27 -0700
>>One thing to note (although probably have no bearing):
>> 1. PCTFREE is not ignored with ASSM segments, that only applies to
PCTUSED.
You're right, Bradd - thanks for the correction. We did try different
settings though for pctfree/pctused, but it did not resolve the problem.
Pctfree was 10 for most tries.
>>and a couple questions:
>> How were the number of blocks and free blocks derived?
Just by looking at the 'Partitions' tab on the right hand side of the
page in Quest TOAD tool for the partitioned table. But the block counts
don't lie regardless of stats, etc since the tablespace itself kept
gobbling up actual disk space. It was obviously requiring 3 times the
block usage since disk usage tripled in conjunction with the block
counts in the Partitions tab.
>> If via dba_tab_partitions, how were the analyzes done in each
environment and with what options (analyze, vs dbms_stats).
dbms_stats I believe (another dba collected the stats for the table, so
I'm not 100% sure of that, but our standard way is using dbms_stats)
>> Have you used DBMS_SPACE to look at the real statistics?
No. I don't see the point in looking at stats when the table expansion
speaks for itself in the amount of disk space it sucks up?
>> Were the rows inserted into the new partitions in parallel or
serially?
Serially.
Thanks,
Ben
________________________________
From: Bradd Piontek [mailto:piontekdd@xxxxxxxxx]
Sent: Tuesday, January 08, 2008 3:33 PM
To: Ben Wittmeier; oracle-l@xxxxxxxxxxxxx
Subject: Re: Help! Db table reorg problem...
One thing to note (although probably have no bearing):
1. PCTFREE is not ignored with ASSM segments, that only applies to
PCTUSED
and a couple questions:
How were the number of blocks and free blocks derived?
If via dba_tab_partitions, how were the analyzes done in each
environment and with what options (analyze, vs dbms_stats).
Have you used DBMS_SPACE to look at the real statistics?
Were the rows inserted into the new partitions in parallel or
serially?
----- Original Message ----
From: Ben Wittmeier <Ben.Wittmeier@xxxxxxxxx>
To: mark.powell@xxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Tuesday, January 8, 2008 3:44:27 PM
Subject: RE: Help! Db table reorg problem...
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
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.
Other related posts:
