Re: RE: Problem with LMT and ASSM

Markus,
if you're not in append mode, then the insert should reuse empty space, do you have LOBS in this table?

rgds

On 6/27/06, Markus Kuehn <Markus.Kuehn_at_lbs-bw.de> wrote:
>
> Hi Joel and Salem,
>
> no APPEND-hint in the insert statement and partitioning is not an option
> due to licensing costs. Any other hints ??
>
> Regards
>
> Markus Kühn
> LBS Landesbausparkasse Baden-Württemberg
> Abteilung OI
> Gruppe Datenbanken und Konfigurationsmanagement
>
> Jägerstraße 36, 70174 Stuttgart
> Postfach 10 60 28, 70049 Stuttgart
>
> Siegfried-Kühn-Str. 4, 76135 Karlsruhe
> Postfach 14 60, 76003 Karlsruhe
>
> Telefon 07 11 / 1 83 - 2915
> Fax 07 11 / 1 83 - 492915
> E-Mail Markus.Kuehn_at_LBS-BW.de
>
> Amtsgericht Stuttgart HRA 12924
> Amtsgericht Karlsruhe HRA 4548
>
>
>
> Wittenmyer Joel -
> CO
> <WITTENMYERJ_at_tusc An
> .com> "'salem.ghassan_at_gmail.com'"
> <salem.ghassan_at_gmail.com>,
> 27.06.2006 16:28 Markus.Kuehn_at_lbs-bw.de
> Kopie
> oracle-l_at_freelists.org
> Thema
> RE: Problem with LMT and ASSM
>
>
>
>
>
>
>
>
>
>
> My first thought also. And if you use 'parallel append' you can actually
> hear the space being chewed up J as each parallel process gets it's own
> extent to devour.
>
>
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Ghassan Salem
> Sent: Tuesday, June 27, 2006 9:26 AM
> To: Markus.Kuehn_at_lbs-bw.de
> Cc: oracle-l_at_freelists.org
> Subject: Re: Problem with LMT and ASSM
>
> Markus,
> when you INSERT, do you do it with /*+ APPEND*/? if so (it looks from your
> post, but just wanted to verify), then
> the space deleted will not get used, as it is below the HWM, hence not
> used
> by the insert in direct mode.
> The best way to do it, if possible, is to partition the table in a way
> that
> allows you to DROP the partitions instead of deleting the records. This
> will really free the space used, and hence let the INSERT /+ append*/
> reuse
> it.
>
> rgds
> On 6/27/06, Markus Kuehn <Markus.Kuehn_at_lbs-bw.de> wrote:
>
> Hello members,
>
> sorry, forgot the subject.
>
> We have a problem with locally managed tablespaces and segment space
> management auto on 9.2.0.6 on AIX 5.2. The problem follows:
>
> We created a table in a tablespace with extent management local
> autoallocate and segment space management auto. Every day about 150.000
> records are inserted into this table. Records which are older than 90 days
> are deleted from the table. The delete-job runs at 17:00, the insert-job
> runs at 00:30, so that they are seperated from each other. No updates are
> taking place in this table. The first inserts created 64 extents with 128
> blocks and 1 MB each. After those extents went full, 120 extents with 1024
> blocks and 8 MB each were created. Now, as they went full too, extents
> with
> 8192 blocks and 64 MB each are created. Due to the deletes the lower
> extents from extent id 0 to extent id 90 are freed but are not reused.
> Instead new extents are created. This will lead to a tablespace covering
> all available disk-space, which is not really satisfying. An "alter table
> move" to a different tablespace and "alter tablespace move" to the
> original
> tablespace reduces the number of used extents. But after some time we run
> in the same problem again.
>
> Anyone out there has a solution or work-around for this type of situation.
>
> Regards
> Mit freundlichen Grüßen
>
> Markus Kühn
> LBS Landesbausparkasse Baden-Württemberg
> Abteilung OI
> Gruppe Datenbanken und Konfigurationsmanagement
>
> Jägerstraße 36, 70174 Stuttgart
> Postfach 10 60 28, 70049 Stuttgart
>
> Siegfried-Kühn-Str. 4, 76135 Karlsruhe
> Postfach 14 60, 76003 Karlsruhe
>
> Telefon 07 11 / 1 83 - 2915
> Fax 07 11 / 1 83 - 492915
> E-Mail Markus.Kuehn_at_LBS-BW.de
>
> Amtsgericht Stuttgart HRA 12924
> Amtsgericht Karlsruhe HRA 4548
>
>
> Die LBS Baden-Württemberg verwendet aktuelle Virenschutzprogramme. Wir
> haften nicht für Schäden, die dem Empfänger durch diese E-Mail entstehen
> könnten. Bitte haben Sie Verständnis, dass wir die rechtliche
> Verbindlichkeit für den Inhalt dieser E-Mail ausschließen. Sind Sie nicht
> der beabsichtigte Empfänger dieser E-Mail, teilen Sie uns dies bitte mit
> und löschen die E-Mail. Vielen Dank.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
> Die LBS Baden-Württemberg verwendet aktuelle Virenschutzprogramme. Wir
> haften nicht für Schäden, die dem Empfänger durch diese E-Mail entstehen
> könnten. Bitte haben Sie Verständnis, dass wir die rechtliche
> Verbindlichkeit für den Inhalt dieser E-Mail ausschließen. Sind Sie nicht
> der beabsichtigte Empfänger dieser E-Mail, teilen Sie uns dies bitte mit
> und löschen die E-Mail. Vielen Dank.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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