The DISPLAY_ACTIVE_SESSION_PLAN function displays the active execution plan in the specified session.
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 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)))) |
+--------------------------------------------------------------------------------------------------------------------+