| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: suddenly high "physical write" from statspack
Hi, yong:
We do have hourly statspack snapshot. From statspack report, we see some tablespaces get more write:
when with less write:
LTablespace IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7412 -7413
->ordered by IOs (Reads + Writes) desc
Tablespace
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
ACCOUNT_TS
when with high write:
Tablespace
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
BIDDB_TBL
10 0 0.0 1.0 5 0 0 0.0
-------------------------------------------------------------
^LFile IO Stats for DB: EACHDB2 Instance: eachdb2 Snaps: 7414 -7415
>From the above report, I can conclude that the eachpay user is causing more write.(as this tablespace is mainly eachpay user's tablespace, though it also contain other schema's data). Oradebug dump global_area 1 generate a tracefile, but I cannot find the changed parameter there. This database is still oracle 8.1.7 and there is no v$segstat table.
1 select /*+rule*/ c.username,c.machine,b.name,sum(a.value)
2 from v$sesstat a, v$statname b,v$session c
3 where a.statistic#=b.statistic#
4 and a.sid=c.sid
5 and b.name like '%physical write%'
6 group by c.username, c.machine,b.name
7* having sum(a.value)>0 order by 4 asc
20:39:52 SQL> /
USERNAME MACHINE NAME SUM(A.VALUE)
------------------------------ -------------------- ------------------------------ ------------
ACCOUNT app3 physical writes 334
ACCOUNT app3 physical writes direct 334
ACCOUNT app3 physical writes non checkpoint 334
EACHPAY pay5 physical writes 435
EACHPAY pay5 physical writes non checkpoint 435
EACHPAY pay5 physical writes direct 435
ACCOUNT app2 physical writes 1322
ACCOUNT app2 physical writes direct 1322
ACCOUNT app2 physical writes non checkpoint 1322
EACHPAY mid2 physical writes 4224
EACHPAY mid2 physical writes direct 4224
USERNAME MACHINE NAME SUM(A.VALUE)
------------------------------ -------------------- ------------------------------ ------------
EACHPAY mid2 physical writes non checkpoint 4224
ACCOUNT app4 physical writes 7029
ACCOUNT app4 physical writes direct 7029
ACCOUNT app4 physical writes non checkpoint 7029
EACHPAY mid1 physical writes 8448
EACHPAY mid1 physical writes direct 8448
EACHPAY mid1 physical writes non checkpoint 8448
ACCOUNT mid1 physical writes 9074
ACCOUNT mid1 physical writes direct 9074
ACCOUNT mid1 physical writes non checkpoint 9074
PERFSTAT main-db3 physical writes 10929
USERNAME MACHINE NAME SUM(A.VALUE)
------------------------------ -------------------- ------------------------------ ------------
PERFSTAT main-db3 physical writes direct 10929
PERFSTAT main-db3 physical writes non checkpoint 10929
ACCOUNT mid2 physical writes 59433
ACCOUNT mid2 physical writes direct 59433
ACCOUNT mid2 physical writes non checkpoint 59433
main-db3 physical writes direct 173681
ACCOUNT appg physical writes 1692664
ACCOUNT appg physical writes direct 1692664
ACCOUNT appg physical writes non checkpoint 1692664
ACCOUNT apph physical writes 1735456
ACCOUNT apph physical writes direct 1735456
USERNAME MACHINE NAME SUM(A.VALUE)
------------------------------ -------------------- ------------------------------ ------------
ACCOUNT apph physical writes non checkpoint 1735456
ACCOUNT appi physical writes 1740670
ACCOUNT appi physical writes direct 1740670
ACCOUNT appi physical writes non checkpoint 1740670
ACCOUNT appj physical writes 1785008
ACCOUNT appj physical writes direct 1785008
ACCOUNT appj physical writes non checkpoint 1785008
main-db3 physical writes non checkpoint 23826637
main-db3 physical writes 62862580
I think sort/hashing does not have much to do with this, as sort used direct read/write in 8i version, right? and there is seperate io for them:
hysical reads 392,154 108.5 1.6 physical reads direct 32,699 9.1 0.1 physical writes 618,698 171.2 2.5 physical writes direct 33,639 9.3 0.1 physical writes non checkpoint 110,514 30.6 0.4The strange thing is that, if more transactions and more modification to the database, there shoule be similar more redo/undo genrated, and similar more db block changes, right? Still cannot understand it.
Regards
Zhu Chao.
> [oracle-l addressed deleted from To: list] > > Zhu Chao, > > If you are indeed curious about the increased physical writes, you probably > have to get the v$filestat and v$tempstat output before and after that period, > possibly plus v$segstat (or v$segment_statistics) before and after on some > usually hot segments. If this data is too late to get, you may rely on OS sar > -d -f sa<thatday> output and approximately track to Oracle datafiles / > tempfiles I/O usage. Even though DBWn does data writes, foreground processes > can do direct write such as sorting and hashing. You can't find anybody's > session-dynamic change of sort_area_size from v$parameter (oradebug dump > global_area can find it). Also, did anybody do direct mode import/export, > insert /*+ append /? (I'm not very sure about the last point. I have to think > about it. I know these operations won't contribute to any wait events but show > up in v$segstat, and likely "physical writes" in v$XXXstat view. > > BTW, what's the last column of your whowrite script output? > > Keep us posted. > > Yong > > --- zhu chao <chao_ping_at_vip.163.com> wrote: > > 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_at_singnet.com.sg> > > To: <oracle-l_at_freelists.org> > > 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 > > > >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 > > __________________________________ > Do you Yahoo!? > Yahoo! Mail SpamGuard - Read only the mail you want. > http://antispam.yahoo.com/tools > > > ----------------------------------------------------------------Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 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 Sat Feb 21 2004 - 06:44:14 CST
 |
 |