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