 | | | Subject: Re: Performance Problem. | Subject: Re: Performance Problem. 2007-11-01 - By Mark Brinsmead
Back
Hmmm... Your description sounds like the classic story describing havoc
wreaked by bind-variable-peeking. The story goes like this:
I have a database supporting my high-volume accounts receivable system.
Performance on this database is stellar -- except for the last friday of
every month, when the accountants do their month-end reporting. The month
end reports only run for an hour, and complete before the rest of the users
arrive to work, but performance is horrible for the rest of the day. The
next day, everything mysteriously goes back to normal.
In this fictitious story, the DBA notes that a high-volume query that
executes in a few milliseconds with an indexed access plan inexplicably
changes to full table scans taking 10 or 15 minutes, but only on the day
they have problems.
The diagnosis goes something like this:
* Every evening, something is done that forces all cached execution plans
out of the SGA. (Offline backup, shared pool is flushed for some obsure
reason, whatever.)
* Most mornings, the first users in the office are the order entry clerks,
who only ever work with one order at a time. (Or perhaps the most recent
day's data.)
* On the "bad" days, the first user in the office is the accountant, who
prepares reports for an entire month or quarter. Or whatever.
* There are one or more SQL statements used by both the order entry clerks
and the accountant.
Basically, what is happening is that the database is optimizing the
"common" queries based on the first set of bind variables it sees. Most
days, the values are supplied by users who access only small amounts of
data, and are optimized to use indexed access plans. On the "bad" days, the
first bind-variable values are supplied by users accessing large amounts of
data, so the query is optimized for full table scans. Either way, the
resulting plan is used for the rest of the day.
Is this the case in your situation? We can't possibly know until you
install Statspack, and we get (a lot) more information. But its definitely
something to look for as you are analysing the data.
Good luck.
On 10/30/07, Zanen van, J (Jacob) <Jacob.van.Zanen@(protected)> wrote:
>
> 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
>
> -- ---- ---- ---- ---- ---- --
>
>
>
>
--
Cheers,
-- Mark Brinsmead
Senior DBA,
The Pythian Group
http://www.pythian.com/blogs
Hmmm... Your description sounds like the classic story describing
havoc wreaked by bind-variable-peeking. The story goes like this:<br><br>
I have a database supporting my high-volume accounts receivable system.
Performance on this database is stellar -- except for the last friday of
every month, when the accountants do their month-end reporting. The month
end reports only run for an hour, and complete before the rest of the users
arrive to work, but performance is horrible for the rest of the day. The
next day, everything mysteriously goes back to normal.
<br><br>In this fictitious story, the DBA notes that a high-volume query that
executes in a few milliseconds with an indexed access plan inexplicably changes
to full table scans taking 10 or 15 minutes, but only on the day they have
problems.
<br><br> The diagnosis goes something like this:<br><br>*
Every evening, something is done that forces all cached execution plans out of
the SGA. (Offline backup, shared pool is flushed for some obsure reason,
whatever.)
<br>* Most mornings, the first users in the office are the order entry
clerks, who only ever work with one order at a time. (Or perhaps the most
recent day's data.)<br>* On the "bad" days, the first user
in the office is the accountant, who prepares reports for an entire month or
quarter. Or whatever.
<br>* There are one or more SQL statements used by both the order entry
clerks and the accountant.<br><br> Basically, what is happening is
that the database is optimizing the "common" queries based on the
first set of bind variables it sees. Most days, the values are supplied
by users who access only small amounts of data, and are optimized to use
indexed access plans. On the "bad" days, the first bind
-variable values are supplied by users accessing large amounts of data, so the
query is optimized for full table scans. Either way, the resulting plan
is used for the rest of the day.
<br><br>Is this the case in your situation? We can't possibly know
until you install Statspack, and we get (a lot) more information. But its
definitely something to look for as you are analysing the data.<br><br>Good
luck.
<br><br><div><span class="gmail_quote">On 10/30/07, <b class="gmail_sendername"
>Zanen van, J (Jacob)</b> <<a href="mailto:Jacob.van.Zanen@(protected)"
>Jacob.van.Zanen@(protected)</a>> wrote:</span><blockquote class="gmail
_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0
.8ex; padding-left: 1ex;">
<div>
<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 <a href="http://10.2.0.3"
target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">10.2.0.3
</a> 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 color="#0000ff" face="Arial" 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 color="#ff0000" face="Arial" 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 color="#0000ff" face="Arial" 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 color="#ff0000" face="Arial" 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 color="#0000ff" face="Arial" 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 color="#ff0000" face="Arial" 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 color="#0000ff" face="Arial" 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>
</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><p>
</p><hr>
<p></p>
<p> </p>
<p></p></div>
</blockquote></div><br><br clear="all"><br>-- <br>Cheers,<br>-- Mark Brinsmead
<br> Senior DBA,<br> The Pythian Group<br>
<a href="http://www.pythian.com/blogs">http://www.pythian.com/blogs</a>
|
|
|