Overview

DBA monitoring is necessary to prevent database failures and ensure high performance.

Performance management can be conducted not only by simple query tuning but also by changing configuration settings and, in some cases, considering changes to application design based on monitoring results.

This document summarizes the elements required for performance management.

Method

1. Monitoring

Database performance monitoring involves verifying whether the database is performing according to the application environment and user expectations,

and whether it is executing as designed by checking operation and execution results.

If the values derived from monitoring reach the limit range, appropriate actions are required.

1.1. Monitoring Targets

Purpose: Describes the monitoring target items of the operating DBMS.

Current Environment

• The current database configuration is composed as TAC.
• Database Hosts: TEST1 (Active), TEST2 (Active)

• The Tibero monitor tool for current monitoring is configured as executable files on each node.

   

Configuration Method

• It is a general shell file, and since queries are executed through tbsql, it exists on the server. No other configuration is needed.

• Execution method: run the tm command

   

Configuration Details

1.2. Using Tibero Studio

Please refer to “Chapter 4 DBA Functions” in the Tibero Studio User Guide document.

1.3. Memory Management

Purpose: Describes the monitoring method for memory management of the operating system.

Current Environment

• Monitoring information about system memory usage can be extracted using Monitor and TPR.
  However, report generation must be performed directly on the system.

Configuration Method

• Telnet or remote desktop connection and FTP access permissions are required on the DB server.
  TPR (Tibero Performance Repository) can be created internally in Monitor via SQL statements.

Operating Procedure

1. Generate TPR Report

   1. Remote desktop connection to DB server
      • Connect to Windows server via remote desktop, Unix or Linux via Telnet
   2. Generate TPR Report
      • TPR Report supports HTML and text formats for reporting. It calls DBMS built-in packages,
        and accepts instance number, start time, and end time as parameters.
   3. Location and files
      • Location: $TB_HOME/instance/$TB_SID
      • File: tpr_report.$[SID].$[execution_date].txt 

   [Example] TPR Report Output

   $ tbsql sys/tibero
   
   SQL> column BEGIN_INTERVAL_TIME format a20;
   SQL> column END_INTERVAL_TIME format a20;
   SQL> alter session set NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
   SQL> select SNAP_ID, THREAD#, INSTANCE_NUMBER, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME 
   from _tpr_snapshot; -- Query TPR targets
   
   SNAP_ID       THREAD# INSTANCE_NUMBER BEGIN_INTERVAL_TIME  END_INTERVAL_TIME 
   ---------- ---------- --------------- -------------------- --------------------
            1          0               0 2023/11/17 00:32:46  2023/11/17 01:32:46
           21          0               0 2023/11/17 09:06:43  2023/11/17 10:06:43
           22          0               0 2023/11/17 10:06:43  2023/11/17 11:06:43
           23          0               0 2023/11/17 11:06:43  2023/11/17 12:06:43  
           24          0               0 2023/11/17 12:06:43  2023/11/17 13:06:43 
           25          0               0 2023/11/17 13:06:43  2023/11/17 14:06:43  
           26          0               0 2023/11/17 14:06:43  2023/11/17 15:06:43
           27          0               0 2023/11/17 15:06:43  2023/11/17 16:06:43
           28          0               0 2023/11/17 16:06:43  2023/11/17 17:06:43
           41          0               0 2023/11/20 10:34:08  2023/11/20 11:34:08
   
   SQL> exec dbms_tpr.report_text('0', '23', '23');    
   
   PSM completed.

Memory Related Information

1. Check Memory Summary

    

    1.2 Memory Usage 
    ================================================================================
                           HOST Mem Size :      4,797M
                          Total SHM Size :      1,024M
                       Buffer Cache Size :        683M
                   Avg. Shared Pool Size :     145.42M
                      Avg. DD Cache Size :       1.42M
                      Avg. PP Cache Size :      28.92M
                           DB Block Size :          8K
                         Log Buffer Size :         10M

• You can see information such as Total Memory, Buffer Cache Size, Shared Pool Size, etc.

2. Check Shared Pool Information

   [Example] TPR Report (Shared Pool Statistics)

   5.2 Shared Pool Statistics 
   ================================================================================
   Name                              Begin(MB)                End(MB)       Diff %       Max(MB)       Min(MB)
   --------------------  ---------------------  ---------------------  -----------  ------------  ------------
   SLAB_TCBUF                   39.63 (27.25%)         39.63 (27.25%)       +0.00%         39.63         39.63
   PP                           28.92 (19.89%)         28.92 (19.89%)       +0.00%         28.92         28.92
   Free Space                   25.10 (17.26%)         25.10 (17.26%)       +0.00%         25.10         25.10

   [Example] TPR Report (Cache Summary)

   3.1 Instance Efficiency
   ==========================================
                                      Value
                                   --------
              Buffer Cache Hit %:    100.00
                   Library Hit %:     92.73  
                        PP Hit %:     95.32  
                     Latch Hit %:    100.00 
                Redo Alloc Hit %:    100.00
                 Non-Parse CPU %:     98.27
                      Parse Time:  2,182.01
                         DB Time:     16.53

   • Item Descriptions

     [Table] Cache Summary Item Descriptions

     Item	Memory Area	Description
     Buffer Cache Hit	Data Buffer Cache	The ratio of data processed in memory
     In-Memory Sort	Data Buffer Cache	Ratio of sort processing in memory (Actual PGA: processed dynamically in WTHR)
     Library Hit	Shared Pool	Ratio of library reuse in memory
     PP Hit	Shared Pool	Ratio of reuse of previously parsed plan information in memory
     Latch Hit	Shared Pool	Probability of acquiring resources using latches in memory

