RE: suddenly high "physical write" from statspack
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Feb 2004 12:53:03 -0500
If business is growing then this new business is logically going to increase
the physical write count. If your application is an OLTP style system then
every new piece of data is going to require table row inserts plus updates
to one or more associated index. Being that there are probably several
tables involved for each new account/user/customer added plus their activity
you may want to try to estimate future physical IO based on your history.
If the IO per transaction has been fairly steady then by going back and
getting customer counts for past periods and matching those to the
transaction count you can get an average IO per customer (or some business
measure like sales orders) then you could try to project the IO figure out
based on future values for the unit of measure.
By comparing this number to the disk system IO capacity numbers this might
provide warning if your application is outgrowing its hardware.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of zhu chao
Sent: Friday, February 20, 2004 10:46 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: Yong Huang; rudolf lu [CNOUG?; Randy; jerrysun
Subject: Re: suddenly high "physical write" from statspack
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@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@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
> -----------------------------------------------------------------
>
>
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
