List Database Peak Usage with DBA_HIGH_WATER_MARK_STATISTICS

calendar Oct 30, 2026 / · 9 min read · Database Usage dba_high_water_mark_statistics dba_feature_usage_statistics  ·
Share on: linkedin copy

List Database Peak Usage with DBA_HIGH_WATER_MARK_STATISTICS

Purpose

DBA_HIGH_WATER_MARK_STATISTICS stores the peak value of a set of counters for database size and load. It also stores the value seen at the last sample. Each row is one statistic. Two examples are the maximum number of concurrent sessions (SESSIONS) and the maximum size of the database in bytes (DB_SIZE).

Each row also carries the database version in which the mark was tracked. This means one statistic name can have rows for more than one version. The script returns one row per statistic name, taken from the newest version. Each row shows the high-water value and the last value.

A DBA runs it to read the largest size the database has reached and the most concurrent sessions it has seen.

Code

 1COLUMN name  FORMAT A40
 2COLUMN highwater FORMAT 999999999999
 3COLUMN last_value FORMAT 999999999999
 4SET PAGESIZE 24
 5
 6SELECT hwm1.name,
 7       hwm1.highwater,
 8       hwm1.last_value
 9FROM   dba_high_water_mark_statistics hwm1
10WHERE  hwm1.version = (SELECT MAX(hwm2.version)
11                       FROM   dba_high_water_mark_statistics hwm2
12                       WHERE  hwm2.name = hwm1.name)
13ORDER BY hwm1.name;
14
15COLUMN FORMAT DEFAULT

