数据库迁移，还担心执行改变吗？

背景：

迁移数据库过程中，我们经常会担心迁移到新的数据库系统后，SQL执行计划发生变化。通常情况下，我们除了要保证新环境中统计信息的准确，还可以通过迁移SPM BASELIN方式来保证执行计划准确性。

大致步骤：

通过11G的 SPM BASELINE在老库生成一个调优集，然后把这个调优集加载到新的数据库上的，然后通过SPM BASELINE提供的过程来为这个调优集生成BASEINE。

这样就能保证这些调优集里的SQL 执行计划不发生变化，而且还可以通过SPM提供的视图来查看那些SQL在新的环境下自动的产生了一些可以改进的执行计划，如果这种改变是好的，我们可以通过BASELINE重演来接受这种改变。最后可以把没有产生执行计划改变BAELINE删除掉。

概念介绍：

STS（SQL Tuning Set）是一个数据库对象，可以用作调优工具的输入。

从18C开始STS系统包发生了变化DBMS_SQLTUNE→DBMS_SQLSET

Oracle 11g开始，提供了一种新的固定执行计划的方法，即SQL plan baseline，中文名SQL执行计划基线（简称基线），基本上它的主要作用可以归纳为如下两个：

1、稳定给定SQL语句的执行计划，防止执行环境或对象统计信息等等因子的改变对SQL语句的执行计划产生影响。
2、减少数据库中出现SQL语句性能退化的概率，理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上。

通过使用包dbms_spm.load_plans_from_sqlset，从SQL调优集合中加载基线，将SQL计划基线加载到其它数据库中。

本文测试，将源端11g 环境的STS 迁移到 19c pdb中。

参考mos文档：How to Move a SQL Tuning Set from One Database to Another (Doc ID 751068.1)

操作步骤：

一、源端11g:

1. 单独创建STS用户

-- 创建用户
SQL> create user STS identified by STS;

User created.

SQL> grant connect,resource to STS;

Grant succeeded.

--- 授权,否则创建STS会报错：ORA-13750: User "STS" has not been granted the "ADMINISTER SQL TUNING SET"
SQL> grant ADMINISTER  SQL TUNING SET  to STS;

Grant succeeded. 

2. 模拟数据及SQL语句

SQL> connect sxc/sxc
Connected.

SQL> create table hold (id number, col_val varchar2(10)) tablespace users;

Table created.

SQL> insert into hold values ('1', 'more');

1 row created.

SQL> declare v_id number := 1;
  2  v_col_val varchar2(10);
  3  begin
  4  while v_id < 100 loop
  5  v_id:=v_id + 1;
  6  v_col_val:=v_id||'_more';
  7  insert into hold values(v_id, v_col_val);
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'hold', cascade => true);

PL/SQL procedure successfully completed.

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

SQL> select count(*) from hold where id <=100;

  COUNT(*)
----------
       100

SQL> select col_val from hold where id = 100;

COL_VAL
--------------------
100_more

SQL> select max(id) from hold;

   MAX(ID)
----------
       100

3. 新建STS

SQL> BEGIN
  2    DBMS_SQLTUNE.CREATE_SQLSET(
  3      sqlset_name => 'TEST_STS',
  4      sqlset_owner => 'STS',
  5      description  => '11g workload');
  6  END;
  7  /

PL/SQL procedure successfully completed.

-- 查看数据库已经创建的SQLSET
SQL> set line222
SQL> col owner for a10
SQL> col name for a10
SQL> select owner, name, id, created, statement_count from dba_sqlset;

OWNER      NAME               ID CREATED             STATEMENT_COUNT
---------- ---------- ---------- ------------------- ---------------
STS        TEST_STS            6 2022-07-19 09:22:51               0

4. 通过游标缓存从内存中读取sql填充

SQL> declare
  2  mycur dbms_sqltune.sqlset_cursor;
  3  begin
  4  open mycur for
  5  select value (P)
  6  from table(dbms_sqltune.select_cursor_cache('parsing_schema_name <> ''SYS'' and elapsed_time > 0', null, null, null, null,1, null, 'ALL')
  7   ) P;
  8  dbms_sqltune.load_sqlset(sqlset_owner =>'STS',sqlset_name => 'TEST_STS', populate_cursor => mycur);
  9  end;
 10  /

