Oracle 19C 在SQL计划基准中显示计划

例如，假定SELECT具有SQL ID 的语句存在SQL计划基线31d96zzzpcys9。

以下查询显示具有SQL ID的语句的执行计划31d96zzzpcys9：

SELECT PLAN_TABLE_OUTPUT
FROM   V$SQL s, DBA_SQL_PLAN_BASELINES b, 
       TABLE(
       DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic') 
       ) t
WHERE  s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND    b.PLAN_NAME=s.SQL_PLAN_BASELINE
AND    s.SQL_ID='31d96zzzpcys9';

示例查询结果如下：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
 
---------------------------------------------------------------------------
SQL handle: SQL_513f7f8a91177b1a
SQL text: select * from hr.employees where employee_id=100
---------------------------------------------------------------------------

---------------------------------------------------------------------------
Plan name: SQL_PLAN_52gvzja8jfysuc0e983c6         Plan id: 3236529094
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
---------------------------------------------------------------------------
 
Plan hash value: 3236529094
 
-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |
|   2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |
-----------------------------------------------------

结果表明，SQL ID计划31d96zzzpcys已命名SQL_PLAN_52gvzja8jfysuc0e983c6并被自动捕获。