oracle数据库10g升级到11g性能分析比较

最新推荐文章于 2024-09-26 07:27:23 发布

烟雨归来

最新推荐文章于 2024-09-26 07:27:23 发布

阅读量1.4k

点赞数 33

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.csdn.net/lmxx2020/article/details/136238238

版权

oracle 专栏收录该内容

70 篇文章 1 订阅

订阅专栏

1 逻辑迁移SPA性能报告分析

SQL Performance Analyzer：SQL性能优化分析器

1.1 创建spa存放数据用户

用来做10g的Spa数据保存
create user spauser identified by oracle;
grant connect,resource,dba to spauser;
conn spauser/oracle

1.2 创建SQL Tuning Set:

exec dbms_sqltune.create_sqlset(‘spa10g’);

1.2 加载数据到sqlset

Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
his1 ZZUHIS 81474 21 Feb 2020 00:00 1
81475 21 Feb 2020 01:00 1
81476 21 Feb 2020 02:00 1
81477 21 Feb 2020 03:00 1
81478 21 Feb 2020 04:00 1
81479 21 Feb 2020 05:00 1
81480 21 Feb 2020 06:00 1
81481 21 Feb 2020 07:00 1
81482 21 Feb 2020 08:00 1
81483 21 Feb 2020 09:00 1
81484 21 Feb 2020 10:00 1
81485 21 Feb 2020 11:00 1
81486 21 Feb 2020 12:00 1
81487 21 Feb 2020 13:00 1
81488 21 Feb 2020 14:00 1
加载8到9点快照之间
declare
own VARCHAR2(30) := ‘spauser’;
bid NUMBER := ‘&begin_snap’;
eid NUMBER := ‘&end_snap’;
stsname VARCHAR2(30) :=‘spa10g’;
sts_cur dbms_sqltune.sqlset_cursor;
begin
open sts_cur for
select value§ from table(dbms_sqltune.select_workload_repository(bid,eid, null, null, null, null, null, 1, null, ‘ALL’)) P;
dbms_sqltune.load_sqlset(sqlset_name => stsname,populate_cursor => sts_cur,load_option => ‘MERGE’);
end;
/
10 11 12 Enter value for begin_snap: 81483
old 3: bid NUMBER := ‘&begin_snap’;
new 3: bid NUMBER := ‘81483’;
Enter value for end_snap: 81484
old 4: eid NUMBER := ‘&end_snap’;
new 4: eid NUMBER := ‘81484’;
PL/SQL procedure successfully completed.
也可以通过其他方式加载指定数据，如下

1.4 创建中转表

创建一个中转表，将SQL Tuning Set导入到中转表，导出中转表并传输到测试库；
–不要使用sys用户创建stgtab表
exec DBMS_SQLTUNE.create_stgtab_sqlset(table_name => ‘SQLSET_TAB’,schema_name => ‘SPAUSER’,tablespace_name => ‘SYSAUX’);
–将优化集打包到stgtab表里面
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => ‘spa10g’,
sqlset_owner => ‘SPAUSER’,
staging_table_name => ‘SQLSET_TAB’,
staging_schema_owner => ‘SPAUSER’);
END;
/

1.5 数据库升级

物理升级，刷试图升级（略）

1.6 创建sqlset

正常情况下，无需操作，异常重建sqlset，并做解压操作
exec DBMS_SQLTUNE.create_sqlset(sqlset_name => ‘spa10g’);

1.7 解压到sqlset

正常无需操作，异常做解压，解压中转表的数据到SQL Tuning Set
BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => ‘spa10g’,
sqlset_owner => ‘SPAUSER’,
replace => TRUE,
staging_table_name => ‘SQLSET_TAB’,
staging_schema_owner => ‘SPAUSER’);
END;
/

1.8 新建SPA分析任务

var tname varchar2(30);
var sname varchar2(30);
exec :sname := ‘spa10g’;
exec :tname := ‘SPA_TEST’;
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);

1.9 生成10g的trail

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => ‘SPA_TEST’,
execution_type => ‘CONVERT SQLSET’,
execution_name => ‘ORCL_10G’);
end;
/

1.10 清空shared pool和buffer cache

alter system flush shared_pool;
alter system flush BUFFER_CACHE;

1.11 生成11g的trail

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => ‘SPA_TEST’,
execution_type => ‘TEST EXECUTE’,
execution_name => ‘ORCL_11G’);
end;
/
该执行需要花些时间。

1.12 执行比较任务