PL/SQL procedure successfully completed.


-- Display the SQL Stored in the STS:
SQL> select sql_text from dba_sqlset_statements where sqlset_name='TEST_STS' and  sql_text like '%hold%';

SQL_TEXT
--------------------------------------------------------------------------------
select max(id) from hold
insert into hold values ('1', 'more')
select count(*) from hold where id <=100
select col_val from hold where id = 100

5. 创建stgtab

SQL> BEGIN
  2  DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB',
  3  schema_name => 'STS',
  4  tablespace_name => 'USERS');
  5  END;
  6  /

PL/SQL procedure successfully completed.

-- 不能新建在SYS账户下
ORA-19381: cannot create staging table in SYS schema

6. Pack TEST_STS into the stgtab

-- From SYS
SQL> CONN / AS SYSDBA
Connected.

SQL> BEGIN
  2    DBMS_SQLTUNE.pack_stgtab_sqlset(      
  3      sqlset_name          => 'TEST_STS',     
  4      sqlset_owner         => 'STS',     
  5      staging_table_name   => 'SQLSET_TAB',
  6      staging_schema_owner => 'STS',
  7      db_version           => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION );
  8  END;
  9  / 

PL/SQL procedure successfully completed.

二、目标端19c:

1. 单独创建STS用户

SXC@ORCLPDB1> create user STS19 identified by STS19;

User created.

SXC@ORCLPDB1> grant connect,resource to STS19;

Grant succeeded.

SXC@ORCLPDB1> grant ADMINISTER SQL TUNING SET to STS19;

Grant succeeded.

2. 将SQLSET_TAB传递到目标服务器

-- 使用Oracle Data Pump or database link or expdp等将表 SQLSET_TAB 迁移到目标服务器.
[oracle@ora19c admin]$ sqlplus  STS19/STS19@ORCLPDB1
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 19 18:06:19 2022
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
STS19@ORCLPDB1> CREATE PUBLIC DATABASE LINK source
  2  CONNECT TO STS
  3  IDENTIFIED BY STS
  4  USING '(DESCRIPTION_LIST=
  5  (DESCRIPTION=
  6  (ADDRESS=(PROTOCOL=tcp)(HOST=172.17.0.2)(PORT=1521))
  7  (CONNECT_DATA=
  8  (SERVICE_NAME=orcl)
  9  )
 10  )
 11  )';

Database link created.

STS19@ORCLPDB1> create table  SQLSET_TAB tablespace USERS as select * from SQLSET_TAB@source;

Table created.

3. 目标服务器新建STS

[oracle@ora19c ~]$ sqlplus  STS19/STS19@ORCLPDB1
 
STS19@ORCLPDB1> BEGIN
  2    DBMS_SQLTUNE.CREATE_SQLSET(
  3      sqlset_name => 'TEST19C_STS',
  4      sqlset_owner => 'STS19',
  5      description  => '19c workload');
  6  END;
  7  /

PL/SQL procedure successfully completed.

4. 导入数据到目标服务器的STS

STS19@ORCLPDB1> BEGIN
  2  DBMS_SQLTUNE.unpack_stgtab_sqlset(
  3  sqlset_name => 'TEST19C_STS',
  4  sqlset_owner => 'STS19',
  5  replace => TRUE,
  6  staging_table_name => 'SQLSET_TAB',
  7  staging_schema_owner => 'STS19');
  8  END;
  9  /
BEGIN
*
ERROR at line 1:
ORA-19377: no "SQL Tuning Set" with name like "TEST19C_STS" exists for owner like "STS19"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_SQLTUNE", line 10510
ORA-06512: at line 2

执行报错，需要重新MAPPING。
The above unpack procedure fails because there is no STS in the staging table named ‘testtarget_test_set’,
owned by SCOTT (or by any other user). Unpack expects you to pass it the name of an STS as it is in the
staging table.

4. MAPPING

