RE: Partitions expanding above the HWM

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 11 Jan 2010 20:46:55 -0500
Message-ID: <9309B37237054D799D2A5D6751D48EB8_at_rsiz.com>

Okay, ASSM has to do with which block you insert a row into in the already allocated extents in non-bulk or direct insert operations. But if you're bumping up form 17G to 640G, that has to do with more extents.  

So, what is your style and size? We know you're locally managed since you're ASSM. Are you system or uniform? What is your blocksize and what is your size? System can trigger large incremental growth something like a non-zero percent free. Uniform is uniform, so you can change the size of extents only at create time.  

There was a bug for a while where each session failing to find an eligible block to insert into asked for a new extent without checking if another session had already asked for a new extent, so if you had many sessions inserting concurrently that could trigger a rapid expansion like the behavior of the universe before Planck time. But I thought that was fixed at least 18 months ago.  

Probably it is worth looking for the support note.  

mwf  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Roach
Sent: Monday, January 11, 2010 5:56 PM
To: Oracle Discussion List
Subject: Partitions expanding above the HWM  

4 node RAC - 10.2.0.4

Linux x86_64 RHEL 5.3  

Tablespace

ASSM on the Tablespaces (but the table has PCTFREE of 10 and PCTUSED of 0
"which should be ignored because of ASSM."
 

Reporting Table

Partitioned by hash into 16 partitions.

Degree of parallel is 1.

PCTUSED 0 PCTFREE 10   Staging Table

Partitioned by server name (7 partitions)

Parallelization is enabled  

I am having an issue with a table that is just using way too much disk space. As of today, it was using 640GB. I shrunk it down to about 17GB by using the move partition command with update indexes. As data is inserted, it appears to be  

I initially thought I had it when I saw PCTUSED 0, but then I found the tablespace was ASSM, so this value "should" be ignored. I found the package that loads this table from a staging table.  

What happens is this.  

"INSERT INTO table SELECT * FROM staging_table"
 

THEN  
"DELETE FROM table WHERE EXISTS"
 

So, I set up a little test case, and I can't seem to replicate the data. This is not doing anything with APPEND in this package that does the ETL. I tested with parallel dml enabled at the session level and that yielded no results. Does anyone have any ideas what I might be missing? Could this be a bug with ASSM? Any help would greatly be appreciated as I have banged my head against the monitor all day (not literally).  

Thanks!

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 11 2010 - 19:46:55 CST