oracle 稳定执行计划 spm 之三模拟生产环境使用固定sql执行计划

秒速五厘米 2022-09-06 00:11 70阅读 0赞

os: centos 7.6.1810  
db: oracle 19.3

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.6.1810 (Core)
    #
    # 
    # su - oracle
    Last login: Thu Aug 19 08:23:16 CST 2021 on pts/2
    $
    $
    $ sqlplus / as sysdba;
    
    SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 19 08:23:53 2021
    Version 19.3.0.0.0
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0
    
    SQL> set lines 500;
    set pages 500;
    set serverout on;

# 数据准备 #

SQL> drop table tmp_t1;
    
    SQL> create table tmp_t1 (
     id integer primary key,
     name varchar2(60),
     memo varchar2(60)
    )
    ;
    
    create index idx1_tmp_t1_name on tmp_t1(name);
    create index idx2_tmp_t1_name_memo on tmp_t1(name,memo);
    
    insert into tmp_t1
    select level as id,
           sys_guid() as name,
           sys_guid() as memo
     from dual
     connect by level <=100000
    ;
    
    commit;

**生产环境不可执行**如下两个 flush 语句

SQL> alter system flush shared_pool;
    alter system flush buffer_cache;

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

# 获得异常SQL的SQL\_ID,装载到SPM里 #

--模拟线上原始sql
    select max(id) from tmp_t1 where name = 'C9D1DAA05F96405FE0535B38A8C009B0';
    
    --查看原始sql的 sql_id，PLAN_HASH_VALUE
    col sql_id format   a30;
    col plan_hash_value format 999999999999999;
    col sql_plan_baseline format a50;
    col sql_text format  a80;
    
    select sa.sql_id,sa.plan_hash_value,sa.sql_plan_baseline,sa.sql_text
           --sa.*
    from v$sqlarea sa
    where 1=1
    and sa.SQL_TEXT like '%max(id)%'
    ;
    
    SQL_ID        PLAN_HASH_VALUE SQL_PLAN_BASELINE          SQL_TEXT
    ------------------------------ ---------------- -------------------------------------------------- --------------------------------------------------------------------------------
    f7z0usaamngu1          3603056274 SQL_PLAN_9pzgk1hvvfkdd4509a91e         select max(id) from tmp_t1 where name = 'C9D1DAA05F96405FE0535B38A8C009B0'
    3mmdhb6qbz9c6           232555890                select sa.sql_id,sa.plan_hash_value,sa.sql_plan_baseline,sa.sql_text        --sa
                               .* from v$sqlarea sa where 1=1 and sa.SQL_TEXT like '%max(id)%'

SQL\_ID= f7z0usaamngu1  
PLAN\_HASH\_VALUE= 3603056274

异常SQL的执行计划装载到 SPM 里

set serverout on;
    declare
      v_temp number;
    begin
      v_temp := dbms_spm.load_plans_from_cursor_cache(
                  sql_id=>'f7z0usaamngu1', 
                  plan_hash_value=>3603056274,
                  fixed  => 'YES',
                  enabled => 'YES'
                );
      dbms_output.put_line(v_temp);
    end;
    /

–确认执行计划已经被装载到SPM。

col sql_handle format a50;
    col plan_name format a50;
    col origin format a20;
    col enabled format a10;
    col accepted format a10;
    col sql_text format a80;
    col last_modified format a20;
    
    select sql_handle, plan_name, origin,enabled, accepted, sql_text ,last_modified
     from dba_sql_plan_baselines 
    where 1=1
      and accepted = 'YES'
    order by last_modified
    ;
    
    SQL_HANDLE             PLAN_NAME                ORIGIN       ENABLED    ACCEPTED   SQL_TEXT                   LAST_MODIFIED
    -------------------------------------------------- -------------------------------------------------- -------------------- ---------- ---------- -------------------------------------------------------------------------------- --------------------
    SQL_9afdf20c37b749ad           SQL_PLAN_9pzgk1hvvfkdd4509a91e         MANUAL-LOAD-FROM-CUR YES        YES    select max(id) from tmp_t1 where name = 'C9D1DAA05F96405FE05       19-AUG-21 08.44.19.0
                                  SOR-CACHE            35B38A8C009B0'                   00000 AM

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

# 优化SQL语句 #

通常是增加新的hint，确认正确的执行计划

