Re: Partitions expanding above the HWM

From: Thomas Roach <troach_at_gmail.com>
Date: Mon, 11 Jan 2010 21:24:10 -0500
Message-ID: <b86ffce61001111824r6b70a3fdobd7d31ecc0ffcf46_at_mail.gmail.com>

Thanks for your response. I am still working on trying to replicate the issue / combined with looking very closely at tonights ETL run to see what's happening.

To answer a couple of your questions. This is a 16k blocksize DB. The extents are uniform and the tablespaces are all locally managed with auto segment space management (ASSM).

I found a few bugs with ASSM but none impacting 10.2.0.4. A few in 10.2.0.2 that were fixed in 10.2.0.3. I am trying to replicate the issue but it's driving me nuts :)

On Mon, Jan 11, 2010 at 8:46 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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!
>

-- 
Thomas Roach
813-404-6066
troach_at_gmail.com

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

Received on Mon Jan 11 2010 - 20:24:10 CST