User/TIP File Settings

1. User Settings for the Monitored Database

When registering the monitoring database in SysMaster DB, the users will be prompted to enter user information. The users can use an existing user or create a new one. The required permissions for the user are CONNECT, ALTER SYSTEM, and SELECT_CATALOG_ROLE. User creation and permission assignment are performed using the following DDL and DCL statements in the SYS account.

CREATE USER [username] IDENTIFIED BY [password];
GRANT CONNECT, ALTER SYSTEM, SELECT_CATALOG_ROLE TO [username];

Additionally, to use the TPR Report and ASH Report features, the users must grant the relevant permissions using the following DCL statement:

GRANT EXECUTE ON SYS.DBMS_TPR TO [username];
GRANT EXECUTE ON UTL_TPR TO [username];

Note

SysMasterDB 8.3 does not create tables or load data into the control database.

---

2. TIP File Settings for the Monitored Database

To collect statistics generated when executing SQL in the monitoring database, add the 'SQL_STAT_HISTORY', 'SQL_STAT_HISTORY_THRESHOLD', and 'SQL_STAT_HISTORY_QSIZE' parameters to the tip file. The descriptions for each parameter are as follows.

Parameter	Description	Recommended value	Dynamic modification
SQL_STAT_HISTORY	Whether to generate SQL execution information	Y (required)	Not possible
SQL_STAT_HISTORY_THRESHOLD	SQL execution information creation threshold execution time (e.g., only create execution information for SQL that took 100 milliseconds or longer to execute)	100	Possible
SQL_STAT_HISTORY_QSIZE	Number of SQL execution information to be stored per session (An integer between 1 and 10000)	10	Not possible

Note

1. If SQL_STAT_HISTORY is set to Y, each session stores SQL execution information that has been executed at least SQL_STAT_HISTORY_THRESHOLD times in a queue of size SQL_STAT_HISTORY_QSIZE.

2. When the queue is full, the oldest SQL execution information is deleted from the queue.

3. The TPM Agent collects SQL execution information every SQLTRACE_FREQ (ms), so

SQL_STAT_HISTORY_QSIZE must be set to a value such that SQLTRACE_FREQ / SQL_STAT_HISTORY_THRESHOLD ≤ SQL_STAT_HISTORY_QSIZE

to ensure no information is lost in normal conditions. QSIZE, as mentioned above, represents the number of entries and must be an integer value. If the result of the division is a decimal such as 0.01, set QSIZE to 1 or higher (an integer greater than or equal to 0.01) to prevent data loss. Refer to the manual for the ranges of FREQ and QSIZE.

Additionally, even if QSIZE is set to the recommended value, data collection may be missed under heavy load conditions. For example, if Tibero experiences high load and the CPU resources are not available for the TPM Agent, the collection interval may unexpectedly increase. If the collection interval was previously set to 1000ms, it may be extended to 2000ms for a few seconds due to insufficient CPU resources. In such cases, even if the recommended value is set, data collection may be missed. To prepare for such situations, the users can allocate a larger QSIZE than the recommended value; however, this will consume more memory from Tibero, so it is advisable to set QSIZE based on the recommended value according to the situation.

The amount of shared memory used increases in proportion to SQL_STAT_HISTORY_QSIZE and the total number of sessions in the database. Therefore, when changing SQL_STAT_HISTORY from N to Y, the increased memory usage must be added to TOTAL_SHM_SIZE. For database stability, we recommend adding an additional 3–5% to the result of (increased memory usage) + TOTAL_SHM_SIZE to obtain the final TOTAL_SHM_SIZE value.

For DB_CACHE_SIZE, which is automatically changed by TOTAL_SHM_SIZE, set it to the value when SQL_STAT_HISTORY was N to minimize the impact.

When SQL_STAT_HISTORY_QSIZE=1, the memory used for SQL STAT HISTORY in a single session is approximately 231,000 bytes. Therefore, multiply this value by the total number of sessions (MAX_SESSION_COUNT + WTHR_PER_PROC) (including both MGWP and FGWP) and SQL_STAT_HISTORY_QSIZE.

Note

MAX_SESSION_COUNT=500, WTHR_PER_PROC=10, SQL_STAT_HISTORY_QSIZE=10, TOTAL_SHM_SIZE=3500M, when SQL STAT_HISTORY=Y, approximately 231,000 × ( 500 + 10 ) × 10 = 1,178,100,000 bytes = 1,123.52 MB is used.

For TOTAL_SHM_SIZE, set it to ( 3500 + 1124 ) × 1.03 ~ 1.05 = 4,763 ~ 4,855 MB.

---

3. libtpmstat.so library

The libtpmstat library is required for the TPM Agent to collect information from Tibero. If the 279651 patch has been applied to the management database and the libtpmstat library is available, the users can use the library by configuring the Tibero environment variables.

If the libtpmstat library is not available in the management database, the users must distribute the appropriate library for that database. After distributing the libtpmstat.so file, move it to the TPM Agent directory and configure the TPM Agent library path to use the library.

Note

To distribute the libtpmstat.so library file, the users must build libtpmstat.so using a management database build that has the exact same patches as the management database patch list. For example, if there are three patches ([1st patch], [2nd patch], [3rd patch]) in Tibero 6, and [1st patch], [3rd patch] in another location, the users must build and distribute both the libtpmstat.so file built from Tibero with [1st patch], [2nd patch], and [3rd patch] applied, and the libtpmstat.so file built from Tibero with [1st patch] and [3rd patch] applied.

Additionally, when building the libtpmstat.so library, all build flags applied during the Tibero binary build must be applied identically during the library build. For example, if the NET_BACKUP build flag was applied when building and distributing the Tibero binary, the same flag must be included when building the libtpmstat.so library for distribution.