3. Check Buffer Pool Information

   • Database Buffer

     Cache is a major part of TSM (Tibero Shared Memory) where data read from tables by SQL statements is stored.

     Therefore, when the same data is accessed again, it uses the data moved to this area instead of reading from disk, reducing disk I/O and naturally speeding up performance.

     [Example] TPR Report (Buffer Cache Statistics)

     7.1 Buffer Cache Statistics 
     ================================================================================
          Num of  DB Cache                           Physical         Physical     Free Buf    Buf Busy-
          Buffers     Hit %       Buffer Gets            Reads           Writes        Waits  Write Waits
     -----------  --------  ----------------  ---------------  ---------------  -----------  -----------
          87,392    100.00           790,140                0              555            0            0

   • Buffer Pool Statistics Item Descriptions

     [Table] TPR Report (Buffer Pool Statistics Information)

     Item	Description
     Number of Buffers	Total number of buffers composing the pool
     DB Cache Hit %	Ratio of reading data from buffer without physical reads
     Buffer Gets	Number of data blocks read from buffer
     Physical Read	Number of data blocks read from disk
     Physical Writes	Number of data blocks written to disk
     Free Buff Wait	Number of times no free buffer space was found to hold blocks read from disk
     Buffer Busy Waits	Number of waits occurring when the buffer for the data is being used by another user

1.4. Lock Management

Purpose: Describes the method for monitoring lock contention.

Configuration Method

• Windows DB servers require remote desktop, Unix and Linux systems require Telnet access and tbsql login
  to check lock information via SQL queries. (Tibero Studio functionality can also be used)

• Operating Procedures

  [Example] Lock Check Query

  SET FEEDBACK OFF  
  SET LINESIZE 132  
  SET PAGESIZE 50
  
  COL "USER" FORMAT A15 
  COL "SID" FORMAT 9999 
  COL "OBJECT" FORMAT A35
  COL "STATUS" FORMAT A8
  COL "LOCK_TIME" FORMAT A15
  COL "LOCK MODE" FORMAT A15
  @$MONITOR/SQL/SQLID_FORMAT.SQL
  
  SELECT S.SESS_ID  "SID"
             ,S.STATUS "STATUS"
             ,S.USER_NAME "USER"
             ,O.OWNER|| '.' ||O.OBJECT_NAME "OBJECT"
             ,FLOOR((SYSDATE - VT.START_TIME)*24) || ':'||
               LPAD(FLOOR(MOD((SYSDATE - VT.START_TIME)*1440, 60)),2,0) ||':'||
               LPAD(FLOOR(MOD((SYSDATE - VT.START_TIME)*86400,60)),2,0) AS "LOCK_TIME"
             ,DECODE(LMODE, 0, '[0]', 1, '[1]ROW-S(RS)', 2, '[2]ROW-X(RX)', 3, '[3]SHARED(S)', 4, '[4]S/ROW-S(SRX)', 5, '[5]EXCLUSIVE(X)', 6, '[6]PIN', TO_CHAR (LMODE) )  "LOCK MODE"
             --,NVL(S.SQL_ID, S.PREV_SQL_ID) "SQL_ID"
             ,NVL(S.SQL_ID, S.PREV_SQL_ID) || '/' || NVL2(S.SQL_ID, S.SQL_CHILD_NUMBER, S.PREV_CHILD_NUMBER) "SQL_ID"
       FROM VT_WLOCK L,
             VT_SESSION S,
             DBA_OBJECTS O ,
             VT_TRANSACTION VT
       WHERE L.TYPE='WLOCK_DML'
         AND L.SESS_ID = S.VTR_TID
         AND L.ID1 = O.OBJECT_ID (+)
         AND L.SESS_ID = VT.SESS_ID ORDER BY "LOCK_TIME" DESC
  /

• Check what kind of locks are held on database objects by SID.

• Check sessions holding locks.

  [Example] Session Information Check

  SET LINES 200 
  SET FEEDBACK OFF
  COL "SID,SERIAL" FORMAT A10
  COL "USERNAME" FORMAT A15
  COL "STATUS" FORMAT A10
  COL "IPADDR" FORMAT A15
  COL "LOGON_TIME" FORMAT A18
  COL "PROGRAM" FORMAT A18
  @$MONITOR/SQL/SQLID_FORMAT.SQL
  
  SELECT * FROM
  (
  SELECT  SID || ',' ||SERIAL# "SID,SERIAL"
         ,USERNAME "USERNAME"
         ,STATUS "STATUS"
         ,IPADDR "IPADDR"
         ,TO_CHAR(LOGON_TIME,'YY/MM/DD HH24:MI:SS') "LOGON_TIME"
         ,PROG_NAME "PROGRAM"
         ,WLOCK_WAIT "WLOCK_WAIT"
         ,NVL(SQL_ID, PREV_SQL_ID) || '/' || NVL2(SQL_ID, SQL_CHILD_NUMBER, PREV_CHILD_NUMBER) "SQL_ID"
         ,PID "WTHR_PID"
         ,TRUNC(PGA_USED_MEM/1024/1024,3) "PGA(MB)"
  FROM   V$SESSION
  WHERE   SID != ( SELECT TID FROM VT_MYTID )
  ORDER BY  1,5
  )
  UNION ALL
  SELECT '[RUN: ' || SUM(DECODE(STATUS, 'RUNNING', CNT, 0)) || ']'
                 , '[TOT: ' || SUM(CNT) || ']'
       ,NULL ,NULL ,NULL ,NULL ,NULL,NULL,NULL,NULL
  FROM (
   SELECT STATUS
         , COUNT(*) CNT
   FROM V$SESSION
       WHERE SID != ( SELECT TID FROM VT_MYTID )
   GROUP BY STATUS
  ) T
  /

• Check session IP and PGA memory usage, etc.

Unlocking Locks

• If a lock occurs, sessions may need to be killed depending on the type.
• Locks are essential algorithms in DBMS systems; sessions should be checked and distinguished if deadlocks occur before unlocking locks.
• If a deadlock is detected through the lock checking procedure, it can be resolved by killing the affected session.
• Save and execute the following after checking the query statement.

[Example] SQL Statement and Plan Check

SET LINESIZE 132  
SET PAGESIZE 120  
SET VERIFY OFF  
SET FEED OFF

COL "SQL TYPE" FORMAT A8 
COL "ID" FORMAT 99999 
COL "PLAN" FORMAT A100

ACCEPT SQL_ID PROMPT 'INPUT SQL_ID(EX: SQL_ID/SQL_CHILD_NUMBER ) : '

VAR SQL_ID VARCHAR(13);
VAR SQL_CHILD_NUMBER NUMBER;
EXEC :SQL_ID := REGEXP_SUBSTR('&SQL_ID','[^/]+', 1, 1)
EXEC :SQL_CHILD_NUMBER := REGEXP_SUBSTR('&SQL_ID','[^/]+', 1, 2)

