共计 6065 个字符，预计需要花费 16 分钟才能阅读完成。
这篇文章将为大家详细讲解有关如何用 oracle 9i 数据库做 spa，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。
获取 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 就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
