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
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30345407/viewspace-2153835/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 9i 資料庫建立手冊Oracle資料庫
- oracle 9I 資料庫異機恢復( Catalog 庫 )Oracle資料庫
- Oracle 9i 配置備用資料庫步驟Oracle資料庫
- oracle 9i的資料庫的dmp匯入到10G資料庫Oracle資料庫
- oracle 9i升級到oracle9208資料庫Oracle資料庫
- oracle 9i資料庫夯住無法歸檔Oracle資料庫
- ORACLE 9i資料庫優化案例(4) --- 索引改善UPDATEOracle資料庫優化索引
- Oracle 9i 資料庫WITH查詢語法小議(轉)Oracle資料庫
- Oracle資料庫9i特性：iSQLPLUS 埠7778Oracle資料庫SQL
- Windows系統崩潰後恢復Oracle 9i資料庫WindowsOracle資料庫
- 網路關係型資料庫的代表Oracle 9i(轉)資料庫Oracle
- [轉]ORACLE資料庫管理員應做的事Oracle資料庫
- Oracle 資料庫歸檔配置-9i,10g,11gOracle資料庫
- Oracle資料庫9i在AIX環境下的效能調整Oracle資料庫AI
- oracle 9i 建庫模板Oracle
- Oracle 9i資料庫的使用者建立以及許可權分配Oracle資料庫
- Oracle 9i Data Guard進行資料庫的災難防護（轉）Oracle資料庫
- oracle rat spaOracle
- 9i資料庫hang死分析處理資料庫
- oracle 9i 手動建庫Oracle
- 用 Oracle sql*loader 把公司的資料庫從 sql server 遷移到 Oracle 9i 中OracleSQL資料庫Server
- 9i新特性之資料庫監控系列資料庫
- 用java做oracle資料庫備份有問題！急急急！！！JavaOracle資料庫
- Oracle 資料庫Oracle資料庫
- Oracle 讀書 -- Oracle 9i & 10g程式設計藝術資料庫體系結構Oracle程式設計資料庫
- oracle 9i DBCA建庫報錯Oracle
- Oracle 9i Data Guard進行資料庫的災難防護簡介(轉)Oracle資料庫
- Oracle 9i & 10g程式設計藝術資料庫體系結構Oracle程式設計資料庫
- 資料庫-oracle-資料庫遷移資料庫Oracle
- 9i 克隆+data guard 實現資料庫搬遷資料庫
- 手工升級9i資料庫到11g資料庫
- Oracle 9i/10g/11g資料庫升級路線圖總覽Oracle資料庫
- oracle 9i、10g、11g資料庫設定listener密碼的方法Oracle資料庫密碼
- ORACLE 9i資料庫優化案例(3) --- 低相異值的列也可以建立索引Oracle資料庫優化索引
- 對 Oracle 9i 新資料型別的學習Oracle資料型別
- BBED 修改oracle 資料檔案的 SCN 號來做資料庫不完全恢復。Oracle資料庫
- oracle SPA 效能分析案例Oracle
- Oracle SPA使用詳解Oracle