Re: How to tune Oracle database 10.1.0.3.0 on slow performance

On Jan 9, 4:02 am, pejantan4u <pejanta..._at_gmail.com> wrote:
> thanks for ur response.
>
> On Jan 9, 3:49 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
> > On Jan 9, 9:33 am, pejantan4u <pejanta..._at_gmail.com> wrote:
>
> > > we're running Oracle 10.1.0.3.0 on Linux Redhat ES 4, 12GB memory.
>
> > > we are testing application with 50user+ that connect into the database
> > > oracle 10.1.0.3.0 and i saw graphic performance on my EM, it looks ok.
> > > and then on top sql, few queries showed percentage 44% and 60%, i
>
> > What percentage?  Did you look at the execution plans?  Why do you say
> > it looks ok and then you say it isn't?  Are there complaints from
> > users?
>
> i saw it on EM in Top SQL section. i said ok when i saw the
> performance of database on EM performance section, but when i saw in
> Top sql section, there are few queries that show large activity
> percentage on EM. Users complaints about, why the database took a long
> time, when they run the application.
>
>
>
> > > tried to run the sql tuning advisor but oracle only recomend gather
> > > statistic, but i think this wasn't enough. what should i do ? could
> > > anyone help me
>
> > Do you _think_ that it was enough or do you _know_ it?
>
> I think, because i don't know for sure.
>
>
>
>
>
> > > and in this case of situation, what kind of tuning method that you
> > > guys recomend, and how the solution beside using ADDM, coz i have
> > > tried to use ADDM and i still couldn't find the solution
>
> > I'd probably get myself a decent book on Oracle Tuning (there are
> > quite a few out there but you can find recommendations in this group)
> > and go from there.
>
> > Kind regards
>
> > robert

Are the users complaining about the performance of a specific part of the application, for instance invoicing, but not complaining about other parts of the application. Is it a performance problem for only a couple users? Have you examined the wait events for the sessions experiencing performance problems (take a look at V$SESSION_EVENT)? Have you looked at the execution plans of the poorly performing SQL statements? Have you examined the initialization parameters (spfile or init.ora) for parameters that are significantly restricting memory that sessions can use for in-memory sorts?

The performance problem could be caused by: * The application (sending many database platform independent SQL statements, rather than a single SQL statement that accomplishes the same task, single row fetching, sending inefficiently constructed SQL, poor table/data layout)
* The network (high latency, incorrectly set MTU, failing switches, bad wiring, very busy or slow network speed) * The server (RAID 5 [especially if 1 drive has died], slow disks, slow CPU, incorrect operating system parameters, 32 bit operating system, competition with other applications running on the same server)
* Oracle (parameters that limit memory that may be used for in-memory sorts, parameters that limit the shared pool, parameters that limit the memory used for buffering data blocks, redo logs that are too small, poor execution plans caused by excessive histograms or bind variable peeking, etc.)

Pick a user who is reporting performance problems and start examining the wait events for that user. If the wait events do not indicate a problem, start a 10046 trace at level 12, and manually examine the trace file. In the trace file, pay close attention to the SQL*Net wait events - in a 10046 trace file those events may be an indication that the performance problem is either on the client or network, and not in the database server.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Jan 09 2008 - 05:52:45 CST