-- 重命名STS
STS19@ORCLPDB1> BEGIN
  2   dbms_sqltune.remap_stgtab_sqlset(
  3   old_sqlset_name  => 'TEST_STS',
  4   old_sqlset_owner => 'STS', 
  5   new_sqlset_name  => 'TEST19C_STS',
  6   new_sqlset_owner => 'STS19',
  7   staging_table_name => 'SQLSET_TAB',
  8   staging_schema_owner => 'STS19');
  9  END;
 10  /

PL/SQL procedure successfully completed.

5. 重新导入数据到目标服务器的STS

STS19@ORCLPDB1> BEGIN
  2   DBMS_SQLTUNE.unpack_stgtab_sqlset(
  3   sqlset_name => 'TEST19C_STS',
  4   sqlset_owner => 'STS19',
  5   replace => TRUE,
  6   staging_table_name => 'SQLSET_TAB',
  7   staging_schema_owner => 'STS19');
  8   END;
  9   /

PL/SQL procedure successfully completed.

6. load the plans

-- 通过SPM BASELINE的包来把SQL调优集里的SQL都批量的生成BASELINE
STS19@ORCLPDB1> set serveroutput on
STS19@ORCLPDB1> declare
  2  my_int pls_integer;
  3  begin
  4  my_int := dbms_spm.load_plans_from_sqlset (
  5  sqlset_name => 'TEST19C_STS',
  6  sqlset_owner => 'STS19',
  7  fixed => 'YES',
  8  enabled => 'YES');
  9  DBMS_OUTPUT.PUT_line(my_int);
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-38171: Insufficient privileges for SQL management object operation
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SMB", line 116
ORA-06512: at "SYS.DBMS_SPM", line 821
ORA-06512: at line 4

[oracle@ora19c ~]$ sqlplus sys/oracle@ORCLPDB1 as sysdba

SYS@ORCLPDB1> set serveroutput on
SYS@ORCLPDB1> declare
  2  my_int pls_integer;
  3  begin
  4  my_int := dbms_spm.load_plans_from_sqlset (
  5  sqlset_name => 'TEST19C_STS',
  6  sqlset_owner => 'STS19',
  7  fixed => 'YES',
  8  enabled => 'YES');
  9  DBMS_OUTPUT.PUT_line(my_int);
 10  end;
 11  /
 
44

PL/SQL procedure successfully completed.

7. 验证plan_baseline

SYS@ORCLPDB1> select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
        44

三、其他操作:

1. 删除存在的STS

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'TEST19C_STS',
	sqlset_owner => 'STS19'
    );
END;
/

2. 从sql调优集中删除sql

BEGIN
  DBMS_SQLTUNE.DELETE_SQLSET(
    sqlset_owner => 'STS',
    sqlset_name  => 'TEST_STS'
   ,basic_filter => 'disk_reads < 2000000');
END;
/

3. Load STS各种案例

-- 3.1 Select all statements in the cursor cache.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT value(P)
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P;
  -- Process each statement (or pass cursor to load_sqlset).
  dbms_sqltune.load_sqlset(sqlset_owner =>'STS',sqlset_name => 'TEST_STS', populate_cursor => cur);
  CLOSE cur;
END;
/
 
 
-- Look for statements not parsed by SYS and sql_text like table_name.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur for
    SELECT VALUE(P)
    FROM table(
     DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
	 '(lower(sql_text) LIKE ''%hold%'') and PARSING_SCHEMA_NAME <> ''SYS''','ALL')) P;
  -- Process each statement (or pass cursor to load_sqlset).
   dbms_sqltune.load_sqlset(sqlset_owner =>'STS',sqlset_name => 'TEST_STS', populate_cursor => cur);
  CLOSE cur;
end;
/

 
 
-- All statements from a particular module/action.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(
      DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
         'module = ''MY_APPLICATION'' and action = ''MY_ACTION''')) P;
  -- Process each statement (or pass cursor to load_sqlset)
   dbms_sqltune.load_sqlset(sqlset_owner =>'STS',sqlset_name => 'TEST_STS', populate_cursor => cur);
  CLOSE cur;
