显示baseline的SQL的执行计划内容

最新推荐文章于 2025-08-02 11:11:28 发布

原创最新推荐文章于 2025-08-02 11:11:28 发布 · 749 阅读

0 ·

CC 4.0 BY-SA版权

本文详细介绍了如何使用DBMS_XPLAN显示SQL计划基线，并通过查询DBA_SQL_PLAN_BASELINES表来获取特定会话的SQL计划基线信息。重点展示了SQL计划的结构、操作类型、成本和时间等关键指标。

1.tool

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_93961960a2d79e68',format=>'all'))

2.first form the view:dba_sql_plan_baselines

select signature,sql_handle,sql_text,plan_name,parsing_schema_name,enabled,accepted,fixed from dba_sql_plan_baselines
where parsing_schema_name='AIKI'
and sql_handle='SQL_93961960a2d79e68'
order by created desc

1 1.06347154729216E19 SQL_93961960a2d79e68 <CLOB> SQL_PLAN_975htc2jdg7m890704d19 AIKI YES YES NO

3. display the plan using dbms_xplan

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_93961960a2d79e68',format=>'all'))
2 ;

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

--------------------------------------------------------------------------------
SQL handle: SQL_93961960a2d79e68
SQL text: select * from t1 where object_id=:object_id and created=:created
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_975htc2jdg7m890704d19         Plan id: 2423278873
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2724989281

---------------------------------------------------------------------------------------------
| Id | Operation                   | Name          | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    13 |     2   (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1            |     1 |    13 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1

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

   1 - filter("CREATED"=:CREATED)
   2 - access("OBJECT_ID"=TO_NUMBER(:OBJECT_ID))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_ID"[NUMBER,22], "CREATED"[DATE,7]
   2 - "T1".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

38 rows selected.