suddenly high "physical write" from statspack
- From: "zhu chao" <chao_ping@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Feb 2004 19:04:45 +0800
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
