如何用oracle 9i数据库做spa

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

这篇文章将为大家详细讲解有关如何用oracle 9i数据库做spa，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

获取trace文件的脚本
mkdir /oracle/qwedir
create table sys.tab_ospid (SID NUMBER,SERIAL# NUMBER,PADDR RAW(8),SPID VARCHAR2(12));

启动捕获trace文件脚本
#!/bin/bash
. /oracle/.profile
sqlplus -s "/ as sysdba" <<QWEEOF
set linesize 1000
set pagesize 0
set feedback off
set heading off
set trimout on
set trimspool on
spool /oracle/qwedir/trackspid.log
select 'exec dbms_system.set_ev('||aa.sid||','||aa.serial#||',10046,4,'''');'
from v\$session aa
where aa.type='USER'
and aa.username in ('ZHJS_APP','ZHJSBY','ZHJS_USER','ZHJS_PARAM')
and aa.program not like 'sqlplus%'
and aa.program<>'plsqldev.exe'
and aa.status='ACTIVE'
and not exists (select 1 from sys.tab_ospid bb where bb.sid=aa.sid and bb.serial#=aa.serial#);
spool off
insert into sys.tab_ospid
select aa.sid,aa.serial#,aa.paddr,bb.spid
from v\$session aa,v\$process bb
where aa.type='USER'
and aa.username in ('ZHJS_APP','ZHJSBY','ZHJS_USER','ZHJS_PARAM')
and aa.program not like 'sqlplus%'
and aa.program<>'plsqldev.exe'
and aa.status='ACTIVE'
and aa.paddr=bb.addr
and not exists (select 1 from sys.tab_ospid cc where cc.sid=aa.sid and cc.serial#=aa.serial#);
commit;
exit;
QWEEOF

ORA_CMD=`cat /oracle/qwedir/trackspid.log`

echo "-------------------------------------- "
echo $ORA_CMD
echo "-------------------------------------- "

sqlplus -s "/ as sysdba" <<ASDEOF
${ORA_CMD}
exit;
ASDEOF

rm /oracle/qwedir/trackspid.log

exit;

可以放在crontab 定时几秒中运行

终止trace文件脚本
#!/bin/bash
. /oracle/.profile
sqlplus -s "/ as sysdba" <<QWEEOF
set linesize 1000
set pagesize 0
set feedback off
set heading off
set trimout on
set trimspool on
spool /oracle/qwedir/ktrack.log
select 'exec dbms_system.set_ev('||sid||','||serial#||',10046,0,'''');'
from sys.tab_ospid;
spool off
exit;
QWEEOF

KORA_CMD=`cat /oracle/qwedir/ktrack.log`

echo "-------------------------------------- "
echo $KORA_CMD
echo "-------------------------------------- "

sqlplus -s "/ as sysdba" <<ASDEOF
${KORA_CMD}
exit;
ASDEOF

rm /oracle/qwedir/ktrack.log

exit;

获取trace文件，将trace文件scp到其他服务器
select 'scp -P12321 /oracle/product/9.2/db/rdbms/log/cntjs_ora_'||SPID||'.trc oracle@135.148.12.1:/backup' from sys.tab_ospid;

ps -ef|grep LOCAL=NO

sqlplus "/ as sysdba"
oradebug setospid 11368
oradebug event 10046 trace name context forever, level 12;
oradebug tracefile_name
oradebug event 10046 trace name context off;

alter system set events '10046 trace name context forever,level 12';
alter system set events '10046 trace name context off';

create table mapping_table tablespace TJ_BK as
select object_id id,owner,substr(object_name,1,30) name from dba_objects
where object_type not in ('CONSUMER GROUP','EVALUATION CONTEXT','FUNCTION','INDEXTYPE','JAVA CLASS','JAVA DATA','JAVA RESOURCE','LIBRARY','LOB','OPERATOR','PACKAGE','PACKAGE BODY','PROCEDURE','QUEUE','RESOURCE PLAN','SYNONYM','TRIGGER','TYPE','TYPE BODY')
union all
select user_id id,username owner,null name from dba_users;

exp \' / as sysdba\' buffer=4096000 file=/backup/mapping.dmp tables=mapping_table
scp mapping.dmp 135.148.12.1:/backup

目标端

imp \' sys/oracle@cntjs as sysdba\' file=/backup/mapping.dmp fromuser=sys touser=sys

sqlplus \' sys/oracle@cntjs as sysdba\'
create or replace directory SPADIR as '/backup';

---创建sqlset
declare
mycur dbms_sqltune.sqlset_cursor;
begin
dbms_sqltune.create_sqlset('9i_prod_wkld');
open mycur for select value(p)
from table(dbms_sqltune.select_sql_trace(
directory => 'TRCDIR',
file_name => '%ora%',
mapping_table_name => 'MAPPING_TABLE',
select_mode => dbms_sqltune.SINGLE_EXECUTION)) p;
dbms_sqltune.load_sqlset( sqlset_name => '9i_prod_wkld',
populate_cursor => mycur,
commit_rows => 1000);
close mycur;
end;
/
-----删除sqlset
BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => '9i_prod_wkld' );
END;
/

---查看sqlset数据内容
select name,statement_count from dba_sqlset;

variable sts_task VARCHAR2(64);
EXEC :sts_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( -
task_name => '9i_12c_spa', -
description => 'experiment for 9i to 12c upgrade', -
sqlset_name => '9i_prod_wkld');

exec dbms_sqlpa.execute_analysis_task( -
task_name => '9i_12c_spa', -
execution_name => '9i_trial', -
execution_type => 'CONVERT SQLSET', -
execution_desc => '9i sql trial generated from STS');

exec dbms_sqlpa.execute_analysis_task( -
task_name => '9i_12c_spa',-
execution_name => '12c_trial',-
execution_type => 'TEST EXECUTE',-
execution_desc => '12c sql trial generated from STS');

select * from v$session_longops where target_desc='sts=9i_prod_wkld';
select sofar,totalwork from v$advisor_progress where task_id='';

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => '9i_12c_spa',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_buffer_gets',
execution_params => dbms_advisor.arglist('execution_name1','9i_trial','execution_name2','12c_trial','comparison_metric','buffer_gets'));
end;
/

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => '9i_12c_spa',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_cpu_time',
execution_params => dbms_advisor.arglist('execution_name1','9i_trial','execution_name2','12c_trial','comparison_metric','cpu_time'));
end;
/

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => '9i_12c_spa',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_elapsed_time',
execution_params => dbms_advisor.arglist('execution_name1','9i_trial','execution_name2','12c_trial','comparison_metric','elapsed_time'));
end;
/

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => '9i_12c_spa',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_disk_reads',
execution_params => dbms_advisor.arglist('execution_name1','9i_trial','execution_name2','12c_trial','comparison_metric','disk_reads'));
end;
/

