 | | | Performance Problem. | Performance Problem. 2007-11-03 - By Best, David
Back
I've supported both Psft Helpdesk and HR systems and I have hit two
issues. One was bind variable peeking and the other was excessive
locking. Once you get some more data I don't mind taking a look.
__ ____ ____ ____ ____ ____ ____
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Zanen van, J (Jacob)
Sent: Tuesday, October 30, 2007 11:01 PM
To: oracle-l@(protected)
Subject: Performance Problem.
Need some help on this.
We have a peoplesoft environment here that seems to have performance
issues every so often.
When this happens performance comes to s standstill for everyone and a
SQL*Plus query that takes about 6-7 seconds normally takes about 13
minutes. During this time it will pretty much wait for sequential reads
only.
I have not yet had the chance to install statspack yet and we are not
licensed for AWR.
We are on oracle 10.2.0.3 on solaris 64bit
When I check the database quickly (needs to be done quickly as business
dictates a reboot at the moment) I do not see any obvious reasons why
this would be happening.
On unix the admins see quite a large increase in use of semaphores.
After the reboot the problems seem to disappear for several weeks/months
Has anyone seen something similar before and found out why this is
happening.
Brgds
Jack
00:00:00 msg/s sema/s
00:05:00 15.19 17.37
00:05:00 16.03 19.85
00:10:00 15.30 16.88
00:15:00 15.20 16.74
00:20:00 15.28 16.33
00:25:00 15.18 16.69
00:30:00 15.29 16.64
00:35:00 15.20 16.77
00:40:00 15.29 16.50
00:45:00 15.20 16.38
00:50:00 15.29 16.85
00:55:00 15.20 16.10
01:00:00 15.29 16.38
01:05:00 15.98 18.36
01:10:00 15.27 17.00
01:15:00 15.20 16.11
01:20:00 15.30 16.87
01:25:00 15.21 16.78
01:30:00 15.28 16.39
01:35:00 15.21 16.62
01:40:00 15.24 16.37
01:45:00 15.25 16.69
01:50:00 15.22 16.46
01:55:00 15.25 16.48
02:00:00 15.26 16.77
02:05:00 15.24 24.18
02:10:00 15.23 17.22
02:15:00 15.25 16.10
02:20:01 15.21 16.84
02:25:00 18.13 38.54
02:30:00 15.41 5314.39
02:35:00 14.98 8429.98
02:40:00 15.65 3357.07
02:45:00 15.31 2490.93
02:50:00 15.65 3482.85
02:55:00 15.73 148.26
03:00:00 15.08 171.86
03:05:00 15.21 183.91
03:10:00 14.92 294.01
03:15:00 15.00 468.95
03:20:00 14.93 520.64
03:25:00 15.01 412.20
03:30:00 14.92 447.94
03:35:00 15.00 424.45
03:40:00 14.94 431.55
03:45:00 15.01 456.17
03:50:01 14.93 411.22
03:55:00 15.00 391.96
04:00:00 14.92 394.48
04:05:00 15.00 498.71
04:10:00 14.94 570.89
04:15:00 15.00 522.34
04:20:00 15.74 400.65
04:25:00 15.34 428.68
04:30:00 14.96 430.52
04:35:00 14.97 472.91
04:40:00 16.23 254.53
04:45:00 15.47 198.73
04:50:00 15.53 198.35
04:55:00 15.99 190.24
05:00:00 16.42 209.91
05:05:00 16.22 174.31
05:10:00 15.94 140.37
05:15:00 15.52 106.79
05:20:00 15.51 329.76
05:25:00 15.90 303.13
05:30:00 15.40 534.14
05:35:00 16.42 577.82
05:40:00 17.19 584.08
05:45:00 15.01 48.89
05:50:00 15.71 69.60
05:55:00 14.93 68.07
06:00:00 15.15 68.18
06:05:00 14.94 70.65
06:05:00 14.17 20.47
06:10:00 15.01 16.73
06:15:00 14.92 16.64
06:20:00 15.00 18.19
06:25:00 15.07 17.92
06:30:00 15.02 47.48
06:35:00 14.92 77.07
06:40:01 15.75 86.36
06:45:00 15.81 42.92
06:50:00 15.44 61.03
06:55:00 16.15 35.05
07:00:00 16.73 98.17
07:05:00 15.13 36.37
07:10:00 15.27 34.78
07:15:00 15.10 221.79
07:20:00 14.99 134.22
07:25:00 15.18 5043.76
07:30:00 15.41 61.87
07:35:01 16.78 68.32
07:40:00 15.76 77.19
07:45:00 15.25 35.46
07:50:00 15.21 27.88
07:55:01 15.26 21.13
08:00:00 15.27 16.33
08:05:00 15.24 16.91
08:10:01 15.99 17.44
08:15:00 15.55 16.72
08:20:00 15.51 16.90
08:25:00 15.53 17.25
08:30:00 15.46 16.53
08:35:00 15.99 17.27
08:40:00 15.35 17.08
08:45:00 17.14 17.50
08:50:01 15.98 19.26
08:55:00 15.50 22.11
09:00:00 16.13 23.00
09:05:00 17.20 26.45
09:10:00 15.00 162.57
09:15:00 16.40 152.22
09:20:00 15.85 637.20
09:25:00 13.10 25387.19
09:30:00 15.77 5485.02
09:35:00 16.88 4685.59
09:40:00 18.28 4743.99
09:45:00 15.72 470.47
09:50:01 16.72 493.55
09:55:00 15.61 646.27
10:00:00 16.61 590.49
10:05:00 16.63 1078.74
10:10:00 16.98 2303.44 -- --> stop/start Psoft/Ost and oracle from 10:15
- 10:27am <---- semaphore back to normal rates.
10:15:00 15.82 17.97
10:20:00 9.27 16.40
10:25:01 3.43 8.64
10:30:00 17.49 17.85
10:35:00 17.52 17.81
10:40:00 16.38 18.27
10:45:01 15.21 17.25
10:50:00 16.32 17.78
10:55:00 15.42 17.84
11:00:00 16.84 18.47
11:05:00 17.00 18.27
__ ____ ____ ____ ____ ____ ____
This email, including any attachments, may be confidential or
privileged, and is sent for the personal attention of the intended
recipient. If you have received this email in error, please delete it
immediately. The views expressed are not necessarily those of the
Rabobank Group. The Group is not liable for the effects of any virus
which may be contained in this email.
If this email contains marketing material and you do not wish to receive
such material by email in future, please reply to this email and place
the words "Remove My Details - Electronic Messages" in the Subject
Header.
The Rabobank Group
Australia: 1800 025 484
New Zealand: 0800 500 933
__ ____ ____ ____ ____ ____ ____
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Performance Problem.</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.3199" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff
size=2></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=687331023-03112007><FONT face=Arial
color=#0000ff size=2>I've supported both Psft Helpdesk and HR systems and I
have
hit two issues. One was bind variable peeking and the other was
excessive locking. Once you get some more data I don't
mind taking a look.</FONT></SPAN><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B>On Behalf Of </B>Zanen van, J
(Jacob)<BR><B>Sent:</B> Tuesday, October 30, 2007 11:01 PM<BR><B>To:</B>
oracle-l@(protected)<BR><B>Subject:</B> Performance
Problem.<BR></FONT><BR></DIV>
<DIV></DIV><!-- Converted from text/rtf format -->
<P><FONT face=Arial size=2>Need some help on this.</FONT> </P><BR>
<P><FONT face=Arial size=2>We have a peoplesoft environment here that seems to
have performance issues every so often.</FONT> </P>
<P><FONT face=Arial size=2>When this happens performance comes to s standstill
for everyone and a SQL*Plus query that takes about 6-7 seconds normally takes
about 13 minutes. During this time it will pretty much wait for sequential
reads
only. </FONT></P>
<P><FONT face=Arial size=2>I have not yet had the chance to install statspack
yet and we are not licensed for AWR.</FONT> <BR><FONT face=Arial size=2>We are
on oracle 10.2.0.3 on solaris 64bit</FONT> </P>
<P><FONT face=Arial size=2>When I check the database quickly (needs to be done
quickly as business dictates a reboot at the moment) I do not see any obvious
reasons why this would be happening.</FONT></P>
<P><FONT face=Arial size=2>On unix the admins see quite a large increase in use
of semaphores.</FONT> </P>
<P><FONT face=Arial size=2>After the reboot the problems seem to disappear for
several weeks/months</FONT> </P>
<P><FONT face=Arial size=2>Has anyone seen something similar before and found
out why this is happening.</FONT> </P><BR>
<P><FONT face=Arial size=2>Brgds</FONT> </P><BR>
<P><FONT face=Arial size=2>Jack</FONT> <BR><FONT face=Arial color=#0000ff
size=2>00:00:00 msg/s sema/s<BR>00:05:00 15.19 17.37<BR>00:05:00 16.03
19.85<BR>00:10:00 15.30 16.88<BR>00:15:00 15.20 16.74<BR>00:20:00 15.28
16.33<BR>00:25:00 15.18 16.69<BR>00:30:00 15.29 16.64<BR>00:35:00 15.20
16.77<BR>00:40:00 15.29 16.50<BR>00:45:00 15.20 16.38<BR>00:50:00 15.29
16.85<BR>00:55:00 15.20 16.10<BR>01:00:00 15.29 16.38<BR>01:05:00 15.98
18.36<BR>01:10:00 15.27 17.00<BR>01:15:00 15.20 16.11<BR>01:20:00 15.30
16.87<BR>01:25:00 15.21 16.78<BR>01:30:00 15.28 16.39<BR>01:35:00 15.21
16.62<BR>01:40:00 15.24 16.37<BR>01:45:00 15.25 16.69<BR>01:50:00 15.22
16.46<BR>01:55:00 15.25 16.48<BR>02:00:00 15.26 16.77<BR>02:05:00 15.24
24.18<BR>02:10:00 15.23 17.22<BR>02:15:00 15.25 16.10<BR>02:20:01 15.21
16.84<BR>02:25:00 18.13 38.54<BR></FONT><FONT face=Arial color=#ff0000
size=2>02:30:00 15.41 5314.39<BR>02:35:00 14.98 8429.98<BR>02:40:00 15.65
3357.07<BR>02:45:00 15.31 2490.93<BR>02:50:00 15.65 3482.85<BR>02:55:00 15.73
148.26<BR>03:00:00 15.08 171.86<BR>03:05:00 15.21 183.91<BR>03:10:00 14.92
294.01<BR>03:15:00 15.00 468.95<BR>03:20:00 14.93 520.64<BR>03:25:00 15.01
412.20<BR>03:30:00 14.92 447.94<BR>03:35:00 15.00 424.45<BR>03:40:00 14.94
431.55<BR>03:45:00 15.01 456.17<BR>03:50:01 14.93 411.22<BR>03:55:00 15.00
391.96<BR>04:00:00 14.92 394.48<BR>04:05:00 15.00 498.71<BR>04:10:00 14.94
570.89<BR>04:15:00 15.00 522.34<BR>04:20:00 15.74 400.65<BR>04:25:00 15.34
428.68<BR>04:30:00 14.96 430.52<BR>04:35:00 14.97 472.91<BR>04:40:00 16.23
254.53<BR>04:45:00 15.47 198.73<BR>04:50:00 15.53 198.35<BR>04:55:00 15.99
190.24<BR>05:00:00 16.42 209.91<BR>05:05:00 16.22 174.31<BR>05:10:00 15.94
140.37<BR>05:15:00 15.52 106.79<BR>05:20:00 15.51 329.76<BR>05:25:00 15.90
303.13<BR>05:30:00 15.40 534.14<BR>05:35:00 16.42 577.82<BR>05:40:00 17.19
584.08<BR></FONT><FONT face=Arial color=#0000ff size=2>05:45:00 15.01
48.89<BR>05:50:00 15.71 69.60<BR>05:55:00 14.93 68.07<BR>06:00:00 15.15
68.18<BR>06:05:00 14.94 70.65<BR>06:05:00 14.17 20.47<BR>06:10:00 15.01
16.73<BR>06:15:00 14.92 16.64<BR>06:20:00 15.00 18.19<BR>06:25:00 15.07
17.92<BR>06:30:00 15.02 47.48<BR>06:35:00 14.92 77.07<BR>06:40:01 15.75
86.36<BR>06:45:00 15.81 42.92<BR>06:50:00 15.44 61.03<BR>06:55:00 16.15
35.05<BR>07:00:00 16.73 98.17<BR>07:05:00 15.13 36.37<BR>07:10:00 15.27
34.78<BR></FONT><FONT face=Arial color=#ff0000 size=2>07:15:00 15.10
221.79<BR>07:20:00 14.99 134.22<BR>07:25:00 15.18 5043.76<BR></FONT><FONT
face=Arial color=#0000ff size=2>07:30:00 15.41 61.87<BR>07:35:01 16.78
68.32<BR>07:40:00 15.76 77.19<BR>07:45:00 15.25 35.46<BR>07:50:00 15.21
27.88<BR>07:55:01 15.26 21.13<BR>08:00:00 15.27 16.33<BR>08:05:00 15.24
16.91<BR>08:10:01 15.99 17.44<BR>08:15:00 15.55 16.72<BR>08:20:00 15.51
16.90<BR>08:25:00 15.53 17.25<BR>08:30:00 15.46 16.53<BR>08:35:00 15.99
17.27<BR>08:40:00 15.35 17.08<BR>08:45:00 17.14 17.50<BR>08:50:01 15.98
19.26<BR>08:55:00 15.50 22.11<BR>09:00:00 16.13 23.00<BR>09:05:00 17.20
26.45<BR></FONT><FONT face=Arial color=#ff0000 size=2>09:10:00 15.00
162.57<BR>09:15:00 16.40 152.22<BR>09:20:00 15.85 637.20<BR>09:25:00 13.10
25387.19<BR>09:30:00 15.77 5485.02<BR>09:35:00 16.88 4685.59<BR>09:40:00 18.28
4743.99<BR>09:45:00 15.72 470.47<BR>09:50:01 16.72 493.55<BR>09:55:00 15.61
646.27<BR>10:00:00 16.61 590.49<BR>10:05:00 16.63 1078.74<BR>10:10:00 16.98
2303.44 -- --> stop/start Psoft/Ost and oracle from 10:15 - 10:27am <----
semaphore back to normal rates.<BR></FONT><FONT face=Arial color=#0000ff
size=2>10:15:00 15.82 17.97<BR>10:20:00 9.27 16.40<BR>10:25:01 3.43
8.64<BR>10:30:00 17.49 17.85<BR>10:35:00 17.52 17.81<BR>10:40:00 16.38
18.27<BR>10:45:01 15.21 17.25<BR>10:50:00 16.32 17.78<BR>10:55:00 15.42
17.84<BR>11:00:00 16.84 18.47<BR>11:05:00 17.00 18.27</FONT> </P>
<P>
<HR>
<FONT size=2>This email, including any attachments, may be confidential or
privileged, and is sent for the personal attention of the intended
recipient. If you have received this email in error, please delete it
immediately. The views expressed are not necessarily those of the
Rabobank
Group. The Group is not liable for the effects of any virus which may be
contained in this email.</FONT>
<P></P>
<P><FONT size=2>If this email contains marketing material and you do not wish
to
receive such material by email in future, please reply to this email and place
the words "Remove My Details - Electronic Messages" in the Subject
Header.</FONT></P>
<P><FONT size=2>The Rabobank Group</FONT></P>
<P><FONT size=2>Australia: 1800 025 484<BR>New Zealand: 0800 500 933 </FONT>
<P>
<HR>
<P></P>
<P> </P></BODY></HTML>
|
|
|