Database Reference
In-Depth Information
hIGh WaterMarK
The
high watermark
is the boundary between used and unused space in a segment. The used blocks are below
the high watermark, and therefore, the unused blocks are above the high watermark. blocks above the high
watermark have never been used or initialized.
in normal circumstances, operations requiring space (for example,
INSERT
statements) increase the high
watermark only if there is no more free space available below the high watermark. A common exception to this is
due to direct-path inserts because they exclusively use blocks above the high watermark (refer to Chapter 15).
Operations releasing space (for example,
DELETE
statements) don't decrease the high watermark. They simply
make space available to other operations. if the free space is released at a rate equal to or lower than the rate
the space is reused, the use of the blocks below the high watermark should be optimal. Otherwise, the free space
below the high watermark would increase steadily. Long-term, this would cause not only an unnecessary increase
in the size of the segment but also suboptimal performance. in fact, full scans access all blocks below the high
watermark. This occurs even if they're empty. The segment should be reorganized to solve such a problem.
Column Statistics
The following query shows how to get the most important column statistics for a table:
SQL> SELECT column_name AS "NAME",
2 num_distinct AS "#DST",
3 low_value,
4 high_value,
5 density AS "DENS",
6 num_nulls AS "#NULL",
7 avg_col_len AS "AVGLEN",
8 histogram,
9 num_buckets AS "#BKT"
10 FROM user_tab_col_statistics
11 WHERE table_name = 'T';
NAME #DST LOW_VALUE HIGH_VALUE DENS #NULL AVGLEN HISTOGRAM #BKT
---- ----- ------------------- ------------------- ------- ----- ------ --------------- -----
ID 1000 C102 C20B .00100 0 4 NONE 1
VAL1 22 C128 C140 .03884 0 3 HYBRID 15
VAL2 6 C20202 C20207 .00050 0 4 FREQUENCY 6
VAL3 6 C20202 C20207 .00050 0 4 TOP-FREQUENCY 5
PAD 1000 202623436F294373342 7E79514A202D4946493 .00100 0 251 HYBRID 254
37B426574336E4A5B30 66C744E253F36264C69
2E4F4B53236932303A2 27557A57737C6D4B225
1215F462B7667457032 9414C442D2544364130
694174782F7749393B6 612F5B3447405A4E714
5735646366D20736939 A403B6237592B3D7B67
335D712B233B3F 7D4D594E766B57
