Monitor and manage database threads
You can view a detailed report of thread activity on any running database. Using the information available in the report, you can cancel threads or disconnect users that are negatively affecting database performance.
Monitor and manage database thread activity in Planning Analytics Workspace Local and Planning Analytics Workspace on Cloud
About this task
To open a database thread report, click the Databases tab on the Databases page, then click the database for which you want to view a report. Next, click the Threads tab to display the report.
When you open the Databases page as an administrator, you can see a list of all databases running in your environment. When you attempt to open a database thread report, Planning Analytics attempts to authenticate you as an administrator on the selected database using the same username and password you used to log into Planning Analytics. If your administrator credentials on the selected database are different from those you used to log into Planning Analytics, authentication will fail and you'll be prompted to provide a valid administrator username and password for the database.
A database thread report provides details on every thread on your database, in table format. You can't change the column widths in the report, but you can hover over any item in the report to see the full value.
You can search for multiple words on the Threads tab. Separate each word with a comma. For example, run,admin,Architect. Search is not case-sensitive.
For each thread you can view these details:
- ID
- The unique numeric ID of the thread.
- Name
- The name of the user who initiated the thread.
- State
- The current thread state.
- Idle: not using resources
- Wait: waiting on other threads, blocked
- Running
- Commit: committing updates made by a thread
- Rollback: rolling back to a state before an update
- Login: when a user logs in and initiates a thread
- Type
- Indicates a User or System thread.
- Function
- The API function being executed by the thread.
- Wait
- The length of time, in seconds, that the thread has been in a wait state.
- Elapsed
- The total elapsed time of the thread in its current state.
- W/R/Ix locks
- The number of write, read, and intent-to-write locks that the thread holds.
A write lock allows only the thread that holds the lock to access and write changes to an object. No other thread can read or modify the object until the W lock has been released.
A read lock allows many threads to read from an object at the same time, but does not allow another thread to modify or write to the object until all R locks have been released.
An intent-to-write lock reserves the right for a thread to obtain a write lock on an object when all read locks have been released. Only one thread at a time is allowed to have an Ix lock on an object.
- Context
- The connected Planning Analytics client that is associated with the thread.
- Info
-
When a thread is in a wait state, this column displays the ThreadID of the thread that is blocking the waiting thread.
- Object name
- The name of the object upon which the thread is operating.
- Object type
- The type of object upon which the thread is operating.
You can click to selectively hide or show any of these columns in your report. The column display configuration is retained between sessions. The next time you open a thread report for any database, you'll see the same columns.
Procedure
- To sort values by any column, click directly on a column name to cycle through the sort options.
-
To cancel a thread, click the Thread actions button , then click Cancel
thread.
You can cancel any user thread, including threads you own. You cannot cancel system threads.
-
To disconnect a user, click the Thread actions button , then click Disconnect
user.
You can disconnect user threads, but not system threads. You cannot disconnect yourself.
Monitor and manage database thread activity in Planning Analytics Workspace Classic
About this task
To open a database thread activity report, click the database name on the tile of any running database on the Planning Analytics Monitoring dashboard.
A database thread activity report provides a summary of current thread status, showing the number of threads in Run, Wait, and Other states. The report displays a chart of thread states over time and a table showing details on every thread on your database.
You can hover over any bar on the Database usage chart to see details on users and thread states.
The report also shows the number of connected users and the average number of connections per user, along with database usage metrics. These are the same metrics that you configure on the Thresholds and alerts page.
You can click to download database log files.
For each thread you can view these details, arranged as columns in the report.
- Thread ID
- The unique numeric ID of the thread.
- State
- The current thread state. One of the following states:
- Idle: not using resources
- Wait: waiting on other threads, blocked
- Running
- Commit: committing updates made by a thread
- Rollback: rolling back to a state before an update
- Login: when a user logs in and initiates a thread
- Thread info
-
When a thread is in a wait state, this column displays the ThreadID of the thread that is blocking the waiting thread.
- Wait time
- The length of time, in seconds, that the thread has been in a wait state.
- Thread type
- Indicates a User or System thread.
- W locks
- The number of write locks the thread holds. A write lock allows only the thread that holds the lock to access and write changes to an object. No other thread can read or modify the object until the W lock has been released.
- Function
- The API function being executed by the thread.
- Thread name
- The name of the user who initiated the thread.
- Object type
- The type of object upon which the thread is operating.
- Elapsed time
- The total elapsed time of the thread in its current state.
- Context
- The connected client that is associated with the thread. For example, TM1® Architect, TM1 Perspectives, or Cognos® Analytics.
- Object name
- The name of the object upon which the thread is operating.
- R locks
- The number of read locks the thread holds. A read lock allows many threads to read from an object at the same time, but does not allow another thread to modify or write to the object until all R locks have been released.
- Ix locks
- The number of intent-to-write locks the thread holds. An intent-to-write lock reserves the right for a thread to obtain a write lock on an object when all read locks have been released. Only one thread at a time is allowed to have an Ix lock on an object.
You can click to selectively hide or show any of these columns in your report.