SELECT dbms_sqlpa.report_analysis_task('9i_12c_spa', 'HTML', 'ALL','ALL', execution_name=>'Compare_disk_reads') FROM dual;

SELECT dbms_sqlpa.report_analysis_task('9i_12c_spa', 'HTML', 'ALL','ALL', execution_name=>'Compare_buffer_gets') FROM dual;

SELECT dbms_sqlpa.report_analysis_task('9i_12c_spa', 'HTML', 'ALL','ALL', execution_name=>'Compare_cpu_time') FROM dual;

SELECT dbms_sqlpa.report_analysis_task('9i_12c_spa', 'HTML', 'ALL','ALL', execution_name=>'Compare_elapsed_time') FROM dual;

--获取所有SQL的性能变化情况
set lines 188 pages 9999 long 999999 trim on trims on
spool spa_elapsed_20180504.html
select xmltype(dbms_sqlpa.report_analysis_task( -
'9i_12c_spa', 'html', 'all', 'all', null, 100, -
'Compare_elapsed_time')).getclobval(0,0) from dual;
spool off

--获取不支持的SQL列表
spool spa_elapsed_unsupported_20180504.html
select xmltype(dbms_sqlpa.report_analysis_task( -
'9i_12c_spa', 'html', 'unsupported', 'all', null, 100, -
'Compare_elapsed_time')).getclobval(0,0) from dual;
spool off

--获取所有执行出错的SQL列表
spool spa_elapsed_errors_20180504.html
select xmltype(dbms_sqlpa.report_analysis_task( -
'9i_12c_spa', 'html', 'errors', 'all', null, 100, -
'Compare_elapsed_time')).getclobval(0,0)-
from dual;
spool off