This forum is currently locked. You can't register or post questions at this time. (read more)

script - high_water_mark.sql

All posts relating to Oracle database administration.

Moderator: Tim...

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

script - high_water_mark.sql

Postby peterx » Sat Oct 05, 2013 9:56 am

hi Tim,

I want to ask a questions about your script - http://www.oracle-base.com/dba/script.p ... r_mark.sql

I have a test case:

Code: Select all

SQL> @high_water_mark_of_table all time_trace
TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK
------------------------------  ---------------  ---------------  ---------------
TEST_TABLE                                  0             80             79

PL/SQL procedure successfully completed.


SQL> delete from time_trace.TEST_TABLE;

10000 rows deleted.

SQL> @high_water_mark_of_table all time_trace
TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK
------------------------------  ---------------  ---------------  ---------------
TEST_TABLE                                  0             80             79

PL/SQL procedure successfully completed.

SQL> truncate table time_trace.TEST_TABLE;

Table truncated.

SQL> @high_water_mark_of_table all time_trace
TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK
------------------------------  ---------------  ---------------  ---------------
TEST_TABLE                                  5              8              2

PL/SQL procedure successfully completed.

SQL>



I want to ask is what value of the figure can show the problem table ?

It only can monitor the table water mark if the table is empty (water mark should tends to zero) ?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: script - high_water_mark.sql

Postby Tim... » Sat Oct 05, 2013 10:28 am

Hi.

I think you are mistaken about what the high water mark is. When you add data to the table, new extents are added to the table, those extents being made up of blocks. The HWM indicates the maximum size number of blocks that have been used. If you delete from the table, even if you delete all the rows, the HWM is not changed. So even if you have loads of empty blocks, the HWM remains the same. This is an issue as as full table scan (FTS) reads all blocks up to the HWM, so it is potentially reading lots of empty blocks.

Resetting the HWM will reduce the impact of empty blocks on FTS. How can you do this:

- Truncate the table and reload it.
- MOVE the table.
- SHRINK the table.

The script is only there to report the HWM. The script on it's owner does not allow you to make a judgement on the nature of the HWM.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: script - high_water_mark.sql

Postby peterx » Sat Oct 05, 2013 1:26 pm

hi Tim,

very thanks for your reply and explain.

I do know the concept of:

even if you delete all the rows, the HWM is not changed. So even if you have loads of empty blocks, the HWM remains the same. This is an issue as as full table scan (FTS) reads all blocks up to the HWM, so it is potentially reading lots of empty blocks.

And this make the performance issue for scanning many empty blocks to read a few data!

The problem of me is, from the figure I can't tell the table is in a good or bad "HWM".

Maybe
The script is only there to report the HWM. The script on it's owner does not allow you to make a judgement on the nature of the HWM.

your words answered my question!

Or can I make a conclusion that if your script tell "UNUSED BLOCKS" is very high then we should see
the HWM has problem or not ?

thanks.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: script - high_water_mark.sql

Postby Tim... » Sat Oct 05, 2013 2:35 pm

Hi.

Not really. What if you have lots of blocks with 1 row in. Then the space usage in the table is still very bad...

If you are concerned about this, you should really be using the segment advisor to determine if a shrink would be beneficial.

http://www.oracle-base.com/articles/10g ... nt_advisor

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 7 guests