Analysis of HWM and swot analysis

Analysis of HWM and swot analysis
The following describes HWM in combination with official documents and experiments:
The following is an English Excerpt from the 11gR2 official document:
HWM (high water mark): The boundary between used and unused space in a segment.
After ORACLE9i, the automatic segment space management (ASSM) is used. It uses bitmap to manage the usage of the segment space. If the tablespace is ASSM, the segment in the tablespace is also ASSM.

At table creation, the HWM is at the beginning of the segment on the left. Because no data has been inserted yet, all blocks in the segment are unformatted and never used.
When a table is created, HWM starts at the leftmost of the segment. Because data has never been inserted, all blocks in the segments are not formatted and used.

Suppose that a transaction inserts rows into the segment. the database must allocate a group of blocks to hold the rows. the allocated blocks fall below the HWM. the database formats a bitmap block in this group to hold the metadata, but does not preformat the remaining blocks in the group. in Figure 12-24, the blocks below the HWM are allocated, whereas blocks above the HWM are neither allocated or formatted. as inserts occur, the database can write to any block with available space. the low high water mark (low HWM) marks the point below which all blocks are known to be formatted because they either contain data or formerly contained data.
Assume that a transaction inserts rows into segments. The database must allocate a group of rows. Smaller than the block allocated by HWM. Metadata of Bitmap blocks in this database format, but the remaining blocks are not in advance. In Figure 12-24, the following block HWM is allocated, while the block HWM is neither allocated nor formatted. During insertion, the database can write any block space. High watermark low (low HWM) indicates that all the blocks under the dot are formatted because they either contain data or previously contain data.

In Figure 12-25, the database chooses a block between the HWM and low HWM and writes to it. the database cocould have just as easily chosen any other block between the HWM and low HWM, or any block below the low HWM that had available space. in Figure 12-25, the blocks to either side of the newly filled block are unformatted.
In Figure 12-25, the database selects a piece between HWM and low HWM and writes. Databases can also easily choose between HWM and low HWM among other blocks, or any block is lower than the low HWM available space. In Figure 12-25, the new filling block of the block has no format.



The low HWM is important in a full table scan. because blocks below the HWM arformatted only when used, some blocks cocould be unformatted, as in Figure 12-25. fthis reason, the database reads the bitmap block to obtain the location of the low HWM. the database reads all blocks up to the low HWM because they are known tbe formatted, and then carefully reads only the formatted blocks between the low HWM and the HWM. assume that a new transaction inserts rows into the table, but the bitmap indicates that insufficient free space exists under the HWM. in Figure 12-26, the database advances the HWM to the right, allocating a new group of unformatted blocks.
Low HWM is important in a full table scan. Because the block is lower than the HWM format only when it is used based on the "Augmented Reality" technology, some blocks can have no format, as shown in 12-25. F. Therefore, the database reads bitmap blocks to obtain low-position HWM. The database reads the low HWM of all blocks because they are known to be formatted, and then carefully reads only the low HWM between formatting blocks. Assume that a new transaction inserts rows into the table, but the bitmap shows that there is insufficient HWM free space. Figure 12-26 The HWM progress on the right of the database, allocating a new group of unformatted blocks.


When the blocks between the HWM and low HWM are full, the HWM advances to the right and the low HWM advances to the location of the old HWM. as the database inserts data over time, the HWM continues to advance to the right, with the low HWM always trailing behind it. unless you manually rebuild, truncate, or shrink the object, the HWM never retreats.
When the block and low HWM between HWM is full, the HWM progress is correct and the position of the low old HWM progress. As the database inserts data over time, the hwm on the right keeps improving, and the low HWM always lags behind. Unless you manually recreate, truncate, or contract the object, the HWM will never retreat.

The above has analyzed HWM through the theory of the official document, and we will analyze it through experiments below:

[Oracle @ localhost ~] $ Cat/etc/redhat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)

SQL> select * from v $ version where rownum = 1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production


SQL> show user;
The USER is "HR"
SQL> desc t;
Is the name empty? Type
-----------------------------------------------------------------------------
Id number (38)
NAME VARCHAR2 (10)


SQL> select count (*) from t;


COUNT (*)
----------
327680

T is a large table in the user's hr.
Index index_t on the column id: (useful below)
SQL> create index index_t on t (id );


The index has been created.

Analysis table:
SQL> exec dbms_stats.gather_table_stats ('hr', 'T'); oracle provides this analysis package

The PL/SQL process is successfully completed.
Of course, you can also use the tools provided in earlier versions:
SQL> analyze table t1 compute statistics;

The table has been analyzed.
Delete analysis: SQL> analyze table t1 delete statistics;

The table has been analyzed.



Total Blocks indicates the Total number of blocks allocated to the table. Unused Blocks indicates the number of Unused data Blocks located above the high watermark.
The above parameters are not described in detail. If you are interested, you can study them.

Analyze the execution plan:
SQL> set autotrace traceonly;
SQL> select * from t; (the second result is executed twice)


Row 327680 has been selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 327K | 2240K | 172 (2) | 00:00:03 |
| 1 | table access full | T | 327K | 2240K | 172 (2) | 00:00:03 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
22418 consistent gets
0 physical reads
0 redo size
6379306 bytes sent via SQL * Net to client
240710 bytes encoded ed via SQL * Net from client
21847 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
327680 rows processed
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

As shown above, the logic reads 22418 times.

We delete table t at this time, but it does not reduce HWM:



Although the deleted part of table t is deleted, the occupied part of the internal space is still not released. You can use alter table t move to release the bucket:
Alter table move has two main functions: 1. It is used to move a table to other tablespaces. 2. It is used to reduce storage fragments in the table and optimize the storage space and performance.
SQL> alter table t move (tablespace users) online; (at this time, other sessions can access this table normally)
The table has been changed.
The rowid of rows also changes.
SQL> select * from t; (the second result is executed twice)


Row 131072 has been selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 327K | 2240K | 172 (2) | 00:00:03 |
| 1 | table access full | T | 327K | 2240K | 172 (2) | 00:00:03 |
--------------------------------------------------------------------------




Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
9274 consistent gets
0 physical reads
0 redo size
4020151 bytes sent via SQL * Net to client
96533 bytes encoded ed via SQL * Net from client
8740 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
131072 rows processed
At this time, the logical read has been reduced to 9274.
However, the index created on the id is unavailable:

At this point, we can solve the problem by re-indexing:
Alter index index_t rebuild (tablespace users) online; (if online is not added, delete, update, and insert operations cannot be performed, which is very slow for large tables)
SQL> alter index index_t rebuild online;


The index has been changed.
Re-indexing: