Antwort: RE : Antwort: RE: Antwort: RE: Problem with LMT and ASSM

From: Markus Kuehn <Markus.Kuehn_at_lbs-bw.de>
Date: Tue, 27 Jun 2006 17:45:06 +0200
Message-ID: <OFD39B60F2.4D1D4A01-ONC125719A.00567112-C125719A.00568686@lbs-bw.de>

Marius,

Thanks for your hint. I'll examine Jonathan Lewis's website.

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

                                                                           
             Marius Raicu                                                  
             <mariu200_at_yahoo.c                                             
             om>                                                        An 
                                        Markus.Kuehn_at_lbs-bw.de             
             27.06.2006 17:40                                        Kopie 
                                        oracle-l_at_freelists.org             
                                                                     Thema 
                                        RE : Antwort: RE: Antwort: RE:     
                                        Problem with LMT and ASSM

Hi,
You could try to simulate the partitioning using different same-strucure tables and a view like
select * from t1
union all
select * from t2
....
This way, your big table would be a view of union all real tables.Something like partition views.Then you may be able to control better the deletes and inserts and also the storage parameters for each table.I think Jonathan Lewis has more about partition views on his site. http://www.jlcomp.demon.co.uk/ind_feat.html Regards,
Marius

Markus Kuehn <Markus.Kuehn_at_lbs-bw.de> a écrit : Joel,

other segments are in this tablespace. These segments are not very large so
they are using small extents (128 blocks 1 MB each), which are reused. Thinking about your answer it seems to me, that maybe I hit a bug ?! I'll think I'll give the tablespace with uniform extents a test ... and I'll open a service request. Let's see what ORACLE says about this.

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

.com> 'Markus Kuehn'

27.06.2006 16:48 Kopie

Thema
RE: Antwort: RE: Problem with LMT
and ASSM

Just a thought. Since Oracle allocates ever larger extent sizes based on the amount of current space, it may not be reusing the smaller extents for the same segment. It might be insisting on larger extents because it thinks
it will need ever larger extents. Do any other segments live in this tablespace? Are they using the smaller extents (assuming that they are not also very large)? I'm wondering if Oracle would reuse the extents if the tablespace were uniform rather than autoallocate.

-----Original Message-----
From: Markus Kuehn [mailto:Markus.Kuehn_at_lbs-bw.de] Sent: Tuesday, June 27, 2006 9:35 AM
To: Wittenmyer Joel - CO
Cc: oracle-l_at_freelists.org
Subject: Antwort: RE: Problem with LMT and ASSM

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

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

Regards,
Marius

Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son interface révolutionnaire.

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

Received on Tue Jun 27 2006 - 10:45:06 CDT