Determine HWM and reduce it by shrink space

After I reviewed "Oracle Database 11g Performance Tuning Recipes A Problem-Solution Approach" book. That helped me interest much about Oracle Database Performance Tuning and what Oracle DBA should know!!! what is "high water mark"? Thank You support.oracle.com

All Oracle segments have an upper boundary containing the data within the segment. This upper boundary is called the "high water mark" or HWM. The high water mark is an indicator that marks blocks that are allocated to a segment, but are not used yet. This high water mark typically bumps up at 5 data blocks at a time.

This post, I just needed to tested about determining HWM and reducing it by shrink space.
Tested INSERT/DELETE data:

declare
i number;
begin
for i in 1..10 loop
execute immediate 'delete from TB_DATA1 where rownum <=100000';
execute immediate 'insert /*+ append (TB_DATA1) */ into TB_DATA1 select * from dba_objects where rownum <= 100000';
commit;
end loop;
end;
/

Determining HWM, You can use *_tables for BLOCKS, EMPTY_BLOCKS - some information for high water mark.
BLOCKS: Number blocks that has been formatted to recieve data
EMPTY_BLOCKS: Among the allocated blocks, the blocks that were never used

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='TB_DATA1';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
3194 6 1000

Tested to gather statistic table:

SQL> exec dbms_stats.gather_table_stats(USER,'TB_DATA1');

PL/SQL procedure successfully completed.

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='TB_DATA1';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
33163 6 74650

The DBMA_STATS didn't keep statistic about EMPTY_BLOCKS.
*** empty_blocks columns needs "analyze table" command***

SQL> analyze table TB_DATA1 compute statistics ;

Table analyzed.

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='TB_DATA1';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
33163 1653 74650

On 11g, You can compare table size with table actual size.
*** block size: 8192, BLOCKS*8192 = table size ***
*** NUM_ROWS*AVG_ROW_LEN = actual size ***

SQL> SELECT TABLE_NAME , (BLOCKS*8192/1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024) "Data lower than HWM in MB" FROM USER_TABLES WHERE table_name='TB_DATA1';

TABLE_NAME Data lower than HWM in MB
------------------------------ -------------------------
TB_DATA1 251.966759

Differed more than 250MB.
Test Query with table, that has differ between table size and table actual size more than 250MB.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> explain plan for select * from TB_DATA1;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4008610712
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74650 | 7290K| 8994 (1)| 00:00:03 |
| 1 | TABLE ACCESS FULL| TB_DATA1 | 74650 | 7290K| 8994 (1)| 00:00:03 |
------------------------------------------------------------------------------

SQL> select count(*) from TB_DATA1;

COUNT(*)
----------
74650

Elapsed: 00:00:00.68

How to reduce HWM?
- ALTER TABLE ... MOVE
- Export/Truncate/Import
- Reorganize Table
- Shrink Space
This post, I needed to use "Shrink Space". Go to SHRINK SPACE

ALTER … SHRINK SPACE [COMPACT][CASCADE]

SQL> ALTER TABLE TB_DATA1 ENABLE ROW MOVEMENT;

Table altered.

SQL> ALTER TABLE TB_DATA1 SHRINK SPACE COMPACT;

Table altered.

From picture "ALTER TABLE TB_DATA1 SHRINK SPACE COMPACT", that not reduce HWM... check it.

SQL> analyze table TB_DATA1 compute statistics ;

Table analyzed.

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='TB_DATA1';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
33163 1653 74650

SQL> SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024) "Data lower than HWM in MB" FROM USER_TABLES WHERE table_name='TB_DATA1';

TABLE_NAME Data lower than HWM in MB
------------------------------ -------------------------
TB_DATA1 251.966759

HWM was not reduced. It's right... then

SQL> ALTER TABLE TB_DATA1 SHRINK SPACE;

Table altered.

SQL> ALTER TABLE TB_DATA1 DISABLE ROW MOVEMENT;

Table altered.

SQL> analyze table TB_DATA1 compute statistics ;

Table analyzed.

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='TB_DATA1';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
1063 33 74650

SQL> SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024) "Data lower than HWM in MB" FROM USER_TABLES WHERE table_name='TB_DATA1';

TABLE_NAME Data lower than HWM in MB
------------------------------ -------------------------
TB_DATA1 1.18550873

Differed 1.2MB, then tested query to check COST and etc...

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> explain plan for select * from TB_DATA1;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4008610712
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74650 | 7290K| 292 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TB_DATA1 | 74650 | 7290K| 292 (1)| 00:00:01 |
------------------------------------------------------------------------------

SQL> select count(*) from TB_DATA1;

COUNT(*)
----------
74650

Elapsed: 00:00:00.09

After, HWM was reduced, query faster than and "Cost (%CPU)" value less than.
then used dbms_stats.gather_table_stats procedure also.

SQL> exec dbms_stats.gather_table_stats(USER,'TB_DATA1');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from TB_DATA1;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4008610712
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74650 | 7071K| 292 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TB_DATA1 | 74650 | 7071K| 292 (1)| 00:00:01 |
------------------------------------------------------------------------------

Shrinking helps to improve the performance of scan and DML operations on that segment.

On Oracle Version 10.2, You know Automatic Segment Advisor. On 11g, You know Automated Segment Advisor. It's feature what is helpful for DBA. You don't need to determine HWM by yourself. You can retrieve information generated by the Segment Advisor.
On "Oracle Database 11g Performance Tuning Recipes" book was also written about Automated Segment Advisor and showed SQL sample to retrieve information generated by the Segment Advisor.

SELECT
'Segment Advice --------------------------'|| chr(10) ||
'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||
'SEGMENT_OWNER : ' || segment_owner || chr(10) ||
'SEGMENT_NAME : ' || segment_name || chr(10) ||
'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||
'RECOMMENDATIONS : ' || recommendations || chr(10) ||
'SOLUTION 1 : ' || c1 || chr(10) ||
'SOLUTION 2 : ' || c2 || chr(10) ||
'SOLUTION 3 : ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))
/