查看正在执行的SQL的执行计划

Posted 2023-02-02 by wukaiqiang; tagged with none

有时需要抓取正在运行的SQL的执行计划，这时我们需要获取SQL的SQL_ID以及SQL的CHILD_NUMEBR，然后将其代入下面SQL，就能获取正在运行的SQL的执行计划。

select * from table(dbms_xplan.display_cursor('sql_id',child_number));

在一个会话中执行如下SQL。

select count(*) from a,b where a.owner=b.owner;

在另外一个会话中执行如下SQL。

select a.sid, a.event, a.sql_id, a.sql_child_number, b.sql_text
  from v$session a, v$sql b
 where a.sql_address = b.address
   and a.sql_hash_value = b.hash_value
   and a.sql_child_number = b.child_number
 order by 1 desc;

接下来我们将SQL_ID和CHILD_NUMBER代入以下SQL。

SQL> select * from table(dbms_xplan.display_cursor('czr9jwxv0xra6',0));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------

SQL_ID  czr9jwxv0xra6, child number 0
-------------------------------------
select count(*) from a,b where a.owner=b.owner

Plan hash value: 319234518

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |       |  2556 (100)|          |
|   1 |  SORT AGGREGATE     |      |     1 |    34 |       |            |          |
|*  2 |   HASH JOIN         |      |   400M|    12G|  1920K|  2556  (78)| 00:00:31 |
|   3 |    TABLE ACCESS FULL| B    | 67547 |  1121K|       |   187   (1)| 00:00:03 |
|   4 |    TABLE ACCESS FULL| A    | 77054 |  1279K|       |   187   (1)| 00:00:03 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OWNER"="B"."OWNER")

Note
-----
   - dynamic sampling used for this statement (level=2)