熊军 oracle 迁移 spm,Oracle ---- 固定执行计划之SPM

今天终于说到了SPM，一个ORACLE官方推荐使用的SQL执行计划固定方法。为什么最常使用的方法最后说呢？因为介绍SPM使用方法的公开资料最多，ORACLE提供的接口也最丰富，用一篇几百字的小文进行说明，总觉得会挂一漏万，以偏概全。

但是考虑再三，作为ORACLE官方大力推荐和持续开发完善的固定执行计划的主要手段，还是不能不说的。

还是像前几个方法一样，我们用一个小的测试例子来说明SPM的使用方法。

1.做成测试用Table。conn test/test@localhost:1521/pdb

create table tab2(c1 number, c2 number, c3 varchar2(10));

declare

a number;

begin

a := 1;

for i in 1 .. 50 loop

for j in 1 .. 100 loop

insert into tab2 values(a,j,'a');

commit;

a := a+1;

end loop;

end loop;

end;

/

create index ind2_2 on tab2(c2);

exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'TAB2',cascade=>TRUE);

2.执行原始SQL文。set autot on

set lin 120 pages 999

select count(*) from tab2 where c2=1;

COUNT(*)

----------

50

実行計画

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

Plan hash value: 3563712581

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

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

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

| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 3 | | |

|* 2 | INDEX RANGE SCAN| IND2_2 | 50 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("C2"=1)

統計

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

4 recursive calls

25 db block gets

4 consistent gets

0 physical reads

832 redo size

573 bytes sent via SQL*Net to client

398 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

3.查看对象SQL文的sql_id和plan_hash_value。conn sys/*****@localhost:1521/pdb

select sql_id, child_number, plan_hash_value from v$sql where sql_text = 'select count(*) from tab2 where c2=1';

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE

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

3uat1k9ssur9p 0 3563712581

4.把对象SQL文的执行计划Load进SPM。SQL> var cnt number;

SQL> exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id', plan_hash_value => '&plan_hash_value');

sql_idに値を入力してください: 3uat1k9ssur9p

plan_hash_valueに値を入力してください: 3563712581

PL/SQLプロシージャが正常に完了しました。

SQL> print :cnt

CNT

----------

1

5.查看SQLPLAN的BaseLine。col sql_handle for a30

col SQL_TEXT for a50

col PLAN_NAME for a30

set lin 120 pages 999

select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1';

SQL_HANDLE SQL_TEXT PLAN_NAME FIX

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

SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stx910d0b22 NO

6.固定执行计划。SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle', plan_name => '&plan_name', attribute_name => 'FIXED', attribute_value => 'YES');

sql_handleに値を入力してください: SQL_156e46a7ed64e33d

plan_nameに値を入力してください: SQL_PLAN_1avk6nzqq9stx910d0b22

PL/SQLプロシージャが正常に完了しました。

SQL> select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1';

SQL_HANDLE SQL_TEXT PLAN_NAME FIX

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

SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stx910d0b22 YES

7.执行原始SQL文，查看SQLPLAN BASELINE是否被使用。conn test/test@localhost:1521/pdb

set autot on

set lin 120 pages 999

SQL> select count(*) from tab2 where c2=1;

COUNT(*)

----------

50

実行計画

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

Plan hash value: 3563712581

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

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

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

| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 3 | | |

|* 2 | INDEX RANGE SCAN| IND2_2 | 50 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("C2"=1)

Note

-----

- SQL plan baseline "SQL_PLAN_1avk6nzqq9stx910d0b22" used for this statement

統計

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

20 recursive calls

41 db block gets

11 consistent gets

0 physical reads

4472 redo size

573 bytes sent via SQL*Net to client

398 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

8.执行加Hint的SQL文。SQL> select /*+ FULL (TAB2) */ count(*) from tab2 where c2=1;

COUNT(*)

----------

50

実行計画

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

Plan hash value: 2781695375

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

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

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

| 0 | SELECT STATEMENT | | 1 | 3 | 5 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 3 | | |

|* 2 | TABLE ACCESS FULL| TAB2 | 50 | 150 | 5 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("C2"=1)

統計

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

4 recursive calls

25 db block gets

16 consistent gets

0 physical reads

904 redo size

573 bytes sent via SQL*Net to client

417 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

9.查看加Hint的SQL文的sql_id和plan_hash_value。conn sys/*****@localhost:1521/pdb

select sql_id, child_number, plan_hash_value from v$sql where sql_text like 'select /*+ FULL (TAB2) */ count(*) from tab2 where c2=1';

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE

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

556psnns1a273 0 2781695375

10.把加了Hint的SQL文的执行计划加到原始SQL文的BaseLine上。SQL> var cnt number;

SQL> exec :cnt := dbms_spm.load_plans_from_cursor_cache( sql_id => '&hinted_SQL_ID', plan_hash_value => &hinted_plan_hash_value, sql_handle => '&sql_handle_for_original');

hinted_sql_idに値を入力してください: 556psnns1a273

hinted_plan_hash_valueに値を入力してください: 2781695375

sql_handle_for_originalに値を入力してください: SQL_156e46a7ed64e33d

PL/SQLプロシージャが正常に完了しました。

SQL> print :cnt

RES

----------

1

11.查看SQLPLAN的BaseLine。col sql_handle for a30

col SQL_TEXT for a50

col PLAN_NAME for a30

set lin 120 pages 999

select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1';

SQL_HANDLE SQL_TEXT PLAN_NAME FIX

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

SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stx910d0b22 YES

SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stxfbe5cdec NO

12.删除最初Load的BaseLine。SQL> exec :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('&original_sql_handle','&original_plan_name');

original_sql_handleに値を入力してください: SQL_156e46a7ed64e33d

original_plan_nameに値を入力してください: SQL_PLAN_1avk6nzqq9stx910d0b22

PL/SQLプロシージャが正常に完了しました。

SQL> print :cnt

CNT

----------

1

13.固定后来Load进来的执行计划。SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle', plan_name => '&plan_name', attribute_name => 'FIXED', attribute_value => 'YES');

sql_handleに値を入力してください: SQL_156e46a7ed64e33d

plan_nameに値を入力してください: SQL_PLAN_1avk6nzqq9stxfbe5cdec

PL/SQLプロシージャが正常に完了しました。

SQL> print :cnt

CNT

----------

1

SQL> select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1';

SQL_HANDLE SQL_TEXT PLAN_NAME FIX

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

SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stxfbe5cdec YES

14.执行原始SQL文，再次查看SQLPLAN。conn test/test@localhost:1521/pdb

set autot on

set lin 120 pages 999

select count(*) from tab2 where c2=1;

COUNT(*)

----------

50

実行計画

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

Plan hash value: 2781695375

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

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

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

| 0 | SELECT STATEMENT | | 1 | 3 | 5 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 3 | | |

|* 2 | TABLE ACCESS FULL| TAB2 | 50 | 150 | 5 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("C2"=1)

Note

-----

- SQL plan baseline "SQL_PLAN_1avk6nzqq9stxfbe5cdec" used for this statement

統計

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

118 recursive calls

41 db block gets

83 consistent gets

0 physical reads

4520 redo size

573 bytes sent via SQL*Net to client

398 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

1 rows processed