PROMPT ## SQL INFO
PROMPT ----------------------------------------------------------------------------------------------------
SELECT SQL_ID, CHILD_NUMBER, HASH_VALUE, PLAN_HASH_VALUE
      ,DECODE(EXECUTIONS, 0, -1, ROUND(BUFFER_GETS/EXECUTIONS,3)) "GETS/EXEC"
      ,DECODE(EXECUTIONS, 0, -1, ROUND(ELAPSED_TIME/EXECUTIONS/1000,3)) AS "ELAP/EXEC(MS)"
      ,EXECUTIONS
FROM V$SQL
WHERE SQL_ID = :SQL_ID AND CHILD_NUMBER = :SQL_CHILD_NUMBER
/

SET HEAD OFF
 PROMPT ## SQL TEXT
 PROMPT ----------------------------------------------------------------------------------------------------
 SELECT SQL_TEXT "PLAN"
 FROM   V$SQLTEXT_WITH_NEWLINES
 WHERE SQL_ID = :SQL_ID AND CHILD_NUMBER = :SQL_CHILD_NUMBER
 UNION ALL
 SELECT  '.----------------.' FROM DUAL
 UNION ALL
 SELECT  '| EXECUTION PLAN |' FROM DUAL
 UNION ALL
 SELECT '+-----------------------------------------------------------------------------------------' FROM DUAL
 UNION ALL
 SELECT *
 FROM
 (
 SELECT SUBSTRB(TO_CHAR(ID),1, 3) || LPAD(' ', LEVEL * 2) || UPPER(OPERATION) ||
       DECODE(OBJECT_NAME, NULL, NULL, ':' || OBJECT_NAME) || '(COST:' || COST || ',%%CPU:' ||
       DECODE(COST, 0, 0, TRUNC( (COST - IO_COST) / COST * 100) ) || ',CARD:' || CARDINALITY || ')' ||
       DECODE(PSTART, '', '', '(PS:' || PSTART || ',PE:' || PEND || ')') AS "EXECUTIONPLAN"
 FROM (
 SELECT *
 FROM V$SQL_PLAN
 WHERE SQL_ID = :SQL_ID  AND CHILD_NUMBER = :SQL_CHILD_NUMBER)
 START WITH DEPTH = 1 CONNECT BY PRIOR ID = PARENT_ID
     AND PRIOR SQL_ID = SQL_ID AND PRIOR CHILD_NUMBER = CHILD_NUMBER
   ORDER SIBLINGS BY POSITION
 )
UNION ALL
 SELECT  '+-----------------------------------------------------------------------------------------' FROM DUAL
 UNION ALL
 SELECT  '| EXECUTION STATS |' FROM DUAL
 UNION ALL
 SELECT  '+-----------------------------------------------------------------------------------------' FROM DUAL
 UNION ALL
 SELECT *
 FROM
 (
 SELECT SUBSTRB(TO_CHAR(ID), 1, 3) ||
     LPAD(' ', L * 2) || UPPER(OPERATION) ||
     DECODE(OBJECT_NAME, NULL, NULL, ': '||OBJECT_NAME) ||
     ' (TIME:' || TO_CHAR(LAST_ELAPSED_TIME, 'FM9999999.99') ||
     ' MS, ROWS:' || LAST_OUTPUT_ROWS ||
     ', STARTS:' || LAST_STARTS || ') ' AS "EXECUTION STAT"
 FROM (SELECT A.ID, A.OPERATION, A.OBJECT_NAME, A.COST,
             A.IO_COST, A.CARDINALITY, A.PSTART, A.PEND,
             S.LAST_ELAPSED_TIME, S.LAST_OUTPUT_ROWS, S.LAST_STARTS, A.L
         FROM (SELECT I.*, LEVEL L
               FROM (SELECT * FROM V$SQL_PLAN
                       WHERE SQL_ID = :SQL_ID AND CHILD_NUMBER = :SQL_CHILD_NUMBER) I
               START WITH DEPTH = 1
               CONNECT BY PRIOR ID = PARENT_ID AND PRIOR SQL_ID = SQL_ID AND PRIOR CHILD_NUMBER = CHILD_NUMBER
               ORDER SIBLINGS BY POSITION) A, V$SQL_PLAN_STATISTICS S
         WHERE A.SQL_ID = S.SQL_ID AND A.CHILD_NUMBER=S.CHILD_NUMBER AND A.ID = S.ID)
 )
/

Session Kill

[Example] Session Kill

alter system kill session(${Session ID},${serial})

Reference Information

• There are DML Locks and DDL Locks as follows.
• DML Lock: Ensures data integrity. (SHARED LOCK (TM), EXCLUSIVE LOCK (TX))

Table-Level Lock (TM)

• Table-level locks (TM) are set for all DML transactions modifying the table.
• INSERT, UPDATE, DELETE, SELECT..FOR UPDATE, LOCK TABLE
• Prevents DDL operations from conflicting with transactions.

[Example] TM Lock Case

# Work in Session1
SQL > UPDATE EMP 

# Work in Session2
SET SALARY = SALARY*100;

Row-Level Lock (TX)

• Row-level locks [TX] are automatically set on each row modified by Insert, Update, Delete, Select … for Update.

Types of DML Locks

• Row Exclusive (RX)
  • This mode allows other transactions to concurrently insert, update, delete, or lock different rows of the same table, but prevents manual table locks for exclusive reads or writes by other transactions.
  • Similar to RX LOCK but S, SRX, X LOCK cannot be set.
  • When UPDATE or DELETE is executed on a row, RX LOCK occurs on the table, so no error occurs, but it waits until COMMIT or ROLLBACK.
    (One of the most common lock types)
     
• Row Share (RS)
  • Allows locking rows during queries using SELECT … FOR UPDATE.
  • Allows other transactions to query, insert, update, delete, or lock different rows of the same table concurrently, and prevents manual table locks for exclusive write access.
     

Share Lock (S)

• S LOCK allows only RS and S LOCK on the same table and disallows RX, SRX, X LOCK.
• INSERT, UPDATE, DELETE are not allowed on this lock.
   