--添加hint
    select/*+ pybtest999 index(tmp_t1 idx2_tmp_t1_name_memo)*/ max(id) from tmp_t1 where name = 'C9D1DAA05F96405FE0535B38A8C009B0';
    
    --查看优化sql的 sql_id，PLAN_HASH_VALUE
    col sql_id format   a30;
    col plan_hash_value format 999999999999999;
    col sql_plan_baseline format a50;
    col sql_text format  a80;
    
    select sa.sql_id,sa.plan_hash_value,sa.sql_plan_baseline,sa.sql_text
           --sa.*
    from v$sqlarea sa
    where 1=1
    and sa.sql_text like '%pybtest999%'
    ;
    
    SQL_ID        PLAN_HASH_VALUE SQL_PLAN_BASELINE          SQL_TEXT
    ------------------------------ ---------------- -------------------------------------------------- --------------------------------------------------------------------------------
    58rg9yr4rt71n           232555890                select sa.sql_id,sa.plan_hash_value,sa.sql_plan_baseline,sa.sql_text        --sa
                               .* from v$sqlarea sa where 1=1 and sa.sql_text like '%pybtest999%'
    
    7gfd3fxczvj3g          3603056274                select/*+ pybtest999 index(tmp_t1 idx2_tmp_t1_name_memo)*/ max(id) from tmp_t1 w
                               here name = 'C9D1DAA05F96405FE0535B38A8C009B0'

SQL\_ID= 58rg9yr4rt71n  
PLAN\_HASH\_VALUE= 232555890

绑定变量的SQL最好通过绑定变量的方式优化。

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

# 将正确的执行计划装载到SPM,准备用来替换 #

set serverout on;
    declare
      v_temp number;
    begin
      v_temp := dbms_spm.load_plans_from_cursor_cache (
           SQL_ID => '58rg9yr4rt71n',
           PLAN_HASH_VALUE => 232555890,
           SQL_HANDLE => 'SQL_9afdf20c37b749ad',
           fixed  => 'YES', 
           enabled => 'YES'
         ) ;
    
      dbms_output.put_line(v_temp);
    end;  
    /

SQL\_ID: 优化后SQL的 SQL\_ID

PLAN\_HASH\_VALUE: 优化后SQL的 PLAN\_HASH\_VALUE

SQL\_HANDLE: 原始SQL的 sql\_handle

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

# 验证SPM执行计划 #

col sql_handle format a50;
    col plan_name format a50;
    col origin format a20;
    col enabled format a10;
    col accepted format a10;
    col sql_text format a80;
    col last_modified format a20;
    
    select sql_handle, plan_name, origin,enabled, accepted, sql_text ,last_modified
     from dba_sql_plan_baselines 
    where 1=1
      and CREATED>sysdate-1/24 
    order by last_modified
    ;
    
    SQL_HANDLE             PLAN_NAME                ORIGIN       ENABLED    ACCEPTED   SQL_TEXT                   LAST_MODIFIED
    -------------------------------------------------- -------------------------------------------------- -------------------- ---------- ---------- -------------------------------------------------------------------------------- --------------------
    SQL_9afdf20c37b749ad           SQL_PLAN_9pzgk1hvvfkdd4509a91e         MANUAL-LOAD-FROM-CUR YES        YES    select max(id) from tmp_t1 where name = 'C9D1DAA05F96405FE05       19-AUG-21 08.44.19.0
                                  SOR-CACHE            35B38A8C009B0'                   00000 AM
    
    
    SQL_9afdf20c37b749ad           SQL_PLAN_9pzgk1hvvfkdd16117c94         MANUAL-LOAD-FROM-CUR YES        YES    select max(id) from tmp_t1 where name = 'C9D1DAA05F96405FE05       19-AUG-21 09.05.12.0
                                  SOR-CACHE            35B38A8C009B0'                   00000 AM

可以发现又2个 PLAN\_NAME,可以通过如下判断哪个是正确的

select * from
    table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('SQL_9afdf20c37b749ad','SQL_PLAN_9pzgk1hvvfkdd4509a91e'));

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

# 从SPM里将错误的执行计划删除 #

set serverout on;
    declare
      v_temp number;
    begin
      v_temp := dbms_spm.drop_sql_plan_baseline(
                    sql_handle=>'SQL_9afdf20c37b749ad', 
                    plan_name=>'SQL_PLAN_9pzgk1hvvfkdd16117c94'
          );
      dbms_output.put_line(v_temp);
    end;
    /

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

# 重新执行语句,检查执行计划 #

explain plan for select max(id) from tmp_t1 where name = 'C9D1DAA05F96405FE0535B38A8C009B0';
    
    select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3603056274
    
    --------------------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |           |     1 |    45 |     0   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE          |           |     1 |    45 |      |      |
    |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TMP_T1        |     1 |    45 |     0   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN        | IDX2_TMP_T1_NAME_MEMO |     1 |       |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("NAME"='C9D1DAA05F96405FE0535B38A8C009B0')
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
       - SQL plan baseline "SQL_PLAN_9pzgk1hvvfkdd4509a91e" used for this statement
    
    20 rows selected.