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>
