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