數據庫遷移，還擔心執行改變嗎？

背景：

遷移數據庫過程中，我們經常會擔心遷移到新的數據庫系統后，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