Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Database Performance Problem between 3:00PM and 4:00PM

Re: Database Performance Problem between 3:00PM and 4:00PM

From: Robert Klemme <>
Date: Tue, 23 Oct 2007 18:25:08 +0200
Message-ID: <>

On 23.10.2007 12:18, wrote:

> On Oct 22, 2:11 pm, "" <> wrote:

>> On Oct 22, 1:04 pm, wrote:
>>> On Oct 22, 1:46 pm, joel garry <> wrote:
>>>> On Oct 22, 8:12 am, gazzag <> wrote:
>>>>> On 22 Oct, 15:56, wrote:
>>>>>> I am Windows 2000 server using Oracle 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
>>>> --
>>>> is bogus.
>>>> At least one web page is still up: text -
>>>> - 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 Received on Tue Oct 23 2007 - 11:25:08 CDT

Original text of this message