OGG rep運行慢的分析步驟
OGG rep運行慢的分析步驟:
第一:按照rep的進程名進行 ps -ef | grep ,獲得rep的進程PID
[Oracle@hosta ~]$ ps -ef | grep repfull
oracle 27906 27861 0 18:01 pts/7 S+ 0:00 \_ grep repfull
oracle 27603 20773 1 17:03 ? Ssl 0:51 \_ /u02/ggs/replicat PARAMFILE /u02/ggs/dirprm/repfull.prm REPORTFILE /u02/ggs/dirrpt/REPFULL.rpt PROCESSID REPFULL USESUBDIRS
第二:按照rep的進程PID 進行 ps -ef | grep,以獲得27603產生的LOCAL=YES的進程
[oracle@hosta ~]$ ps -ef f | grep 27603
oracle 27910 27861 0 18:01 pts/7 S+ 0:00 \_ grep 27603
oracle 27603 20773 1 17:03 ? Ssl 0:52 \_ /u02/ggs/replicat PARAMFILE /u02/ggs/dirprm/repfull.prm REPORTFILE /u02/ggs/dirrpt/REPFULL.rpt PROCESSID REPFULL USESUBDIRS
oracle 27607 27603 2 17:03 ? Ds 1:41 \_ oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) ---->這一個就是。
[oracle@hosta ~]$
sqlplus 裡執行如下sql即可:
SELECT sql_text,sql_id FROM v$sqltext a WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b
WHERE b.SID =( select s.sid from v$session s,v$process p where s.paddr=p.addr and p.spid='27607')) ---> 替換上27607
ORDER BY piece ASC;
select plan_table_output from table(dbms_xplan.display_cursor('6ufrk02y1h6u5')); --->替換為上一步查詢得到的sql_id,查看其執行計畫。