从elapsed_time来进行比较
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => ‘SPA_TEST’,
execution_type => ‘COMPARE PERFORMANCE’,
execution_name => ‘Compare_elapsed_time’,
execution_params => dbms_advisor.arglist(‘execution_name1’, ‘ORCL_10G’, ‘execution_name2’, ‘ORCL_11G’, ‘comparison_metric’, ‘elapsed_time’) );
end;
/

从cpu_time来进行比较
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => ‘SPA_TEST’,
execution_type => ‘COMPARE PERFORMANCE’,
execution_name => ‘Compare_CPU_time’,
execution_params => dbms_advisor.arglist(‘execution_name1’, ‘ORCL_10G’, ‘execution_name2’, ‘ORCL_11G’, ‘comparison_metric’, ‘CPU_TIME’) );
end;
/
从buffer_gets来进行比较
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => ‘SPA_TEST’,
execution_type => ‘COMPARE PERFORMANCE’,
execution_name => ‘Compare_BUFFER_GETS_time’,
execution_params => dbms_advisor.arglist(‘execution_name1’, ‘ORCL_10G’, ‘execution_name2’, ‘ORCL_11G’, ‘comparison_metric’, ‘BUFFER_GETS’) );
end;
/

1.13再生成SPA报告

获取全部结果：
ALTER SESSION SET EVENTS ‘31156 trace name context forever, level 0x400’;
SET LONG 9999999 longchunksize 100000 linesize 200 head off feedback off echo off
spool 10g_11g_change.html
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(‘SPA_TEST’,
‘HTML’,
‘TYPICAL’,
‘ALL’,
NULL,
100,
NULL,
NULL,
NULL)
FROM DUAL;
spool off

分开生成SPA报告
set trimspool on
set trim on
set pages 0
set long 999999999
set linesize 1000

spool spa_report_elapsed_time.html
SELECT dbms_sqlpa.report_analysis_task(‘SPA_TEST’, ‘HTML’, ‘ALL’,‘ALL’, top_sql=>300,execution_name=>‘Compare_elapsed_time’) FROM dual;
spool off;

spool spa_report_CPU_time.html
SELECT dbms_sqlpa.report_analysis_task(‘SPA_TEST’, ‘HTML’, ‘ALL’,‘ALL’, top_sql=>300,execution_name=>‘Compare_CPU_time’) FROM dual;
spool off;

spool spa_report_buffer_time.html
SELECT dbms_sqlpa.report_analysis_task(‘SPA_TEST’,‘HTML’,‘ALL’,‘ALL’,top_sql=>300,execution_name=>‘Compare_BUFFER_GETS_time’) FROM dual;
spool off;

spool spa_report_errors.html
SELECT dbms_sqlpa.report_analysis_task(‘SPA_TEST’, ‘HTML’, ‘errors’,‘summary’) FROM dual;
spool off;

spool spa_report_unsupport.html
SELECT dbms_sqlpa.report_analysis_task(‘SPA_TEST’, ‘HTML’, ‘unsupported’,‘all’) FROM dual;
spool off;
/

1.14 11g数据从awr快照中获取

11g也可以从现有awr报告中获取数据做分析数据来源，跟10g的做比较
先删除spa分析任务并重新创建
SQL> exec DBMS_SQLPA.DROP_ANALYSIS_TASK(‘SPA_TEST’);
var tname varchar2(30);
var sname varchar2(30);
exec :sname := ‘spa10g’;
exec :tname := ‘SPA_TEST’;
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);
生成10g的trail
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => ‘SPA_TEST’,
execution_type => ‘CONVERT SQLSET’,
execution_name => ‘ORCL_10G’);
end;
/
创建seqset
exec DBMS_SQLTUNE.create_sqlset(sqlset_name => ‘spa11g’);
加载awr快照到sqlset
declare
own VARCHAR2(30) := ‘spauser’;
bid NUMBER := ‘&begin_snap’;
eid NUMBER := ‘&end_snap’;
stsname VARCHAR2(30) :=‘spa11g’;
sts_cur dbms_sqltune.sqlset_cursor;
begin
open sts_cur for
select value§ from table(dbms_sqltune.select_workload_repository(bid,eid, null, null, null, null, null, 1, null, ‘ALL’)) P;
dbms_sqltune.load_sqlset(sqlset_name => stsname,populate_cursor => sts_cur,load_option => ‘MERGE’);
end;
/
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => ‘SPA_TEST’,
EXECUTION_TYPE => ‘convert sqlset’,
EXECUTION_NAME => ‘ORCL_11G’,
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST(‘sqlset_name’,
‘spa11g’,
‘sqlset_owner’,
‘SPAUSER’));
END;
/
执行比较任务
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => ‘SPA_TEST’,
execution_type => ‘COMPARE PERFORMANCE’,
execution_name => ‘Compare_elapsed_time’,
execution_params => dbms_advisor.arglist(‘execution_name1’, ‘ORCL_10G’, ‘execution_name2’, ‘ORCL_11G’, ‘comparison_metric’, ‘elapsed_time’) );
end;
/

