Re: Performance problem.

Yes, it will be hard to compare both environments.

We have defined "init" parameters according to this Note:216205.1.

We haven't done SQL's tuning process yet, by the way one of the most
"huge" processes scheduled to run like request is report "Inactive Items"
which is embeeded in the application (it is taking 15 minutes to finish),
I have traced this process and it is doing a "FULL" scan of a big table
"MTL_MATERIAL_TRANSACTIONS" (8831854 rows).

If it is a embeeded report inside of the application, how can I tune it?

We are waiting for server configuration (eliminating virtualization),
after that we are going to "test" again.

I have heard that "CIO" is better than "DIO". CIO can be only used on
"JFS2" (which has similar performance than raw devices), but they want to
use "snapshot method" to do fast backups, but it is restricted only on
"JFS" filesystem types, so I dpn't know if we are going to use JFS2
filesystems.

Yury, do you have any environment of Apps on AIX?, if so, which kind of
filesystems are you using there?, which mounting options are you using on
your filesystems? what about minservers and maxservers parameters?

I would really appreciate any information about that.

Thanks in advance.

Kind regards,

Francisco Mtz.



> Sorry,
>
> I lost context in the previous post. Forget about index count suggestion.
> I would say there are to many changes you have introduced
> - DB migration
> - Platform migration
> - Apps version migration
> to be able to compare to environments (prod & test).
>
> It looks more like you on performance tuning step in you upgrade project.
>
> First I would recommend to check
> Note:216205.1 Subject:        Database Initialization Parameters for Oracle
> Applications 11i
> In order to set Optimizer sedative parameters.
>
> Then trace 10046 one particular session and try to identify most
> resources consuming SQL-s.
> Try to understand how to improve those. It can appear that you will
> find overall solution.
>
> Yury
>
>
> On 5/11/06, francisco.martinez@xxxxxxxxxxxxxx
> <francisco.martinez@xxxxxxxxxxxxxx> wrote:
>> Well, unfortunately we haven't taken times in their current production
>> environment "11.0.3 (HPUX)", also I think the latest "tests" we have
>> done
>> with Development people and not with end users aren't according to
>> reality, we scheduled some "huge" reports to simulate "load" in this
>> environment.
>>
>> By the way when we had "stress tests" with end users (60 concurrent
>> users,
>> when is supposed to support 120 concurrent users) we had "performance
>> problems", slow time when trying to navigate inside of the application
>> (changing screens).
>>
>> I don't know if we are limited with systems resources (I didn't do
>> machine's sizing). In the first try with "stress test" this machine was
>> configured with 9 Gbytes of RAM, 2.8 dual CPU's at 1.9 GHz and 5 hard
>> disks using RAID 0+1.
>>
>> We haven't tested this version on HPUX platform.
>>
>> Yes, we used FULL export on 8.0.5, then we imported on 8.1.7.4 and
>> finally
>> we upgraded to 9.2.0.6. I haven't compared index count on both
>> environments, unfortunately we don't have access to their production
>> environment to do this comparision.
>>
>> Yes, we are on TEST. We can't go LIVE with these current problems.
>>
>> Thanks in advance.
>>
>> Kind regards,
>>
>> Francisco Mtz.
>>
>> > Is it overall performance slowdown, or you can identify particular
>> > actions which are slow comparing with previous state?
>> > Have you used the same DB version on HPUX?
>> > To migrate you used exp/imp methode are you? can you compare the index
>> > count on both environments?
>> >
>> > PS I hope this is something that is going on on non prod env :)
>> >
>> > Yury
>> >
>> > On 5/11/06, francisco.martinez@xxxxxxxxxxxxxx
>> > <francisco.martinez@xxxxxxxxxxxxxx> wrote:
>> >> Hi Yury,
>> >>
>> >> Thanks for your quick response.
>> >>
>> >> We are on 9.2.0.6 version. We haven't tested yet this environment
>> with
>> >> "SYS" and "SYSTEM" statistics collection, so we are going after
>> >> eliminating virtualization on this server.
>> >>
>> >> Kind regards,
>> >>
>> >> Francisco Mtz.
>> >>
>> >>
>> >>
>> >> > Francisco,
>> >> >
>> >> > Which version of Oracle database you are on?
>> >> >
>> >> > PS If you are on 8.1.7, 9.0.1 I would definetely recommend to
>> consider
>> >> > migration to 9.2
>> >> >
>> >> > Yury
>> >> >
>> >> >
>> >> > On 5/11/06, francisco.martinez@xxxxxxxxxxxxxx
>> >> > <francisco.martinez@xxxxxxxxxxxxxx> wrote:
>> >> >> Hi partners,
>> >> >>
>> >> >> We have upgraded one Ebusiness environment from 11.0.3 to
>> 11.5.10.2,
>> >> >> also
>> >> >> we moved from HPUX to AIX, after that we are having some
>> performance
>> >> >> issues on this new platform.
>> >> >>
>> >> >> We have gathered statistics for "ALL" the schemas and the main
>> >> problem
>> >> >> is
>> >> >> due to I/O, all the disks reach 100% of their usage.
>> >> >>
>> >> >> I haven't analyzed the "huge" queries causing a lot of I/O's, the
>> >> main
>> >> >> waits events in my Statpack report are next ones:
>> >> >>
>> >> >> Top 5 Timed Events
>> >> >> ~~~~~~~~~~~~~~~~~~ % Total
>> >> >> Event Waits Time (s) Ela Time
>> >> >> -------------------------------------------- ------------
>> -----------
>> >> >> --------
>> >> >> db file sequential read 426,010 9,678 60.75
>> >> >> db file scattered read 98,033 2,469 15.49
>> >> >> latch free 31,605 1,800 11.30
>> >> >> buffer busy waits 19,365 566 3.55
>> >> >> direct path read 6,879 458 2.88
>> >> >>
>> >> >> I have read Note:257338.1 "Direct I/O (DIO) and Concurrent I/O
>> (CIO)
>> >> on
>> >> >> AIX 5L", unfortunately Oracle's file systems were created like
>> "JFS"
>> >> >> (due
>> >> >> snapshot utility for Operating System) and they are mounted with
>> big
>> >> >> files
>> >> >> support, so we are going to change them according to above
>> document
>> >> to
>> >> >> support "cio" option.
>> >> >>
>> >> >> Also, I have read another note regarding to "rwrb" option when
>> >> mounting
>> >> >> filesystems to avoid Operating System buffer cache.
>> >> >>
>> >> >> Also I have heard that minservers and maxservers need to be
>> >> increased,
>> >> >> we
>> >> >> have next default configuration:
>> >> >>
>> >> >> oraprod@p5501:/admin/oraprod$ lsattr -El aio0
>> >> >> autoconfig available STATE to be configured at system restart True
>> >> >> fastpath   enable    State of fast path                       True
>> >> >> kprocprio  39        Server PRIORITY                          True
>> >> >> maxreqs    4096      Maximum number of REQUESTS               True
>> >> >> maxservers 10        MAXIMUM number of servers per cpu        True
>> >> >> minservers 1         MINIMUM number of servers                True
>> >> >>
>> >> >> Has anybody had the same performance problems on AIX?
>> >> >>
>> >> >> In this server we have "virtualization" configuration, we are
>> going
>> >> to
>> >> >> eliminate this to avoid "memory" and "cpu" consumption.
>> >> >>
>> >> >> Any advice or comment will be really appreciated.
>> >> >>
>> >> >> Kind regards,
>> >> >>
>> >> >> Francisco Mtz.
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> > --
>> >> > Yury
>> >> > +44 7738 013090 (GMT)
>> >> > ============================================
>> >> > http://otn.oracle.com/ocm/jvelikanovs.html
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>> > --
>> > Yury
>> > +44 7738 013090 (GMT)
>> > ============================================
>> > http://otn.oracle.com/ocm/jvelikanovs.html
>> >
>>
>>
>>
>>
>
>
> --
> Yury
> +44 7738 013090 (GMT)
> ============================================
> http://otn.oracle.com/ocm/jvelikanovs.html
>

Other related posts: