Re: Partitions expanding above the HWM
- From: Thomas Roach <troach@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Mon, 11 Jan 2010 21:24:10 -0500
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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *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@xxxxxxxxx
Other related posts:
