View the thread status

When your database is running, you can view the status of threads in the current cluster as needed to facilitate troubleshooting or performance diagnostics.

Background information

Threads can be classified into worker threads and background threads. Worker threads are tenant-specific and therefore are also called tenant worker threads. Background threads contain both tenant-specific and non-tenant-specific threads.

Procedure

1. Log on to a MySQL or Oracle tenant of the cluster as an administrator user.

2. Execute the following statement to view the status of threads.

   • MySQL mode

     SELECT * FROM oceanbase.GV$OB_THREAD;
     

     A sample query result is as follows:

     +----------------+----------+-----------+------+-----------------+--------+------------+------------+-----------------------------------+
     | SVR_IP         | SVR_PORT | TENANT_ID | TID  | TNAME           | STATUS | LATCH_WAIT | LATCH_HOLD | TRACE_ID                          |
     +----------------+----------+-----------+------+-----------------+--------+------------+------------+-----------------------------------+
     | xx.xx.xx.xx    |     2882 |      1002 | 7268 | T1002_MFLaunch  | Wait   |            |            | Y0-0000000000000000-0-0           |
     | xx.xx.xx.xx    |     2882 |      1002 | 7267 | T1002_MergeSche | Wait   |            |            | Y0-0000000000000000-0-0           |
     | xx.xx.xx.xx    |     2882 |      1002 | 7266 | T1002_FrzInfoDe | Wait   |            |            | YB42AC1E87F4-0005FBC6A0F81772-0-0 |
     | xx.xx.xx.xx    |     2882 |      1002 | 7265 | T1002_ServerPro | Wait   |            |            | Y0-0000000000000000-0-0           |
     | xx.xx.xx.xx    |     2882 |      1002 | 7264 | T1002_Occam     | Wait   |            |            | Y0-0000000000000000-0-0           |
     | xx.xx.xx.xx    |     2882 |      1002 | 7263 | T1002_L0_G2     | Wait   |            |            | Y0-0000000000000000-0-0           |
     | xx.xx.xx.xx    |     2882 |      1002 | 7262 | T1002_L0_G2     | Wait   |            |            | Y0-0000000000000000-0-0           |
     | xx.xx.xx.xx    |     2882 |      1002 | 7102 | T1002_L0_G0     | Wait   |            |            | Y0-0000000000000000-0-0           |
     | xx.xx.xx.xx    |     2882 |      1002 | 7101 | T1002_L0_G0     | Wait   |            |            | Y0-0000000000000000-0-0           |
     | xx.xx.xx.xx    |     2882 |      1002 | 7100 | T1002_L0_G0     | Wait   |            |            | Y0-0000000000000000-0-0           |
     +----------------+----------+-----------+------+-----------------+--------+------------+------------+-----------------------------------+
     10 rows in set
     

   • Oracle mode

     SELECT * FROM SYS.GV$OB_THREAD;
     

     A sample query result is as follows:

     +----------------+----------+-----------+------+--------------+--------+------------+------------+-------------------------+
     | SVR_IP         | SVR_PORT | TENANT_ID | TID  | TNAME        | STATUS | LATCH_WAIT | LATCH_HOLD | TRACE_ID                |
     +----------------+----------+-----------+------+--------------+--------+------------+------------+-------------------------+
     | xx.xx.xx.xx    |     2882 |      1004 | 7372 | T1004_CdcSrv | Sleep  | NULL       | NULL       | Y0-0000000000000000-0-0 |
     +----------------+----------+-----------+------+--------------+--------+------------+------------+-------------------------+
     1 row in set
     
     
     

   In the query result, each row indicates a thread. The query result may vary based on the query time. When a thread is created, the system adds a thread record in the table. When a thread ends, the system deletes the corresponding thread record from the table.

   Columns in the query result are described as follows.

   Column	Description
   SVR_IP	The IP address of the OBServer node where the current thread resides.
   SVR_PORT	The port number of the OBServer node where the current thread resides.
   TENANT_ID	The ID of the tenant to which the current thread belongs. The tenant ID of a process-level thread is 500.
   TID	The ID of the thread.
   TNAME	The name of the thread.
   STATUS	The status of the thread. Valid values: Run: The thread is in the running state. Wait: The thread is in the waiting state. Sleep: The thread is in the sleep state. Join: The thread is waiting for another thread to end.
   LATCH_WAIT	The address of the latch that the current thread is waiting for.
   LATCH_HOLD	The address of the latch held by the current thread. Multiple latches may be held by the thread.
   TRACE_ID	The ID of the trace executed by the current thread.

   For more information about columns in the GV$OB_THREAD view, see GV$OB_THREAD.

More information

For more information about OceanBase Database threads, see Threads.