关于数据库:Oracle-固定执行计划之SPM

4次阅读

共计 8242 个字符，预计需要花费 21 分钟才能阅读完成。

明天终于说到了 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
正文完
数据库
发表至： 数据库
2021-02-24
 0
​我们的系统需要什么样的分布式锁
关于数据库:Tapdata-Connector实用指南如何将-CRM-数据从-Salesforce-实时同步到MongoDB等其他数据库
关于数据库:MongoDB数据库新手入门
关于数据库:数据库系统概论王珊第二章关系数据库习题

站内搜索

-「」