SPA测试

以下作为数据库升级前部分SQL语句执行计划性能对比的测试。该测试结合命令与使用OEM图像化工具，
方便直观地看到某些SQL语句的执行计划在系统升级前后的性能的变化。

--生成目录对象：

SQL> create or replace directory tune_results as '/home/oracle/';

Directory created.

 

--进入scripts目录：

[oracle@oracle scripts]$ pwd

/u01/app/oracle/admin/ORA11GR2/scripts

--执行导入旧系统导出的SQL文件：

[oracle@oracle scripts]$ impdp directory=tune_results dumpfile=appsandstage.dmp logfile=appsandstage.log

 

Import: Release 11.2.0.4.0 - Production on Fri Oct 14 23:50:17 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Username: / as sysdba

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=tune_results dumpfile=appsandstage.dmp logfile=appsandstage.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "APPS"."FACT_PD_OUT_ITM_293"                175.0 MB 1501663 rows

. . imported "APPS"."ADM_PG_FEATUREVALUE"                14.26 MB  172392 rows

. . imported "APPS"."LU_ELEMENTGROUP_REL"                3.478 MB   84468 rows

. . imported "APPS"."LU_OUTLET_293"                      3.404 MB   22289 rows

. . imported "APPS"."LU_ITEM_293"                        1.280 MB    5355 rows

. . imported "APPS"."LU_ELEMENTRANGE_REL"                791.0 KB    8155 rows

. . imported "APPS"."LU_ELEMENTRANGE_REL_Q2"             791.0 KB    8155 rows

. . imported "APPS"."STS_JFV_TAB"                        123.8 KB      29 rows

. . imported "APPS"."STS_JFV_TAB_CPLANS"                 281.8 KB     500 rows

. . imported "APPS"."STS_JFV_PS"                         46.48 KB       8 rows

. . imported "APPS"."ADM_CC_FEATUREVALUE"                250.2 KB    3047 rows

. . imported "APPS"."LU_PG_FEATUREVALUE_15_Q2"           123.5 KB    2505 rows

. . imported "APPS"."STS_JFV_PS_CPLANS"                  106.5 KB     145 rows

. . imported "APPS"."LU_PERIOD_293"                      8.593 KB      31 rows

. . imported "APPS"."STS_JFV_PS_CBINDS"                      0 KB       0 rows

. . imported "APPS"."STS_JFV_TAB_CBINDS"                     0 KB       0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Fri Oct 14 23:55:13 2016 elapsed 0 00:04:36

 

--执行程序包：

SQL> exec dbms_sqltune.unpack_stgtab_sqlset('%','%',true,'STS_JFV_PS','APPS');

 

PL/SQL procedure successfully completed.

图一：分析测试


图二：选定分析的两个系统的版本信息


图三：选择对比分析的类型



图四：开始进行执行计划性能变化分析



图五：查看分析报告


图六：查看分析报告中SQL语句在高版本中性能下降的语句


--将分析报告中性能下降的语句放到优化器中：

优化：将下降的sql执行计划加入baseline

DECLARE

  my_plans PLS_INTEGER;

  my_plans1 PLS_INTEGER;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'STS_PS',basic_filter=>'sql_id=''bxajng3zk2vn1''');

my_plans1 := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'STS_PS',basic_filter=>'sql_id=''bxajng3zk2vn1''');

END;

/

SQL>

SQL> DECLARE

  2    my_plans PLS_INTEGER;

  3    my_plans1 PLS_INTEGER;

  4  BEGIN

  5    my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'STS_PS',basic_filter=>'sql_id=''bxajng3zk2vn1''');

  6  my_plans1 := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'STS_PS',basic_filter=>'sql_id=''bxajng3zk2vn1''');

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

 

分析完成，并将性能下降的语句获取到binslene:

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2134718/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2134718/