• Shared Row Exclusive (SRX)
  • SRX LOCK allows only RS LOCK on the same table and disallows RX, SRX, X LOCK. SELECT .. FOR UPDATE is allowed but INSERT, DELETE, UPDATE are not.
     

• DDL Lock Types: The server automatically sets DDL locks to prevent other DDL operations that might modify or reference the same schema object.

  Lock Types

  • Exclusive (X)
    • X LOCK disallows any lock on the same table. This lock is created by DDL statements like DROP TABLE, ALTER TABLE, etc.

DEADLOCK

• Deadlock occurs when two or more transactions wait for each other’s locked data.
• Tibero monitors deadlocks and resolves them when detected.

1.5. Session Management

Purpose: Describes how to check session connection status and detailed information.

Windows DB servers require remote desktop, Unix and Linux systems require Telnet access and tbsql login to check lock information via SQL queries. (Tibero Studio functionality can also be used)

Operating Procedures

1. Check total session count

   [Example] Total Session Count with Memory

   SET LINESIZE 120
   COL "WPM(WORKING PROCESS MEMORY)" FOR A30
   
   SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MM:SS') "TIME", 
        "WPM(WORKING PROCESS MEMORY)",
        (A.ACS + B.RUN) "TOTAL SESSION",
        B.RUN "RUNNING SESSION",
        C.RECOVER "RECOVER SESSION"
   FROM (SELECT COUNT(1) ACS
        FROM V$SESSION
        WHERE STATUS='ACTIVE') A,
        (SELECT COUNT(1) RUN
        FROM V$SESSION
        WHERE STATUS='RUNNING') B,
        (SELECT COUNT(1) RECOVER
        FROM V$SESSION
        WHERE STATUS='SESS_RECOVERING') C,
       (SELECT (ROUND(SUM(PGA_USED_MEM)/1024, 2)||' K' ) AS  "WPM(WORKING PROCESS MEMORY)"
      FROM V$PROCESS P, V$SESSION S WHERE P.NAME = 'WTHR' AND S.TYPE = 'WTHR') D
   /

   • Column (Alias) Descriptions

     [Table] Total Session Count Column Descriptions

     Column	Description
     Time	Current time
     WPM	Working Process Memory: PGA usage
     Total Session	Total number of sessions
     Running Session	Number of currently running sessions (cf. sessions only connected: Active)
     Recover Session	State of transaction recovery during booting or forced session termination

2. Session Count by Program

   [Example] Session Count by Program

   SELECT PROG_NAME AS PGM_NAME,
        TYPE,
        COUNT(1) AS PGM_CNT,
        COUNT(DECODE(STATUS, 'RUNNING', 1)) AS ACT_CNT,
        COUNT(DECODE(STATUS, 'ACTIVE', 0, 1)) AS INACT_CNT, 
        SUM(DECODE(WLOCK_WAIT, NULL, 0, 1)) AS LOCKWAIT
   FROM V$SESSION
   GROUP BY PROG_NAME, TYPE
   /

   • Column (Alias) Descriptions

     Column	Description
     PGM_NAME	Name of running program (e.g., Tibero Studio, tbsql, JDBC Thin Client, etc.)
     TYPE	Process type
     PGM_CNT	Program session count
     ACT_CNT	Active (RUNNING) session count
     INACT_CNT	Inactive session count (sessions other than running)
     LOCKWAIT	Number of sessions waiting due to locks

      

3. Number of Sessions Running on Server (e.g., Batch)

   [Example] Session Types and Counts Connected to DB Instance

   SET LINESIZE 120
   COL MACHINE FOR A30
   COL USERNAME FOR A30
   
   SELECT MACHINE, USERNAME, PROG_NAME, COUNT(1) CNT    
   FROM V$SESSION
   GROUP BY MACHINE, USERNAME, PROG_NAME;

    

4. Check Current Transactions
   • Check the list and related information of ongoing transactions.
     • [object_name] means the table name used in the current transaction.
     • [used_blk] means the number of blocks used by the current transaction.
     • [start_time] means the time the transaction started.

     • [Transaction Time] means the duration of the transaction.

        

• [Example] Check Current Transactions

  SELECT DISTINCT VS.SID,
         VS.SERIAL#,
         VS.USERNAME,
         VS.USERNAME || '.' || VA.OBJECT "OBJECT",
         VS.STATUS,
         VT.USED_BLK,
         VT.USN,
         VT.START_TIME,
         FLOOR(MOD((SYSDATE-VT.START_TIME)*24, 24))||':'||
         LPAD(FLOOR(MOD((SYSDATE-VT.START_TIME)*1440,60)), 2, 0)||':'||
         LPAD(FLOOR(MOD((SYSDATE - VT.START_TIME)*86400, 60)),2,0) AS "TRANSACTION TIME",
         VST.SQL_TEXT
  FROM V$SESSION VS,
     VT_TRANSACTION VT,
     V$SQLTEXT VST,
     V$ACCESS VA
  WHERE VT.SESS_ID = VS.SID
  AND VT.SESS_ID = VA.SID
  AND NVL(VS.SQL_ID,VS.PREV_SQL_ID) = VST.SQL_ID
  /

• Column (Alias) Descriptions

  [Table] Description of Currently Executing SQL Query

  Column	Description
  SID	ID of the session performing the transaction
  User Name	User of the session performing the transaction
  Object Name	List of tables used in the current transaction (one row per table if multiple tables are accessed in one transaction)
  Status	Status information of the session performing the transaction
  Transaction Time	Duration of the transaction
  SQL_TEXT	Currently executing SQL statement

1.6. Disk I/O Management

Purpose: Describes how to check information to understand disk I/O performance.

Current Environment

• The current system operates as Single (Backup configured using HA Cluster), and storage is configured with D (Data storage), E (Archive storage), and V (Online Backup storage) drives.

Configuration Method

• Remote desktop access (Windows) or Telnet (Unix, Linux) is required on the DB server to execute SQL and check disk I/O. (Tibero Studio Tool can be used)

Check Procedure

• File I/O

