Skip to main content

Search

Welcome to Tibero GTS!

Checking Tibero Internal Memory Usage

Document Type | Technical Information

Category | Administration

Applicable Product Versions | 6FS06, 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS

Document Number | TADTI070

 

Overview

This guide explains how to check the internal memory usage of Tibero.
 
 

Method

Viewing TSM (Tibero Shared Memory) Usage Statistics

You can check the total size and usage by region of TSM (shared memory) through the v$sga view.
SQL>  select * from v$sga;

NAME                                          TOTAL       USED
---------------------------------------- ---------- ----------
SHARED MEMORY                            2147483648 2147483648
FIXED MEMORY                             1757257680 1757257680
SHARED POOL MEMORY                        390201344  222298112
SHARED POOL ALLOCATORS (LC)                46137344   43286616
SHARED POOL ALLOCATORS (DD)                33554432   19895864
SHARED POOL ALLOCATORS (SLAB)              75497472   74745168
SHARED POOL ALLOCATORS (MISC)              33554432    8494256
SHARED POOL ALLOCATORS (LONG-TERM)         33554432   29262360
SHARED POOL ALLOCATORS (Total)            222298112  175684264
Database Buffers                         1431699456 1431699456
Redo Buffers                              104857600  104857600

 

Querying SGA Usage Based on Snapshots (Using _TPR_SGASTAT Table)

Since v$sga does not provide maximum value information, when checking based on TPR reports, query as shown below.
SQL> select SNAP_ID , sum("SIZE")/1024/1024/1024 gb from _TPR_SGASTAT
group by SNAP_ID
order by 2 desc
fetch first 1 rows only; 

   SNAP_ID         GB
---------- ----------
      2494  .33839564

 

Viewing PGA Usage Statistics

You can check the memory usage status of PGA (Process Global Area) through the v$pgastat view.
SQL>  select * from v$pgastat;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
Ex memory soft limit                                             1785328181
Ex memory operation limit                                         107374182
Total ex memory allocated                                           7701312
Total ex memory allocated (max)                                    18218688
FIXED pga memory                                                  122540215
ALLOCATED pga memory                                              135352320
USED pga memory (from ALLOCATED)                                   40480392
Total workarea memory used                                       5.6632E+10
Total workarea temporary segment used                                     0
Item
Description
Ex memory soft limit
Total memory limit available for SQL operations excluding FIXED pga memory
Ex memory operation limit
Memory limit for a single operation (e.g., sort, join) in SQL Work Area. If insufficient, temporary segments are used.
Total ex memory allocated
Total SQL Work Area memory currently allocated to all Worker Threads
Total ex memory allocated (max)
Highest PGA memory usage since DB startup.
FIXED pga memory
Size of the area fixedly allocated to PGA at Tibero startup.
ALLOCATED pga memory
Size of the area variably allocated to PGA since Tibero startup.
USED pga memory (from ALLOCATED)
Total PGA memory currently in use.
Total workarea memory used
Total cumulative Work Area memory used for SQL operations since DB startup.
Total workarea temporary segment used
Total memory used in temporary segments since DB startup.

 

Viewing PGA Usage by Session

# Worker process

SQL> select SID, TYPE, PGA_USED_MEM from v$session ORDER BY PGA_USED_MEM DESC;

   SID 		TYPE            PGA_USED_MEM
---------- --------------- ---------------------------
      92 WTHR                 2209008
      90 WTHR                 1726272
      91 WTHR                 1726272
      96 WTHR                  750960
      95 WTHR                  750936
      89 WTHR                  734456
      93 WTHR                  143152
      94 WTHR                  142992
 

# Background process

SQL> select SID, TYPE, PGA_USED_MEM from v$bg_session ORDER BY PGA_USED_MEM DESC;

  SID  		TYPE            PGA_USED_MEM
---------- --------------- -------------------------
      11 DBWR_CTHR           10001192
      12 RECO_CTHR            4280304
       4 CTHR                 1881664
       5 CTHR                 1881664
       6 CTHR                 1881664
       7 CTHR                 1881664
       8 CTHR                 1881664
       9 CTHR                 1881664
       2 CTHR                 1242496
       3 CTHR                 1229016
       1 CTHR                 1217968
      10 AGNT_CTHR            1201224
      21 AGNT_WTHR             739320
      17 AGNT_WTHR             736432
       0 MTHR                  150536
      14 AGNT_WTHR             134288
      22 AGNT_WTHR              93136
      20 AGNT_WTHR              93136
      19 AGNT_WTHR              93136
      15 AGNT_WTHR              93136
      13 AGNT_WTHR              93136
      70 RECO_WTHR                  0
      71 RECO_WTHR                  0
      69 RECO_WTHR                  0
      68 RECO_WTHR                  0
      67 RECO_WTHR                  0
      66 RECO_WTHR                  0
      65 RECO_WTHR                  0
      64 RECO_WTHR                  0
      63 RECO_WTHR                  0
      62 RECO_WTHR                  0
      61 RECO_WTHR                  0
      60 RECO_WTHR                  0

Note

TSM (TIBERO SHARED MEMORY)

TSM is the shared memory area of Tibero, configured as a memory space where multiple processes within an instance can simultaneously share data.
TSM is divided into a Fixed Memory area allocated at boot time and a Shared Pool Memory area dynamically allocated and used during operation.
 
Fixed Memory
  • Database Buffer: Memory area holding data of tables and indexes
  • Redo Log Buffer: Memory area for storing changes as logs
  • System Area: Internal control information such as global variable space, session/thread control structures
 
Shared Pool Memory
  • Data Dictionary Cache (DD Cache): Data Dictionary information of objects (tables, columns, users, permissions) used by recently executed SQL
  • Physical Plan Cache (PP Cache): Statements, parsing information, and execution plans of recently used SQL

PGA (PROCESS GLOBAL AREA)

PGA is independent memory allocated individually for each Worker process and consists of four areas.
  • Process Memory : Fixed memory used by the Worker process itself
  • SQL Info Area : Information related to the currently executing SQL statement
  • Session Info Area : Logon and session-related information
  • SQL Work Area : Space dynamically expanded by Worker Threads as a workspace during SQL execution for sorting, hash join, merge join, etc.
 
โ€ป The total PGA that can be allocated to all Worker Threads executing SQL is approximately (MEMORY_TARGET - TOTAL_SHM_SIZE).

 

 

 

Related to