明天终于说到了 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