| 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: v$sql 'db block changes'
Thanks John, but in this case the writes are not direct writes. Most of the write activity is actually to the UNDO tablespace, which I moved off of the RAID5 filesystem and on to RAID1 instead (yes, RAID1 - mirroring but no striping - unfortunately that's all I was given to work with). Here are some snippets from statspack this morning.
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ CSDPRD83 2745275672 csdprd83 1 9.2.0.6.0 NO IM-DBS01
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 10583 15-Dec-05 08:06:24 109 #########
End Snap: 10591 15-Dec-05 10:07:01 95 #########
Elapsed: 120.62 (mins)
Cache Sizes (end)
Buffer Cache: 1,752M Std Block Size: 8K
Shared Pool Size: 208M Log Buffer: 1,024K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 286,941.55 7,104.31
Logical reads: 39,026.03 966.23
Block changes: 2,129.71 52.73
Physical reads: 280.24 6.94
Physical writes: 160.70 3.98
User calls: 1,834.40 45.42
Parses: 432.94 10.72
Hard parses: 2.28 0.06
Sorts: 87.71 2.17
Logons: 0.00 0.00
Executes: 613.49 15.19
Transactions: 40.39
% Blocks changed per Read: 5.46 Recursive Call %: 41.24
Rollback per transaction %: 0.01 Rows per Sort: 20.09
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.88 Redo NoWait %: 100.00
Buffer Hit %: 99.28 In-memory Sort %: 100.00
Library Hit %: 99.72 Soft Parse %: 99.47
Execute to Parse %: 29.43 Latch Hit %: 99.67
Parse CPU to Parse Elapsd %: 90.90 % Non-Parse CPU: 96.56
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.57 94.42
% SQL with executions>1: 30.07 33.51
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 1,674,430 39,101 57.18
log file sync 965,126 10,775 15.76
CPU time 7,421 10.85
buffer busy waits 340,177 7,326 10.71
db file scattered read 55,598 1,704 2.49
-------------------------------------------------------------
Instance Activity Stats for DB: CSDPRD83 Instance: csdprd83 Snaps: 10583 -1059
Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical reads 2,028,069 280.2 6.9 physical reads direct 140 0.0 0.0 physical reads direct (lob) 1,159 0.2 0.0 physical writes 1,162,969 160.7 4.0 physical writes direct 149 0.0 0.0 physical writes non checkpoint 1,095,920 151.4 3.8
Tablespace IO Stats for DB: CSDPRD83 Instance: csdprd83 Snaps: 10583 -10591 ->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TRIRIGA_DATA
-----Original Message-----
From: John Kanagaraj [mailto:john.kanagaraj_at_hds.com]
Sent: Thursday, December 15, 2005 1:01 PM
To: Allen, Brandon; Ethan Post
Cc: oracle-l_at_freelists.org
Subject: RE: v$sql 'db block changes'
> in terms of cpu, elapsed_time, buffer_gets and disk_reads are all
SELECT statements,
> so I know they're not causing the write activity.
Brandon,
Some SELECT statements do require writing to TEMP (due to SORT or HASH operations). And since direct writes to TEMP do not have to be (redo)logged, they are done fairly rapidly which can result in the kind of problems with RAID 5 that you are seeing....
Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 15 2005 - 14:24:37 CST
 |
 |