Code Breakdown

  • COLUMN name FORMAT A40 makes the statistic name column 40 characters wide.
  • COLUMN highwater FORMAT 999999999999 and COLUMN last_value FORMAT 999999999999 give each number column 12 digits. That fits counts of sessions, files and tables. DB_SIZE and SEGMENT_SIZE are stored in bytes, and a value of 10^12 bytes (about 931 GiB) or more has 13 digits. When a value does not fit, SQL*Plus displays pound signs (#) in place of the number. The fix is in "Widen the format for byte statistics".
  • The COLUMN commands use the bare names highwater and last_value, but the query selects hwm1.highwater and hwm1.last_value. The formats still apply. hwm1. is a table prefix (an alias), and COLUMN ignores table name prefixes in SELECT commands.
  • SET PAGESIZE 24 sets the output page size. The script does not set it back at the end.
  • SELECT hwm1.name, hwm1.highwater, hwm1.last_value returns three values for each row: the statistic name, the highest value of the statistic seen at sampling time, and the value at the last sample time. The script does not select DBID, VERSION or DESCRIPTION.
  • WHERE hwm1.version = (SELECT MAX(hwm2.version) ...) is a correlated subquery. For each row in the outer query, it finds the highest VERSION stored for the same NAME. Only rows at that version are kept, so older rows from before an upgrade drop out.
  • VERSION is a VARCHAR2(17) column. MAX on a text column compares characters from left to right. Text order matches release order only while every version string starts with a release number of the same length. Check the stored VERSION values once on your own system before you trust the order (see Troubleshooting).
  • The subquery matches on NAME only. It does not match on DBID, the identifier of the database the statistics belong to. If the view holds rows for more than one DBID, the subquery takes the highest version across all of them. Every DBID that has a row at that version then returns its own row, and the same name appears twice.
  • ORDER BY hwm1.name sorts the output by statistic name.
  • COLUMN FORMAT DEFAULT is the last line. It does not name name, highwater or last_value. CLEAR COLUMNS resets the attributes of all columns. Add CLEAR COLUMNS at the end of your local copy, or run it after the script, so the column formats do not carry into the next query.

What the main statistics mean (19c)

NameMeaning
DB_SIZEMaximum size of the database, in bytes
SEGMENT_SIZESize of the largest segment, in bytes
SESSIONSMaximum number of concurrent sessions seen in the database
ACTIVE_SESSIONSMaximum number of active sessions seen in the system
CPU_COUNTMaximum number of CPUs
INSTANCESOracle Database instances
DATAFILESMaximum number of datafiles
TABLESPACESMaximum number of tablespaces
EXADATA_DISKSNumber of physical disks
QUERY_LENGTHMaximum query length
PART_TABLESMaximum number of partitions in one user table
SQL_NCHAR_COLUMNSMaximum number of SQL NCHAR columns
USER_TABLESNumber of user tables
USER_INDEXESNumber of user indexes
USER_MVMaximum number of materialized views (user)

Key Points

  • The view is documented from Oracle Database 10g Release 1 (10.1) onward, with its HIGHWATER and LAST_VALUE columns. The script sits in a folder named 10g in its repository.
  • The list of statistics changes between releases. The 10.1 list includes SERVICES (maximum number of services), and the 19c list does not. ACTIVE_SESSIONS, EXADATA_DISKS, INSTANCES, SQL_NCHAR_COLUMNS and USER_MV are in the 19c list, and none of these five is in the 10.1 list. Two databases on different releases therefore return different sets of rows.
  • The script does not select DESCRIPTION, so its output does not change when that column grows to VARCHAR2(4000) in release 26, up from VARCHAR2(128) in 10.1 and 19c. Release 26 also describes INSTANCES as "Oracle AI Database instances".
  • The segment high water mark is a separate concept with a similar name. A segment HWM is the point in a segment beyond which blocks are unformatted and have never been used. It drops only after you rebuild, truncate or shrink the object. This view stores database-wide peaks such as size, sessions and CPUs.
  • V$LICENSE also has high-water columns, but they count from instance startup: SESSIONS_HIGHWATER, CPU_COUNT_HIGHWATER and CPU_CORE_COUNT_HIGHWATER. A restart starts them again.

Insights and Best Practices

Compare HIGHWATER with LAST_VALUE

HIGHWATER is the highest value seen at sampling time, and LAST_VALUE is the value at the last sample. When HIGHWATER is far above LAST_VALUE, the peak was recorded at an earlier sample. The view has no date column, so it cannot tell you when that peak was recorded.

Pair with feature usage

A license review asks two questions: how big and busy the database is, and which features it uses. This script answers the first. DBA_FEATURE_USAGE_STATISTICS answers the second. This query lists only the features with at least one detection:

1SELECT name, version, detected_usages, total_samples,
2       currently_used, first_usage_date, last_usage_date,
3       last_sample_date
4FROM   dba_feature_usage_statistics
5WHERE  detected_usages > 0
6ORDER BY name, version;
  • DETECTED_USAGES is the number of times the system detected usage of the feature. TOTAL_SAMPLES is the number of times the system woke up and checked. Read the two together. A feature detected in 1 of 200 samples was seen once. A feature detected in 200 of 200 samples was seen at every check.
  • CURRENTLY_USED is TRUE only if usage was detected the last time the system checked. A FALSE value says nothing about earlier samples.
  • FIRST_USAGE_DATE and LAST_USAGE_DATE are sample times: the first and last checks that detected usage.
  • LAST_SAMPLE_DATE is the last time the system checked for usage.
  • Like the high-water view, this view has DBID and VERSION columns. One feature can have a row for each release in which it was tracked.

Widen the format for byte statistics

In a local copy, edit the two number COLUMN lines so each format has more 9s than the digit count of your largest byte value. Each 9 is one digit, so FORMAT 999999999999999 (15 nines) shows values up to 999,999,999,999,999 bytes.

For a number column, a COLUMN FORMAT setting takes precedence over SET NUMFORMAT, and SET NUMFORMAT takes precedence over SET NUMWIDTH. The script sets COLUMN FORMAT, so changing NUMFORMAT or NUMWIDTH for the session leaves the width as it is. Edit the COLUMN lines.

To read a byte value in GiB, divide it by 1024 three times in a separate query, for example ROUND(highwater/1024/1024/1024, 1). Leave the script's own columns in bytes so the output matches the view.

Read the older version rows after an upgrade

The script hides rows from earlier versions on purpose. To see every row, drop the WHERE clause and add DBID, VERSION and DESCRIPTION:

1COLUMN description FORMAT A50
2SELECT dbid, name, version, highwater, last_value, description
3FROM   dba_high_water_mark_statistics
4ORDER BY name, version, dbid;

Each statistic name now shows one row for each version in which it was tracked, and one for each DBID. Use this output to compare a statistic across the releases the database has run, and to see which rows the MAX(version) filter kept. DESCRIPTION carries the meaning of each statistic, so a name you do not know is explained in the same output.

When to Run This

  • On a fixed schedule. Save the output and compare DB_SIZE, SEGMENT_SIZE, DATAFILES and TABLESPACES with the copy from the last run to measure growth.
  • Before a license review. Read the CPU_COUNT and SESSIONS rows and the feature usage query together, so you know what the dictionary records.
  • After a migration or upgrade. Confirm which VERSION the script picked, and run the query in "Read the older version rows after an upgrade" to compare both releases.

Troubleshooting Common Issues

  • ORA-00942: table or view does not exist. The connected user cannot see the view. Run the script as a user who can read DBA_HIGH_WATER_MARK_STATISTICS, or have that access granted.
  • Pound signs in HIGHWATER or LAST_VALUE. See "Widen the format for byte statistics".
  • The same name appears twice. If the view holds rows for more than one DBID at the same version, the script returns one row for each. Run the query from "Read the older version rows after an upgrade" to see which DBID each row belongs to. Then filter on the DBID of the database you are reviewing.
  • NULL core or socket counts in V$LICENSE. V$LICENSE returns NULL when a statistic is not available on the operating system platform. A NULL in CPU_CORE_COUNT_HIGHWATER means the platform did not supply a core count. V$LICENSE keeps logical CPUs (CPU_COUNT_HIGHWATER) and cores (CPU_CORE_COUNT_HIGHWATER) in separate columns. The high-water view defines its CPU_COUNT statistic only as "Maximum Number of CPUs".
  • The script picks the wrong version. Run the same query for one statistic, such as SESSIONS. If the text order of the VERSION values does not match release order, filter on the exact version you need in place of MAX.
  • Feature usage rows look out of date. Check LAST_SAMPLE_DATE first. If it is far in the past, the system has not checked for usage since then, and CURRENTLY_USED describes that old check.

References

Posts in this series