Document Type | Technical Information
Category | Monitoring/Inspection
Applicable Product Version | 7FS02PS
Document Number | TMOTI047
Overview
This document provides information and guidelines on performing TAC monitoring.
Method
Chapter 1 Introduction to Tibero
This chapter explains basic Tibero database information and TSM information.
1.1. Tibero Instance and Database Information
This section covers how to check basic information about the TAC database instance and database.
SELECT i.version,
i.instance_name ,
d.name "DB_name" ,
i.status,
ch.value "NLS_CHARACTERSET" ,
d.log_mode ,
d.create_date "DB_create_date" ,
d.current_tsn ,
d.ckpt_tsn ,
l.edition,
l.expire_date
FROM gv$instance i,
v$database d,
_vt_license l,
(SELECT value
FROM _vt_version
WHERE name='BUILD_NUMBER') bd ,
(SELECT value
FROM _dd_props
WHERE name='NLS_CHARACTERSET') ch;
VERSION INSTANCE_N DB_name STATUS NLS_CHARACTERSET LOG_MODE DB_create_date CURRENT_TSN CKPT_TSN EDITION EXPIRE_DATE
-------- ---------- --------------- ---------------- ---------------- ------------ --------------- ----------- ---------- ---------------- -----------
7 tibero0 tac NORMAL UTF8 ARCHIVELOG 2026/03/25 12523168 12408253 enterprise 2026/06/16
7 tibero1 tac NORMAL UTF8 ARCHIVELOG 2026/03/25 12523168 12408253 enterprise 2026/06/16
| Item | Description |
|---|---|
| VERSION | The build number of Tibero. |
| INSTANCE_NAME | The name of the database instance. |
| DB name | The SID when the database was created. (It is the name of the database written in the control file and is always the same as the instance name.) |
| STATUS | The current boot mode status of the Tibero server. (Examples: NOMOUNT, MOUNT, NORMAL) |
| NLS_CHARACTERSET | The encoding used when data is stored in the database. Determined by the Create Database statement. (Supports character sets such as UTF-8, MS949(EUC-KR), ASCII, etc.) |
| LOG_MODE | Indicates whether Redo Log archiving is enabled. |
| DB create_date | The creation date of the database. |
| CURRENT_TSN | The current TSN value. |
| CKPT_TSN | The time when the last checkpoint occurred (= TSN). |
| EDITION | License edition information. |
| EXPIRE_DATE | The license expiration date. |
1.2. TAC DB TSM (Tibero Shared Memory) Information
SELECT Inst_id,
TSM as "TSM(Tibero Shared Memory)"
FROM (SELECT Inst_id,
'TSM(Tibero Shared Memory) : '||total/1024 || ' K' as TSM
FROM gv$sga
WHERE name = 'SHARED MEMORY'
UNION ALL
SELECT Inst_id,
'Shared Cache Size : '||ROUND(total/1024, 0) || ' K' as TSM
FROM gv$sga
WHERE name = 'SHARED POOL MEMORY'
UNION ALL
SELECT inst_id,
'Database Buffer Size : '||value/1024 || ' K' as TSM
FROM GV$PARAMETERS
WHERE name='DB_CACHE_SIZE'
UNION ALL
SELECT inst_id,
'DB Block Size : '||value/1024 || ' K' as TSM
FROM GV$PARAMETERS
WHERE name = 'DB_BLOCK_SIZE'
UNION ALL
SELECT inst_id,
'Redo Log BUffer Size : '||value/1024 || ' K' as TSM
FROM GV$PARAMETERS
WHERE name = 'LOG_BUFFER'
UNION ALL
SELECT inst_id,
'WPM(Working Process Memory) : ' || ROUND(value/1024, 0) || ' K'
FROM GV$PARAMETERS
WHERE name='EX_MEMORY_HARD_LIMIT')
ORDER BY 1;
Executing this query returns the following results, showing the TSM information.
INST_ID TSM(Tibero Shared Memory)
------- ---------------------------------------------------------------
1 TSM(Tibero Shared Memory) : 15728640 K
1 Shared Cache Size : 6727944 K
1 Database Buffer Size : 7864320 K
1 DB Block Size : 8 K
1 Redo Log Buffer Size : 10240 K
1 WPM(Working Process Memory) : 0 K
2 TSM(Tibero Shared Memory) : 15728640 K
2 Shared Cache Size : 6727944 K
2 Database Buffer Size : 7864320 K
2 DB Block Size : 8 K
2 Redo Log Buffer Size : 10240 K
2 WPM(Working Process Memory) : 0 K
| Item | Description |
|---|---|
| TSM (Tibero Shared Memory) | The total shared memory size used by Tibero, adjusted by the Init parameter (TOTAL_SHM_SIZE). TSM = Shared Cache Size + Database Buffer Size + Fixed Memory (Fixed Memory: Size determined at runtime based on initial global variables or thread count.) |
| Shared Cache Size | The shared memory used by Tibero (Shared Pool: Library Cache, DD Cache) for various purposes. Calculated as (TSM - Fixed Memory - Database Buffer Size). There is no fixed limit between Shared Cache and Library Cache regions. |
| Database Buffer Size | The size of the database buffer cache, adjustable via the DB_CACHE_SIZE parameter in the environment file. |
| DB Block Size | The size of the database block, adjustable via the Init parameter (DB_BLOCK_SIZE). Cannot be changed after database creation. |
| Redo Log Buffer Size | The size of the memory space for storing Redo Logs, adjustable via the Init parameter (LOG_BUFFER). |
| WPM (Working Process Memory) | The total physical memory available for Tibero, adjustable via the Init parameter (EX_MEMORY_HARD_LIMIT). |
Chapter 2 Memory
This chapter explains the memory information used in Tibero.
2.1. Database Buffer Cache Hit Ratio
Check the hit ratio of the database buffer cache.
The buffer cache hit ratio indicates the percentage of memory blocks accessed by users that were already cached, allowing access without physical I/O.
If the buffer cache hit ratio is below the recommended value, it means the allocated database buffer size is too small or there are applications causing excessive I/O.
2.1.1 TAC DB
Below is an example query for TAC DB.
SELECT ins.inst_id,
pr1.value + pr2.value "Physical read",
bg1.value+bg2.value+bg3.value+bg4.value+bg5.value "Logical read" ,
TRUNC((1-((pr1.value+pr2.value)/ (bg1.value+bg2.value+bg3.value+bg4.value+bg5.value)) ) *100, 2)
"hit(MORE THAN 60-70%)"FROM (select distinct inst_id
from gv$sysstat
) ins,
gv$sysstat pr1,
gv$sysstat pr2,
gv$sysstat bg1,
gv$sysstat bg2,
gv$sysstat bg3,
gv$sysstat bg4,
gv$sysstat bg5
WHERE pr1.name = 'block disk read'
nd pr2.name = 'multi block disk read - blocks'
and bg1.name = 'consistent block gets'
and bg2.name = 'consistent multi gets - blocks'
and bg3.name = 'block gets (CRX)'
and bg4.name = 'current block gets'
and bg5.name = 'current block gets - no wait'
and pr1.inst_id = ins.inst_id
and pr2.inst_id = ins.inst_id
and bg1.inst_id = ins.inst_id
and bg2.inst_id = ins.inst_id
and bg3.inst_id = ins.inst_id
and bg4.inst_id = ins.inst_id
and bg5.inst_id = ins.inst_id;Result
INST_ID Physical read Logical read hit(MORE THAN 60-70%)
---------- ------------- ------------ ---------------------
101 556 4799 88.41
102 415 5621 92.61
2 rows selected.
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | The number of the Tibero instance. - Assigned according to startup order. - Different from the instance_number column in v$instance. |
| Physical read | The number of times data blocks were read from disk. |
| Logical read | The number of times data blocks were requested from the buffer cache. |
| hit | The buffer cache hit ratio, calculated as (1 - physical read / logical read) * 100. |
The recommended buffer cache hit ratio varies depending on the type of database application such as OLTP, DSS, or DW.
Generally, an OLTP system is considered good if the hit ratio is over 90%, and DSS or DW systems are good with 80-85% or higher. However, for systems mainly processing batch jobs, a hit ratio below 50% does not necessarily indicate a problem.
If the buffer cache hit ratio is below the standard value, check the OS memory availability and increase the DB_CACHE_SIZE in the tip file within the range where paging and swapping due to memory shortage do not occur.
However, it is practically impossible to cache all data blocks on disk, so increasing the buffer cache size is not always the best solution.
Especially if applications frequently perform full scans of many disk blocks, maintaining a high hit ratio is impossible; inefficient access patterns should be identified and continuously tuned.
2.2. SQL Cache Hit Ratio
This section explains how to check the SQL Cache hit ratio.
2.2.1 TAC DB
You can check the SQL Cache hit ratio per TAC node with the following query.
SELECT inst_id,
namespace,
gets,
gethits,
gethitratio,
pins,
pinhits,
pinhitratio
FROM GV$LIBRARYCACHE
WHERE namespace = 'SQL AREA'
Result
INST_ID NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS PINHITRATIO ------- --------- ------- ------- ----------- ------- ------- ----------- 2 SQL AREA 611,279 608,470 99.54 618,230 618,230 100 1 SQL AREA 595,924 595,391 99.91 595,395 595,395 100
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | The number of the Tibero instance. - Assigned according to startup order. - Different from the instance_number column in v$instance. |
| NAMESPACE | Items of Library Cache and DD Cache. (SQL AREA: Library Cache) |
| GETS | Total access count to the cache. |
| GETHITS | Number of cache hits. |
| GETHITRATIO | The cache hit ratio. Calculated as (PINHITS / PINS) * 100. |
| PINS | Number of requests to pin objects in the cache. |
| PINHITS | Number of hits on objects already pinned in the cache. |
| PINHITRATIO | The ratio of PINHITS to PINS, representing the PIN CACHE HIT ratio. A result close to 1 means most objects in the system are pinned and accessed in the cache. |
It is recommended to maintain a Data Dictionary Cache hit ratio above 90%.
If the hit ratio is below the standard value, you can increase the shared cache size by adjusting the ratio between the initialization parameters TOTAL_SIZE and DB_CACHE_SIZE to improve the hit ratio.
2.3. TAC DB Dictionary Cache Hit Ratio
Check the Dictionary Cache hit ratio.
Below is an example query for TAC DB.
SELECT INST_ID,
TO_CHAR(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Current Time",
ROUND( ( sum(hit_cnt) - sum(miss_cnt) ) / sum(hit_cnt) * 100, 1)
"Dictionary Cache Hit Ratio(%)"FROM gv$rowcache
GROUP BY INST_ID;
The results are as follows.
INST_ID Current Time Dictionary Cache Hit Ratio(%) ------- ------------------- ----------------------------- 1 2026/04/10 14:53:25 99.9 2 2026/04/10 14:53:25 99.8
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | The number of the Tibero instance. - Assigned according to startup order. - Different from the instance_number column in v$instance. |
| Current Time | The time when the SQL was executed. |
| Hit Ratio | The hit ratio of the Data Dictionary Cache. |
The Data Dictionary Cache is an area that caches data dictionary information and is allocated from the shared cache. Its size cannot be specified separately by the user. Tibero prioritizes allocation to the Dictionary Cache from the shared cache, so if the Library Cache hit ratio is good, the Dictionary Cache hit ratio is also maintained at a good level.
The Dictionary Cache hit ratio is meaningful only after some time has passed since the instance startup and is recommended to be maintained above 90%.
If the hit ratio is below the standard value, you can increase the shared cache size by adjusting the ratio between the initialization parameters TOTAL_SHM_SIZE and DB_CACHE_SIZE to improve the hit ratio.
2.4. TAC DB Shared Cache Free Memory
This section explains how to check the free memory size of the TAC shared cache.
Example query for checking TAC DB shared cache free memory size:
SELECT inst_id,
round(total/1024/1024, 1) "Shared Cache Total (MB)",
round(used/1024/1024, 1) "Used (MB)",
round((total - used)/1024/1024, 1) "free (MB)"FROM gv$sga
WHERE name='SHARED POOL MEMORY';
Result
INST_ID Shared Cache Total (MB) Used (MB) Free (MB)
---------- ----------------------- ---------- ----------
101 2704.6 75.2 2629.4
102 2704.6 74.3 2630.3
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | The number of the Tibero instance. - Assigned according to startup order. - Different from the instance_number column in v$instance. |
| Shared Cache Total | The total memory size of the shared cache. |
| Used | The amount of memory currently in use. |
| free | The amount of available free memory. |
The shared cache area consists of the library cache and dictionary cache.
Since the shared cache is basically an area for caching, it keeps existing objects in the cache and continues to use them as long as free space remains.
Therefore, if the library cache or dictionary cache hit ratio is good (90% or higher), it is normal for the free memory size to be low during regular operating hours.
Conversely, if free memory is always large, it means the shared cache is set unnecessarily large. Also, if free memory suddenly increases temporarily, it indicates heavy fragmentation and flushing of many objects simultaneously, which may signal a system problem.
To prevent memory fragmentation in the shared cache, avoid using large PSM blocks, and use bind variables for literal SQL statements.
Chapter 3 Tibero Wait Events
This chapter explains wait event information occurring in Tibero.
3.1. V$SYSTEM_EVENT
Tibero defines various wait events to measure waiting phenomena during processing by server and background processes and uses this data to manage process execution. Each wait event occurrence and wait time are stored internally and can be queried.
The v$system_event view allows checking accumulated event occurrence status at the system level since instance startup.
To check cumulative information by individual sessions, use v$session_event. To check current or recent wait event information at the session level, use v$session_wait.
3.1.1 TAC DB
Below is an example query to retrieve Tibero events in TAC DB.
SELECT inst_id,
name, total_waits,
time_waited/1000000 "time_waited",
average_wait/1000000 "average_wait",
total_timeouts
FROM gv$system_event
WHERE Total_waits>0
ORDER BY inst_id, time_waited DESC ;
Result
INST_ID NAME TOTAL_WAITS time_waited average_wait TOTAL_TIMEOUTS ------- ------------------------- ------------- ------------ -------------- ------------- 1 WE_ACF_CMPT 1902 5.854036 .003077832 0 1 WE_CONN_IDLE 522 1.556958 .002982678 0 1 WE_ACF_ATH 720 .499886 .000694286 0 1 WE_ACF_GC 462 .473068 .001023957 462 1 WE_DBWR_IDLE 2497 .46998 .000188218 2477 1 WE_NMON_IDLE 232 .23756 .001023966 232 1 WE_NTF_SNDR_IDLE 232 .237513 .001023763 232 1 WE_TPM_COLLECTOR_IDLE 232 .237505 .001023728 232 1 WE_TASK_MGR_IDLE 234 .236753 .001011765 231 1 WE_ACF_RECONF 49 .235883 .004813939 46 1 WE_ACTM_IDLE 23 .235396 .010234609 23 1 WE_LGWR_IDLE 94 .235207 .002502202 77 1 WE_LGWR_SLAVE 95 .235205 .002475842 0 1 WE_RSRC_IDLE 78 .23469 .003008846 78 1 WE_CKPT_IDLE 231 .234052 .001013212 229 1 WE_TPM_SENDER_IDLE 7 .211622 .030231714 7 1 WE_LARC_IDLE 8 .211508 .0264385 7 1 WE_WTHR_RECV 9 .105829 .011758778 0 1 WE_BG_READY 17 .017796 .001046824 0 1 WE_JOB_SCHED_IDLE 4 .003438 .0008595 3 1 WE_ACF_CRAS 3 .003133 .001044333 0 1 WE_ACF_RCF 16 .002916 .00018225 0 .....
2 WE_GV_REPLY 5132 .000007 0 0
2 WE_CCC_AST_CR 2631 .000003 0 0
2 WE_SPIN_SHP_ALLOC_LC 10 .000002 0 0
2 WE_WLOCK_SEQ_GET_NEXTVAL 1 .000001 0 0
2 WE_WLOCK_EXPAND_LKBSET 1 .000001 0 0
2 WE_GV_REQ 1277 .000001 0 0
2 WE_CR_BUF_BUSY_LOCAL 1 0 0 0
2 WE_PE_DEQ 411 0 0 0
2 WE_JC_SSGMT_READ_TIME 14 0 0 0
2 WE_ACF_GMC 289 0 0 0
2 WE_LOG_FLUSH_REQ 1 0 0 0
2 WE_SEARCH_SPACE_REPLY 13 0 0 0
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | The number of the Tibero instance. - Assigned according to startup order. - Different from the instance_number column in v$instance. |
| TOTAL_WAITS | The total number of times the event was called. |
| time_waited | The total wait time for the event (unit: seconds). |
| Average_wait | The average wait time per occurrence of the event (unit: seconds). |
| Total_Timeout | The number of timeouts that occurred if a timeout is set for the event. |
The representative major events are as follows:
| Event | Description |
|---|---|
| WE_SEQ_WRITEBACK | Event waiting for the agent to write back the sequence. |
| WE_SEQ_FREESLOT | Event waiting for a free slot in the Seq_Buffer. |
| WE_SEQ_NEXTVAL | Event waiting for the agent to load a new value into the cache. |
| WE_BUF_WAIT | Event waiting for buffer pinning. |
| WE_BUF_WRITE | Event waiting for buffer write. |
| WE_BUF_FREE | Event waiting for a free buffer. |
| WE_MBR_WAIT | Event waiting for MBR to finish. |
| WE_SEQ_FLUSH | Event waiting for sequence alba to finish cache flush. |
| WE_WTHR_READY | Event waiting for WTHR ready. |
| WE_LGWR_ARCHIVE | Event waiting for LGWR process to complete archive. |
| WE_LGWR_LNW | Event waiting for LGWR process to write logs over the network. |
| WE_LOG_FLUSH | Event waiting for log flush. |
| WE_LOG_FLUSH_SPACE | Event waiting for space to flush logs. |
| WE_LOG_FLUSH_REQ | Event waiting after requesting log flush. |
| WE_CKPT_WAIT | Event waiting for checkpoint. |
| WE_PROC_DOWN | Event occurring when shutdown finishes. |
| WE_WTHR_START | Event waiting for message to start WTHR. |
| WE_WTHR_MSG | Event where WTHR waits for client message. |
| WE_ACF_SCVR | Event where SCVR (scavenger) waits for CCC/CWS RSB (Resource Block) reclaim request. Even without a request, it wakes periodically (_ACF_SCVR_TIMEOUT default: 1 second) to perform reclaim. |
| WE_ACF_AST | Event for asynchronous system trap (AST) in WTHR. |
Waiting degrades actual task performance, so analyze the most frequent wait events first and resolve causes based on each eventโs characteristics.
All wlock-related information appears as wait events, useful for analyzing wlock contention.
Event Example
The following example shows frequent WE_LOG_FLUSH_COMMIT events when multiple sessions continuously update a specific block and commit simultaneously.
Querying v$session_wait shows the following.
[Figure 3.1] V$session_wait Query Result
From the v$system_event result, the event was called 255,471 times as shown below.
[Figure 3.2] V$system_event Query Result
Reducing the number of commits significantly improves the number of WE_LOG_FLUSH_COMMIT calls and wait times.
[Figure 3.3] WE_LOG_FLUSH_COMMIT Improvement Effect
As WE_LOG_FLUSH_COMMIT events decrease, WE_BUF_WAIT events caused by hot block contention occur.
WE_BUF_WAIT is an event waiting for buffer pinning and occurs when updates happen simultaneously on the same block.
3.2. V$SYSSTAT
The v$sysstat view is used to check cumulative performance statistics at the system level since instance startup. The v$sesstat view is for individual session performance statistics. The v$mystat view shows statistics for the current session.
3.2.1 TAC DB
Below is an example query for TAC DB.
SELECT * FROM gv$sysstat order by inst_id, value desc;
The results are as follows.
INST_ID STAT# NAME CLASS VALUE ------- ------ ---------------------------------------------- ----- --------- 1 2527 Total Times sleeps to wait acf task ready 5 21164597 1 7116 INC messages received size 5 4622210 1 7121 INC packets received size 5 4622210 1 6742 parse time elapsed 4 1483226 1 6747 hard parse elapsed time 4 1477528 1 7141 INC packets sent size 5 963718 1 7136 INC messages sent size 5 799709 1 2037 optimizer time 4 797031 1 2472 Total Times sleeps to wait ctx sync 5 713767 1 32 block disk read time 17 605534 1 6847 optimizer loading statistics time 4 595535 1 4842 dd search time 4 574467 1 4847 dd search - load time 4 560479 1 3292 optimizer dynamic sampling time 4 502320 1 8002 current block received RTT 5 486245 1 1632 transactions total time 7 473131 1 3297 optimizer dynamic sampling block read time 4 442447 1 622 redo write time 2 354457 1 2547 Total Round Trip Times to grant lock 5 342502 1 297 dbwr write time 7 324160 1 292 dbwr write time - OS 2 322394 1 9847 dbwr write suspend time 7 319952 1 7552 dbwr aio slave bitq read time 7 312180 1 5827 current block received RTT:data block 5 301297 1 4532 isgmt get cr time 3 255159 1 7742 INC messages passed to CMPT - time 5 252813 1 6892 INC messages sent without block time 5 243157 1 4527 dsgmt get cr time 4 242525 1 4537 isgmt get cr in lvl time 3 240762 1 4217 Total CR blocked time by CUR request 5 234883 1 4507 tdi fetch start time 3 221763 1 4897 Total Round Trip Times to grant wait-lock 5 200031 1 6757 SQL execute elapsed time 4 197059 1 4412 profile check time 7 171434 1 4427 total times to begin tx 7 161552 1 2567 csr fetch select time 4 150829 1 4442 total times to get undo block and apply 7 150210 1 4447 total times to get undo block 7 148789 1 7782 Total Times sleeps to wait WRCF status 5 122150 1 5837 current block received RTT:index leaf block 5 90114 1 7772 Total Times sleeps to wait CRCF status 5 83969 1 617 redo wait time for flush time 1 79085 1 7137 INC messages sent time 5 79046 1 7752 INC messages sent from send queue - time 5 75017 1 8857 INC messages received by batch - time 5 57922 1 9632 PSM SQL processing time for cursor open 7 52662 1 9662 PSM SQL processing time 7 52642 1 1687 req service time 7 51710 1 1692 SQL processing time 4 51695 1 8827 Inner SQL processing time 4 51336 1 9767 stat load query soft parse time elapsed 4 50949 1 9752 stat load query hard parse elapsed time 4 49593 1 7767 Total Times to process CCC message 5 41603 1 596 redo log size 1 39491 1 5822 current block received RTT:segment header 5 36180 1 6762 PSM execution elapsed time 0 35483 1 11097 index scan time 4 35305
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | Tibero instance information. (TAC DB query) |
| STAT# | Stat number. |
| NAME | Stat name. |
| VALUE | Stat value. |
Values in v$sysstat and v$sesstat are cumulative since instance startup or session establishment, so simple magnitude comparison is not meaningful.
To use effectively, calculate differences between two points in time to analyze what internal operations occurred during SQL execution.
3.3. TAC DB Spin Lock Contention
Check the hit ratio of all spin locks in TSM (Tibero Shared Memory).
Below is an example query for TAC DB.
SELECT INST_ID,
name "Name",
SUM(fast_cnt + try_get_cnt) as "Gets",
SUM(spin_cnt + sleep_cnt + try_fail_cnt) as "Misses",
SUM(sleep_cnt) as "Sleeps",
round((1 - (SUM(spin_cnt + sleep_cnt + try_fail_cnt)
/ SUM(fast_cnt + spin_cnt + sleep_cnt + try_get_cnt + try_fail_cnt)))
* 100, 3) as "Hit Ratio"FROM _gvt_spinlock
GROUP BY INST_ID,
type,
spin_level,
name
HAVING SUM(fast_cnt + try_get_cnt) > 0ORDER BY 2,
5 DESC,
3 DESC;
Result
INST_ID Name Gets Misses Sleeps Hit Ratio ------- ------------------------------ ------- ---------- -------- ---------- 1 SPIN_SHP_ALLOC_SLAB 3927 49 2 98.768 1 SPIN_BUF_BUCKET 206134 58 1 99.972 1 SPIN_LC_BUCKET 51315 1 1 99.998 1 SPIN_SHP_ALLOC_MISC 9636 60 1 99.381 1 SPIN_ROOT_ALLOC 7419 9 1 99.879 1 SPIN_BUF_WS 189376 0 0 100 1 SPIN_BUF_WS_CKPT 174230 0 0 100 1 SPIN_ALLOC 45179 15 0 99.967 1 SPIN_SQLSTATS 24092 1 0 99.996 1 SPIN_RECR_UNPIN 16527 0 0 100 1 SPIN_BITQ 16014 0 0 100 1 SPIN_L1CL 11200 0 0 100 1 SPIN_PARAM 5012 16 0 99.682 1 SPIN_DD_CACHE_BUCKET 4594 14 0 99.696 1 SPIN_WLOCK 4194 28 0 99.337 1 SPIN_SESS_CSR_POOL 3065 0 0 100 1 SPIN_ENABLED_ROLES 2061 0 0 100 1 SPIN_DBWR_AIOQ 1449 0 0 100 1 SPIN_SLAB_ALLOC 1371 0 0 100 1 SPIN_ALLOC_LRU 1064 0 0 100 1 SPIN_PP_QSTAT 791 1 0 99.874 1 SPIN_FD_BUCKET 704 0 0 100 1 SPIN_GLOBAL_MEM_MGR 551 0 0 100 1 SPIN_SC_ALLOC 547 0 0 100 1 SPIN_FIXED_SYS_MEM_INFO 527 0 0 100 1 SPIN_SHP_ALLOC_DD 405 0 0 100 1 SPIN_SESS_TSAM_HNDL_POOL 405 0 0 100 1 SPIN_SESS_LTIME 384 0 0 100 1 SPIN_CF 368 0 0 100 1 SPIN_REDO_ALLOC 337 0 0 100 1 SPIN_IMCS_PARAM 336 0 0 100 1 SPIN_GV_PARAM 336 0 0 100 1 SPIN_REDO_COPY 264 0 0 100 1 SPIN_WTHR_READY 240 0 0 100 1 SPIN_CKPT_PROGRESS 166 0 0 100 1 SPIN_SHP_ALLOC_LC 142 0 0 100 1 SPIN_CL_CONN 122 0 0 100 1 SPIN_USGMT_HASH 122 0 0 100 1 SPIN_PROC_MEM_MGR 116 0 0 100 1 SPIN_TSGMT_LIST 116 0 0 100 1 SPIN_SESS_SERIAL_NO 114 7 0 94.215 1 RW_SYSCTX 112 0 0 100 1 SPIN_ALERT 112 0 0 100 1 SPIN_PE_SESS 112 0 0 100 1 SPIN_MAIO_POOL 112 0 0 100 1 SPIN_TEMP_TS_ID 110 0 0 100 1 SPIN_LISTENER 107 0 0 100 1 SPIN_SERVER 107 0 0 100 1 SPIN_IMCS_REQ_LIST 96 0 0 100 1 SPIN_TSN_TIME_MAP 95 1 0 98.958 1 SPIN_DBWR_IOSLOT 80 0 0 100 1 SPIN_FDPOOL 53 0 0 100 1 SPIN_ASH_CSR_READ 53 0 0 100 1 SPIN_DBWR_LOW 52 0 0 100 1 SPIN_DBWR_IR_STATUS 50 0 0 100 1 SPIN_PP_EXPCN_LIST 48 0 0 100 1 SPIN_PPID 48 0 0 100 1 SPIN_XA_VTI 39 0 0 100 1 SPIN_SVRMODE_ACK 36 0 0 100 1 SPIN_BUF_GLOBAL_RECOQ 29 0 0 100 1 SPIN_REDO_WRITING 26 0 0 100 1 SPIN_USGMT_CACHE 25 0 0 100 1 SPIN_XQC 20 0 0 100 1 SPIN_LGWR_AIO_REQ 16 0 0 100 1 SPIN_DML_STAT_BUCKET 15 0 0 100 1 SPIN_TXR 14 0 0 100 1 SPIN_BUDDY_ALLOC 13 0 0 100 1 SPIN_ALLOC_POOL 12 0 0 100 1 SPIN_MSG_LIST 10 0 0 100 1 SPIN_SHP_ALLOC_SUPER 9 0 0 100 1 SPIN_TX 9 0 0 100 1 SPIN_SHUTDOWN 8 0 0 100 1 SPIN_PEP 6 0 0 100 1 SPIN_TEMP_UNIT_POOL 4 0 0 100 1 SPIN_BUF_FREE_WAIT 2 0 0 100 1 SPIN_LOGNET_WRITE 1 0 0 100
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | Tibero instance information. (TAC DB query) |
| NAME | The type of Spin Lock. |
| Gets | Incremented by 1 each time a spin lock acquisition is requested on the first attempt. |
| Misses | Incremented by 1 each time a spin lock acquisition fails on the first attempt. |
| Sleeps | Incremented by 1 each time the process enters sleep state. |
| Hit Ratio | The hit ratio. |
Statistics on latch acquisition success and failure in Willing-to-Wait mode can be viewed via the v$latch view.
Gets(I) and Misses(I) represent Gets (or Immediate gets) and Misses (or Immediate misses), showing the greater value between Gets and Immediate gets.
If Gets is requested in Willing-to-Wait mode, Immediate gets are requested in No-Wait mode.
Willing-to-Wait mode retries acquiring spin lock by spinning and sleeping until successful. No-Wait mode returns immediately if the spin lock cannot be acquired. Using Willing-to-Wait mode means waiting until the spin lock is acquired.
3.4. TAC DB Spin Lock Monitoring
Check information on spin lock contention.
SELECT inst_id,
name,
gets,
misses,
spin_gets,
sleeps,
decode(gets,0,0,trunc((1-misses/(misses+gets))*100,2)) "Hit Ratio",
wait_time,
round(ratio_to_report(wait_time)over()*100,2) "WT(%)"
FROM gv$latch
ORDER BY 1 asc,
8 desc,
4 desc;
Result
INST_ID NAME GETS MISSES SPIN_GETS SLEEPS Hit Ratio WAIT_TIME WT(%) ------- -------------------------- ------- ---------- ---------- ---------- ---------- ---------- ---------- 1 SPIN_CWS_RSB 6091 130 80 50 97.91 110322 65.15 1 SPIN_ALLOC 135601 264 261 3 99.8 12052 7.12 1 SPIN_ROOT_ALLOC 4365 5 0 5 99.88 12026 7.1 1 SPIN_SHP_ALLOC_SLAB 3888 25 23 2 99.36 2319 1.37 1 SPIN_BUF_WS 80234 12 10 2 99.98 786 .46 1 SPIN_BITQ 72616 3 3 0 99.99 532 .31 1 SPIN_SHP_ALLOC_MISC 10902 477 476 1 95.8 439 .26 1 SPIN_BUF_BUCKET 142547 501 501 0 99.64 280 .17 1 SPIN_WLOCK 14999 141 140 1 99.06 140 .08 1 SPIN_LC_BUCKET 212034 1 1 0 99.99 120 .07 1 SPIN_DD_CACHE_BUCKET 7999 109 109 0 98.65 111 .07 1 SPIN_CWS_RSBTBL 4221 112 112 0 97.41 91 .05 1 SPIN_CCC_RSB 5415 11 9 2 99.79 83 .05 1 SPIN_PARAM 8316 243 243 0 97.16 80 .05 1 SPIN_CCC_RSBTBL 113351 11 11 0 99.99 47 .03 1 SPIN_DBWR_AIOQ 5998 0 0 0 100 21 .01 1 SPIN_GMC_HANDLE 228 0 0 0 100 16 .01 1 SPIN_SQLSTATS 13903 0 0 0 100 5 0 1 SPIN_L1CL 47948 0 0 0 100 5 0 1 SPIN_CCC_RECL_WS 3147 6 6 0 99.8 4 0 1 SPIN_SHP_ALLOC_DD 712 3 3 0 99.58 3 0 1 SPIN_GV_PARAM 1428 1 1 0 99.93 2 0 1 SPIN_TSN_TIME_MAP 386 0 0 0 100 2 0 1 SPIN_ACF_MTX_RW 2220 0 0 0 100 2 0 1 SPIN_ACF_RCF 246 0 0 0 100 2 0 1 SPIN_RECR_UNPIN 5795 0 0 0 100 2 0 1 SPIN_CWS_RECL_WS 842 2 2 0 99.76 1 0 1 SPIN_SVRMODE_ACK 39 1 1 0 97.5 1 0 1 SPIN_CF 1080 0 0 0 100 1 0 1 SPIN_CKPT_PROGRESS 699 0 0 0 100 1 0 1 SPIN_ACF_NMGR 188 0 0 0 100 1 0 1 SPIN_SESS_SERIAL_NO 468 89 89 0 84.02 0 0 1 SPIN_CWS_LCB 16880 40 40 0 99.76 0 0 1 SPIN_MAIO 0 0 0 0 0 0 0 1 SPIN_CCC_LKBTBL 2675 0 0 0 100 0 0 1 SPIN_ACF_TIMEOUT 3408 0 0 0 100 0 0 1 SPIN_CWS_LKBTBL 1321 0 0 0 100 0 0 1 SPIN_TXR 55 0 0 0 100 0 0 1 SPIN_TEST3 0 0 0 0 0 0 0 1 SPIN_USGMT_CACHE 107 0 0 0 100 0 0 1 SPIN_PPID 66 0 0 0 100 0 0 1 SPIN_BUF_GLOBAL_RECOQ 116 0 0 0 100 0 0 1 SPIN_CCC_CRAS 0 0 0 0 0 0 0 1 SPIN_REDO_COPY 0 0 0 0 0 0 0 1 SPIN_MAIO_POOL 459 0 0 0 100 0 0 1 SPIN_USGMT_HASH 309 0 0 0 100 0 0 1 SPIN_PP_EXPCN_LIST 66 0 0 0 100 0 0 1 SPIN_PPSTAT 0 0 0 0 0 0 0 1 SPIN_RCMAP_BUCKET 0 0 0 0 0 0 0 1 SPIN_RCACHE 0 0 0 0 0 0 0 1 SPIN_TAS_DS 0 0 0 0 0 0 0 1 SPIN_SM_BUCKET 0 0 0 0 0 0 0 1 SPIN_TEST1 0 0 0 0 0 0 0 1 SPIN_TEST1A 0 0 0 0 0 0 0 1 SPIN_TEST1B 0 0 0 0 0 0 0 1 SPIN_BUF_FREE_WAIT 10 0 0 0 100 0 0 1 SPIN_RCPOOL 0 0 0 0 0 0 0 1 SPIN_ACF_ATH_LOG_FLUSH_WAIT 0 0 0 0 0 0 0 1 SPIN_WTHR_READY 144 0 0 0 100 0 0 1 SPIN_IICD 0 0 0 0 0 0 0 1 SPIN_TAS_NAME_HASH 0 0 0 0 0 0 0 1 SPIN_TAS_IOINFO 0 0 0 0 0 0 0 1 SPIN_RECR_LIST 0 0 0 0 0 0 0 1 SPIN_SLAB_PEND 0 0 0 0 0 0 0 1 SPIN_TEST2 0 0 0 0 0 0 0 1 SPIN_LOGNET_POOL 3 0 0 0 100 0 0 1 SPIN_FB_COPY 0 0 0 0 0 0 0 1 SPIN_LOGNET_WRITE 1 0 0 0 100 0 0 1 SPIN_SHP_ALLOC 0 0 0 0 0 0 0 1 SPIN_SHP_ALLOC_LC 214 0 0 0 100 0 0 1 SPIN_SHP_ALLOC_SUPER 17 0 0 0 100 0 0
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | The number of the Tibero instance. - Assigned according to startup order. - Different from the instance_number column in v$instance. |
| NAME | The type of Spin Lock. |
| GETS | Incremented by 1 each time a spin lock acquisition is requested on the first attempt. |
| MISSES | Incremented by 1 each time a spin lock acquisition fails on the first attempt. |
| SPIN_GETS | Indicates the number of times spin lock acquisition failed until success, incremented by 1 for each failure. |
| SLEEPS | Incremented by 1 each time the process enters sleep state. |
| Hit Ratio | The hit ratio. |
| WAIT_TIME | Total sleep time waiting for spin lock. |
| WT | WAIT_TIME expressed as a percentage. |
Below are descriptions of parameters that may cause spin lock contention.
Shared Pool
- SPIN_SHP_ALLOC
| Related Parameter | _SHARED_POOL_ALLOC_CNT (Default: 1) |
|---|---|
| Description | The Shared Pool can be divided into multiple allocators. Determines how many allocators to divide into. Up to 15 can be set. Higher values reduce contention but decrease memory efficiency. |
- SPIN_ALLOC_LRU
| Related Parameter | _SHARED_POOL_LRU_PER_ALLOC (Default: 4) |
|---|---|
| Description | Determines the number of LRU lists used by one Shared Pool Allocator. To minimize performance degradation, setting to a power of 2 is recommended. Higher values reduce contention but decrease memory efficiency. If contention remains high after increasing SHARED_POOL_LRU_PER_ALLOC, increase SHARED_POOL_ALLOC_CNT. However, SHARED_POOL_ALLOC_CNT allows use of full allocators even if others have free space. The total number of LRU lists (product of the two parameters) can be up to 16; latest versions allow up to 15. |
- SPIN_RECR_HANDLE_POOL
| Related Parameter | _RECR_HANDLE_FREELIST_CNT (Default: 8) |
|---|---|
| Description | Sets the number of free lists for structures used in DD Cache and Library Cache. Handles are used to manage these structures to avoid overhead of creating new handles each time. |
WLOCK
- SPIN_WLOCK
| Related Parameters | _WLOCK_BUCKETSET_CNT (Default: 64) _WLOCK_BUCKET_PER_SET (Default: 1) |
|---|---|
| Description | Determines the number of bucket sets used by hash buckets for wlock. Each bucket set has a spin lock; more sets reduce contention but increase hash table size and memory usage. To increase only spin locks, adjust these parameters. To improve overall distribution, also increase _WLIST_FREELIST_CNT. |
- SPIN_WLIST_FREELIST
| Related Parameter | _WLIST_FREELIST_CNT (Default: 8) |
|---|---|
| Description | Determines the number of freelists in the structure pool used by wlock or buffer cache. Setting to a power of 2 is recommended. |
Buffer Cache
- SPIN_BUF_WS / SPIN_BUF_WS_CKPT
| Related Parameter | _DB_BLOCK_LRU_LATCHES (Default: CPU count * 3) |
|---|---|
| Description | Determines the number of LRUs used in buffer cache, which affects the working set size. |
- SPIN_BUF_BUCKET
| Related Parameters | _DB_BLOCK_HASH_BUCKETS (Default: 131091) _DB_BLOCK_HASH_LATCHES (Default: 131091) |
|---|---|
| Description | _DB_BLOCK_HASH_BUCKETS sets the number of hash buckets used to find specific buffers in buffer cache. _DB_BLOCK_HASH_LATCHES sets the number of latches used in the buffer cache hash. If contention is severe, buckets and latches can be set equal, but this reduces memory efficiency as each bucket has a latch. |
Redo
- SPIN_REDO_COPY
| Related Parameter | _LOG_SIMULTANEOUS_COPIES (Default: CPU count * 2) |
|---|---|
| Description | Sets the number of simultaneous transactions copying in the redo buffer. |
Undo
- SPIN_USGMT_HASH
| Related Parameter | _USGMT_HASH_CNT (Default: 32) |
|---|---|
| Description | Sets the number of spin lock hash buckets for undo segments. Since increasing hash buckets does not consume much memory, setting it to at least 64 is recommended. |
DD Cache
- SPIN_DD_CACHE_BUCKET
| Related Parameters | _DD_BUCKETSET_CNT (Default: 16) _DD_BUCKET_PER_SET (Default: 64) |
|---|---|
| Description | Determines the number of hash buckets and bucket sets similar to wlock. |
PP Cache
- SPIN_PPC_BUCKET
| Related Parameter | _PPC_BUCKET_CNT (Default: 1) |
|---|---|
| Description | Determines the number of hash buckets similar to wlock. |
Temp
- SPIN_TEMP_UNIT_POOL_TF
One spin lock is allocated per temp file, which occurs when multiple sessions use Temp Tablespace.
If spin lock contention is abnormally high, add multiple temp files to the Temp Tablespace to resolve.
Chapter 4 Lock & Current Transactions
This chapter explains locks and transactions.
4.1. Number of Current Sessions
Check all sessions connected to the target database, the number of sessions currently working, and total WPM (Working Process Memory). Session states include READY, RUNNING, TX_RECOVERING, etc.
4.1.1 TAC DB
Below is an example query for TAC DB.
SELECT a.INST_ID,
to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Time",
"Work Proc Memory",
a.tots "Tot Session",
b.run "Run Session"FROM (SELECT INST_ID,
SUM(pga_used_mem) "Work Proc Memory",
COUNT(*) tots
FROM gv$session
group by inst_id) a,
(SELECT INST_ID,
COUNT(*) run
FROM gv$session
WHERE status='RUNNING' group by inst_id) b
WHERE a.INST_ID = b.INST_ID(+);Result
INST_ID Time Work Proc Memory Tot Session Run Session ------- ------------------- ---------------- ----------- ----------- 1 2023/07/04 14:24:59 8703984 14 11 2 2023/07/04 14:24:59 653512 5 1 2 rows selected.
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | Tibero instance information. |
| Time | The current time when the query was run. |
| Work Proc Memory | The total size of PGA currently used by all sessions. |
| Tot Session | Total number of connected sessions. |
| Run Session | Number of sessions currently performing work. |
If a significant number of sessions are running, check lock and current transaction information and take appropriate action.
4.2. TAC DB Current Transactions
Check the list and related information of currently ongoing transactions.
SELECT distinct vs.inst_id,
vs.sid,
vs.serial#,
vs.username,
va.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 gv$session vs,
gv$transaction vt,
gv$sqltext vst,
gv$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
ORDER BY 1, 2;The results are as follows.
INST_ID SID SERIAL# USERNAME OBJECT STATUS USED_BLK USN START_TIME Transaction Tim SQL_TEXT
---------- ----- ---------- --------------- --------------- ---------- ---------- ---------- --------------- --------------- ------------------------------ 1 152 10785 TPCC BMSQL_NEW_ORDER RUNNING 1 4 2023/07/04 0:00:16 DELETE FROM bmsql_new_order
2 134 97 TPCC BMSQL_NEW_ORDER READY 1 8 2023/07/04 0:00:02 select * from tabDescriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | Tibero instance information. (TAC DB query) |
| SID | Session ID performing the transaction. |
| USERNAME | User performing the transaction. |
| OBJECT | List of tables used in the current transaction. (If multiple tables are accessed, each appears as one row.) |
| STATUS | Status of the session performing the transaction. |
| USED_BLK | Number of blocks currently used by the transaction. |
| START_TIME | Time when the transaction started. |
| Transaction Time | Elapsed time of the transaction. |
| SQL_TEXT | The SQL statement currently executing. |
Check the usage of current transactions, and if any transactions are maintained for a long time without commit or rollback, determine whether it is due to application coding errors or administrator/developer mistakes, and recommend commit or rollback for unnecessary transactions.
4.3. TAC DB Current SQL Information (For Sessions)
Check the SELECT and DML statements currently executed per session.
Below is an example query for TAC DB.
SELECT max(vs.inst_id) inst_id,sid,
serial#,
'{' || aggr_concat(sql_text, '' ORDER BY PIECE) || '}' SQL
FROM gv$session vs,
gv$sqltext vst
WHERE vs.sql_id=vst.sql_id
GROUP BY SID, SERIAL#;
Result
INST_ID SID SERIAL# SQL
------- ---- ------- --------------------------------------------------------------------------------------------------
1 134 12535 {DELETE FROM bmsql_new_order
WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?}
2 143 12223 {SELECT "INST_ID","HASH_VALUE","PLAN_HASH_VALUE","SQL_ID","CHILD_NUMBER",
"COMMAND_TYPE","PIECE","SQL_TEXT"
FROM (
SELECT instance_id() AS inst_id,
"V"."HASH_VALUE",
"V"."PLAN_HASH_VALUE",
"V"."SQL_ID",
"V"."CHILD_NUMBER",
"V"."COMMAND_TYPE",
"V"."PIECE",
"V"."SQL_TEXT"
FROM v$sqltext v
) /*GV$SQLTEXT*/ QB_006
WHERE ("SQL_ID" IS NOT NULL)}
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | Tibero instance information. (TAC DB query) |
| SID | Session ID. |
| SERIAL# | Session serial number. (Changes if user changes even with the same session ID.) |
| SQL | The SQL statement currently executing. |
Check SQL statements of active sessions and identify any problematic or looping SQL causing DB load, then take appropriate action for those sessions.
4.4. TAC DB WLOCK Information (For Sessions)
Check transaction time and lock modes of currently running sessions.
Below is an example query for TAC DB.
SELECT vt.inst_id,
s.sess_id "SID",
s.serial_no "SERIAL#",
s.status "STATUS",
s.user_name "USER",
o.object_name,
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) || '/' || NVL2(s.sql_id, s.sql_child_number, s.prev_child_number) "SQL_ID"FROM vt_wlock l,
vt_session s,
dba_objects o ,
gv$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;
The results are as follows.
INST_ID SID SERIAL# STATUS USER OBJECT_NAME LOCK_TIME LOCK MODE SQL_ID
------- --- ------- ------- ---- ---------------- --------- ------------ --------------------
1 136 196 RUNNING TPCC BMSQL_OORDER 0:00:00 [2]Row-X(RX) 8c940cxrfrc8d/78
1 136 196 RUNNING TPCC BMSQL_NEW_ORDER 0:00:00 [2]Row-X(RX) 8c940cxrfrc8d/78
2 137 198 RUNNING TPCC BMSQL_DISTRICT 0:00:00 [1]Row-S(RS) f3sacguuzunmw/57
2 143 210 RUNNING TPCC BMSQL_DISTRICT 0:00:00 [1]Row-S(RS) f3sacguuzunmw/57
Descriptions of each item related to DML Lock information are as follows:
| Item | Description |
|---|---|
| INST_ID | Tibero instance information. (TAC DB query) |
| SID | Session ID performing the transaction. |
| SERIAL# | Serial number of the session performing the transaction. |
| STATUS | Status of the session performing the transaction. |
| USER | User performing the transaction. |
| OBJECT_NAME | Object locked by DML lock. |
| LOCK_TIME | Duration of the DML lock. |
| LOCK_MODE | Type of DML lock (RS=1, RX=2, S=3, SRX=4, X=5). |
| SQL_ID | Currently executing SQL_ID. |
Chapter 5 Disk I/O Monitoring
This chapter explains disk I/O.
5.1. TAC DB Data File I/O
Check the amount and time of I/O on data files.
Below is an example query for TAC DB.
SELECT rownum num, inst_id
,fl.tablespace_name
,df.name
,fs.phyrds
,fs.phywrts
,round((PHYRDS / (SELECT sum(phyrds)
FROM GV$FILESTAT fs
WHERE inst_id = fs.inst_id)) *100, 1) "P_READ(%)" ,round((PHYWRTS / DECODE((SELECT sum(phywrts)
FROM GV$FILESTAT fs
WHERE inst_id = fs.inst_id), 0, 1,(SELECT sum(phywrts)
FROM GV$FILESTAT fs
WHERE inst_id = fs.inst_id)))*100, 1) "P_WRITE(%)" ,round((phyrds + phywrts) / (SELECT sum(phyrds) + sum(phywrts)
FROM GV$FILESTAT fs
WHERE inst_id = fs.inst_id)*100, 1) "TOTAL IO (%)" ,round(fs.AVGIOTIM/1000,3) "AVG_TIME(msec)"FROM V$DATAFILE df,
GV$FILESTAT fs,
dba_data_files fl
WHERE df.file# = fs.file#
AND df.file# = fl.file_id
ORDER BY 1, phyrds+phywrts DESC;The results are as follows.
NUM INST_ID TABLESPACE_NAME NAME PHYRDS PHYWRTS P_READ(%) P_WRITE(%) TOTAL IO(%) AVG_TIME(msec) --- ------- --------------- ----------------------------------- ------ ------- --------- ---------- ----------- -------------- 1 1 SYSTEM +DS0/tac/datafile/system001.dtf 465 11 47.3 8.9 43.0 .012 2 2 SYSTEM +DS0/tac/datafile/system001.dtf 273 5 27.7 4.0 25.1 .012 3 1 UNDO0 +DS0/tac/datafile/undo001.dtf 24 49 2.4 39.5 6.6 .059 4 2 UNDO0 +DS0/tac/datafile/undo001.dtf 0 4 0.0 3.2 0.4 .024 5 1 USR +DS0/tac/datafile/usr001.dtf 1 0 0.1 0.0 0.1 .001 6 2 USR +DS0/tac/datafile/usr001.dtf 0 0 0.0 0.0 0.0 0 7 1 UNDO1 +DS0/tac/datafile/undo101.dtf 12 1 1.2 0.8 1.2 .031 8 2 UNDO1 +DS0/tac/datafile/undo101.dtf 11 27 1.1 21.8 3.4 .088 9 1 SYSSUB +DS0/tac/datafile/syssub001.dtf 74 11 7.5 8.9 7.7 .018 10 2 SYSSUB +DS0/tac/datafile/syssub001.dtf 123 16 12.5 12.9 12.5 .018 11 1 TIBERO7 +DS0/tac/datafile/tibero7.dtf 1 0 0.1 0.0 0.1 .001 12 2 TIBERO7 +DS0/tac/datafile/tibero7.dtf 0 0 0.0 0.0 0.0 0
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | Tibero instance information. (TAC DB query) |
| TABLESPACE_NAME | Name of the tablespace. |
| NAME | Name of the created data file. |
| PHYRDS | Number of times blocks were read from disk. |
| PHYWRTS | Number of times blocks were written to disk. |
| P_READ(%) | Read frequency of the data file (percentage of total block reads). |
| P_WRITE(%) | Write frequency of the data file (percentage of total block writes). |
| TOTAL IO(%) | Read and write frequency of the data file (percentage of total block reads and writes). |
| AVG_TIME | Average I/O time. (Default unit is microseconds, converted here to milliseconds.) |
Check the amount and time of I/O on data files. Read(%), Write(%), and Total IO(%) represent the proportion of each data file in all database file reads, writes, and combined reads/writes respectively.
If average I/O time is long for a data file, consider the disk performance where the file is located. Also check if many data files are concentrated on the same device causing I/O bottlenecks. Distribute heavily accessed data files across multiple devices.
5.2. TAC DB Online Redo Log Switch Count
Check the number of online redo log switches by hour.
Below is an example query for TAC DB.
SELECT 'Instance : '|| THREAD# as Inst_id,
TO_CHAR(first_time,'MM/DD') ||' :'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'00',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'01',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'02',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'03',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'04',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'05',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'06',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'07',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'08',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'09',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'10',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'11',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'12',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'13',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'14',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'15',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'16',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'17',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'18',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'19',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'20',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'21',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'22',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'23',1,0)),'99') ||'|'as "Online Log_history : 00(h)~"FROM v$log
GROUP BY THREAD#, TO_CHAR(first_time,'MM/DD')
ORDER BY 1;
Result
INST_ID Online Log_history : 00(h)~ --------------- ---------------------------------------------------------------------------------------------------------------- Instance : 0 06/28 : 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 3| 0| 0| 0| 0| 0| 0| 0| 0| Instance : 1 06/28 : 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 3| 0| 0| 0| 0| 0| 0| 0| 0| 2 rows selected.
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | Tibero instance information. (TAC DB query) |
| Online Log_history | Number of log switches occurred per hour. |
Check the number of log switches per time period for online logs.
The hourly online redo log switch count helps predict database transaction volume and identify peak transaction periods. It is advisable to distribute large batch jobs to other times during peak transaction periods.
Chapter 6 Space Management
This chapter explains disk space management.
6.1. Frequent Extents Occurrence
Check objects in the database with many extents.
Below is an example query.
SELECT owner,
segment_name,
segment_type,
tablespace_name,
count(*) numext,
round(sum(bytes)/1024/1024, 1) MB
FROM dba_extents
WHERE owner not in ('SYS','SYSCAT')
GROUP BY segment_name,
segment_type ,
owner,
tablespace_name
HAVING COUNT(extent_id) > 40ORDER BY segment_type,
round(sum(bytes)/1024/1024, 1) desc,
segment_name;Result
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME NUMEXT MB --------------- ------------------------------ ------------------ --------------- ---------- ---------- TPCC BMSQL_ORDER_LINE_PKEY INDEX USR 223 160 TPCC BMSQL_STOCK TABLE USR 143 360 TPCC BMSQL_ORDER_LINE TABLE USR 42 350 TPCC BMSQL_CUSTOMER TABLE USR 128 210
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| OWNER | Owner information. |
| SEGMENT_NAME | Segment information. |
| SEGMENT_TYPE | Segment type. |
| TABLESPACE_NAME | Name of the tablespace used by the segment. |
| NUMEXT | Number of extents used. |
| MB | Current usage of the segment. |
Check objects with more than 40 extents.
Having many extents does not necessarily cause serious performance degradation, but frequent real-time extent allocation may negatively impact performance.
For objects with excessive extents, adjust the next extent size according to data growth to reduce frequent extent allocation. If data size is small but extent size is large, consider reorganization.
6.2. Tablespace Usage
Check tablespace usage and fragmentation.
Below is an example query.
SELECT A.tablespace_name ,
A.totbytes/1024/1024 "BYTES(MB)",
B.freebytes/1024/1024 "FREE(MB)",
(A.totbytes - B.freebytes)/1024/1024 "USED(MB)",
A.totblocks "TOTAL BLK" ,
B.freeblocks "FREE BLK",
A.totblocks - B.freeblocks "USED BLK"FROM (SELECT tablespace_name,
sum(bytes) totbytes,
sum(blocks) totblocks
FROM dba_data_files
GROUP BY tablespace_name) A,
(SELECT tablespace_name,
sum(bytes) freebytes,
sum(blocks) freeblocks
FROM dba_free_space
GROUP BY tablespace_name) B
WHERE A.tablespace_name = B.tablespace_name;
The results are as follows.
TABLESPACE_NAME BYTES(MB) FREE(MB) USED(MB) TOTAL BLK FREE BLK USED BLK --------------- ---------- ---------- ---------- ---------- ---------- ---------- UNDO2 1024 936 88 131072 119808 11264 USR 1024 871.125 152.875 131072 111504 19568 SYSTEM 1024 920.25 103.75 131072 117792 13280 UNDO1 1024 892 132 131072 114176 16896 SYSSUB 1024 992.5 31.5 131072 127040 4032
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| TABLESPACE_NAME | Name of the tablespace. |
| BYTES(MB) | Allocated size. |
| FREE(MB) | Available free size. |
| USED(MB) | Used size. |
| TOTAL BLK | Number of allocated blocks. |
| FREE BLK | Number of free blocks. |
| USED BLK | Number of used blocks. |
Check free space and redefine tablespace if space is insufficient.
Below are methods for redefining tablespace.
- Create Tablespace
CREATE TABLESPACE my_space DATAFILE '/usr/tibero/dbf/my_file.tbf' SIZE 32000M AUTOEXTEND OFFEXTENT MANAGEMENT LOCAL AUTOALLOCATE;
- Create multiple data files in one tablespace
CREATE TABLESPACE my_space2 DATAFILE'/usr/tibero/dbf/my_file21.dbf' SIZE 10G AUTOEXTEND OFF, '/usr/tibero/dbf/my_file22.dbf' SIZE 10G AUTOEXTEND OFFEXTENT MANAGEMENT LOCAL AUTOALLOCATE;
- Drop Tablespace
Tablespace is removed but data files remain.
DROP TABLESPACE my_space;
- Delete all data inside tablespace
DROP TABLESPACE my_space INCLUDING CONTENTS AND DATAFILES;
- Add data file to tablespace
ALTER TABLESPACE my_space ADD DATAFILE 'my_file02.dbf' SIZE 10G;
- Resize data file
ALTER DATABASE DATAFILE 'my_file02.dbf' RESIZE 100M;
6.3. TAC DB Undo Segment Usage
Check usage of Undo Tablespace (Rollback segments).
Below is an example query for TAC DB.
SELECT vr.inst_id,
dr.segment_ID,
dr.tablespace_name,
dr.status,
vr.extents ,
(vr.rssize * pt.value)/1024 "rssize[K]" ,
vr.curext,
vr.cursize,
vr.xacts
FROM dba_rollback_segs dr,
GV$ROLLSTAT vr,
(SELECT inst_id,
value FROM gv$parameters
WHERE name='DB_BLOCK_SIZE') pt
WHERE dr.segment_id = vr.usn
and vr.inst_id = pt.inst_id
ORDER BY 1;
Result
INST_ID SEGMENT_ID TABLESPACE_NAME STATUS EXTENTS RSSIZE[K] CUREXT CURSIZE XACTS ------- ---------- --------------- ------ ------- --------- ------ ------- ----- 1 22 UNDO2 ONLINE 2 8184 0 511 15 1 15 UNDO2 ONLINE 2 8184 0 511 13 1 13 UNDO2 ONLINE 2 8184 0 511 12 1 12 UNDO2 ONLINE 2 8184 0 511 16 1 16 UNDO2 ONLINE 2 8184 0 511 11 1 11 UNDO1 ONLINE 5 20472 4 512 10 1 10 UNDO1 ONLINE 5 20472 4 512 17 1 9 UNDO2 ONLINE 2 8184 0 511 9 1 8 UNDO1 ONLINE 5 20472 4 512 8 1 18 UNDO1 ONLINE 5 20472 4 512 18 1 7 UNDO2 ONLINE 2 8184 0 511 7 1 14 UNDO1 ONLINE 5 20472 4 512 14 1 6 UNDO2 ONLINE 2 8184 0 511 6 ... INST_ID SEGMENT_ID TABLESPACE_NAME STATUS EXTENTS RSSIZE[K] CUREXT CURSIZE XACTS ------- ---------- --------------- ------ ------- --------- ------ ------- ----- 2 0 SYSTEM ONLINE 2 8184 0 511 10 2 10 UNDO1 ONLINE 5 20472 4 512 9 2 9 UNDO1 ONLINE 5 20472 4 512 8 2 8 UNDO1 ONLINE 5 20472 4 512 7 2 7 UNDO1 ONLINE 5 20472 4 512 6 2 6 UNDO1 ONLINE 5 20472 4 512 5 2 5 UNDO1 ONLINE 5 20472 4 512 4 2 4 UNDO1 ONLINE 5 20472 4 512 3 2 3 UNDO1 ONLINE 5 20472 4 512 2 2 2 UNDO1 ONLINE 5 20472 4 512 1 2 1 UNDO1 ONLINE 5 20472 4 512 11 2 11 UNDO1 ONLINE 5 20472 4 512 ...
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | Tibero instance information. (TAC DB query) |
| SEGMENT_ID | Segment ID. |
| TABLESPACE_NAME | Name of the rollback (undo) tablespace. |
| STATUS | Segment status (ONLINE, OFFLINE, UNDEFINED). |
| EXTENTS | Number of allocated extents. |
| RSSIZE(K) | Segment usage. |
| CUREXT | Currently used extent. |
| CURSIZE | Size of the currently used extent. |
| XACTS | Number of transactions assigned to the undo segment. |
6.3.1. Check Current TAC DB Undo Usage
Below is an example query for TAC DB.
SELECT vr.inst_id,
vs.sid,
vs.serial#,
dr.segment_ID,
DECODE(vst.command_type, 1, 'SELECT' , 2, 'INSERT' , 3, 'UPDATE' , 4, 'DELETE' , 5, 'CALL', 0) "SQL Type" ,
dr.tablespace_name,
vt.used_blk,
vr.curext,
vr.cursize,
vr.xacts
FROM dba_rollback_segs dr ,
gv$rollstat vr ,
gv$transaction vt ,
gv$session vs ,
gv$sqltext vst
WHERE ( dr.segment_id = vr.usn
and vr.inst_id = vt.inst_id
and vr.usn = vt.usn
and dr.segment_id = vt.usn
and vs.sid = vt.sess_id)
and ( vs.prev_sql_id = vst.sql_id
or vs.sql_id = vst.sql_id);
The results are as follows.
INST_ID SID SERIAL# SEGMENT_ID SQL Type TABLESPACE_NAME USED_BLK CUREXT CURSIZE XACTS ------- --- ------- ---------- -------- --------------- -------- ------ ------- ----- 1 133 719507 4 SELECT UNDO1 1 3 512 1 1 133 719507 4 SELECT UNDO1 1 3 512 1 1 146 719493 6 UPDATE UNDO1 1 3 512 1 1 146 719493 6 UPDATE UNDO1 1 3 512 1 1 146 719493 6 INSERT UNDO1 1 3 512 1 1 146 719493 6 INSERT UNDO1 1 3 512 1 1 146 719493 6 INSERT UNDO1 1 3 512 1 1 147 719495 7 UPDATE UNDO1 1 3 512 1 1 147 719495 7 UPDATE UNDO1 1 3 512 1 1 152 719505 10 SELECT UNDO1 2 0 511 0 1 152 719505 10 SELECT UNDO1 2 0 511 0 1 134 719509 11 UPDATE UNDO1 1 3 512 0 1 134 719509 11 UPDATE UNDO1 1 3 512 0
Descriptions of each item are as follows:
| Item | Description |
|---|---|
| INST_ID | Tibero instance information. (TAC DB query) |
| SID | Session ID. |
| SERIAL# | Session serial number. |
| SEGMENT_ID | Segment ID. |
| SQL Type | Type of SQL used (SELECT, INSERT, UPDATE, DELETE, CALL). |
| TABLESPACE_NAME | Name of the rollback (undo) tablespace. |
| USED_BLK | Number of blocks used. |
| CUREXT | Currently used extent. |
| CURSIZE | Size of the currently used extent. |
| XACTS | Number of transactions assigned to the undo segment. |
If the undo tablespace is full, reusable extents are reused (stealed).
When extending extents, reusable segments are checked first, and extents are expanded only if no reusable segments exist.
Undo segments can be reused after transactions (TX) using them are committed. The retention time of undo images for committed transactions is determined by the undo_retention setting. It is recommended to adjust undo_retention to maximize undo guarantee without causing Snapshot Too Old errors.
6.3.3 Manual Undo Segment Shrink
You can obtain the USN number and shrink undo segments using the following commands.
SELECT usn, extents, rssize, seqno, xacts, shrinks, shrink_size, incno FROM v$rollstat; ALTER ROLLBACK SEGMENT <USN NUMBER> SHIRINK;
You can also verify the results by running the following SQL, which produces the same outcome.
SELECT 'ALTER ROLLBACK SEGMENT' || USN || ' SHRINK;' FROM v$rollstat;
Chapter 7 Parameter Tuning
7.1 Location Parameters
Below are types of location parameters.
| Parameter | Description |
|---|---|
| CONTROL_FILES | Specify absolute paths for control files and duplicate them on separate disks for fault tolerance. Example: /data01/Tibero_Data/c1.ctl, /data02/Tibero_Data/c2.ctl |
| DB_CREATE_FILE_DEST | Specify absolute path for data files and distribute them across multiple disks for I/O distribution. |
| LOG_ARCHIVE_DEST | Specify absolute path for archive logs, separated from data and log file locations. |
7.2 Optimizer Setting Parameters
Below are types of optimizer setting parameters.
| Parameter | Description |
|---|---|
| OPTIMIZER_MODE | Parameter to set the optimizer mode. |
| FIRST_ROWS_n | Optimizes to quickly return the first n rows. |
| ALL_ROWS | Determines the execution method with the least cost to return all results. |
7.3 Execute Memory Parameters
Below are types of execute memory parameters.
| Parameter | Description |
|---|---|
| SORT_AREA_SIZE | Sets the size for sort operations; ignored if EX_MEMORY_AUTO_MANAGEMENT is Y. |
| HASH_AREA_SIZE | Sets the size for hash operations; ignored if EX_MEMORY_AUTO_MANAGEMENT is Y. EX_MEMORY_AUTO_MANAGEMENT determines whether execute memory is automatically set by memory tuner or manually by parameters like sort_area_size and hash_area_size. |
| EX_MEMORY_HARD_LIMIT | Defines the size of execute memory. |
| EX_MEMORY_SOFT_LIMIT_RATIO | Defines the ratio of execute memory available for operations like sort and join. (Default: 80%) |
| EX_MEMORY_OPERATION_LIMIT | Defines the maximum execute memory allocatable per operation like sort or join. (Default: 0) Prevents single operations from using excessive execute memory. |
| EX_MEMORY_DRIFT_CHECK_INTERVAL | Checks every 0.1 seconds for rapid memory changes. (Default: 2) Checks if execute memory change exceeds 10% of total EX_MEM, then triggers drift. |
| EX_MEMORY_COMPENSATE_INTERVAL | Defines the reset interval for SOFT_LIMIT_RATIO and OPERATION_LIMIT in 0.1 second units. (Default: 30) |
7.4 Other Setting Parameters
Below are types of other setting parameters.
| Parameter | Description |
|---|---|
| CURSOR_SHARING | Sets whether to share cursors for similar SQL statements.
|
| DB_FILE_MULTI_BLOCK_READ_COUNT | Defines the number of blocks read at once from disk data files during full table scans. Reduces I/O calls and improves full table scan performance. (Default: 32) Platform-specific limits apply. |
| UNDO_RETENTION | Sets the time in seconds to retain undo information before reuse. Helps reduce errors like โSnapshot too oldโ. Undo info is not reused until transaction expiration if sufficient space exists. Transactions do not fail if space is insufficient but errors occur if still insufficient. |
Note
It is recommended to share cursors via bind variables in applications rather than relying on CURSOR_SHARING.
7.5 Database I/O Tuning
Distribute files across multiple disks and controllers to minimize disk I/O.
Considerations when tuning database I/O:
File Distribution
Place data files, redo log files, and archivelog files on separate disks, and separate members of redo log groups onto different disks.
Tablespace
Store only data dictionary information in system tablespace; separate tables and indexes into different tablespaces. Use separate tablespaces for LOB data or partitions.
Database I/O Tuning Monitoring
Use the V$FILESTAT dynamic performance view to distribute heavily accessed files across devices.
Disk I/O Tuning
Disk I/O greatly affects database performance. Consider the following:
- Interface between host and disk: EIDE, SCSI, Ultra SCSI. - RAID configuration: RAID 0, RAID 1, RAID 5, RAID 0+1, RAID 1+0. - File system: Consider read performance. - Raw device: Consider write performance.
Tuning Factors
Distribute load evenly across multiple disks and controllers to improve I/O performance. More disks reduce bottlenecks; many small disks can outperform fewer large disks.