• Check the amount and time of I/O on data files

  [Example] File I/O Check

  SET LINESIZE 150
  COL TABLESPACE_NAME FOR A20 
  COL NAME FOR A40
  
  SELECT FL.TABLESPACE_NAME,      DF.NAME,
       FS.PHYRDS,
       FS.PHYWRTS,
       ROUND((PHYRDS / (SELECT SUM(PHYRDS)
                         FROM V$FILESTAT)) *100, 1) "P_READ(%)",
       ROUND((PHYWRTS / DECODE ((SELECT SUM(PHYWRTS)
                                   FROM V$FILESTAT), 0, 1, (SELECT SUM(PHYWRTS)  
                                                             FROM V$FILESTAT)))*100, 1) "P_WRITE(%)",
       ROUND((PHYRDS + PHYWRTS) / (SELECT SUM(PHYRDS) + SUM(PHYWRTS)
                   FROM V$FILESTAT) *100, 1) "TOTAL IO(%)" ,
       ROUND(FS.AVGIOTIM/1000, 3) "AVG_TIME(MSEC)"
  FROM V$DATAFILE DF,
       V$FILESTAT FS,
       DBA_DATA_FILES FL
  WHERE DF.FILE# = FS.FILE#
  AND DF.FILE# = FL.FILE_ID
  ORDER BY PHYRDS+PHYWRTS DESC
  /

  • Column (Alias) Descriptions

    Column	Description
    PHYRDS	Number of blocks read from disk
    PHYWRTS	Number of blocks written to disk
    P_READ	Frequency of reads on the datafile (percentage of total block reads)
    P_WRITE	Frequency of writes on the datafile (percentage of total block writes)
    TOTAL IO	Frequency of reads/writes on the datafile (percentage of total block reads/writes)
    AVG TIME	Average I/O time (original unit is usec, converted to msec in example)

• Guidelines
  • Check the amount and time of I/O on data files. Read (%), Write (%), Total IO (%) indicate the percentage share of the file among all database files for read, write, and combined operations.
  • For data files with long average I/O times, consider the performance of the disk where the file is located and check for possible I/O bottlenecks caused by many data files on the same disk.
  • Also, distribute data files that have heavy I/O loads across different devices to avoid concentration.
  • The number of online redo log switches by time can predict the amount of database transactions, helping identify peak transaction times.
  • It is advisable to schedule large batch jobs during times with fewer transactions.

• Reference

  [Table] File I/O Related Views

  Option	Description
  V$FILESTAT	Provides information on file reads/writes
  V$DATAFILE	Provides information on data files in the database
  DBA_DATA_FILES	Provides information on data file size, status, etc.
  V$LOG	Provides information on log groups

   

2. Reorg

2.1. Reorg Overview

Reorganization is performed on tables with many extents or row chains, and indexes with blevel 4 or higher, as these can cause performance degradation. Periodic reorg operations should be performed to operate the database efficiently and optimally.

Reorg can be performed in various ways; here, the shrink function and DBMS_REDEFINITION are explained.

Configuration Method

• This function is available upon installation; no separate configuration is needed.

2.2. Reorg Target Selection

Reorg operations are performed on tables where the size occupied by each table differs significantly from the estimated table size.

• (row count - row max length) / (table block count - block size)
• Reorg target query
  • Tables with extent size exceeding estimated table size by more than 100MB and where the ratio of estimated table size to extent size is 50% or less are selected.
  • Reorg target queries are applied to tables with collected statistics.

[Example] Reorg Target Selection Query

SET LINESIZE 160
COL OWNER FOR A15
COL TABLE_NAME FOR A15

SELECT *
FROM ( SELECT EXT.OWNER,
             TBL.TABLE_NAME,
             TBL.TABLE_TYPE,
             TO_CHAR(TBL.LAST_ANALYZED,'YYYY/MM/DD HH24:MI:SS') LAST_ANALYZED,
             TBL.NUM_ROWS,
             ROUND(EXT.EXTENTS_SIZE/1024/1024,2) AS "EXTENTS_SIZE(MB)",
             ROUND(TBL.MAX_DATA_SIZE/1024/1024, 2) AS "MAX[DATA_SIZE(MB)]",
             ROUND((EXT.EXTENTS_SIZE - TBL.MAX_DATA_SIZE)/1024/1024, 2) AS "DIFF_SIZE(MB)",
             ROUND((TBL.MAX_DATA_SIZE / EXT.EXTENTS_SIZE * 100), 2) AS "DATA_RATE(%)"
     FROM   ( SELECT OWNER,
                     SEGMENT_NAME,
                     SUM(BYTES) AS EXTENTS_SIZE,  -- TABLE EXTENTS TOTAL SIZE
                     SUM(BLOCKS) AS EXTENTS_BLOCK_COUNT --TABLE EXTENTS BLOCK TOTAL COUNT
               FROM DBA_EXTENTS
               WHERE OWNER NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
               AND SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITON')
               GROUP BY OWNER, SEGMENT_NAME ) EXT,
             ( SELECT A.OWNER,
                     A.TABLE_NAME,
                     DECODE(PARTITIONED, 'YES', 'PARTITION TABLE', 'TABLE') AS TABLE_TYPE,
                     LAST_ANALYZED,
                     ROUND(NUM_ROWS) AS NUM_ROWS,
                     BLOCKS,
                     AVG_ROW_LEN,
                     MAX_ROW_LEN,
                     (ROUND(NUM_ROWS) * MAX_ROW_LEN) AS MAX_DATA_SIZE   -- MAX DATA SIZE
               FROM DBA_TABLES A,
                   ( SELECT OWNER,
                             TABLE_NAME,
                             SUM(DATA_LENGTH) AS MAX_ROW_LEN  -- 1 ROW MAX SIZE
                     FROM DBA_TBL_COLUMNS
                     WHERE OWNER NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
                     GROUP BY OWNER, TABLE_NAME ) B
             WHERE A.OWNER = B.OWNER
             AND A.TABLE_NAME = B.TABLE_NAME ) TBL
     WHERE 1=1
     AND EXT.OWNER=TBL.OWNER
     AND EXT.SEGMENT_NAME=TBL.TABLE_NAME ) X
WHERE 1=1
AND "DIFF_SIZE(MB)" > 100
AND "DATA_RATE(%)" < 50
ORDER BY "DATA_RATE(%)" DESC, "DIFF_SIZE(MB)" DESC
/

2.3. Reorg Operating Procedures

Applicable Targets

• Normal tables, indexes, LOBs, materialized views

Pre-Reorg Checklist