获取结果：
SET LONG 9999999 longchunksize 100000 linesize 200 head off feedback off echo off
spool 10g_11g_change.html
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(‘10g_compare_11g’,
‘HTML’,
‘TYPICAL’,
‘ALL’,
NULL,
100,
NULL,
NULL,
NULL)
FROM DUAL;
spool off

二、SPA性能比较报告

2.1 总体情况

升级前后比价：

全部155条语句，执行计划改变的有45条
性能改进的有2条
性能倒退的5条
性能不变的有135条
带报错的有8条
不支持的有5条

2.2 Top 100 sql

2.3 查看具体sql信息

如sql_id为：fp6mb0ja0u1xp，性能变差了，执行计划也变了

计划Hash Value变了：

三、优化

3.1 SQL Tuning Advisor优化建议

对升级后11g收集的sqlset为“spa11g”加载的快照信息进行分析，获取SQL Tuning Advisor优化建议

DECLARE
  sts_task   VARCHAR2(64);
  tname      VARCHAR2(100);
  sta_exists number;
BEGIN
  tname := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sqlset_name  => 'spa11g', 
        rank1        => 'BUFFER_GETS',
        time_limit   => 360,
        task_name    => 'compare_2sets',
        description  => 'Tune AWR Workload for upgrade to 11g');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( 
     task_name      => 'compare_2sets');
END;
/

获取优化语句
SET SERVEROUT ON
SET PAGESIZE 1000
SET LONG 2000000
SET LONGCHUNKSIZE 100000
SET LINESIZE 10000
SET PAGESIZE 10000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name=>‘compare_2sets’, section=>‘FINDINGS’, result_limit => 20) FROM DUAL;
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(task_name=>‘compare_2sets’, rec_type=>‘ALL’) FROM DUAL;

execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'WRH$_ACTIVE_SESSION_HISTORY', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'SCHEDULER$_EVENT_LOG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'ACCESS$', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'PROCEDUREPLSQL$', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'IDL_UB1$', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'WRM$_SNAPSHOT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'WRI$_ADV_TASKS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'DEPENDENCY$', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
create index TPCC.IDX$$_004D0002 on TPCC.STOCK("S_W_ID","S_QUANTITY","S_I_ID");
create index TPCC.IDX$$_004D0001 on TPCC.ORDER_LINE("OL_D_ID","OL_W_ID","OL_O_ID","OL_I_ID");
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 2, plan_hash_value => 3773649912);
execute  dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 2, plan_hash_value => 3773649912);
execute   dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 2, plan_hash_value => 3773649912);
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 31, plan_hash_value => 3352376173);
execute  dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 31, plan_hash_value => 3352376173);
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 52, plan_hash_value => 2544153582);
execute  dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 52, plan_hash_value => 2544153582);
execute   dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 52, plan_hash_value => 2544153582);
execute    dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 52, plan_hash_value => 2544153582);
execute     dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 52, plan_hash_value => 2544153582);
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'compare_2sets', object_id => 25, plan_hash_value => 3643809231);

通过该优化结果优化完，继续做spa性能分析比较，跟最新的snap快照做比较。
先把11g做的spa分析数据删除，再按照1.14方法：

SQL> select description, created, owner
  2  from DBA_SQLSET_REFERENCES
  3  where sqlset_name ='spa11g';

DESCRIPTION
--------------------------------------------------------------------------------
CREATED   OWNER
--------- ------------------------------
created by: SQL Performance Analyzer - task: 10g_compare_11g
19-FEB-20 SPAUSER

created by: SQL Performance Analyzer - task: 10_compare_11
19-FEB-20 SPAUSER

created by: SQL Tuning Advisor - task: compare_2sets
20-FEB-20 SPAUSER


SQL> exec DBMS_SQLPA.DROP_ANALYSIS_TASK('compare_2sets');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SQLPA.DROP_ANALYSIS_TASK('10_compare_11');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SQLPA.DROP_ANALYSIS_TASK('10g_compare_11g');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SQLTUNE.drop_sqlset(sqlset_name => 'spa11g');

PL/SQL procedure successfully completed.