oracle sql plan baseline,Oracle SQL执行计划基线总结（SQL Plan Baseline）

于 2021-04-07 00:48:07 发布
阅读量315 收藏
点赞数
文章标签： oracle sql plan baseline

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

未选定行

SQL> alter system flush shared_pool;

系统已更改。

SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;

ID NAME TYPE

---------- ------------------------------ ---------------

711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL> select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711;

ID NAME TYPE

---------- ------------------------------ ---------------

711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time

2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME

------------------------------------------------------- ------------- ---------- ------------ --------------- --------

select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:27:31

h_stat where id=711

select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41

dh_stat where id=711

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 4vaj9fgjysy9c, child number 0

-------------------------------------

select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where

id=711

Plan hash value: 1845196118

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 124 (100)| |

|* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=711)

已选择19行。

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

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID fm35jcmypb3qu, child number 0

-------------------------------------

select /* outlinetest3 */ /*+ index(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)

已选择20行。

SQL> DECLARE

2 k1 pls_integer;

3 begin

4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

5 sql_id=>'4vaj9fgjysy9c',

6 plan_hash_value=>1845196118

7 );

8 end;

9 /

PL/SQL 过程已成功完成。

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ACC

------------------------------ ------------------------------ ------------------------------------------------------- ---

SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES

h_sta

刚生产sql plan baseline的时候，第一次查询，无法找到执行计划，直到第二次执行的时候，才能看到，如下

SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;

ID NAME TYPE

---------- ------------------------------ ---------------

711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));

PLAN_TABLE_OUTPUT

---------------------------------------------------------

SQL_ID: 4vaj9fgjysy9c cannot be found

SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time

2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME

------------------------------------------------------- ------------- ---------- ------------ --------------- --------

select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41

dh_stat where id=711

SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;

ID NAME TYPE

---------- ------------------------------ ---------------

711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time

2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME

------------------------------------------------------- ------------- ---------- ------------ --------------- --------

select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:30:54

h_stat where id=711

select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41

dh_stat where id=711

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 4vaj9fgjysy9c, child number 0

-------------------------------------

select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where

id=711

Plan hash value: 1845196118

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 124 (100)| |

|* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=711)

Note

-----

- SQL plan baseline SQL_PLAN_13g6p1maja17934f41c8d used for this statement

已选择23行。

将符合我们预期的执行计划的加载到第一次生成的sql baseline中！

SQL> DECLARE

2 k1 pls_integer;

3 begin

4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

5 sql_id=>'fm35jcmypb3qu',

6 plan_hash_value=>2780970545,sql_handle=>'SYS_SQL_11bcd50cd51504e9'

7 );

8 end;

9 /

PL/SQL 过程已成功完成。

可以看到，SYS_SQL_11bcd50cd51504e9下目前有两个plan_name

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ACC

------------------------------ ------------------------------ ------------------------------------------------------- ---

SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja1790cce5f0e select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES

h_sta

SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES

h_sta

删除第一个plan_name，即将我们不需要的执行计划版本去除掉！

SQL> DECLARE

2 k1 pls_integer;

3 begin

4 k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja17934f41c8d');

5 end;

6 /

PL/SQL 过程已成功完成。

通过下面的一部分测试，我们可以看到，新的SQL计划基线已经正常生效，及时语句中包含full提示，执行计划也走索引定位数据

SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;

ID NAME TYPE

---------- ------------------------------ ---------------

711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));

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

-----------------------------------------------------------------------------------------------------------------------------------------------

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX_RS_ASC(@"SEL$1" "DH_STAT"@"SEL$1" ("DH_STAT"."ID"))

已选择6行。

czxz1234
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值