1 文档背景
现因客户现场需求，需要对CRM1进行数据库迁移，迁移前需要对新环境进行SPA性能压测，下文内容为SPA的操作步骤。
2.11g旧环境相关操作
2.1 11g旧环境情况
数据库版本：oracle11g
服务器：IBM
2.2 表空间使用率查询
操作前对业务表空间大小进行查询，确保有足够的空间进行SPA用户创建以及相关表的创建。
select a.tablespace_name, round((a.maxbytes / 1024 / 1024 / 1024), 2) "tab_maxG", round(((a.bytes - b.bytes) / 1024 / 1024 / 1024), 2) "tab_usedG", round(((a.maxbytes - a.bytes + b.bytes) / 1024 / 1024 / 1024), 2) "tab_freeG", round(((a.bytes - b.bytes) / a.maxbytes) * 100, 2) "tab_pct(%)" from (select tablespace_name, sum(sumnow) bytes, sum(summax) maxbytes from (select t1.tablespace_name, sum(t1.bytes) sumnow, sum(t1.maxbytes) summax from dba_data_files t1 where t1.maxbytes <> 0 group by t1.tablespace_name union all select t2.tablespace_name, sum(t2.bytes) sumnow, sum(t2.bytes) summax from dba_data_files t2 where t2.maxbytes = 0 group by t2.tablespace_name) group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by ((a.bytes - b.bytes) / a.maxbytes) desc; |
2.3 在业务表空间创建SPA用户
#根据现场管理规范，可以使用和业务用户相同的表空间，建议最好创建spa的自己表空间后期压测完成可以进行清理。 create user spa identified by "密码" default tablespace 业务用户表空间名; grant connect ,resource to spa; grant ADMINISTER SQL TUNING SET to spa; grant execute on dbms_sqltune to spa; grant select any dictionary to spa; grant advisor to spa; grant dba to spa; |
2.4 创建SQL集合
2.4.1 登录连接SPA用户
#登录数据库连接SPA用户，之后所有的操作都是使用SPA进行操作 sqlplus / as sysdba conn spa/"密码" |
2.4.2 创建名为ora11g的SQL集合
#集合名称和描述可以自行定义 BEGIN DBMS_SQLTUNE.CREATE_SQLSET ( sqlset_name => 'ora11g0909', description => 'ora11g0909' ); END; / |
2.4.3 查询创建的集合
select name,OWNER,CREATED,STATEMENT_COUNT from dba_sqlset where owner='SPA'; |
2.5 将业务SQL放入ora11g0909集合中
将业务SQL放入在SQL集合中，一般会采取将当前内存中的SQL放入集合中，然后再AWR中历史SQL也放进去（具体时间根据需求选择），这样能够保障大多数SQL都被采集到。
2.5.1 将内存中的SQL语句放入ora11g0909SQL集合中
DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(p) FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''',NULL,NULL,NULL,NULL,1,NULL,'ALL')) p; DBMS_SQLTUNE.LOAD_SQLSET ( sqlset_name => 'ora11g0909', populate_cursor => cur,load_option=>'MERGE' ); END; / |
2.5.2 将AWR的SQL语句放入到ora11g0909SQL集合中
#awr报告的开始时间和结束时间，一般取业务高峰期的3天左右即可 declare own VARCHAR2(30) := 'SPA'; bid NUMBER := 1; eid NUMBER := 4; stsname VARCHAR2(30) :='ora11g0909'; sts_cur dbms_sqltune.sqlset_cursor; begin open sts_cur for select value(P) from table(dbms_sqltune.select_workload_repository(bid,eid, 'parsing_schema_name <> ''SYS''', null, null, null, null, 1, null, 'ALL')) P; dbms_sqltune.load_sqlset(sqlset_name => stsname,populate_cursor => sts_cur,load_option => 'MERGE',commit_rows=>1000); end; / |
2.6 删除ora11g0909SQL集合中的解析相关的内容
begin DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'ora11g0909',basic_filter => 'SQL_TEXT LIKE ''%Analyze(%''',sqlset_owner => 'SPA'); END; / |
2.7 查询SQL集合内容
COLUMN SQL_TEXT FORMAT a30 COLUMN SCH FORMAT a3 COLUMN ELAPSED FORMAT 999999999 SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, ELAPSED_TIME AS "ELAPSED", BUFFER_GETS FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('ora11g0909')); |
2.8 创建sts中转表
#创建中转表T1，表名必须大写 exec dbms_sqltune.create_stgtab_sqlset(table_name=>'T1',schema_name=>'SPA',tablespace_name => '业务表空间名'); exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name=>'ora11g0909',sqlset_owner=>'SPA',staging_table_name =>'T1',staging_schema_owner=>'SPA'); |
2.9 去掉硬解析和未使用变量的SQL
#因为未使用绑定变量的缘故，存在很多sqlid，会导致表内容过多分析起来耗时较长， 这里根据FORCE_MATCHING_SIGNATURE类进行去重，表名T1必须大写。 #临时中间表t_temp1进行过滤操作 create table t_temp1 as select FORCE_MATCHING_SIGNATURE,max(sql_id) sqlid from T1 group by FORCE_MATCHING_SIGNATURE; delete from T1 where sql_id not in (select sqlid from t_temp1); commit; |
2.10 导出测试数据
expdp spa/密码 tables=t1 directory=dpdata1 dumpfile=t1.dmp cluster=n |
3 19c目标端SPA操作步骤
3.1 目标端环境
数据库：oracle19C
服务器：oracle-linux 一体机
3.2 创建SPA用户
#新建SPA用户并使用业务用户表空间 create user spa identified by "密码" default tablespace 业务用户表空间; grant connect ,resource to spa; grant ADMINISTER SQL TUNING SET to spa; grant execute on dbms_sqltune to spa; grant select any dictionary to spa; grant advisor to spa; grant dba to spa; |
3.3 导入测试表
impdp spa/"密码"@ip/pdb dumpfile=t1.dmp directory=dpdata1 cluster=no |
3.4 创建ora19cSQL集合
#创建ora19c集合 exec DBMS_SQLTUNE.create_sqlset(sqlset_name => 'ora19C'); #查看新建的集合 select name,OWNER,CREATED,STATEMENT_COUNT from dba_sqlset where owner='SPA'; |
3.5 解析旧环境传过来的SQL集合包
BEGIN DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'ora11g0909', sqlset_owner => 'SPA', replace => TRUE, staging_table_name => 'T1', staging_schema_owner => 'SPA'); END; / |
3.6 创建spa任务
var tname varchar2(30); var sname varchar2(30); exec :sname := 'ora11g0909'; exec :tname := 'SPA_01'; exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname); |
3.7 源端和目标端平台不同时设置参数
#如果源端和目标端平台不同时执行以下参数设置，如果相同可以跳过该步骤 BEGIN DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER( task_name => 'SPA_01', parameter => 'PLAN_LINES_COMPARISON', value => 'ALWAYS'); END; / |
3.8 超时设置
#执行之前刷新内存 alter system flush shared_pool; alter system flush buffer_cache; #超时时间根据实际情况设置，尽量不要太大 exec dbms_sqlpa.set_analysis_task_parameter('SPA_01', 'LOCAL_TIME_LIMIT', 60); exec bms_sqlpa.set_analysis_task_parameter('SPA_01', parameter=>'WORKLOAD_IMPACT_THRESHOLD', value=>0); |
3.9 执行比较分析任务
#生成19c的任务 begin BMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPA_01', execution_type => 'CONVERT SQLSET', execution_name => 'CONVERT_19c'); end; / #生成11g任务 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPA_01', execution_type => 'TEST EXECUTE', execution_name => 'EXEC_11G'); end; / #从buffer_gets来进行比较 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPA_01', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_BUFFER_GETS_time', execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_19c', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'BUFFER_GETS') ); end; / #从elapsed_time来进行比较 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPA_01', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_elapsed_time', execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_19c', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'elapsed_time') ); end; / #从cpu time 来进行比较 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPA_01', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_CPU_time', execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_19c', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'CPU_TIME') ); end; / |
3.10 生成SPA对比报告
set trimspool on set trim on set pages 0 set linesize 1000 set long 2000000000 set longchunksize 1000000 #生成BUFFER_GETS_time对比报告 spool spa_ngstapdb_report_buffer_time.html SELECT dbms_sqlpa.report_analysis_task('SPA_01','HTML','ALL','ALL',top_sql=>500,execution_name=>'Compare_BUFFER_GETS_time') FROM dual; spool off; #生成elapsed_time对比报告 spool spa_ngstapdb_report_elapsed_time.html SELECT dbms_sqlpa.report_analysis_task('SPA_01', 'HTML', 'ALL','ALL', top_sql=>500,execution_name=>'Compare_elapsed_time') FROM dual; spool off; #生成CPU_time对比报告 spool spa_ngstapdb_report_CPU_time.html SELECT dbms_sqlpa.report_analysis_task('SPA_01', 'HTML', 'ALL','ALL', top_sql=>500,execution_name=>'Compare_CPU_time') FROM dual; spool off; #生成错误输出报告 spool spa_ngstapdb_report_errors.html SELECT dbms_sqlpa.report_analysis_task('SPA_01', 'HTML', 'errors','ALL') FROM dual; spool off; #生成超时内容报告 spool spa_ngstapdb_report_timeout.html SELECT dbms_sqlpa.report_analysis_task('SPA_01', 'HTML', 'TIMEOUT','ALL') FROM dual; spool off; #生成不支持内容报告 spool spa_ngstapdb_report_unsupport.html SELECT dbms_sqlpa.report_analysis_task('SPA_01', 'HTML', 'unsupported','all') FROM dual; spool off; |
