以下作为数据库升级前部分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/