OracleSQL执行计划基线总结（SQLPlanBaseline） - 数据库编程

fgjysy9c','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4vaj9fgjysy9c, child number 1 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711 Plan hash value: 2780970545 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=711) Note ----- - SQL plan baseline SQL_PLAN_13g6p1maja1790cce5f0e used for this statement 已选择24行。

可以通过dba_sql_plan_baselines来显示可用的SQL计划基线的一般信息，也可以通过如下这种方式显示执行SQL计划基线的详细信息！ select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'));
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL handle: SYS_SQL_11bcd50cd51504e9 SQL text: select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711 --------------------------------------------------------------------------------
-------------------------------------------------------------------------------- Plan name: SQL_PLAN_13g6p1maja1790cce5f0e Plan id: 214851342 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD --------------------------------------------------------------------------------
Plan hash value: 2780970545
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------

Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("ID"=711) 已选择26行。
查看SQL计划基线中保存的hint提示集合 SQL> conn /as sysdba 已连接。 SQL> select 2 extractvalue(value(d), '/hint') as outline_hints 3 from 4 xmltable('/outline_data/hint' 5 passing ( 6 select 7 xmltype(comp_data) as xmlval 8 from 9 sqlobj$data sod, sqlobj$ so 10 where so.signature = sod.signature 11 and so.plan_id = sod.plan_id 12 and comp_data is not null 13 and name like '&baseline_plan_name' 14 ) 15 ) d; 输入 baseline_plan_name 的值: SQL_PLAN_13g6p1maja1790cce5f0e 原值 13: and name like '&baseline_plan_name' 新值 13: and name like 'SQL_PLAN_13g6p1maja1790cce5f0e'
OUTLINE_HINTS ----------------------------------------------------------------------------------------------------------------------------------------------- I

OracleSQL执行计划基线总结（SQLPlanBaseline）(六)