END;
/
 
 
-- all statements that ran for at least five seconds
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000')) P;
  -- Process each statement (or pass cursor to load_sqlset)
   dbms_sqltune.load_sqlset(sqlset_owner =>'STS',sqlset_name => 'TEST_STS', populate_cursor => cur);
  CLOSE cur;
end;
/
 
 
-- select all statements that pass a simple buffer_gets threshold and are coming from an APPS user
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(
      DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
        'buffer_gets > 100 and parsing_schema_name = ''APPS'''))P;
  -- Process each statement (or pass cursor to load_sqlset)
   dbms_sqltune.load_sqlset(sqlset_owner =>'STS',sqlset_name => 'TEST_STS', populate_cursor => cur);
  CLOSE cur;
end;
/
 
 
-- select all statements exceeding 5 seconds in elapsed time, but also
-- select the plans (by default we only select execution stats and binds
-- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row
-- is NULL)
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(dbms_sqltune.select_cursor_cache(
      'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL,
      'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN')) P;
  -- Process each statement (or pass cursor to load_sqlset)
   dbms_sqltune.load_sqlset(sqlset_owner =>'STS',sqlset_name => 'TEST_STS', populate_cursor => cur);
  CLOSE cur;
END;
/
 
 
-- Select the top 100 statements in the cursor cache ordering by elapsed_time.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL,
                                                NULL,
                                                'ELAPSED_TIME', NULL, NULL,
                                                1,
                                                100)) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
   dbms_sqltune.load_sqlset(sqlset_owner =>'STS',sqlset_name => 'TEST_STS', populate_cursor => cur);
  CLOSE cur;
end;
/
 
 
-- Select the set of statements which cumulatively account for 90% of the
-- buffer gets in the cursor cache.  This means that the buffer gets of all
-- of these statements added up is approximately 90% of the sum of all
-- statements currently in the cache.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL,
                                                NULL,
                                                'BUFFER_GETS', NULL, NULL,
                                                .9)) P;
  -- Process each statement (or pass cursor to load_sqlset).
   dbms_sqltune.load_sqlset(sqlset_owner =>'STS',sqlset_name => 'TEST_STS', populate_cursor => cur);
  CLOSE cur;
END;
/

-- 使用AWR中高资源的sql来填充sql优化集
-- 查看可用的快照范围（数据库重启过，测试也可以）
SELECT snap_id, instance_number, end_interval_time FROM dba_hist_snapshot ORDER BY snap_id;
--指定快照号从awr中将执行计划load进sqlset
DECLARE
 cur sys_refcursor;
 BEGIN
 OPEN cur FOR 
 SELECT VALUE(P)
 FROM TABLE(
 dbms_sqltune.select_workload_repository(
 begin_snap=>180,
 end_snap=>189,
 basic_filter=>'parsing_schema_name not in (''SYS'',''DBSNMP'',''MDSYS'')',
 attribute_list=>'ALL')
 ) p;
 -- Process each statement (or pass cursor to load_sqlset).
 DBMS_SQLTUNE.LOAD_SQLSET( sqlset_owner=> 'STS', sqlset_name =>'TEST_STS',populate_cursor=>cur);
 CLOSE cur;
 END;
/

4. 增量方式 load sts

-- 可以利用shell脚本，放到后台执行
SQL> BEGIN
  2    DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
  3     SQLSET_NAME=>'TEST_STS',
  4     TIME_LIMIT=> 172800,
  5     REPEAT_INTERVAL=>3600,
  6     basic_filter => 'parsing_schema_name <> ''SYS''',
  7     SQLSET_OWNER => 'STS'
  8     );
  9  END;
 10  /

PL/SQL procedure successfully completed.

-- 参数解释：
--TIME_LIMIT=> 172800,      共抓取48小时;
--REPEAT_INTERVAL=>3600,    每隔1个小时;

SQL> select sql_text from dba_sqlset_statements;

no rows selected


-- 注意：开启capture cursor之后，新加载到内存的SQL才会被抓取，之前v$sql中已存在的SQL，不会被抓取。
SQL> select sql_text from dba_sqlset_statements where sqlset_name='TEST_STS' and  sql_text like '%hold%'; 

SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from hold where id <=100
select col_val from hold where id = 100
select max(id) from hold
delete from  hold where id = 100