Data Pump işlemi sırasında çalıştırılan sql deyiminin tespiti

Yavaş ilerleyen ya da asılı kalan Data Pump işlemlerinde , o an hangi sorgunun çalıştığının tespiti , sorunun anlaşılmasında yardımcı olacaktır. Aşağıdaki script , Data Pump işleminin her 10 dakikada bir, geçerli bekleme olayıyla birlikte yürütülen sql deyimini gösterecektir.

Öncelikle aşağıdaki kodu içeren dp_current_sql.sql isimli bir script oluşturun.

spool current_sql.out

oradebug setospid &spid

select to_char (sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

oradebug current_sql
break by 1;

select w.event, w.wait_time,w.seconds_in_wait, w.p1,w.p2,w.p3
from v$session s, v$process p, v$session_wait w
where s.paddr=p.addr and s.module='Data Pump Worker' and s.sid=w.sid;

exec DBMS_LOCK.SLEEP(600);

select to_char (sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

oradebug current_sql
break by 1;

select w.event, w.wait_time,w.seconds_in_wait, w.p1,w.p2,w.p3
from v$session s, v$process p, v$session_wait w
where s.paddr=p.addr and s.module='Data Pump Worker' and s.sid=w.sid;

exec DBMS_LOCK.SLEEP(600);

select to_char (sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

oradebug current_sql
break by 1;

select w.event, w.wait_time,w.seconds_in_wait, w.p1,w.p2,w.p3
from v$session s, v$process p, v$session_wait w
where s.paddr=p.addr and s.module='Data Pump Worker' and s.sid=w.sid;

spool off;

sqlplus ile SYSDBA olacak şekilde bağlanın. Aşağıdaki sorgu ile Data Pump Worker (DW00) prosesinin SPID bilgisini tespit edin.

set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char (sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, 
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid 
from v$session s, v$process p, dba_datapump_sessions d 
where p.addr=s.paddr and s.saddr=d.saddr;

 

sqlplus üzerinden yukarıda oluşturduğunuz dp_current_sql.sql script’ini çalıştırın. spid değerini istediğinde yukarıdaki adımda tespit edilen SPID değerini girin.

 

SQL> @dp_current_sql.sql
Enter value for spid:

 

Script’i çalıştıdığınızda aşağıdakine benzer çıktı üretecektir. Çıktıyı bulunduğunuz dizindeki current_sql.out dosyasında da bulabilirsiniz.

 

SQL> @dp_current_sql.sql
Enter value for spid: 70112
Oracle pid: 129, Unix process pid: 70112, image: oracle@oradb.oracle.local (DW00)

TO_CHAR(SYSDATE,'YY
-------------------
2018-10-13 17:29:42


insert /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ 
into sys.dbms_stats_id_map_tab (c5, c1, c2, cn)
select /*+ leading(s) index(s) */ 
distinct s.c5, s.c1, s.c2, i.partition_name cn
from "SYS"."IMPDP_STATS" s, dba_ind_partitions i
where s.c5 = :1 and s.c1 = :2
and s.type in ('I','i') and s.n13 is not null 
and s.c2 like 'SYS_%' and s.c3 is null
and s.c5 = i.index_owner and s.c1 = i.index_name 
and s.n13 = i.partition_position
and s.c2 != i.partition_name and s.statid is null

EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
db file sequential read -1
2 1 151735 1

wait for unread message on broadcast channel 0
2 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
3 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
5 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
3 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
5 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
2 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
5 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
4 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
2 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
4 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
4 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
2 9981956688 1.0534E+10 0


16 rows selected.


PL/SQL procedure successfully completed.


TO_CHAR(SYSDATE,'YY
-------------------
2018-10-13 17:39:43

merge /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ into optstat_user_prefs$ d using (select o.obj#, s.c2 pname, s.n1 valnum, s.cl1 valchar, s.d1 chgtime, s.n2 spare1 from "SYS"."IMPDP_STATS" s, obj$ o, user$ u where s.c5= :ownname and s.c1= :tabname and s.type = 'P' and SYS_OP_MAP_NONNULL(s.statid) = SYS_OP_MAP_NONNULL(:stid) and o.owner#=u.user# and u.name= :ownname and o.type# = 2 and o.name= :tabname ) s on (d.obj#=s.obj# and d.pname=s.pname) when matched then update set valchar=s.valchar when NOT matched then insert (obj#, pname, valnum, valchar, chgtime, spare1) values(s.obj#, s.pname, s.valnum, s.valchar, s.chgtime, s.spare1)

EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
db file sequential read -1
5 1 163263 1

wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
2 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
4 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
2 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
4 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
5 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
4 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
3 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
3 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
3 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
5 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
5 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0


16 rows selected.


PL/SQL procedure successfully completed.


TO_CHAR(SYSDATE,'YY
-------------------
2018-10-13 17:49:43


insert /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */
into sys.dbms_stats_id_map_tab (c5, c1, c2, cn)
select /*+ leading(s) index(s) */ 
distinct s.c5, s.c1, s.c2, d.partition_name cn
from "SYS"."IMPDP_STATS" s, 
(select u.name table_owner, op.name table_name, 
op.subname partition_name, 
tp.part# partition_position
from user$ u, obj$ op, 
(select obj#, part# from tabpartv$
where bo# = :1
union all
select obj#, part# from tabcompartv$
where bo# = :2
) tp
where u.user# = op.owner# and op.type# = 19 and op.obj# = tp.obj#
) d
where s.c5 = :3 and s.c1 = :4
and s.type in ('T','C','E',

EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
db file sequential read -1
497 1 137287 1

wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
2 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
4 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
2 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
4 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
5 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
4 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
3 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
3 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
3 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
5 9981956688 1.0534E+10 0

wait for unread message on broadcast channel 0
5 9981956688 1.0534E+10 0


EVENT WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT P1 P2 P3
--------------- ---------- ---------- ----------
wait for unread message on broadcast channel 0
1 9981956688 1.0534E+10 0


16 rows selected.

SQL>

 

Bir cevap yazın

Your email address will not be published. Required fields are marked *.

*
*

Lütfen captcha kodunu giriniz *

Lütfen Resimdeki Kodu Boşluğa Giriniz.
You may use these <abbr title="HyperText Markup Language">HTML</abbr> tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>