RE: Help! Db table reorg problem...

From: Ben Wittmeier <Ben.Wittmeier_at_gov.ab.ca>
Date: Wed, 9 Jan 2008 16:17:27 -0700
Message-ID: <2BC7419BF42B0146A7BB8C52A236313D02F1C74A@E03-GOA-EXCH-66.goa.ds.gov.ab.ca>


>>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_at_yahoo.com] Sent: Tuesday, January 08, 2008 3:33 PM
To: Ben Wittmeier; oracle-l_at_freelists.org 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_at_gov.ab.ca> To: mark.powell_at_eds.com; oracle-l_at_freelists.org 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.

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 09 2008 - 17:17:27 CST

Original text of this message