如何用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" <

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" <

${ORA_CMD}

exit;

ASDEOF

rm /oracle/qwedir/trackspid.log

exit;

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

终止trace文件脚本

#!/bin/bash

. /oracle/.profile

sqlplus -s "/ as sysdba" <

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" <

${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就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。