Home » RDBMS Server » Networking and Gateways » Issues with the package execution (oracle,9.2.0.8.0,sunsolaris 5.1)
Issues with the package execution [message #463261] Wed, 30 June 2010 07:47 Go to next message
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.

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.
Re: Issues with the package execution [message #463262 is a reply to message #463261] Wed, 30 June 2010 07:49 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Forgot to attach the statspack report. attaching the same here.

[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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10615
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 Go to previous messageGo to next message
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.

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.

Re: Issues with the package execution [message #463275 is a reply to message #463261] Wed, 30 June 2010 08:45 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
ping statistics are given below.

$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
Re: Issues with the package execution [message #463525 is a reply to message #463275] Thu, 01 July 2010 12:46 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member

Can anybody help me to sort out this issue?
Re: Issues with the package execution [message #463527 is a reply to message #463525] Thu, 01 July 2010 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 22558
Registered: January 2009
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
Re: Issues with the package execution [message #463528 is a reply to message #463525] Thu, 01 July 2010 12:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10615
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 Go to previous messageGo to next message
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.
Re: Issues with the package execution [message #463543 is a reply to message #463528] Thu, 01 July 2010 14:47 Go to previous messageGo to next message
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
Re: Issues with the package execution [message #463544 is a reply to message #463541] Thu, 01 July 2010 14:59 Go to previous message
BlackSwan
Messages: 22558
Registered: January 2009
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.
Previous Topic: ORA-12170: TNS:CONNECT TIMEOUT OCCURED
Next Topic: Can't Connect to Remote DB Listener
Goto Forum:
  


Current Time: Sat Aug 02 03:05:13 CDT 2014

Total time taken to generate the page: 0.08050 seconds