On Oct 23, 8:26 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Oct 23, 4:37 pm, zigzag..._at_yahoo.com wrote:
>
>
>
>
>
> > On Oct 23, 2:44 pm, zigzag..._at_yahoo.com wrote:
>
> > > On Oct 23, 12:25 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
> > > > On 23.10.2007 12:18, zigzag..._at_yahoo.com wrote:
>
> > > > > On Oct 22, 2:11 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> > > > >> On Oct 22, 1:04 pm, zigzag..._at_yahoo.com wrote:
>
> > > > >>> On Oct 22, 1:46 pm, joel garry <joel-ga..._at_home.com> wrote:
> > > > >>>> On Oct 22, 8:12 am, gazzag <gar..._at_jamms.org> wrote:
> > > > >>>>> On 22 Oct, 15:56, zigzag..._at_yahoo.com wrote:
> > > > >>>>>> I am Windows 2000 server using Oracle 9.2.0.6. We have strange problem
> > > > >>>>>> where by database becomes extremely slow between 3:00PM and 4:00PM.
> > > > >>>>>> Same queries which take 15 seconds before 3:00PM (e.g., 2:55PM) take
> > > > >>>>>> 3-4 minutes at 3:00PM and after that. Poblem starts right at 3:00PM
> > > > >>>>>> every day. No of users etc are same. We have asked users not to use
> > > > >>>>>> the system during that period for isolating performance problem,
> > > > >>>>>> without any success.
> > > > >>>>>> When one looks at CPU Usage, Memory Usage and Disk Usage nothing
> > > > >>>>>> changes between 2:55PM and (3:00PM-4:00PM). I kooked at task mgr, no
> > > > >>>>>> new processes. I have looked at Windows scheduler, Oracle dbms_job.
> > > > >>>>>> Nothing is running between 3:00PM-4:00PM. I have no clue why system
> > > > >>>>>> becomes so slow in that period. I was hoping that some job starts at
> > > > >>>>>> that time, but cannot find any. Any ideas for troubleshooting will be
> > > > >>>>>> appreciated.
> > > > >>>>> The most likely explanation is that something else is running against
> > > > >>>>> the database between those times. Note: this process need not
> > > > >>>>> originate from the database server itself, hence nothing in Task
> > > > >>>>> Manager, Scheduled Tasks and DBA_JOBS.
> > > > >>>>> Monitor V$SESSION for any suspect sessions.
> > > > >>>>> HTH
> > > > >>>>> -g
> > > > >>>> I agree, and the first thing I would look for is some performance
> > > > >>>> monitoring tool!
> > > > >>>> When I was taking networking (circa 1982) in school, my professor was
> > > > >>>> the fellow who had done the arconet, which connected AM/PM minimarts
> > > > >>>> with a 9600 multidrop line. It had just been shutdown due to
> > > > >>>> insufficient performance. Anyhow, he told a story which totally
> > > > >>>> cracked me up. I've posted it before, but can't remember where, so
> > > > >>>> apologies to those who've heard this before. Imagine a thick
> > > > >>>> Hungarian accent...
> > > > >>>> As I recall the story went, a particular subnet would shut down every
> > > > >>>> night at 9PM. They tried a number of tools (in those days, one wrote
> > > > >>>> ones own), and could only find that some noise started at that time.
> > > > >>>> Finally he went to one of the stores involved and waited until 9, and
> > > > >>>> sure enough it went down. He plugged in a phone (in those days, it
> > > > >>>> was analog), and heard "THIS IS THE VOICE OF GOD!"
> > > > >>>> Turns out, a large AM transmitter was nearby, and would start
> > > > >>>> broadcasting a Christian radio show at 9PM every night, overwhelming
> > > > >>>> the data signal via induction.
> > > > >>>> I've also worked in a couple of industrial areas where the place next
> > > > >>>> door would turn on large machines at particular times, affecting
> > > > >>>> hardware that was not isolated enough. Even my current customer
> > > > >>>> recently got affected by some transient packet storm that overwhelmed
> > > > >>>> one of an hp-ux machine's network interfaces, killing telnetd and the
> > > > >>>> console. It's a dirty, dirty world.
> > > > >>>> jg
> > > > >>>> --
> > > > >>>> @home.com is bogus.
> > > > >>>> At least one web page is still up: http://www.sdcountyemergency.com/-Hidequotedtext-
> > > > >>>> - Show quoted text -
> > > > >>> FRom the beginning we suspected Network to be an issue, We contacted
> > > > >>> our Networking departament, They monitored Network, could not find any
> > > > >>> thing. I have run perfstat, I have run Window's perfmon (for
> > > > >>> monitoring cpu, disk, memory usage..), do n''t see anything different
> > > > >>> between before 3:00PM and after 3:00PM/ It really baffels me that for
> > > > >>> weeks no one has clue on cause of the problem which happens
> > > > >>> consistenetly.- Hide quoted text -
> > > > >>> - Show quoted text -
> > > > >> I suggest again that you run Statspack at 30 minute intervals between
> > > > >> 1 PM and 5PM and examine the reports generated. They may not tell you
> > > > >> everything, but they might tell you something which would provide a
> > > > >> proper direction for further investigation.
>
> > > > >> David Fitzjarrell- Hide quoted text -
>
> > > > >> - Show quoted text -
>
> > > > > Thanks. I forgot to mention I have alreday done that, startspack do
> > > > > not show naything differemt. Same is true of Windows's permfon,
>
> > > > Did you compare execution plans from inside and outside the "bad" period?
>
> > > > Maybe you fill up your database with a regular stream of data and at
> > > > around 3pm statistics are so much outdated that the old plan is inefficient.
>
> > > > Another idea: what hardware do you use for storage? There are network
> > > > attached storages around that can do backups on their own (NetApp filer
> > > > can do snapshots). Maybe something is going on in the storage
> > > > increasing response times.
>
> > > > The other idea I had was that something uses the network segment for
> > > > large data transfers during that period but you said you ruled that out
> > > > already...
>
> > > > Just my 0.02EUR...
>
> > > > Kind regards
>
> > > > robert- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Thanks a lot. I did look at the execution plan before 3:00PM and after
> > > 3:00PM, they are identical. As for as netwiork, I have to rely on my
> > > network folks, For storage, we use disk arrays and SAN's. We asked
> > > our backup team to look at whether any backups are being done at that
> > > time. They say none. I suspect that some thing is being run in data
> > > center with disk access etc, but data center people are unable to
> > > identify it.- Hide quoted text -
>
> > > - Show
>
> > Top wait events between 2:30PM-3:00PM
> > Event Waits Timeouts Time (s)
> > (ms) /txn
> > ---------------------------- ------------ ---------- ---------- ------
> > --------
> > db file scattered read 18,476 0 66
> > 4 342.1
> > db file sequential read 3,708 0 5
> > 1 68.7
> > control file sequential read 538 0 2
> > 3 10.0
> > control file parallel write 606 0 1
> > 1 11.2
> > direct path read 31 0 0
> > 14 0.6
> > db file parallel write 19 0 0
> > 9 0.4
> > control file single write 9 0 0
> > 11 0.2
> > log file sync 28 0 0
> > 2 0.5
> > SQL*Net break/reset to clien 2 0 0
> > 7 0.0
>
> > Top wait events between 3:00PM-3:30PM
>
> > Event Waits Timeouts Time (s)
> > (ms) /txn
> > ---------------------------- ------------ ---------- ---------- ------
> > --------
> > db file scattered read 57,418 0 959
> > 17 368.1
> > db file sequential read 9,694 0 107
> > 11 62.1
> > control file parallel write 591 0 28
> > 48 3.8
> > control file sequential read 460 0 5
> > 12 2.9
> > log file sync 52 0 2
> > 48 0.3
> > db file parallel write 20 0 1
> > 65 0.1
> > direct path read 31 0 1
> > 24 0.2
> > SQL*Net break/reset to clien 4 0 0
> > 37 0.0
> > direct path write 4 0 0
> > 6 0.0
>
> In agreement with Robert and Joel, it appears to be an issue of disk
> contention. Is it possible that an Oracle backup job, export, or
> statistics calculation job should have been scheduled for 3AM? How
> frequent are the redo log file switches? Are the servers connected to
> the SANs through a network switch that also connects to other devices
> - some gigabit switches limit backplane speed to 2GB between switch
> modules, so there may be contention with other IP devices.
>
> When trying to track performance problems on Windows, I often turn to
> either Filemon (what files are being accessed) or Wireshark (what
> network traffic may be contributing to performance issues). Filemon
> is likely the best fit for your issue:http://www.microsoft.com/technet/sysinternals/utilities/filemon.mspx
>
> Consider running Filemon for 5 minutes, possibly between 3:10 and
> 3:15, although a shorter time interval will likely be sufficient.
> Review the log file for patterns.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -
No Oracle backup jobs or database statistics run at that time. SAN is
used for disks. I have to check details of SAN with storage group. I
will look into Filemon, but I have been using Windows perfmon which
lets me collect disk usage, memory usgae, cpu usage etc
periodically .It is pretty nice utility.
Received on Wed Oct 24 2007 - 04:39:22 CDT
