oracle 稳定执行计划 spm 之二自主绑定、解绑

约定不等于承诺〃 2023-02-22 12:21 29阅读 0赞

os: centos 7.4  
db: oracle 11.2.0.4

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # su - oracle
    Last login: Tue Jan 21 03:40:05 CST 2020 on pts/0
    $ sqlplus / as sysdba;
    
    SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 3 10:29:09 2020
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
    
    SQL> set lines 300;
    SQL> set pages 300;
    SQL> 
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE	11.2.0.4.0	Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production
    
    SQL>

# 准备基础数据 #

# su - oracle
    $ sqlplus system/oracle
    
    SQL> set lines 500;
    set pages 500;
    
    
    SQL> create table tmp_t0 (
      id   integer,
      name varchar(100)
    )
    ;
    
    SQL> insert into tmp_t0
     select level,dbms_random.string('a',20)
       from dual
    connect by level <=1000000
    ;
    
    commit;
    
    SQL> declare
      v_id   integer;
      v_name varchar2(100);
    begin
      for c_f in (
         select      9 as id from dual union all  
         select     99 as id from dual union all
         select    999 as id from dual union all
         select   9999 as id from dual union all
         select  99999 as id from dual union all
         select 999999 as id from dual
      )
      loop
         execute immediate 'select id,name from tmp_t0 where 1=1 and id=:a' into v_id,v_name using c_f.id;
      end loop;
    end;
    /

查找 SQL\_ID 及执行计划

SQL> select sql_text,sql_id from v$sqlarea sa where 1=1 and sa."SQL_TEXT" like 'select id,name from tmp_t0%';
    
    SQL_TEXT	                            SQL_ID
    select id,name from tmp_t0 where 1=1 and id=:a	3m2k16z7wwz0u
    
    
    SQL> select * from v$sql_plan sp where 1=1 and sp."SQL_ID"='3m2k16z7wwz0u';

可以看到 TABLE ACCESS FULL

# spm 固化执行计划 #

SQL> set serverout on;
    declare
      v_temp number;
    begin
      v_temp := dbms_spm.load_plans_from_cursor_cache(
                  sql_id=>'3m2k16z7wwz0u', 
    			  plan_hash_value=>1652152861
    			);
      dbms_output.put_line(v_temp);
    end;
    /
    
    SQL> commit;

查看spm

SQL> select sql_handle,plan_name,origin,enabled,accepted,to_char(sql_text) sql_text from dba_sql_plan_baselines;
    
    SQL_HANDLE		       PLAN_NAME		      ORIGIN	     ENA ACC
    ------------------------------ ------------------------------ -------------- --- ---
    SQL_TEXT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_b4eef12eee3c0831	       SQL_PLAN_b9vrj5vr3s21j273ab46a MANUAL-LOAD    YES YES
    select id,name from tmp_t0 where 1=1 and id=:a

# 创建索引后查看执行计划 #

SQL> create index idx_tmp_t0_id on tmp_t0(id);

再次执行

SQL> declare
      v_id   integer;
      v_name varchar2(100);
    begin
      for c_f in (
         select      9 as id from dual union all  
         select     99 as id from dual union all
         select    999 as id from dual union all
         select   9999 as id from dual union all
         select  99999 as id from dual union all
         select 999999 as id from dual
      )
      loop
         execute immediate 'select id,name from tmp_t0 where 1=1 and id=:a' into v_id,v_name using c_f.id;
      end loop;
    end;
    /

发现从 v$sql\_plan 并没有找到有SQL使用 IDX\_TMP\_T0\_ID 索引，

# SPM 重新绑定使用索引的执行计划 #

先清除之前绑定的信息

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

再执行一遍

SQL> select * from v$sqlarea sa where 1=1 and sa.sql_id='3m2k16z7wwz0u'

会留意到 VERSION\_COUNT=2，再查看

SQL> select * from v$sql_plan sp where 1=1 and sp."SQL_ID"='3m2k16z7wwz0u';

重新固化

SQL> set serverout on;
    declare
      v_temp number;
    begin
      v_temp := dbms_spm.load_plans_from_cursor_cache(
                  sql_id=>'3m2k16z7wwz0u', 
    			  plan_hash_value=>3728389598
    			);
      dbms_output.put_line(v_temp);
    end;
    /
    
    SQL> commit;

参考: