10G迁移升级到11G使用SPA 分析SQL性能实例
10G database 执行
14:35:34 SQL> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'RAC38N1',description => 'SPA TEST');
PL/SQL procedure successfully completed
Executed in 0.109 seconds
将快照ID 20660,20661 的SQL 放到SQLSET
14:46:04 SQL> declare cur sys_refcursor;
2 begin
3 open cur for
4 SELECT VALUE(P) FROM
5 TABLE(DBMS_SQLTUNE.select_workload_repository(20660,20661)) p;
6 dbms_sqltune.load_sqlset(sqlset_name => 'RAC38N1',populate_cursor => cur);
7 close cur;
8 end;
9 /
PL/SQL procedure successfully completed
Executed in 0.546 seconds
--也可以用V$SQL中的符合条件的SQL_TEXT建立SQLSET
-- dbms_sqltune.select_cursor_cache('sql_text like ''select * from mytab%''',
null,null,null,null,null,null,'ALL')) P;
--也可以用V$SQL中的符合条件的SQL_ID建立SQLSET
-- dbms_sqltune.select_cursor_cache('sql_text = ''YOUR SQL_ID''',
null,null,null,null,null,null,'ALL')) P;
查看SQLSET中的内容
select * from table(dbms_sqltune.select_sqlset('RAC38N1'));
......result
将10G环境的SQLSET传输到 11G 环境
--transporting
首先在10G中建立存储SQLSET 的表
14:46:06 SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => 'SPA_38N1',schema_name => 'PAYADM');
PL/SQL procedure successfully completed
Executed in 0.438 seconds
将SQLSET的内容PACK到表中
14:51:24 SQL> EXEC DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'RAC38N1',
staging_table_name => 'SPA_38N1',
staging_schema_owner => 'PAYADM');
PL/SQL procedure successfully completed
Executed in 1.047 seconds
11G database 执行
使用EXPDP或者DBLINK的方式将存储SQLSET内容的表结构内容传到11G数据库55.52
运行下面语句将SQLSET 加载到11G SQLSET
15:04:43 SQL> EXEC DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'RAC38N1',
replace => TRUE,
staging_table_name => 'SPA_38N1',
staging_schema_owner => 'PAYADM');
PL/SQL procedure successfully completed
Executed in 0.422 seconds
创建analysis_task RAC38N1
DECLARE V_SPA_NAME VARCHAR2(100);
BEGIN
V_SPA_NAME:=spa_dbms_xhl.create_analysis_task(sqlset_name => 'RAC38N1',task_name => 'spa_38_task');
dbms_output.put_line('spa_name='||v_spa_name);
end;
/
如果没有安装real application testing 组件将报下面错误
ORA-00438: 未安装 Real Application Testing 选件
ORA-06512: 在 "SYS.PRVT_SMGUTIL", line 80
ORA-06512: 在 "SYS.DBMS_SQLPA", line 220
ORA-06512: 在 line 4
shutdown 数据库 运行$ORACLE_HOME/oui/runInstaller 添加此组件
9:38:20 SQL> DECLARE V_SPA_NAME VARCHAR2(100);
2 BEGIN
3 V_SPA_NAME:=dbms_sqlpa.create_analysis_task(sqlset_name => 'RAC38N1',task_name => 'spa_38_task');
4 dbms_output.put_line('spa_name='||v_spa_name);
5 end;
6 /
PL/SQL procedure successfully completed
Executed in 0.578 seconds
生成10G 的trail
9:42:35 SQL> exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',
execution_type => 'CONVERT SQLSET',
execution_name => 'SPA_3810G');
PL/SQL procedure successfully completed
生成11G 的trail
9:43:57 SQL> exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',
execution_type => 'TEST EXECUTE',
execution_name => 'SPA_5211G');
PL/SQL procedure successfully completed
Executed in 57.484 seconds
开始比较
9:54:54 SQL>
elapsed_time 比较
exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',execution_type => 'COMPARE PERFORMANCE',
execution_name => 'compare_elp_time',
execution_params => dbms_advisor.argList('comparison_metric','elapsed_time'));
buffer_gets 比较
exec dbms_sqlpa.execute_analysis_task(task_name => 'spa_38_task',execution_type => 'COMPARE PERFORMANCE', execution_name => 'compare_buff_gets', execution_params => dbms_advisor.argList('comparison_metric','buffer_gets'));
PL/SQL procedure successfully completed
Executed in 0.532 seconds
生成比较报告
select dbms_sqlpa.report_analysis_task('spa_38_task','HTML','ALL','ALL') FROM DUAL
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-774284/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/308563/viewspace-774284/