Home » RDBMS Server » Server Administration » High watermark identyfying
High watermark identyfying [message #205340] Fri, 24 November 2006 05:24 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I have a large table with around 10000000 records.

Becuase of some delete the high-watermark has not reduced, and as a reason the table size is showing very big
(initial_extent+(next_extent*extents)).

How can I know whehter the high watermark is not reduced to do a alter table move/export&import.

Brian.
Re: High watermark identyfying [message #205432 is a reply to message #205340] Fri, 24 November 2006 13:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
A little search will always help Smile
http://orafaq.com/faq/where_can_one_find_the_high_water_mark_for_a_table
http://www.orafaq.com/forum/m/150823/42800/?srch=dbms_space#msg_150807
Re: High watermark identyfying [message #205451 is a reply to message #205340] Fri, 24 November 2006 23:59 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
I have a doubt,

I'm finding differece in BLOCKS value in dba_tables and dba_segemnts.

I have already done ANALYZE TABLE table1 ESTIMATE STATISTICS;

Brian
Re: High watermark identyfying [message #205455 is a reply to message #205451] Sat, 25 November 2006 00:16 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi

SQL> exec dbms_stats.gather_schema_stats ( 'HR');

PL/SQL procedure successfully completed.

SQL> column segment_name format a25
SQL> select s.segment_name , s.blocks,t.blocks
  2  from dba_segments s , dba_tables t
  3  where s.segment_name = t.table_name
  4  and s.owner = 'HR';

SEGMENT_NAME                  BLOCKS     BLOCKS
------------------------- ---------- ----------
REGIONS                            8          5
LOCATIONS                          8          5
DEPARTMENTS                        8          5
JOBS                               8          5
EMPLOYEES                          8          5
JOB_HISTORY                        8          5
BIG_TABLE                       2816       2764
TAB1                               8          5
TAB2                               8          5
T                                  8          5

10 rows selected.


dba_segments.blocks= Size, in Oracle blocks, of the segment
dba_tables.blocks =
Number of used data blocks in the table


hope this helps
Taj
Re: High watermark identyfying [message #205461 is a reply to message #205340] Sat, 25 November 2006 00:53 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Taj,

Is that means dba_segments.blocks - dba_tables.blocks are the empty blocks because of delte/udate?

Brian.
Re: High watermark identyfying [message #205466 is a reply to message #205461] Sat, 25 November 2006 01:19 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi ,

Yes but no exactly...
bcoz time size is growing...
it is not total size of table.

hope this helps
Taj
Re: High watermark identyfying [message #205485 is a reply to message #205340] Sat, 25 November 2006 02:44 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

In my one of table <table1>,

dba_segments.blocks = 5244870
dba_tables.blocks = 4537586
dba_table.empty_blocks = 706211

Suppose, If I do "alter table table1 move" or export/import will the dba_tables.blocks becomes 0.

Brian.
Re: High watermark identyfying [message #205509 is a reply to message #205485] Sat, 25 November 2006 06:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
My second URL talks about this.
Did you care to read it?
Re: High watermark identyfying [message #205517 is a reply to message #205485] Sat, 25 November 2006 06:21 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi
In my one of table <table1>,

dba_segments.blocks = 5244870
dba_tables.blocks = 4537586
dba_table.empty_blocks = 706211


go through below link...


http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:669044239081




Suppose, If I do "alter table table1 move" or export/import will the dba_tables.blocks becomes 0

NO,


SQL> exec dbms_stats.gather_table_stats('HR','BIG_TABLE');

PL/SQL procedure successfully completed.

SQL> SELECT BLOCKS, EMPTY_BLOCKS ,NUM_ROWS FROM DBA_TABLES
  2  WHERE TABLE_NAME = 'BIG_TABLE' AND OWNER='HR';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      5284            8     381096


SQL> SELECT BLOCKS FROM DBA_segments
  2  WHERE segment_NAME = 'BIG_TABLE' AND OWNER='HR';

    BLOCKS
----------
      5376


SQL> delete hr.big_table where rownum <= 100000;

100000 rows deleted.

SQL> commit;

Commit complete.

SQL> SELECT BLOCKS FROM DBA_segments
  2  WHERE segment_NAME = 'BIG_TABLE' AND OWNER='HR';

    BLOCKS
----------
      5376

SQL> SELECT BLOCKS, EMPTY_BLOCKS ,NUM_ROWS FROM DBA_TABLES
  2  WHERE TABLE_NAME = 'BIG_TABLE' AND OWNER='HR';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      5284            8     381096

Note : bcoz delete never reset HVM. you have to used import or export / alter table ... move option for reset HVM.

SQL> host exp hr/hr tables=big_table

Export: Release 10.1.0.2.0 - Production on Sat Nov 25 16:01:49 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                      BIG_TABLE     281808 rows exported
Export terminated successfully without warnings.

SQL> drop table hr.big_table purge;

Table dropped.


SQL> host imp hr/hr tables=big_table

Import: Release 10.1.0.2.0 - Production on Sat Nov 25 16:02:43 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing HR's objects into HR
. . importing table                    "BIG_TABLE"     281808 rows imported
Import terminated successfully without warnings.

SQL> exec dbms_stats.gather_table_stats('HR','BIG_TABLE');

PL/SQL procedure successfully completed.

SQL> SELECT BLOCKS, EMPTY_BLOCKS ,NUM_ROWS FROM DBA_TABLES
  2  WHERE TABLE_NAME = 'BIG_TABLE' AND OWNER='HR';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      3898            0     281808


SQL> SELECT BLOCKS  FROM DBA_segments
  2  WHERE segment_NAME = 'BIG_TABLE' AND OWNER='HR';

    BLOCKS
----------
      3968

SQL>




hope this helps
Taj
Re: High watermark identyfying [message #205667 is a reply to message #205340] Mon, 27 November 2006 00:49 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Thanks very much Taj.

In my case, I've already analysed the table and the
dba_table.empty_blocks = 706211

So, I need to reorganize the table.

Thanks
Brian.
Re: High watermark identyfying [message #205705 is a reply to message #205340] Mon, 27 November 2006 03:23 Go to previous message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
Hey if you have a table with 10.000.000 rows,
and you delete lets say 1.000.000.

Then your highwatermark will still be at 10.000.000, and as you know when you perform a full table scan, the scan will always run until it reaches the HWM.

So in case of a large delete it is always recommended that you reorg your table.

Kr
Karel.
Previous Topic: checkpoint time
Next Topic: Listener do not work
Goto Forum:
  


Current Time: Sun Dec 04 00:37:05 CST 2016

Total time taken to generate the page: 0.12227 seconds