• There are essential checks before starting the REDEFINITION reorg function:
• Whether backup of target objects has been performed
• Whether online table copy or export backup has been performed

Check Data Size

SELECT OWNER,
       SEGMENT_NAME,
       SUM(BYTES) AS EXTENTS_SIZE,
       SUM(BLOCKS) AS EXTENTS_BLOCK_COUNT
     FROM DBA_EXTENTS
     WHERE 1=1
     AND OWNER NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
     AND SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION')
     AND SEGMENT_NAME = '${OBJECT_NAME}'
     GROUP BY OWNER, SEGMENT_NAME
/

Check Index Status

SELECT INDEX_NAME, LAST_ANALYZED, NUM_ROWS, STATUS 
FROM DBA_INDEXES
WHERE TABLE_OWNER = '${USER}'
AND TABLE_NAME = '${OBJECT_NAME}'
ORDER BY TABLE_OWNER, TABLE_NAME, INDEX_NAME
/

Check Index Columns

SELECT INDEX_OWNER, INDEX_NAME, COLUMN_NAME
FROM DBA_IND_COLUMNS
WHERE TABLE_OWNER = '${USER}'
AND TABLE_NAME = '${OBJECT_NAME}'
ORDER BY TABLE_OWNER, TABLE_NAME, INDEX_OWNER, INDEX_NAME, COLUMN_POSITION
/

Check Table-Related Trigger Status

SELECT TRIGGER_NAME, STATUS
FROM DBA_TRIGGERS
WHERE TABLE_OWNER = '${USER}'
AND TABLE_NAME = '${OBJECT_NAME}'
/

Check Privileges

SELECT *
FROM DBA_TAB_PRIVS
WHERE OWNER = '${USER}'
AND TABLE_NAME = '${OBJECT_NAME}'
/

Check Table Status

SELECT TABLE_NAME, LAST_ANALYZED, NUM_ROWS
FROM DBA_TABLES
WHERE OWNER = '${USER}'
AND TABLE_NAME = '${OBJECT_NAME}'
/

Check Table Constraints

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, STATUS 
FROM DBA_CONSTRAINTS
WHERE OWNER = '${USER}'
AND TABLE_NAME = '{$OBJECT_NAME}'
ORDER BY 1,2,3
/

Check Data Count

SELECT COUNT(*) FROM ${USER}.${OBJECT_NAME};

3. Tuning

3.1. DB Tuning Overview

Purpose: Describes the goal of DB tuning.

• The best tuning is to meticulously design the system and application. Most performance improvements come from application tuning. The main goal of tuning is to allow users to get responses to their statements as quickly as possible. This is usually measured in terms of response time, throughput, or concurrency.
• Tibero provides the TPR (Tibero Performance Repository) feature, similar to Oracle's AWR (Automatic Workload Repository).
   

Configuration Method

1. Snapshot Storage
   • Various performance statistics such as V$SYSSTAT, V$SYSTEM_EVENT must be periodically (hourly) stored in tables.
      
2. Storage Environment and Principles
   • Changes are queried from statistics-related views and current values are stored in temporary tables.
   • Minimal data is stored in TPR-related tables, and performance is improved by querying views once via multi-table inserts.
   • Delete previously stored temporary table data.
   • Delete statistical data older than 7 days.
   • Data size per snapshot (max): approx. 150kB
   • Maximum space occupied: 150kb * 24 * 7 = 25MB
      
3. Snapshot Storage Function
   • Snapshot storage stores various Tibero performance statistics like V$SYSSTAT, V$SYSTEM_EVENT, V$SQLSTATS, V$SGASTAT periodically (usually every hour) in tables.
   • Stored information is called snapshots.
   • Snapshot information is used to generate performance analysis reports.
   • Reports can be generated for specified periods to diagnose DB performance problems.
4. Session State Storage Function
   • Session state storage saves IDs and waiting events of currently running sessions every second in memory.
   • This information can be queried via V$ACTIVE_SESSION_HISTORY view.
   • This view allows more detailed diagnosis of DB performance issues.
   • Session state storage can be vulnerable under load, so increasing the interval is recommended based on monitoring results.

3.2. Tuning Target Selection and Execution Method

Learn about TPR features for selecting tuning targets and methods to perform tuning.

Configuration Method

1. TIP Settings

   • To use snapshot storage, set 'TIBERO_PERFORMANCE_REPOSITORY=Y' in the tip file,

     and to use session state storage, set 'ACTIVE_SESSION_HISTORY=Y'.

     This is sufficient in most cases.

     TOTAL_SHM_SIZE should be set above 2GB.
      

   • Related TIP Settings (Tibero 7 version)
     • TIBERO_PERFORMANCE_REPOSITORY: Setting to 'Y' enables snapshot storage. (Default: Y)
        
     • TPR_SNAPSHOT_SAMPLING_INTERVAL: Sets snapshot extraction interval. (Default: 60 minutes)
        

     • TPR_SNAPSHOT_RETENTION: Sets maximum snapshot retention period. (Default: 7 days)

        

     • TPR_SNAPSHOT_TOP_SQL_CNT: Sets number of top SQLs in report. (Default: 5)
        
     • TPR_SEGMENT_STATISTICS: Setting to 'Y' enables segment-level stat collection in TPR. (Default: N)
        
     • TPR_SNAPSHOT_TOP_SEGMENT_CNT: Sets number of top segments in report. (Default: 5)
        
     • TPR_METRIC: Setting to 'Y' enables TPR metric function. (Default: N)
        
     • TPR_AGGREGATION: Setting to 'Y' enables TPR aggregation function. (Default: N)
        
     • ACTIVE_SESSION_HISTORY: Setting to 'Y' enables session state storage. (Default: N)
        
     • _ACTIVE_SESSION_HISTORY_SAMPLING_INTERVAL: Sets session state storage interval. (Default: 1 second)
        
