Re: Partitions expanding above the HWM

From: Thomas Roach <troach_at_gmail.com>
Date: Mon, 11 Jan 2010 21:29:19 -0500
Message-ID: <b86ffce61001111829y10771fc0gcefcaaa22100e89e_at_mail.gmail.com>

Thanks for the assistance. The blocksize for the whole DB is 16kb. The partition is hash by customer with a sub partition so the range I do not get help with, but it is definitely an option. What happens is the insert (and we have 7 processes running in parallel, so there is some contention on these heavy inserts), then each one cleans up the old copies of the records. We only delete old records when new ones are uploaded, so if we insert 50,000 records, and only 30,000 of them are updates, then it inserts all 50k, then drops the old 30k ones, so we could still have 20k from the old run. So as more records are inserted, the old blocks look like swiss cheese, but the old space does not appear to be used. With ASSM, I wonder if I am hitting some sort of bug because the operations I see don't tell me that it is avoiding previously used blocks.

On Mon, Jan 11, 2010 at 9:13 PM, Surachart Opun <surachart_at_gmail.com> wrote:

> ASSM - PCTUSED is no need (ignore)
>
> If you use tablespace (ASSM), you can shrink segment (each partition)
> http://www.oracle.com/technology/pub/articles/10gdba/week15_10gdba.html
>
> ALTER TABLE [TABLE_NAME] ENABLE ROW MOVEMENT;
> ALTER TABLE [TABLE_NAME] MODIFY PARTITION [PARTITION_NAME] SHRINK SPACE
> COMPACT; -- shrink segment, but no reduce HWM
> ALTER TABLE [TABLE_NAME] MODIFY PARTITION [PARTITION_NAME] SHRINK SPACE; --
> reduce HWM
>
> This case may occur, if you have many insert sessions(and have session to
> delete HIGH values).
> But if 640 GB and then shrunk to 17 GB
>
> How about block_size in this tablespace? -- I have grew up like this on big
> block_size(32K)
>
> I have some question - How about delete data process?
> If you delete old day ... you may point to make table partition by RANGE
> (and then truncate old day)
>
> Good Luck
> Surachart Opun
> http://surachartopun.com
>
>
>
> On Tue, Jan 12, 2010 at 5:55 AM, Thomas Roach <troach_at_gmail.com> wrote:
>
>> 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:29:19 CST