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

On Oct 23, 2:44 pm, zigzag...@xxxxxxxxx wrote:
On Oct 23, 12:25 pm, Robert Klemme <shortcut...@xxxxxxxxxxxxxx> wrote:



On 23.10.2007 12:18, zigzag...@xxxxxxxxx wrote:

On Oct 22, 2:11 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On Oct 22, 1:04 pm, zigzag...@xxxxxxxxx wrote:

On Oct 22, 1:46 pm, joel garry <joel-ga...@xxxxxxxx> wrote:
On Oct 22, 8:12 am, gazzag <gar...@xxxxxxxxx> wrote:
On 22 Oct, 15:56, zigzag...@xxxxxxxxx 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


.

Relevant Pages

  • Re: Database Performance Problem between 3:00PM and 4:00PM
    ... where by database becomes extremely slow between 3:00PM and 4:00PM. ... When one looks at CPU Usage, Memory Usage and Disk Usage nothing ... FRom the beginning we suspected Network to be an issue, ... Is it possible that an Oracle backup job, export, or ...
    (comp.databases.oracle.server)
  • Re: Database Performance Problem between 3:00PM and 4:00PM
    ... where by database becomes extremely slow between 3:00PM and 4:00PM. ... When one looks at CPU Usage, Memory Usage and Disk Usage nothing ... FRom the beginning we suspected Network to be an issue, ... what hardware do you use for storage? ...
    (comp.databases.oracle.server)
  • Re: Database Performance Problem between 3:00PM and 4:00PM
    ... where by database becomes extremely slow between 3:00PM and 4:00PM. ... FRom the beginning we suspected Network to be an issue, ... monitoring cpu, disk, memory usage..), do n''t see anything different ...
    (comp.databases.oracle.server)
  • Re: Database Performance Problem between 3:00PM and 4:00PM
    ... where by database becomes extremely slow between 3:00PM and 4:00PM. ... FRom the beginning we suspected Network to be an issue, ... monitoring cpu, disk, memory usage..), do n''t see anything different ...
    (comp.databases.oracle.server)
  • Re: Database Performance Problem between 3:00PM and 4:00PM
    ... where by database becomes extremely slow between 3:00PM and 4:00PM. ... When one looks at CPU Usage, Memory Usage and Disk Usage nothing ... FRom the beginning we suspected Network to be an issue, ... - some gigabit switches limit backplane speed to 2GB between switch ...
    (comp.databases.oracle.server)