Re: Problems running Oracle / Performance

Rehi again,

the Server now runs for a couple of days some changes here .. a few indexes
dropped and recreated ... and ... it runs much faster now. (From Highload average of 5.00 down to 1.00 to 2.00) It does not run really great compared to
the system it was installed before.

And I really wonder why creating indexes anew would speedup DB use .. or is the
analyze stuff still working and getting better performance over time?

I should ask an oracle? :)

After getting my support id for metalink I could now run statspack which always tell me stuff about "TX enqueues" which I dont really understand :/

For you who is so friendly to try to help me here is the link where you can download 4 of the statspackresults under load. (I could provide more of them if needed)

Statspack: http://www.brettspielwelt.de/Other/sp_10_14.lst

For now my understanding of TX enqueues is that there are locks on tables which
are accessed at the same time. (I have normally about 5 concurrent clients connected to the DB) But what to do against that I dont know ...

So if somebody can help me I would be very happy and thankfull.

    Many thanx in advance

        Alexander Zbiek

drak0nian_at_yahoo.com (Paul Drake) wrote in message news:<1ac7c7b3.0401122223.58537e03_at_posting.google.com>...
> ARMistice_at_gmx.de (Alexander Zbiek) wrote in message news:<32293e40.0401121509.1ef69f29_at_posting.google.com>...
> > Hello *,
> >
> > I hope somebody could perhaps help me ... I dont know exactly what is
> > needed to
> > help me with my problem. Hopefully somebody can tell me that :)
> >
> > I am running an Oracle Server on a SuSE linux 9 (Linux linux
> > 2.4.21-99-default #1 Wed Sep 24 13:30:51 UTC 2003 i686 i686 i386
> > GNU/Linux)
> > there is a RAID-5 and a RAID-1 system for the database files ...
> > /dev/sdb1:
> > Timing buffered disk reads: 166 MB in 3.02 seconds = 54.91
> > MB/sec
> > /dev/sda1:
> > Timing buffered disk reads: 182 MB in 3.00 seconds = 60.65
> > MB/sec
> >
> > The system moved from an older computer to this new one. We never
> > encountered
> > big performance problems on the old system. But now: load average:
> > 3.40, 3.50, 4.10
> > and thats not the worst. There are always running "oracle" processes
> > with high CPU usage.
> > Theres nothing else running on that system.
> >
> > I dont know where the problem could be, so I try to send some stuff
> > from the DB v$ tables ... with hope somebody can tell something or
> > instruct me what is needed.
> >
> > I have set all oracle parameters in the config files (init.ora ...)
> > like the other old system.
> > I could post a lot of stuff :) But I dont even know if its usefull...
> >
> > Many thanks in advance
> > Alexander Zbiek
>
> enqueues look very out of proportion to the other events.
> The main problem with just selecting from v$system_event, is that the
> figures are totaled since instance startup.
>
> install statspack, take some snapshots in 15 minute intervals (while
> the system is under load).
> generate reports across the 15 minute intervals and see what your top
> wait events are (such as enqueues).
>
> if you don't know how to install statspack, there are numerous
> articles out there, OTN is a good source, here's one:
>
> http://otn.oracle.com/deploy/performance/pdf/20TUNING_dialeris.pdf
>
> Oracle Magazine no longer has the articles from 2000 available online.
>
> upload a copy of a report to oraperf http://www.oraperf.com/ and
> review their comments.
>
> Before you reply to this thread, make sure you at least include the
> oracle version and edition (although if you posted a statspack report,
> it would be included in there).
>
> Pd
Received on Fri Jan 16 2004 - 10:05:27 CST