Table of Contents
This chapter describes Tibero Performance Repository (TPR), which is used to diagnose Tibero's performance.
Tibero provides a variety of statistics to help DBAs diagnose performance problems. Tibero Performance Repository (hereafter TPR) automatically and periodically collects such statistics and provides its own analysis reports about the statistics for system load analysis.
TPR has the following functions:
Saving snapshots
Periodically saves Tibero's various performance statistics such as _vt_jcntstat, v$system_event, v$sqlstats, and v$sgastat (usually once an hour) in a table. The saved information is called a snapshot. Snapshots are used to create an analysis report to diagnose database performance problems.
Saving session status
Saves the current session IDs and pending event information in memory every second. The saved information can be viewed using the view v$active_session_history. With this view, a database's performance problems can be analyzed in detail. Increasing the save cycle based on the environment is recommended because this function can reduce the performance of the database.
To use the snapshot saving function, set 'TIBERO_PERFORMANCE_REPOSITORY=Y' in the tip file. To use the session status saving function, set 'ACTIVE_SESSION_HISTORY=Y'. To use other APM functions, specify the following parameters in the tip file.
| Parameter | Description |
|---|---|
| TIBERO_PERFORMANCE_ REPOSITORY | If specified as Y, the snapshot saving function is enabled. (Default value: Y) |
| TPR_SNAPSHOT_SAMPLING_ INTERVAL | Interval at which to save snapshots, in minutes. (Default value: 60) |
| TPR_SNAPSHOT_RETENTION | Period to maintain snapshots, in days. (Default value: 7) |
| TPR_SNAPSHOT_TOP_SQL_CNT | The number of top SQL to print in the report. (Default value:5) |
| TPR_SEGMENT_STATISTICS | TPR enables statistics collection for each segment. (Default value: N) |
| TPR_SNAPSHOT_TOP_ SEGMENT_CNT | Number of top segments to be printed in the report. (Default value: 5, unit: count) |
| TPR_METRIC | Enables the TPR METRIC function. (Default value: N) |
| TPR_AGGREGATION | Enables the TPR AGGREGATION function. (Default value: N) |
| ACTIVE_SESSION_HISTORY | If specified as Y, the session status saving function is enabled. (Default value: N) |
| _ACTIVE_SESSION_HISTORY_ SAMPLING_INTERVAL | Interval at which to save session status, in seconds. (Default value: 1) |
Saved snapshots and session status can be viewed using the relevant tables and views. By default, snapshots and session status are removed from a table after seven days.
Tables related to snapshot saving:
| Table | Description |
|---|---|
| _TPR_SNAPSHOT | Contains saved snapshot IDs and time information. |
| _TPR_BASELINE | Manages registered baseline information. |
| _TPR_ACTIVE_SESSION_HISTORY | Manages saved ASH sample information. |
| _TPR_METRIC | Manages saved TPR metric information. |
| _TPR_JCNTSTAT | Contains snapshot information for the view _VT_JCNTSTAT. |
| _TPR_SQLSTATS | Contains snapshot information for the view V$SQLSTATS. |
| _TPR_SQL_PLAN | Contains snapshot information for the view V$SQL_PLAN. |
| _TPR_SQL_PLAN_STAT | Contains snapshot information for the view V$SQL_PLAN_STATISTICS. |
| _TPR_LATCH | Contains snapshot information for the view V$LATCH. |
| _TPR_SYSTEM_EVENT | Contains snapshot information for the view V$SYSTEM_EVENT. |
| _TPR_WAITSTAT | Contains snapshot information for the view V$WAITSTAT. |
| _TPR_SGASTAT | Contains snapshot information for the view V$SGASTAT. |
| _TPR_PGASTAT | Contains snapshot information for the view V$PGASTAT . |
| _TPR_LIBRARYCACHE | Contains snapshot information for the view V$LIBRARYCACHE. |
| _TPR_SQLTEXT | Contains snapshot information for the view V$SQLTEXT . |
| _TPR_FILESTAT | Contains snapshot information for the view V$FILESTAT. |
| _TPR_SEGMENTSTAT | Contains snapshot information for the view V$SEGMENT_STATISTICS. |
| _TPR_TEMPSEG_OP_USAGE | Contains snapshot information for the view V$TEMPSEG_OP_USAGE. |
| _TPR_PROCESS | Contains snapshot information for the view V$PROCESS. |
| _TPR_SESSION | Contains snapshot information for the view V$SESSION. |
| _TPR_WAITER_SESSION | Contains snapshot information for the view V$WAITER_SESSION. |
| _TPR_UNDOSTAT | Contains snapshot information for the view V$UNDOSTAT. |
| _TPR_OSSTAT2 | Contains snapshot information for the view V$OSSTAT2. |
| _TPR_SQLWA_HIST | Contains snapshot information for the view V$SQLWA_HIST. |
| _TPR_MODIFIED_PARAM | Contains snapshot information for the table _VT_PARAMETER. |
| _TPR_MISC | Contains snapshot information such as the number of sessions. |
A sample of the table _TPR_SNAPSHOT is shown below:
TABLE '_TPR_SNAPSHOT' ------------------------------------------------------------- COLUMN_NAME TYPE CONSTRAINT --------------------------------- ---------------- ---------- SNAP_ID NUMBER THREAD# NUMBER INSTANCE_NUMBER NUMBER BEGIN_INTERVAL_TIME DATE END_INTERVAL_TIME DATE SNAP_GID NUMBER
Tables related to session status saving
| Table | Description |
|---|---|
| _TPR_ACTIVE_SESSION_HISTORY | Contains running session status. |
| V$ACTIVE_SESSION_HISTORY | Contains session status during the past hour. |
The following is an example of the table V$ACTIVE_SESSION_HISTORY:
VIEW 'V$ACTIVE_SESSION_HISTORY' ---------------------------------------------------------- COLUMN_NAME TYPE CONSTRAINT ------------------------------- -------------- ----------- SAMPLE_ID NUMBER THREAD# NUMBER SAMPLE_TIME DATE SID NUMBER SESS_SERIAL_NO NUMBER USER_NO NUMBER WAIT_EVENT NUMBER TIME_WAITED NUMBER SQL_ID VARCHAR(13) SQL_CHILD_NUMBER NUMBER CURR_HASHVAL NUMBER MODULE_NAME VARCHAR(64) ACTION_NAME VARCHAR(64) CLIENT_INFO_NAME VARCHAR(64) PROG_NAME VARCHAR(30) SQL_EXEC_START DATE SQL_EXEC_ID NUMBER SQL_PLAN_LINE_ID NUMBER ID1 NUMBER ID2 NUMBER WE_SEQ NUMBER USGMT_ID NUMBER SLOTNO NUMBER WRAPNO NUMBER PORT NUMBER DELTA_TIME NUMBER DELTA_PHY_READ_BLKS NUMBER DELTA_PHY_WRITE_BLKS NUMBER DELTA_LOG_READ_BLKS NUMBER PGA_SIZE NUMBER WAIT_OBJ_ID NUMBER WAIT_FILE_NO NUMBER WAIT_BLOCK_NO NUMBER WAIT_ROW_NO NUMBER
A snapshot can be created manually as shown below:
SQL> exec dbms_tpr.create_snapshot();
Saved snapshots and session status may be used with tables and views, but in general, they are used with an analysis report. A performance analysis report can currently be created by analyzing snapshot information, but cannot be created by analyzing session status information.
Use the table _TPR_SNAPSHOT to check if the start and end time of a period are available to create a performance analysis report.
Specify the period's start time begin and end time end as shown below to create a performance analysis report: All snapshots with a BEGIN_INTERVAL_TIME value that falls between begin_date and end_date in _TPR_SNAPSHOT table are displayed in a report.
/* exec dbms_tpr.report_text(begin_date, end_date) */
SQL> exec dbms_tpr.report_text('2013-01-01 13:00:00', '2013-01-01 14:59:00');
The report file is generated in the following path:
$TB_HOME/instance/$TB_SID/tpr_report.{mthr_pid}.{current_time}
Descriptions for each performance analysis item are as follows:
Overview Part
System Overview
CPU Usage
Memory Usage
Workload Overview
Workload Summary
Workload Stats
Instance Overview
Instance Efficiency
TAC Statistics Overview (Cluster Cache Activity, Cluster Buffer Cache, Cluster Cache and Wait Lock Statistics)
Top 5 Wait Events by Wait Time
I/O Overview
SQL Overview
PGA Work Area Statistics
Top 3 SQL Ordered by Elapsed Time
Top 3 SQL Ordered by Executions
Top 3 SQL Ordered by Gets
Detail Part
System Detail
OS Statistics
Shared Pool Statistics
Physical Plan Cache Statistics
Data Dictionary Cache Statistics
PGA Statistics
Workload Detail
Workload Stats (Time-based)
Workload Stats (Number-based)
Workload Stats (Size-based)
Instance Detail
Buffer Cache Statistics
Wait Event Summary (by Class)
Wait Events by Wait Time
Session Status with Wait Event
Blocking Session Status with Wait Event
Wlock Statistics
Spinlock (Latch) Statistics
Spinlock (Latch) Sleep Statistics
Tablespace I/O Statistics
File I/O Statistics
Temp Segment Usage Statistics
Segments Ordered by Physical Reads (When "TPR_SEGMENT_STATISTICS" is set to Y. )
Segments Ordered by Logical Reads (When "TPR_SEGMENT_STATISTICS" is set to Y.)
Segments Ordered by ITL Waits (When "TPR_SEGMENT_STATISTICS" is set to Y.)
Segments Ordered by Buffer Busy Waits (When "TPR_SEGMENT_STATISTICS" is set to Y.)
Segments Ordered by Row Lock Waits (When "TPR_SEGMENT_STATISTICS" is set to Y.)
Undo Statistics
Wait Statistics (When "_DB_BLOCK_PIN_WAIT_USE_STAT" is set to Y.)
SQL Detail
PGA Summary
PGA Work Area Histogram
SQL Ordered by Elapsed Time (with Physical Plan)
SQL Ordered by Elapsed Time/Execution (with Physical Plan)
SQL Ordered by Executions (with Physical Plan)
SQL Ordered by Gets (with Physical Plan)
SQL Ordered by Reads (with Physical Plan)
SQL Ordered by Extra I/O (with Physical Plan)
SQL Ordered by CPU (with Physical Plan)
Etc
Tibero Init.Parameters (.tip)
Modified Parameters