Dynamic Views ​

DV_DSS_TIME_STATS ​

DSS device information view. It supports collecting operation statistics of DSS devices in the database, recording the wait time and execution count of each DSS operation. It can diagnose I/O bottlenecks and latency issues at the storage layer, helping DBAs identify storage performance problems such as high-latency read and write operations.

Field description:

DV_SQL_EXECUTION ​

A view of historical execution plans for SQL statements in the database. You can query the execution plan and status information of each SQL statement historically executed in the database for SQL performance analysis and optimization, and compare the performance differences of different execution plans through historical data.

Field description:

DV_SLOW_SQL ​

Slow SQL view. It records slow SQL statements whose execution time exceeds the preset threshold, including key information such as SQL text, execution time, wait events, and lock waits, which can be used for performance optimization.

The slow SQL view information is obtained from slow SQL logs. Therefore, to obtain slow SQL information, you need to enable slow SQL logging:

--Enable slow SQL logging, corresponding to _LOG_LEVEL = 256
alter system set SLOWSQL_LOG_MODE=ON;
--Set the threshold for slow SQL logging, used to determine whether a SQL statement should be logged as slow
alter system set SQL_STAGE_THRESHOLD=2;
--Check the setting result
show parameter SLOWSQL_LOG_MODE;
show parameter SQL_STAGE_THRESHOLD;

Field description:

NOTE

1. Only statements whose execution time exceeds the preset threshold and that are DML statements (SELECT, UPDATE, INSERT, DELETE, MERGE, REPLACE) are recorded.

2. STAGE indicates the SQL execution stage (PREPARE / EXECUTE / PREP_EXEC / QUERY / FETCH). When a command in a certain stage of an SQL statement times out, it is recorded in the slow SQL log. When the execution time of an SQL statement times out, it is also recorded in the slow SQL log, and the recorded STAGE is EXECUTE.

DV_DRC_BUF_INFO ​

Local node buffer page view. It can monitor the buffer page usage of the local database node, display page status information, and can be used for memory management analysis, especially when lock conflicts or page conversion delays occur.

Field description:

DV_DRC_LOCAL_LOCK_INFO ​

Lock information view of the local database node. It can be used to monitor lock usage on the local database node, helping identify issues such as lock contention and deadlocks.

Field description:

DV_BUF_CTRL_INFO ​

View of buffer control block information for the local database node. It can be used for buffer management and page status analysis.

Field description: