Re: Informal poll: What's your avg redo write time?
From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Thu, 19 Feb 2015 17:28:29 -0500
Message-ID: <54E6638D.6050307_at_yahoo.com>
On 02/19/2015 04:10 PM, Rich Jesse wrote:
> Hey all,
>
> In 11.2.0.3 on AIX 7.1, I'm tracing down an issue on an SR and
> stumbled into the world of LGWR. It's causing blocking locks (falsely
> being reported, I believe) when stressed, so I'm looking at LGWR
> performance.
>
> Using Kyle Hailey's average log write time query at the bottom of --
> *NOTE: TUNING PACK LICENSE REQUIRED* --
> http://www.oaktable.net/content/lgwr-redo-write-times-log-file-parallel-write
> <javascript:void(0);/*1424380114607*/> my average is about 0.52ms with
> 90% of all between 0.4ms and 0.6ms, and another 8.4% at 0.7ms.
>
> Knowing that there's over-committing going on, I'm thinking that
> 0.52ms isn't my issue, but what are other folks getting?
>
> TIA!
> Rich
>
> p.s. Testing HTML formatting -- apologies if this message doesn't
> look like it should!
Date: Thu, 19 Feb 2015 17:28:29 -0500
Message-ID: <54E6638D.6050307_at_yahoo.com>
On 02/19/2015 04:10 PM, Rich Jesse wrote:
> Hey all,
>
> In 11.2.0.3 on AIX 7.1, I'm tracing down an issue on an SR and
> stumbled into the world of LGWR. It's causing blocking locks (falsely
> being reported, I believe) when stressed, so I'm looking at LGWR
> performance.
>
> Using Kyle Hailey's average log write time query at the bottom of --
> *NOTE: TUNING PACK LICENSE REQUIRED* --
> http://www.oaktable.net/content/lgwr-redo-write-times-log-file-parallel-write
> <javascript:void(0);/*1424380114607*/> my average is about 0.52ms with
> 90% of all between 0.4ms and 0.6ms, and another 8.4% at 0.7ms.
>
> Knowing that there's over-committing going on, I'm thinking that
> 0.52ms isn't my issue, but what are other folks getting?
>
> TIA!
> Rich
>
> p.s. Testing HTML formatting -- apologies if this message doesn't
> look like it should!
Hi Rich,
I am now a consultant so I don't have the database that I am in charge
of and, consequently, no numbers for you. However, from the numbers you
presented, the duration of commit at this particular database is about
half a millisecond. That means that the database would theoretically be
able to commit 2000 times per second, which is not a shabby performance
by any standard. With all due respect, and we communicate by email on
and off this list for about two decades, you don't have a problem with
the duration of commit.
However I have recently encountered falsely reported blocking lock which
were happening whenever TEMP tablespace was being extended.
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 19 2015 - 23:28:29 CET