FAQ
Our applications connect to only 1 node of a 2-node 10gR2 (10.2.0.5)
RAC system on Solaris 10. ASM is configured.
The TPS is mostly around 5 and at times 11.
There is hardly any load on the server. CPU is around 8% on a HP DL380
box (it has 2 cpu with 6 cores each). Out of that 8%, 7.9% is consumed
by lgwr. Dont understand why? prstat -Lw for the lgwr pid shows that
there are 2 LWPs and for one of them CPU in SYS mode is around 50-60%.

To troubleshoot, checked statspack and AWR reports and found that
maximum time spent is on log file sync (avg is 75msec). For log file
parallel write, avg is 69msec. I understand that is too high but
iostat always shows 0.2msec. Why this mismatch?
So enabled 10046 tracing for the lgwr and it showed only log file
parallel write and ela was in line with what is reported by AWR i.e.
69msec.
asmiostat shows 0msec and after every 6-8 seconds, it shows 135msec.
But I think that the asmiostat script as available on metalink has a
bug. Because what it gathers is in centisec and what it displays is in
millsec. And hence it should multiply by 10 but it multiplies by 1000.
Pls correct me if I am wrong here.

As a result of the above, the system is very vulnerable to contention.
At times, application sessions wait for as long as 10-15 seconds on
log file sync and as a result, apps restart.

Thanks.

Search Discussions

  • Riyaj Shamsudeen at Jan 12, 2012 at 3:59 pm
    Hello Purav
    So, your log file parallel write is averaging at 69ms. That number is
    high. By any chance, are you running your database in Maximum availability
    mode? Can you post your values for log_archive_dest* parameters?
    I don't have an answer for you, but my thinking would be along these
    lines: If the database is not running in Max availability then, you have to
    figure out whether the wait time is spent in the OS side, SAN side, or in
    the path. What type of luns do you have? do you have iSCSI, if yes, is the
    network route optimal? Is the iostat output that you mentioned specific to
    those redo devices? Have they configured SAN cache for write caching? Who
    else is using the same SAN? Is DBWR suffering from same I/O write latency?
    Cheers

    Riyaj Shamsudeen
    Principal DBA,
    Ora!nternals - http://www.orainternals.com - Specialists in Performance,
    RAC and EBS
    Blog: http://orainternals.wordpress.com
    OakTable member http://www.oaktable.com and Oracle ACE Director

    Co-author of the books: Expert Oracle
    Practices<http://tinyurl.com/book-expert-oracle-practices/>,
    Pro Oracle SQL, Expert PL/SQL
    Practices<http://tinyurl.com/book-expert-plsql-practices>


    On Thu, Jan 12, 2012 at 8:40 AM, Purav Chovatia wrote:

    Our applications connect to only 1 node of a 2-node 10gR2 (10.2.0.5)
    RAC system on Solaris 10. ASM is configured.
    The TPS is mostly around 5 and at times 11.
    There is hardly any load on the server. CPU is around 8% on a HP DL380
    box (it has 2 cpu with 6 cores each). Out of that 8%, 7.9% is consumed
    by lgwr. Dont understand why? prstat -Lw for the lgwr pid shows that
    there are 2 LWPs and for one of them CPU in SYS mode is around 50-60%.

    To troubleshoot, checked statspack and AWR reports and found that
    maximum time spent is on log file sync (avg is 75msec). For log file
    parallel write, avg is 69msec. I understand that is too high but
    iostat always shows 0.2msec. Why this mismatch?
    So enabled 10046 tracing for the lgwr and it showed only log file
    parallel write and ela was in line with what is reported by AWR i.e.
    69msec.
    asmiostat shows 0msec and after every 6-8 seconds, it shows 135msec.
    But I think that the asmiostat script as available on metalink has a
    bug. Because what it gathers is in centisec and what it displays is in
    millsec. And hence it should multiply by 10 but it multiplies by 1000.
    Pls correct me if I am wrong here.

    As a result of the above, the system is very vulnerable to contention.
    At times, application sessions wait for as long as 10-15 seconds on
    log file sync and as a result, apps restart.

    Thanks.
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 12, '12 at 2:41p
activeJan 12, '12 at 3:59p
posts2
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase