Home » RDBMS Server » Server Administration » How the HWM move? (Oracle 9.2.0.8,AIX 5L)
How the HWM move? [message #351335] Tue, 30 September 2008 06:33 Go to next message
yr0123
Messages: 5
Registered: September 2008
Junior Member
I want to know how the HWM move,so i do a test
1.I create a tablespace xcgl(block size is 8k):
create tablespace xcgl
datafile '/dbf/oradata/orakf/xcgl1.dbf' size 1000M
extent management local uniform size 1m segment space management auto;
2.create table in it
create table test(a number(10));
analyze the table,the blocks in "user_tables" is 0.empty_blocks is 60.
3.insert records,from 1 to 5000,then analyze table test,in "user_tables",blocks is 60,empty_blocks is 68.
4.I insert from 1 to 5000 to the table test repeatly,the blocks in "user_tables" suddenly grows to 124 ,and empty_blocks is 4
5.continue to insert record, the size as flowing:
blocks empty_blocks
0 128
60 68
124 4
186 70
my question is how the HWM(blocks) move in ASSM?
Sorry very much,I am not good at english,i put the post error place,how can i do?

[Updated on: Tue, 30 September 2008 06:40]

Report message to a moderator

Re: How the HWM move? [message #351336 is a reply to message #351335] Tue, 30 September 2008 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
block size is 8k

Quote:
extent management local uniform size 1m

So each extent is 128 blocks.

When an extent is filled Oracle allocates a new one.

Regards
Michel
Re: How the HWM move? [message #351451 is a reply to message #351336] Tue, 30 September 2008 21:48 Go to previous messageGo to next message
yr0123
Messages: 5
Registered: September 2008
Junior Member
thanks,Michel.
but i think than HWM is "blocks" in user_tables,
and my question is why the "blocks" in user_tables grows
from 0 to 60, 124, 186?
60-0=60
124-60=64
186-124=62
does the HWM move about 60 blocks each time?
and why the size is different?

thanks.
Re: How the HWM move? [message #351474 is a reply to message #351451] Wed, 01 October 2008 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but i think than HWM is "blocks" in user_tables,

From doc:
Quote:
BLOCKS* NUMBER Number of used data blocks in the table

Yes, it is (about) HWM which is different from allocated blocks.

Quote:
does the HWM move about 60 blocks each time?

No, it depends on what you insert.

Regards
Michel
Re: How the HWM move? [message #352472 is a reply to message #351335] Tue, 07 October 2008 22:04 Go to previous messageGo to next message
yr0123
Messages: 5
Registered: September 2008
Junior Member
hello! Michel.
Maybe the situation I describled is not clear,here is an exmple:
1.create a new table
create table test(a number(10));
now the blocks in "dba_tables" is 0
2.insert 5000(for example,from 1 to 5000) records into the table
now the blocks in "dba_tables" is 60
3.insert 5000 records again into the table,now the table has 10,000 records,
now the bocks in "dba_tables" is the same:60,
my question is why blocks doesnot increase here?
4. insert 5000 records again,now the table has 15,000 records,
the blocks is also 60
5. insert 5000 records again,now the table has 20,000 records
it suddenly grows to 124.

why blocks in dba_tables does not increase in step 3,4,and suddenly grows to 124 in step 5?
Re: How the HWM move? [message #352494 is a reply to message #352472] Wed, 08 October 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why blocks in dba_tables does not increase in step 3,4,and suddenly grows to 124 in step 5?

Because in step 3 and 4, Oracle does not need to use new blocks to insert these new rows and in step 5 there was not enough space to insert the new rows, so it allocates a new extent.

Regards
Michel
Re: How the HWM move? [message #352661 is a reply to message #351335] Wed, 08 October 2008 20:18 Go to previous messageGo to next message
yr0123
Messages: 5
Registered: September 2008
Junior Member
In step 3 and 4,why the HWM does't move?
I think the HWM should move after each 5000 ercords insert complete.
Re: How the HWM move? [message #352691 is a reply to message #352661] Thu, 09 October 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In step 3 and 4,why the HWM does't move?

No because there is space in the current used blocks.

Quote:
I think the HWM should move after each 5000 ercords insert complete.

It is not mandatory, depends on the available space in the current used blocks.

Regards
Michel
Re: How the HWM move? [message #352725 is a reply to message #351335] Thu, 09 October 2008 03:45 Go to previous message
yr0123
Messages: 5
Registered: September 2008
Junior Member
Thank you very much for your patient answer. Laughing
Previous Topic: jumping sequences
Next Topic: undo_retention
Goto Forum:
  


Current Time: Thu Dec 08 14:12:12 CST 2016

Total time taken to generate the page: 0.06298 seconds