2. Snapshot

   • TPR takes snapshots automatically at set intervals, but snapshots can be manually created at any time.

     [Example] Manual Snapshot Creation

     SQL> exec dbms_tpr.create_snapshot();

   • Operating Procedure

     Preparation

     1. Query the SNAPSHOT table to check start and end times for the desired period.

        [Example] Query TPR Snapshot Start and End Times

        SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
        
        SQL> select * from _tpr_snapshot;

     2. Generate TPR Report

        • Set the desired begin and end times, and execute the following in tbsql to generate a performance analysis report.
          Pass BEGIN_INTERVAL_TIME and END_INTERVAL_TIME as parameters.

          SQL> exec DBMS_TPR.REPORT_TEXT(BEGIN_INTERVAL_TIME, END_INTERVAL_TIME );

           

        • If no other settings are specified, the performance report is generated as a file in the default path: $TB_HOME/instance/$TB_SID/tpr_report.[mthr_pid].[current_time]

         

• TPR Analysis

  1. Cache Summary

     [Example] TPR (Cache Summary)

     3.1 Instance Efficiency
       ===============================================
                                   Value
                                 --------
            Buffer Cache Hit %:    100.00
                 Library Hit %:     92.73
                      PP Hit %:     95.32
                   Latch Hit %:    100.00
              Redo Alloc Hit %:    100.00
               Non-Parse CPU %:     98.27
                    Parse Time:  2,182.01
                       DB Time:     16.53

     • Tuning Guide
       • Buffer Cache Hit
         • This is the buffer cache hit ratio within TSM. If low, consider increasing buffer cache size or check for unnecessary full scans on large tables.
            
       • PP Hit

         • This relates to SQL reuse hit ratio (%).
           If low, many static queries cause frequent hard parsing; applications should be modified to use bind queries for soft parsing.

            

  2. Wait Events by Wait Time

     [Example] TPR (Wait Events by Wait Time)

     7.3 Wait Events by Wait Time
       ================================================================================
                                                     Time-         Wait        Max         Avg          Waits    DB
                             Event            Waits  out(%)       Time(s)    Wait(ms)    Wait(ms)         /TX  Time(%)
      -----------------------------  ---------------  ------  ------------  ----------  ----------  ----------  -------
               block disk read time            1,316     0.0          0.81        35.0        0.62         9.6     4.92
          log flush wait for commit              129     0.0          0.53        72.0        4.11         0.9     3.21
                          ckpt wait                2     0.0          0.32       165.0      159.00         0.0     1.92
         multi block disk read time                8     0.0          0.01         6.0        1.63         0.1     0.08
       direct path write wait time                4     0.0          0.00         0.0        0.00         0.0     0.00
                   connection wait            8,083     0.0     52,502.86   3.599E+06     6,495.5       59.00
                 wthr receive client msg            1,562     0.0      8,327.81   275,828.0     5,331.5       11.40
                         dbwr idle           39,431    99.6      7,199.68     1,088.0       182.6      287.82
                         RSRC idle            1,200   100.0      3,600.80     3,060.0     3,000.7        8.76
                 notification coordinator            3,598   100.0      3,600.54     1,115.0     1,000.7       26.26
                         notification sender            3,598   100.0      3,600.52     1,115.0     1,000.7       26.26
                   TPM collector idle            3,598   100.0      3,600.49     1,115.0     1,000.7       26.26
                         ACTM idle              360   100.0      3,600.24    10,036.0    10,000.7        2.63
                 job scheduler idle               12    50.0      3,600.18   600,003.0   300,015.1        0.09

     • Tuning Guide
       • Top wait events based on wait time.
       • Shows wait events consuming the most wait time.
       • Check which events consume the most time by name.
          

  3. SQL Ordered by Elapsed Time

     [Example] TPR (SQL Ordered by Elapsed Time)

     8.3 SQL Ordered by Elapsed Time
     ================================================================================
     === SQL #1 ===
     Overall Stats
     =============
           Elapsed       Execution          Time         DB          SQL        Plan
           Time(s)           Count      /Exec(s)       Time(%)  Hash Value  Hash Value         SQL ID  Module
     --------------  --------------  ------------  ----------  ----------  ----------  -------------  --------------------
               2.06             216            0.01       12.45   775257190  1090908829  fngnts8r3az36  TPR SESSION
     SQL Text
     ========
     SELECT /*+ default_stat index(HH _DD_HIST_HEAD_IDX1) use_nl_with_index(H _DD_HISTOGRAM_IDX1) ordered */ 
     HH.BUCKET_CNT, HH.NULL_CNT, HH.SAMPLE_SIZE,HH.DISTINCT_CNT, HH.LOW_VAL, HH.HIGH_VAL, HH.AVG_COL_SIZE, NVL(H.BUCKET, 0), NVL(H.DISTINCT_CNT, HH.DISTINCT_CNT), H.END_POINT_ACTUAL 
     FROM _DD_HIST_HEAD HH, _DD_HISTOGRAM H WHERE HH.OBJ_ID = ? AND HH.COL_NO = ? AND HH.HIST_HEAD_ID = H.HIST_HEAD_ID(+) ORDER BY BUCKET
         Execution Plan
         ==============
           ID    Output Rows    Elapsed(ms)      CR Gets      CU Gets Plan
         ----- -------------- -------------- ------------ ------------ --------------------------------------------------------------------------------
             1              0          0.00            0            0 ORDER BY (SORT) (Cost:6, %%CPU:0, Rows:15)
             2              0          0.00            0            0   INDEX JOIN (LEFT OUTER) (Cost:6, %%CPU:0, Rows:15)
             3              0          0.00            0            0     TABLE ACCESS (ROWID): _DD_HIST_HEAD (Cost:4, %%CPU:0, Rows:1)
             4              0          0.00            1            0       INDEX (UNIQUE SCAN): _DD_HIST_HEAD_IDX1 (Cost:3, %%CPU:0, Rows:1)
             5              0          0.00            0            0     TABLE ACCESS (ROWID): _DD_HISTOGRAM (Cost:15, %%CPU:0, Rows:14)
             6              0          0.00            0            0       INDEX (RANGE SCAN): _DD_HISTOGRAM_IDX1 (Cost:3, %%CPU:0, Rows:14)
     : Plan statistics is from snapshot #1 (2023/11/17 00:32:46)

     • Tuning Guide
       • Check the execution plan of SQL queries that took a long time and tune using hints or by creating/modifying/dropping indexes.
          

  4. I/O Statistics

     7.9 Tablespace I/O Statistics
     ================================================================================
     Tablespace
     ---------------
                       Avg.        Avg.      Avg.                    Avg.        Avg.      Avg.
           Reads   Reads/s    Read(ms)   Blks/Rd        Writes   Write/s   Write(ms)   Blks/Wt
     ------------  --------  ----------  --------  ------------  --------  ----------  --------
     SYSTEM
           1,340      0.37        0.61      1.08           227      0.06       10.18      1.00
     SYSSUB
               69      0.02        0.70      1.00             8      0.00        1.43      1.00
     UNDO
               11      0.00        2.99      1.00           232      0.06       13.29      1.00
     USR
               6      0.00        1.67      1.00            33      0.01        9.81      1.00
     TBS_USER2_002
               2      0.00        0.40      1.00            28      0.01       10.16      1.00
     TBS_USER1_001
               2      0.00        0.26      1.00            27      0.01        9.89      1.00
     TEMP
               0      0.00        0.00      0.00             0      0.00        0.00      0.00
       7.10 File I/O Statistics
     ================================================================================
     Tablespace       Filename
     ---------------  ------------------------------------------------------------
                       Avg.        Avg.      Avg.                    Avg.        Avg.      Avg.
           Reads   Reads/s    Read(ms)   Blks/Rd        Writes   Write/s   Write(ms)   Blks/Wt
     ------------  --------  ----------  --------  ------------  --------  ----------  --------
     SYSSUB           /DATA/tibero_data/syssub001.dtf
               69      0.02        0.70      1.00             8      0.00        1.43      1.00
     SYSTEM           /DATA/tibero_data/system001.dtf
           1,340      0.37        0.61      1.08           227      0.06       10.18      1.00
     TBS_USER1_001    /tbdata/tbs_user_001.dtf
               2      0.00        0.26      1.00            27      0.01        9.89      1.00
     TBS_USER2_002    /tbdata/tbs_user_002.dtf
               2      0.00        0.40      1.00            28      0.01       10.16      1.00
     TEMP             /DATA/tibero_data/temp001.dtf
               0      0.00        0.00      0.00             0      0.00        0.00      0.00
     UNDO             /DATA/tibero_data/undo001.dtf
               11      0.00        2.99      1.00           232      0.06       13.29      1.00
     USR              /DATA/tibero_data/usr001.dtf
               6      0.00        1.67      1.00            33      0.01        9.81      1.00

     • Tuning Guide
       • You can check tablespace and datafile I/O.
       • If I/O is concentrated on specific tablespaces or datafiles, consider distributing tables and indexes.

