suddenly high "physical write" from statspack

From: zhu chao <chao_ping_at_vip.163.com>
Date: Fri, 20 Feb 2004 19:04:45 +0800
Message-ID: <001901c3f7a1$5c226860$9005a8c0@chaos>

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

Received on Fri Feb 20 2004 - 05:04:45 CST