RE: Problem with LMT and ASSM
- From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
- Date: Tue, 27 Jun 2006 13:19:26 -0500
Hmmmmm...on 9.2.0.5.0 on HPUX, I'm not able to reproduce this using a procedure
similar to BUG 2917432, which is related to the MetaLink note you mentioned:
CREATE TABLESPACE R2TESTTS DATAFILE
'r2testts_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
DROP TABLE TEST;
CREATE TABLE TEST(c NUMBER) TABLESPACE R2TESTTS;
BEGIN
FOR b IN 1..41 LOOP
FOR i IN 1..324 LOOP
INSERT INTO TEST VALUES(i);
END LOOP;
END LOOP;
COMMIT;
END;
/
variable ub NUMBER;
variable uby NUMBER;
variable fs1 NUMBER;
variable fs1by NUMBER;
variable fs2 NUMBER;
variable fs2by NUMBER;
variable fs3 NUMBER;
variable fs3by NUMBER;
variable fs4 NUMBER;
variable fs4by NUMBER;
variable fb NUMBER ;
variable fby NUMBER;
EXECUTE dbms_space.space_usage(SEGMENT_OWNER => 'R2_DBA', -
SEGMENT_NAME => 'TEST', -
SEGMENT_TYPE => 'TABLE', -
UNFORMATTED_BLOCKS => :ub, -
UNFORMATTED_BYTES => :uby, -
FS1_BLOCKS => :fs1, -
FS1_BYTES => :fs1by, -
FS2_BLOCKS => :fs2, -
FS2_BYTES => :fs2by, -
FS3_BLOCKS => :fs3, -
FS3_BYTES => :fs3by, -
FS4_BLOCKS => :fs4, -
FS4_BYTES => :fs4by, -
FULL_BLOCKS => :fb, -
FULL_BYTES => :fby);
print ub;
print fs1;
print fs2;
print fs3;
print fs4;
print fb;
SELECT 'Count: '||COUNT(*) FROM TEST;
DELETE TEST WHERE c< 300;
COMMIT;
EXECUTE dbms_space.space_usage(SEGMENT_OWNER => 'R2_DBA', -
SEGMENT_NAME => 'TEST', -
SEGMENT_TYPE => 'TABLE', -
UNFORMATTED_BLOCKS => :ub, -
UNFORMATTED_BYTES => :uby, -
FS1_BLOCKS => :fs1, -
FS1_BYTES => :fs1by, -
FS2_BLOCKS => :fs2, -
FS2_BYTES => :fs2by, -
FS3_BLOCKS => :fs3, -
FS3_BYTES => :fs3by, -
FS4_BLOCKS => :fs4, -
FS4_BYTES => :fs4by, -
FULL_BLOCKS => :fb, -
FULL_BYTES => :fby);
print ub;
print fs1;
print fs2;
print fs3;
print fs4;
print fb;
SELECT 'COUNT: '||COUNT(*) FROM TEST;
--
The final output for me is all zeros, except the 75-100% FS$, which is "28" --
exactly what one would expect.
I'm happy that I don't see the problem, but I'm confused as to "why"....
Rich
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Markus Kuehn
Sent: Tuesday, June 27, 2006 10:44 AM
To: rjamya
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Antwort: Re: RE: Antwort: RE: Problem with LMT and ASSM
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@xxxxxxxxx
Amtsgericht Stuttgart HRA 12924
Amtsgericht Karlsruhe HRA 4548
rjamya
<rjamya@xxxxxxxxx
> An
Markus.Kuehn@xxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxx
>
> Amtsgericht Stuttgart HRA 12924
> Amtsgericht Karlsruhe HRA 4548
>
>
>
> Wittenmyer Joel -
> CO
> <WITTENMYERJ@tusc
An
> .com> 'Markus Kuehn'
> <Markus.Kuehn@xxxxxxxxx>
> 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@xxxxxxxxx]
> Sent: Tuesday, June 27, 2006 9:35 AM
> To: Wittenmyer Joel - CO
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxx
>
> Amtsgericht Stuttgart HRA 12924
> Amtsgericht Karlsruhe HRA 4548
>
>
>
> Wittenmyer Joel -
> CO
> <WITTENMYERJ@tusc
An
> .com> "'salem.ghassan@xxxxxxxxx'"
> <salem.ghassan@xxxxxxxxx>,
> 27.06.2006 16:28 Markus.Kuehn@xxxxxxxxx
>
Kopie
> oracle-l@xxxxxxxxxxxxx
>
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Ghassan Salem
> Sent: Tuesday, June 27, 2006 9:26 AM
> To: Markus.Kuehn@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxx> 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@xxxxxxxxx
>
> 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.
>
> --
> //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.
>
> --
> //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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