3.3. Parameter Tuning

Location Parameters

CONTROL_FILES

• Specify the location of control files with absolute paths.

• For failure recovery, it is recommended to duplicate control files on separate disks.

  CONTROL_FILES='/data01/Tibero_data/c1.ctl, /data02/Tibero_Data/c2.ctl'

   

DB_CREATE_FILE_DEST

• Specify the location where datafiles are stored using absolute paths.
• It is recommended to distribute datafiles across multiple disks to spread I/O load.

LOG_ARCHIVE_DEST

• Specify the location where archive log files are stored using absolute paths.
• It is recommended to separate this location from datafile and logfile locations.

OPTIMIZER Settings

OPTIMIZER_MODE

• Parameter to set the optimizer mode.
• FIRST_ROWS_N: Optimizes to quickly return the first n results.
• ALL_ROWS: Determines the execution method with the least cost to return all results.

Size Parameters

DB_BLOCK_SIZE

• Specifies the size of database blocks.
• Settable when creating the database; cannot be changed afterward.
• Typically 8K for OLTP, 16K or 32K for OLAP or DW environments.

TOTAL_SHM_SIZE

• Defines the total shared memory size used by the instance.

DB_CACHE_SIZE

• Defines the size of the database cache.
• Usually set to about 2/3 of TOTAL_SHM_SIZE for single instances, and about 1/2 for TAC.
• A very large database cache increases checkpoint costs but improves cache hit ratio and reduces I/O.
• A very small database cache reduces checkpoint costs but may increase I/O.

Execute Memory Parameters

SORT_AREA_SIZE

• Sets the size for sort operations.
• Ignored if EX_MEMORY_AUTO_MANAGEMENT is set to Y.

HASH_AREA_SIZE

• Sets the size for hash operations.
• Ignored if EX_MEMORY_AUTO_MANAGEMENT is set to Y.

EX_MEMORY_AUTO_MANAGEMENT

• Determines whether execute memory is managed automatically by the Memory Tuner or manually via SORT_AREA_SIZE, HASH_AREA_SIZE, etc.

EX_MEMORY_HARD_LIMIT

• Defines the size of execute memory.

EX_MEMORY_SOFT_LIMIT_RATIO

• Defines the percentage of execute memory available for sort, join, etc. (default 80%)

EX_MEMORY_OPERATION_LIMIT

• Defines the maximum execute memory allocatable to a single sort or join operation (default 5%).
• Prevents a single operation from using excessive execute memory.

EX_MEMORY_DRIFT_CHECK_INTERVAL

• Checks for sudden memory changes every 0.1 seconds (default 2).
• Detects if execute memory changes exceed 10% of total EX_MEM and marks it as drift.

EX_MEMORY_COMPENSATE_INTERVAL

• Defines the interval in seconds to reset SOFT_LIMIT_RATIO and OPERATION_LIMIT (default 30).

Other Parameters

CURSOR_SHARING

• Sets whether similar SQL statements share cursors.
• EXACT: Share only if text is exactly the same.
• FORCE: Share even if literals differ in WHERE clause.
• Cursor sharing via CURSOR_SHARING is less recommended than using bind variables in applications.

DB_FILE_MULTIBLOCK_READ_COUNT

• Defines the number of blocks read at once during full table scans.
• Reduces I/O calls and improves full table scan performance. Default: 8, set at instance level.
• Limits vary by platform; verify accordingly.

UNDO_RETENTION

• Sets the time in seconds that undo information is retained on disk before reuse.
• Helps reduce errors like 'Snapshot Too Old'.
• If undo tablespace has enough space or can be extended, undo info is not reused until transaction expires.
• Even if space is insufficient, transactions do not fail; undo space from committed transactions can be reused. Errors occur only if space is still insufficient.

3.4. Tuning References

[Table] Snapshot Storage Related Views

[Table] Session State Storage Related Views