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:
declareDetermining HWM, You can use *_tables for BLOCKS, EMPTY_BLOCKS - some information for high water mark.
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;
/
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';Tested to gather statistic table:
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
3194 6 1000
SQL> exec dbms_stats.gather_table_stats(USER,'TB_DATA1');The DBMA_STATS didn't keep statistic about EMPTY_BLOCKS.
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
*** empty_blocks columns needs "analyze table" command***
SQL> analyze table TB_DATA1 compute statistics ;On 11g, You can compare table size with table actual size.
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
*** 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';Differed more than 250MB.
TABLE_NAME Data lower than HWM in MB
------------------------------ -------------------------
TB_DATA1 251.966759
Test Query with table, that has differ between table size and table actual size more than 250MB.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;How to reduce HWM?
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
- 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;From picture "ALTER TABLE TB_DATA1 SHRINK SPACE COMPACT", that not reduce HWM... check it.
Table altered.
SQL> ALTER TABLE TB_DATA1 SHRINK SPACE COMPACT;
Table altered.
SQL> analyze table TB_DATA1 compute statistics ;HWM was not reduced. It's right... then
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
SQL> ALTER TABLE TB_DATA1 SHRINK SPACE;Differed 1.2MB, then tested query to check COST and etc...
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
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;After, HWM was reduced, query faster than and "Cost (%CPU)" value less than.
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
then used dbms_stats.gather_table_stats procedure also.
SQL> exec dbms_stats.gather_table_stats(USER,'TB_DATA1');Shrinking helps to improve the performance of scan and DML operations on that segment.
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 |
------------------------------------------------------------------------------
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 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'))
/