Re: suddenly high "physical write" from statspack

From: zhu chao <chao_ping_at_vip.163.com>
Date: Fri, 20 Feb 2004 23:45:56 +0800
Message-ID: <006001c3f7c8$a3cdf6d0$562d5fd3@chaos>

Hi, Hemant:

Thanks very much for your feedback.
We did not see any performance degration. This is the good thing.You are right, the transaction number does increased some percent, as the business continues to grow. But all other statistics are similar, only the "physical write" statistics seems out of control:)

The bad thing is that BOSS do not like that picture.

And being an oracle DBA, I am also curious about it. I would like to do some research and find out the reason. Maybe oracle bug about the statistics, maybe some internal arithmetic about how the dirty buffer is written onto the disk.

Thanks
Zhu Chao.

Original Message ----- From: "Hemant K Chitale" <hkchital_at_singnet.com.sg> To: <oracle-l_at_freelists.org> Sent: Friday, February 20, 2004 11:10 PM Subject: Re: suddenly high "physical write" from statspack

>
> 1. Did you notice a performance degradation ? Did your jobs take longer
> to run ?
> If not, why worry about the "physical writes per transaction" ?
> You haven't shown how many transactions/second you now have.
> 2. You earlier had 47.72*3600=171792 physical writes. At 0.67 per
> transaction,
> you had 256,405 transactions ! You now have 661968 physical writes. You
> now have 270,191 transactions ! You actually had __more__ transactions
> succeeding.
> 3. Your total redo generated is slightly higher, but _lower_ per
transaction.
> 4. Your block changes are slightly higher, but _lower_ per transaction.
> 5. Your total executions are slightly higher, but _lower_ per
transaction.
>
> I don't see a problem. I see that transactions are slightly __more__
efficient
> and overall throughput is higher.
>
> Hemant
>
> At 07:04 PM 20-02-04 +0800, you wrote:
> >Hi friends:
> > I have a cron to read some critical db performance data and draw a
> >picture. "physical write" trippled suddenly since last night.
> > Two days before:
> > Load Profile
> >~~~~~~~~~~~~ Per Second Per Transaction
> > --------------- ---------------
> > Redo size: 110,557.24 1,551.16
> > Logical reads: 60,151.08 843.94
> > Block changes: 774.88 10.87
> > Physical reads: 80.07 1.12
> > Physical writes: 47.72 0.67
> > User calls: 1,922.77 26.98
> > Parses: 332.12 4.66
> > Hard parses: 0.01 0.00
> > Sorts: 80.83 1.13
> > Logons: 0.13 0.00
> > Executes: 993.17 13.93
> > Transactions: 71.27
> >
> >
> > Today:
> > Load Profile
> >~~~~~~~~~~~~ Per Second Per Transaction
> > --------------- ---------------
> > Redo size: 111,542.94 1,488.84
> > Logical reads: 61,666.46 823.10
> > Block changes: 778.37 10.39
> > Physical reads: 76.27 1.02
> > Physical writes: 183.88
> >2.45----this value tripped
> > User calls: 1,980.19 26.43
> > Parses: 329.13 4.39
> > Hard parses: 0.01 0.00
> > Sorts: 82.45 1.10
> > Logons: 0.12 0.00
> > Executes: 1,012.86 13.52
> > The data is from a one-hour statspack report during 10:00-11:00.
> >
> > We see no performance degration, but boss is upset because of the
> >strange high in physical write. We made no modification in these days,
and
> >other database parameters are normal.
> > As "physical write" is caused mainly by dbwr, we feel it really
> >difficult to track down where the more physical write come from. And the
> >amount of redo does not change much, and the block change per second does
> >not change much either.
> > I also checked there is no parameter dynamically modified since
oracle
> >boot.(v$parameter.ismodified)
> >
> >The following is some data from my system:
> >
> >18:51:38 SQL> @whowrite
> >
> >USERNAME MACHINE SUM(A.VALUE)
> >------------------------------ -------------------- ------------
> >ACCOUNT app2 842
> >ACCOUNT app3 327
> >ACCOUNT app4 5,619
> >ACCOUNT appg 1,606,119
> >ACCOUNT apph 1,646,769
> >ACCOUNT appi 1,640,034
> >ACCOUNT appj 1,692,672
> >ACCOUNT mid1 7,692
> >ACCOUNT mid2 81,789
> >EACHPAY mid1 4,512
> >EACHPAY mid2 2,256
> >
> >USERNAME MACHINE SUM(A.VALUE)
> >------------------------------ -------------------- ------------
> >EACHPAY pay5 435
> >PERFSTAT main-db3 204
> > main-db3 51,085,303
> >
> >Can someone give me some suggestion on how to find out what on earth
caused
> >this more physical write?
> >
> >Regards
> >Zhu Chao
> >
> >
> >----------------------------------------------------------------
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >----------------------------------------------------------------
> >To unsubscribe send email to: oracle-l-request_at_freelists.org
> >put 'unsubscribe' in the subject line.
> >--
> >Archives are at http://www.freelists.org/archives/oracle-l/
> >FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> >-----------------------------------------------------------------
>
> Hemant K Chitale
> Oracle 9i Database Administrator Certified Professional
> http://hkchital.tripod.com {last updated 24-Jan-04}
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 20 2004 - 09:45:56 CST