如何用oracle 9i数据库做spa

这篇文章将为大家详细讲解有关如何用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

关于如何用oracle 9i数据库做spa就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。