Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Antwort: Re: RE: Antwort: RE: Problem with LMT and ASSM

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

From: Markus Kuehn <Markus.Kuehn_at_lbs-bw.de>
Date: Tue, 27 Jun 2006 17:44:01 +0200
Message-ID: <OFE3BB93C1.01CFE7E2-ONC125719A.00564B19-C125719A.00566D75@lbs-bw.de>


Rjamya,

I checked 3329096.8 which exactly described our problem. I'll run segment_fix_status and see if it does anything good.

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

                                                                           
             rjamya                                                        
             <rjamya_at_gmail.com                                             
             >                                                          An 
                                        Markus.Kuehn_at_lbs-bw.de             
             27.06.2006 17:36                                        Kopie 
                                                                           
                                                                     Thema 
                                        Re: RE: Antwort: RE: Problem with  
                                        LMT and ASSM                       
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           




My memory is bad, but there was an issue with bitmaps (as in lmt) not reflecting correct fullness of segments in 9204 and related versions. Could it be that? Metablink had a fix for that check doc id 3329096.8. Now given that it says the issue is fixed in 9206, I'd still check if dbms_repair.segment_fix_status can help.

should be easy to test ... after you purge, get table space info using dbms_space. Run above mentioned code and check the space again. If you find a difference, the bug isn't fixed.

The process is supposed to be non-corruptive. I'd check on a test db first. Raj

On 6/27/06, Markus Kuehn <Markus.Kuehn_at_lbs-bw.de> wrote:
> 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
> <WITTENMYERJ_at_tusc

An
> .com> 'Markus Kuehn'
> <Markus.Kuehn_at_lbs-bw.de>
> 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
> <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.
>
>
>
>
> 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
>
>
>

--
----------------------------------------------
Got RAC?




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
Received on Tue Jun 27 2006 - 10:44:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US