Re: suddenly high "physical write" from statspack

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Fri, 20 Feb 2004 23:10:42 +0800
Message-Id: <5.1.1.6.0.20040220230140.00ab63f0@pop.singnet.com.sg>

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
-----------------------------------------------------------------

Received on Fri Feb 20 2004 - 09:10:42 CST