当前位置：首页 > article >正文

Oracle 数据库使用SPM固定执行计划

article 2025/1/5 21:28:26

使用SPM固定执行计划

SQL Plan Management （简称SPM）
SPM是Oracle推荐使用的一种方法。使用起来也很简单。以下是一个手动固定执行计划的例子：
create table zzh_tb01 (id number, name varchar2(30));
create table zzh_tb02 (id number, name varchar2(30));
insert into zzh_tb01 values (1,'ahern');
insert into zzh_tb02 values (1,'zzh');
以如下语句为例，先执行该语句：
SQL> select * from zzh_tb01 a, zzh_tb02 b where a.id = b.id;
ID NAME ID NAME
---------- ------------------------------ ---------- ------------------------------
1 ahern 1 zzh

收集该查询语句的SQL_ID 以及 PLAN_HASH_VALUE ：
SQL> col sql_text for a60
SQL> select sql_id, hash_value, plan_hash_value, sql_text from v$sql where sql_text like 'select * from zzh_tb01 a, zzh_tb02 b where a.id = b.id%';
SQL_ID        HASH_VALUE PLAN_HASH_VALUE SQL_TEXT
------------- ---------- --------------- ------------------------------------------------------------
37uy7rwzvm55y 1069126846       876607223 select * from zzh_tb01 a, zzh_tb02 b where a.id = b.id

先确认一下目前该语句的执行计划：
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '37uy7rwzvm55y'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 37uy7rwzvm55y, child number 0
-------------------------------------
select * from zzh_tb01 a, zzh_tb02 b where a.id = b.id

Plan hash value: 876607223

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| ZZH_TB01 | 1 | 30 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| ZZH_TB02 | 1 | 30 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."ID"="B"."ID")

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
- dynamic statistics used: dynamic sampling (level=2)

24 rows selected.

将该语句的cursor信息载入到Baseline中：
SQL> DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id => '37uy7rwzvm55y', plan_hash_value => '876607223');
END;
/

确认载入之后的状态：
SQL> col plan_name for a30
SQL> select sql_handle,plan_name, origin, enabled, accepted, fixed, reproduced, autopurge from dba_sql_plan_baselines where sql_text like 'select * from zzh_tb01 a, zzh_tb02 b where a.id = b.id%';

SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX REP AUT
------------------------------ ------------------------------ ----------------------------- --- --- --- --- ---
SQL_8f0f55a93a637e86 SQL_PLAN_8y3upp4x66zn6dbf94702 MANUAL-LOAD-FROM-CURSOR-CACHE YES YES NO YES YES
注意：这里我们看 FIX 这列是 NO，说明目前该执行计划没有被固定。

执行如下语句将固定执行计划：
SQL> var res number
SQL> exec :res := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_8f0f55a93a637e86', -
  plan_name => 'SQL_PLAN_8y3upp4x66zn6dbf94702', -
  attribute_name => 'FIXED', -
  attribute_value => 'YES' );

PL/SQL procedure successfully completed.

再次来看baseline信息，FIX列已经变成了YES。
SQL> select sql_handle,plan_name, origin, enabled, accepted, fixed, reproduced, autopurge from dba_sql_plan_baselines where sql_text like 'select * from zzh_tb01 a, zzh_tb02 b where a.id = b.id%';
SQL_HANDLE                     PLAN_NAME                      ORIGIN                        ENA ACC FIX REP AUT
------------------------------ ------------------------------ ----------------------------- --- --- --- --- ---
SQL_8f0f55a93a637e86           SQL_PLAN_8y3upp4x66zn6dbf94702 MANUAL-LOAD-FROM-CURSOR-CACHE YES YES YES YES YES

这个时候我们再执行同样的语句，发现执行计划被固定并被查询使用，如下：
SQL> set autotrace on
SQL> select * from zzh_tb01 a, zzh_tb02 b where a.id = b.id;
        ID NAME                                   ID NAME
---------- ------------------------------ ---------- ------------------------------
         1 ahern                                   1 zzh

Execution Plan
----------------------------------------------------------
Plan hash value: 876607223

-------------------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    60 |     6   (0)| 00:00:01 |
|* 1 | HASH JOIN         |          |     1 |    60 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| ZZH_TB01 |     1 |    30 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| ZZH_TB02 |     1 |    30 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."ID"="B"."ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - SQL plan baseline "SQL_PLAN_8y3upp4x66zn6dbf94702" used for this statement
   -- 上述这里表明baseline SQL_PLAN_8y3upp4x66zn6dbf94702 被用于该语句。

Statistics
----------------------------------------------------------
        998 recursive calls
         65 db block gets
       1287 consistent gets
          7 physical reads
      12204 redo size
        777 bytes sent via SQL*Net to client
        416 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
         52 sorts (memory)
          0 sorts (disk)
          1 rows processed

查看全文

http://www.kler.cn/a/465034.html