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 11g歷史庫升級遷移資料至19c CDBOracle
- 「Oracle」Oracle 資料庫安裝Oracle資料庫
- Oracle資料庫配置Oracle資料庫
- oracle資料庫與oracle例項Oracle資料庫
- 「Oracle」Oracle資料庫基本概念Oracle資料庫
- 「Oracle」Oracle 資料庫基本概念Oracle資料庫
- Oracle資料庫-----資料庫的基本概念Oracle資料庫
- oracle 備份資料庫，匯出資料庫Oracle資料庫
- DataX將MySql資料庫資料同步到Oracle資料庫MySql資料庫Oracle
- 4.2. Oracle資料庫Oracle資料庫
- Laravel 使用 Oracle 資料庫LaravelOracle資料庫
- oracle資料庫卡頓Oracle資料庫
- Oracle資料庫閃回Oracle資料庫
- oracle資料庫資料字典應用Oracle資料庫
- sqlserver讀取oracle資料庫資料SQLServerOracle資料庫
- Oracle 9i統計資訊備份與恢復Oracle
- Oracle資料庫遷移至PolarDb(阿里雲資料庫)Oracle資料庫阿里
- 「Oracle」Oracle 資料庫備份還原Oracle資料庫
- [Oracle]Oracle資料庫資料被修改或者刪除恢復資料Oracle資料庫
- BBED 修改oracle 資料檔案的 SCN 號來做資料庫不完全恢復。Oracle資料庫
- DataX將Oracle資料庫資料同步到達夢資料庫Oracle資料庫
- oracle資料庫建立資料庫例項-九五小龐Oracle資料庫
- oracle資料庫的impdp，expdpOracle資料庫
- Oracle資料庫語句大全Oracle資料庫
- Oracle資料庫跟蹤SQLOracle資料庫SQL
- Oracle 資料庫打包安裝Oracle資料庫
- Access 匯入 oracle 資料庫Oracle資料庫
- 清理oracle資料庫空間Oracle資料庫
- oracle資料庫%notfound的理解Oracle資料庫
- oracle資料庫調優描述Oracle資料庫
- Oracle：容器資料庫簡介Oracle資料庫
- Python 連線 Oracle資料庫PythonOracle資料庫
- oracle資料庫常用語句Oracle資料庫
- Oracle資料庫表碎片整理Oracle資料庫
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- Oracle資料庫管理——表資料庫高水位及shrink操作Oracle資料庫
- Installing Oracle 9i on OELRHEL 4.8 64bitOracle
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