How to interpret this?
Marcio, June 01, 2004 - 11:36 pm UTC
This was a question from Prometric Simulate Test
A table is created as follows:
CREATE TABLE MY_TABLE (COL1 NUMBER) STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6
PCTINCREASE 0);
When you issue the following statement, what will be the size of the table,
if the high-water mark of the table is 200KB?
ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;
A. 1000KB
B. 200KB
C. 12000KB
D. 2MB
E. 13MB
The question is what would be the size of the table?
As simple as set up a test suppose, but you can interpret what is "if the high-water mark of the table is 200KB?"
Could you explain me?
Assumption:
Odddddddeeeeeeeeeeeeeeeeeeee.. 2m ext, minextents 6, d=12mb, if hwm is 200kb I suppose that was never touched -- so, allocate unused KEEP N will ajust initial to me.
And the answer to me is a) 1000k -- what is wrong accord simulate answer said c) 12mb as the table has been flooded -- that's why would like to interpret "if the hwm of the table is 200kb" is.
ops$marcio@MRP10G> CREATE TABLE MY_TABLE (COL1 NUMBER) STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6
2 PCTINCREASE 0);
Table created.
ops$marcio@MRP10G> exec show_space('MY_TABLE')
Total Blocks............................1536
Total Bytes.............................12582912
Unused Blocks...........................1535
Unused Bytes............................12574720
PL/SQL procedure successfully completed.
ops$marcio@MRP10G> ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;
Table altered.
ops$marcio@MRP10G> exec show_space('MY_TABLE')
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................127
Unused Bytes............................1040384
PL/SQL procedure successfully completed.
June 02, 2004 - 8:15 am UTC
what a stupid question eh?
my answer is
f) you have given totally insufficient data to even begin answering the question
the reason (F) is the only correct answer is because I'll have created that table in a locally managed tablespace, with uniform extents. The answer set is therefore "infinite" and not knowable by the person asking the question :)
OK
Marcio, June 02, 2004 - 9:57 am UTC
Well, this kind of question evaluate the new OCP (Oracle Certified Professional ;)
But, going to further -- suppose you have a dmt tablespace, which will be your answer and why? -- Please consider (f) as well ;)
A table is created as follows:
CREATE TABLE MY_TABLE (COL1 NUMBER) STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6
PCTINCREASE 0);
When you issue the following statement, what will be the size of the table,
if the high-water mark of the table is 200KB?
ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;
A. 1000KB
B. 200KB
C. 12000KB
D. 2MB
E. 13MB
Thanks
June 02, 2004 - 11:18 am UTC
answer is definitely "none of the above choices"
ops$tkyte@ORA817DEV> create tablespace silly datafile '/tmp/silly.dbf' size 25m reuse;
Tablespace created.
ops$tkyte@ORA817DEV> create table my_table( col1 number ) tablespace silly
2 storage ( initial 2m next 2m minextents 6 pctincrease 0 );
Table created.
ops$tkyte@ORA817DEV> insert into my_table values ( 1 );
1 row created.
ops$tkyte@ORA817DEV> insert into my_table values ( 2 );
1 row created.
ops$tkyte@ORA817DEV> alter table my_table minimize records_per_block;
Table altered.
ops$tkyte@ORA817DEV> insert into my_table select rownum from all_objects where rownum <= 48;
48 rows created.
ops$tkyte@ORA817DEV> analyze table my_table compute statistics;
Table analyzed.
ops$tkyte@ORA817DEV> select blocks, empty_blocks from user_tables where table_name = 'MY_TABLE';
BLOCKS EMPTY_BLOCKS
---------- ------------
25 1534
1 row selected.
<b>HWM is absolutely 200k</b>
ops$tkyte@ORA817DEV> select sum(bytes)/1024 from user_extents where segment_name = 'MY_TABLE';
SUM(BYTES)/1024
---------------
12480
1 row selected.
<b>we have 12m allocated...</b>
ops$tkyte@ORA817DEV> select extent_id, blocks from user_extents where segment_name = 'MY_TABLE';
EXTENT_ID BLOCKS
---------- ----------
0 260
1 260
2 260
3 260
4 260
5 260
6 rows selected.
<b>in 6 extents...</b>
ops$tkyte@ORA817DEV> ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;
Table altered.
ops$tkyte@ORA817DEV> analyze table my_table compute statistics;
Table analyzed.
ops$tkyte@ORA817DEV> select blocks, empty_blocks from user_tables where table_name = 'MY_TABLE';
BLOCKS EMPTY_BLOCKS
---------- ------------
25 125
1 row selected.
<b>HWM is still 200k -- but we have lots less blocks</b>
ops$tkyte@ORA817DEV> select sum(bytes)/1024 from user_extents where segment_name = 'MY_TABLE';
SUM(BYTES)/1024
---------------
1208
1 row selected.
<b>the answer it 1208Kb -- not in that list - perhaps the 12000kb is a typo, but still "wrongish"</b>
ops$tkyte@ORA817DEV> select extent_id, blocks from user_extents where segment_name = 'MY_TABLE';
EXTENT_ID BLOCKS
---------- ----------
0 151
1 row selected.
Too bad explanations
A reader, June 02, 2004 - 1:01 pm UTC
Tom,
You need to answer the questions to the point.
June 02, 2004 - 1:32 pm UTC
I need to do whatever i want maybe. If you don't like the answer, you don't really have to read them?
The point was "silly question".
The answer is, was, will be "none of the above".
The point is, as asked the question "could not be answered", period.
The point is, as refined, the question doens't have an answer in that list and the point was made.
re: Too bad explanations
Marcio, June 02, 2004 - 1:52 pm UTC
Sorry "a reader" allow me as poster to follow up, to accept the answer... and it is the only valid answer I've found.
Just set it up and you see (create table, fill it until 200k and alter table)...
I had not understood "200k hwm part", but it was explained as well.
This question have (am sure) a typo on list's answers, and such Tom's sample proved it -- 1208k table size.
reducing high water mark with out truncating
Prasanth PV, July 28, 2006 - 8:26 am UTC
Hi Tom,
Is there any method to lower the HWM without doing the truncate table operation.
Thanks And Regards
Prasanth PV.
July 28, 2006 - 8:00 pm UTC
in 10g, sure.
using the alter table T shrink space compact and shrink space commands.
that would be an online operation.
in 9i and before, alter table T move; would do it - but that is "offline" (table is not useable while you do this) and would require rebuilds of indexes as well.
row chaining
Prasanth.pv, August 11, 2006 - 1:07 am UTC
Hi Tom,
Whether row chaining will reduce the performace of insert?
Regards,
Prasanth.PV
August 11, 2006 - 10:39 am UTC
ask in one place please. so go look in the other place you cut and pasted this same text.
how to find table HWM if...
Peter Larsen, March 13, 2007 - 6:33 am UTC
Hi
Years ago when I was still working with ANALYZE command if was pretty easy to find those tables whose HWM is high and the table is quite empty.
It was done with
BLOCKS
EMPTY_BLOCKS
NUM_ROWS * AVG_ROW_LEN
if NUM_ROWS * AVG_ROW_LEN is much smaller than BLOCKS then the HWM is high and we probably need to rebuild the table.
Can we still apply the same logic with DBMS_STATS?
March 13, 2007 - 11:29 am UTC
sure, it populates those
A reader, August 17, 2008 - 7:31 pm UTC
Very good explanation Tom.
High water mark
Vithal, March 24, 2009 - 3:30 pm UTC
Hi Tom,
I have table with 1 column with number format and 100 rows. this table is cosuming 160 GB space. when I have asked to DBA's they have told that its highwatermark issue but how can i find what is the high watermark for this table? is there any stadered query for this?
March 29, 2009 - 10:57 am UTC
you know the high water mark, it is 160gb. You told us how big the table is. The high water mark of a table is simply the largest size it was at some point.
If you only have 100 rows and it is 160gb, it would sort of make sense to fix it.
assuming you are using locally managed tablespaces:
ops$tkyte%ORA11GR1> alter table t1 move storage ( initial 1k next 1k minextents 1 pctincrease 0 );
Table altered.
would shrink it right down. (rebuild all indexes on T1 after this) If you have 10g and are using ASSM (automatic segment space management), you could alternatively:
ops$tkyte%ORA11GR1> alter table t1 enable row movement;
Table altered.
ops$tkyte%ORA11GR1> alter table t1 shrink space compact;
Table altered.
ops$tkyte%ORA11GR1> alter table t1 shrink space
2 /
Table altered.
that will shrink the space down to nothing as well.