OraFAQ Forum: Networking and Gateways » Issues with the package execution

Home » RDBMS Server » Networking and Gateways » Issues with the package execution (oracle,9.2.0.8.0,sunsolaris 5.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Issues with the package execution [message #463261]

Wed, 30 June 2010 07:47

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

The procedure mis_bbv_sp_pop_daily_actions resides in the package MIS_PKG_IKPORT(attached herewith) is was running fine before may 15th. And it was completed within 7 hrs.

But now it is taking more than 18 hours to complete.

I checked my initial investigation from the statspack report(attached herewith).

the top 5 wait events are given below.

Code: [Select all] [Show/ hide]

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read                        10,410,564      26,177    33.72
SQL*Net more data from dblink                     542,146      16,425    21.16
db file scattered read                          5,300,068      12,572    16.19
db file parallel write                             39,145       7,018     9.04
SQL*Net message from dblink                       149,959       4,339     5.59
          -------------------------------------------------------------

from the wait event,I understood it is the proplem with network. Since the procedure is
connecting remote db while every insert operation happends.

is there anyother way to confirm as this is the problem with network?
or can you help me how can I find the root cause of this issue?

Note: No change happened after april in this database. and also data volume is same.

Attachment: MIS_PKG_IMPORT.txt
(Size: 101.87KB, Downloaded 2905 times)

Report message to a moderator

Re: Issues with the package execution [message #463262 is a reply to message #463261]

Wed, 30 June 2010 07:49

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

Forgot to attach the statspack report. attaching the same here.

Attachment: sp_3667_3670.lst
(Size: 104.99KB, Downloaded 2108 times)

[Updated on: Wed, 30 June 2010 07:50]

Report message to a moderator

Re: Issues with the package execution [message #463266 is a reply to message #463262]

Wed, 30 June 2010 08:15

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Are the statistics on tables and indexes updated?
If so, how?

[Updated on: Wed, 30 June 2010 08:19]

Report message to a moderator

Re: Issues with the package execution [message #463271 is a reply to message #463266]

Wed, 30 June 2010 08:36

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

There are 5 tables invovled in that procedure. In that 5,we analyzed only 2 tables on last week.

Code: [Select all] [Show/ hide]

TABLE_NAME                    TO_CHAR(LAST_ANALYZ
----------------------------- -------------------
MIS_BBV_ACTION_LOG            17/06/2010 15:40:49
MIS_BBV_DAILY_ACTIONS         04/07/2008 20:29:27
MIS_BBV_MAILBOX_INFO          04/07/2008 20:30:55
R1OSS_BBV_ACCOUNTS            04/07/2008 20:48:03
R1OSS_BBV_ACCOUNT_SUMMARY     17/06/2010 16:39:54

The tables which are present in the remote db were updated on 27/06/2010.

Report message to a moderator

Re: Issues with the package execution [message #463275 is a reply to message #463261]

Wed, 30 June 2010 08:45

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

ping statistics are given below.

Code: [Select all] [Show/ hide]

$ping -s 10.544.789.12
PING 10.544.789.12: 56 data bytes
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=0. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=1. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=2. time=112. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=3. time=163. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=4. time=159. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=5. time=690. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=6. time=701. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=7. time=791. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=8. time=801. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=9. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=10. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=11. time=920. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=12. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=13. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=14. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=15. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=16. time=129. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=17. time=559. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=18. time=569. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=19. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=20. time=1051. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=21. time=51. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=22. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=23. time=389. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=24. time=399. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=25. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=26. time=710. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=27. time=719. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=28. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=29. time=308. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=30. time=778. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=31. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=32. time=809. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=33. time=819. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=34. time=1848. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=35. time=849. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=36. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=37. time=808. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=38. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=39. time=258. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=40. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=41. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=42. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=43. time=918. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=44. time=0. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=45. time=59. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=46. time=56. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=47. time=110. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=48. time=120. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=49. time=131. ms
64 bytes from bykasi22_cfc_vas.com (10.544.789.12): icmp_seq=50. time=0. ms
^C
----10.544.789.12 PING Statistics----
51 packets transmitted, 51 packets received, 0% packet loss
round-trip (ms)  min/avg/max = 0/329/1848

Report message to a moderator

Re: Issues with the package execution [message #463525 is a reply to message #463275]

Thu, 01 July 2010 12:46

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

Can anybody help me to sort out this issue?

Report message to a moderator

Re: Issues with the package execution [message #463527 is a reply to message #463525]

Thu, 01 July 2010 12:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the slow code
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here

You need to know where time is being spent before you can try to improve performance

Report message to a moderator

Re: Issues with the package execution [message #463528 is a reply to message #463525]

Thu, 01 July 2010 12:51

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Is there a VPN / Firewall connection involved?

[Updated on: Thu, 01 July 2010 12:53]

Report message to a moderator

Re: Issues with the package execution [message #463541 is a reply to message #463527]

Thu, 01 July 2010 14:43

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

thanks for the response.

The problem here is there is low free space in the udump location. So if I trace the 18 hours job,surely the dump location will be getting filled up. Thatsy I didnt do that.

is there any possiblity to dump the trace file in /tmp without changing the parameter?
and also can you help me to trace the particular procudure alone. this would reduce the file size.

Report message to a moderator

Re: Issues with the package execution [message #463543 is a reply to message #463528]

Thu, 01 July 2010 14:47

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

Hi mahesh,

I could not able to understand your question. can you please give me some more detail? and also tell me the way to check this

Report message to a moderator

Re: Issues with the package execution [message #463544 is a reply to message #463541]

Thu, 01 July 2010 14:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>and also can you help me to trace the particular procudure alone. this would reduce the file size.

a LOGON trigger can be written to enable SQL_TRACE only for specified procedure.

Report message to a moderator

Previous Topic:	ORA-12170: TNS:CONNECT TIMEOUT OCCURED
Next Topic:	Can't Connect to Remote DB Listener

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 00:05:23 CDT 2025