Spa的主要作用: 主要测试由于系统环境变更包括操作系统变更,数据库升级等对sql性能的影响。尽量减少由于系统环境的变更对业务的影响程度。
SPA管理
——导入tunning set并解包:
先利用SecureFX传输dump文件到/home/oracle下
[oracle@wang ~]$ ls
appsandstage.dmp BCT
[oracle@wang ~]$ pwd
/home/oracle
——创建目录对象并授权
SYS@ORA11GR2>create directory tune_results as '/home/oracle';
Directory created.
SYS@ORA11GR2>grant read,write on directory tune_results to public;
Grant succeeded.
——执行导入:
[oracle@wang ~]$ impdp directory=tune_results dumpfile=appsandstage.dmp logfile=appsandstage.log
Import: Release 11.2.0.4.0 - Production on Thu Oct 13 21:29:42 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 Thu Oct 13 21:30:59 2016 elapsed 0 00:01:08
——对APPS用户下的STS_JFV_PS表进行spa分析;
SYS@ORA11GR2>exec dbms_sqltune.unpack_stgtab_sqlset('%','%',true,'STS_JFV_PS','APPS');
PL/SQL procedure successfully completed.
2 .EM通过SPA分析
点击Server页签:
![](https://i-blog.csdnimg.cn/blog_migrate/2ae01fef1e5eb20fa83b7368aaefdde8.png)
![](https://i-blog.csdnimg.cn/blog_migrate/0edf7a48dca0a511e58b9069dbb5f697.png)
![](https://i-blog.csdnimg.cn/blog_migrate/2223df5389279b61566526a1b168cd56.png)
![](https://i-blog.csdnimg.cn/blog_migrate/dcb8dfa91ec82ac905dd94aeed4fa436.png)
然后点击提交按钮;
点击查看最新的报告;
优化:将下降的sql执行计划加入baseline
declare
my_plans pls_integer;
begin
my_plans := dbms_spm.load_plans_from_sqlset( sqlset_name => 'sts_ps',basic_filter=>'sql_id=''7866641pah2zg''');
end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2126493/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2126493/