The DISPLAY_ACTIVE_SESSION_PLAN function displays the active execution plan in the specified session.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
DBMS_XPLAN.DISPLAY_ACTIVE_SESSION_PLAN(
session_id INTEGER DEFAULT 0,
format VARCHAR2 DEFAULT 'TYPICAL',
svr_ip VARCHAR2 DEFAULT NULL, -- default value: server connected by client
svr_port INTEGER DEFAULT 0 -- default value: server connected by client
)
return DISPLAY_ACTIVE_SESSION_PLAN;
Parameters
| Parameter | Description |
|---|---|
| session_id | The session ID of the user connection. Note that the value is not the session ID of OceanBase Database Proxy (ODP) but the session ID of the connected OBServer node. |
| format | The plan format. Valid values:
|
| svr_ip | The IP address of the OBServer node where the plan is located. The default value is the IP address of the OBServer node to which the session is connected. |
| svr_port | The port number of the OBServer node where the plan is located. The default value is the port number of the OBServer node to which the session is connected. |
Examples
/* Execute a slow SQL statement. */
obclient> SELECT COUNT(*) FROM TABLE(GENERATOR(100000)) A, TABLE(GENERATOR(10000))B;
+------------+
| COUNT(*) |
+------------+
| 1000000000 |
+------------+
1 row in set
/* Connect to another session and run the SHOW PROCESSLIST command to query the ID of the session in which the slow SQL statement is being executed. */
obclient> SHOW PROCESSLIST;
+------------+------+--------------------+------+---------+------+--------+---------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+------------+------+--------------------+------+---------+------+--------+---------------------------------------------------------------------------+
| 3221675847 | SYS | 11.x.x.44:57841 | SYS | Query | 0 | ACTIVE | show processlist |
| 3221668463 | SYS | 11.x.x.44:57530 | SYS | Query | 2 | ACTIVE | select count(*) from table(generator(100000)) A, table(generator(10000))B |
+------------+------+--------------------+------+---------+------+--------+---------------------------------------------------------------------------+
/* Display the details of the execution plan of the slow SQL statement based on the obtained session ID. */
obclient> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_ACTIVE_SESSION_PLAN(3221668463));
+--------------------------------------------------------------------------------------------------------------------+
| COLUMN_VALUE |
+--------------------------------------------------------------------------------------------------------------------+
| ============================================================================================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)| |
| -------------------------------------------------------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |1794 |0 |0 |0 |0 | |
| |1 |└─NESTED-LOOP JOIN CARTESIAN | |39601 |1076 |0 |0 |0 |0 | |
| |2 | ├─FUNCTION_TABLE |A |199 |1 |0 |0 |0 |0 | |
| |3 | └─MATERIAL | |199 |80 |0 |0 |0 |0 | |
| |4 | └─FUNCTION_TABLE |B |199 |1 |0 |0 |0 |0 | |
| ============================================================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 |
| group(nil), agg_func([T_FUN_COUNT(*)]) |
| 1 - output(nil), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 2 - output(nil), filter(nil) |
| value(GENERATOR(cast(:0, BIGINT(-1, 0)))) |
| 3 - output(nil), filter(nil), rowset=256 |
| 4 - output(nil), filter(nil) |
| value(GENERATOR(cast(:1, BIGINT(-1, 0)))) |
+--------------------------------------------------------------------------------------------------------------------+