Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Performance -- Possible Disk Bottleneck
I'm digging into the performance article tonight. Thanks so much for
the feedback.
Here is the statspack. I hate posting something so large, so I hope it's ok. If someone is kind enough to take a peak, then it is appreciated.
SQL> SQL> column library format a12 trunc; SQL> column pinhitratio heading 'PINHITRATI'; SQL> column gethitratio heading 'GETHITRATI'; SQL> column invalidations heading 'INVALIDATI'; SQL> set numwidth 10; SQL> Rem Select Library cache statistics. The pin hit rate should behigh.
2 gets, 3 round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3) 4 gethitratio, 5 pins, 6 round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3) 7 pinhitratio, 8 reloads, invalidations
LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS
INVALIDATI
------------ ---------- ---------- ---------- ---------- ----------
BODY 696 .993 709 .961 22 0 CLUSTER 1645 .998 1462 .995 0 0 INDEX 0 1 0 1 0 0 JAVA DATA 0 1 0 1 0 0 JAVA RESOURC 0 1 0 1 0 0 JAVA SOURCE 0 1 0 1 0 0 OBJECT 0 1 0 1 0 0 PIPE 0 1 0 1 0 0 SQL AREA 122439 .944 1060773 .982 2886 10 TABLE/PROCED 461062 .991 434007 .978 3622 0 TRIGGER 2914 .972 2914 .93 122 0
11 rows selected.
SQL> SQL> column "Statistic" format a27 trunc; SQL> column "Per Transaction" heading "Per Transact"; SQL> column ((start_users+end_users)/2) heading "((START_USER" SQL> set numwidth 12; SQL> Rem The total is the total value of the statistic between thetime
'USERSCONNECTEDAT' TO_CHAR(START_TIME '
START_USERS
------------------- ------------------ -
SQL> select 'Users connected at ',to_char(end_time, 'dd-mon-yy hh24:mi:ss'),':',end_users from stats$dates;
'USERSCONNECTEDAT' TO_CHAR(END_TIME,' '
END_USERS
------------------- ------------------ -
SQL> select 'avg # of connections: ',((start_users+end_users)/2) from stats$dates;
'AVG#OFCONNECTIONS:' ((START_USER
SQL>
SQL> select n1.name "Statistic",
2 n1.change "Total", 3 round(n1.change/trans.change,2) "Per Transaction", 4 round(n1.change/((start_users + end_users)/2),2) "Per Logon", 5 round(n1.change/(to_number(to_char(end_time,Per Second
'J'))*60*60*24 -
6 to_number(to_char(start_time, 'J'))*60*60*24 + 7 to_number(to_char(end_time, 'SSSSS')) - 8 to_number(to_char(start_time, 'SSSSS'))) 9 , 2) "Per Second" 10 from 11 stats$stats n1, 12 stats$stats trans, 13 stats$dates 14 where 15 trans.name='user commits' 16 and n1.change != 0 17 order by n1.name; Statistic Total Per Transact Per Logon
--------------------------- ------------ ------------ ------------ ------------ CR blocks created 489 1.01 4.77 .27 DBWR buffers scanned 837286 1726.36 8168.64 465.68 DBWR checkpoint buffers wri 10 .02 . 1 .01 DBWR checkpoints 1 0 . 01 0 DBWR free buffers found 833915 1719.41 8135.76 463.8 DBWR lru scans 1682 3.47 16.41 .94 DBWR make free requests 2061 4.25 20.11 1.15 DBWR summed scan depth 837286 1726.36 8168.64 465.68 DBWR transaction table writ 86 .18 . 84 .05 DBWR undo block writes 1107 2.28 10.8 .62 SQL*Net roundtrips to/from 240230 495.32 2343.71 133.61 SQL*Net roundtrips to/from 161 .33 1.57 .09 active txn count during cle 1193 2.46 11.64 .66 background checkpoints comp 1 0 . 01 0 background checkpoints star 1 0 . 01 0 background timeouts 3992 8.23 38.95 2.22 buffer is not pinned count 96882351 199757.42 945193.67 53883.4 buffer is pinned count 209263905 431471.97 2041599.07 116387.04 bytes received via SQL*Net 38590790 79568.64 376495.51 21463.18 bytes received via SQL*Net 28719 59.21 280.19 15.97
bytes sent via SQL*Net to d 13584 28.01 132.53 7.56 calls to get snapshot scn: 4293150 8851.86 41884.39 2387.74 calls to kcmgas 5178 10.68 50.52 2.88 calls to kcmgcs 625 1.29 6.1 .35 cleanout - number of ktugct 1529 3.15 14.92 .85 cleanouts and rollbacks - c 368 .76 3.59 .2 cleanouts only - consistent 159 .33 1.55 .09 cluster key scan block gets 204645 421.95 1996.54 113.82 cluster key scans 186011 383.53 1814.74 103.45 commit cleanout failures: b 556 1.15 5.42 .31 commit cleanouts 8022 16.54 78.26 4.46 commit cleanouts successful 7466 15.39 72.84 4.15 commit txn count during cle 1385 2.86 13.51 .77 consistent changes 8930 18.41 87.12 4.97 consistent gets 106281336 219136.78 1036891.08 59110.87 consistent gets - examinati 37888575 78120.77 369644.63 21072.62 current blocks converted fo 3 .01 . 03 0 cursor authentications 3841 7.92 37.47 2.14 data blocks consistent read 978 2.02 9.54 .54 db block changes 92528 190.78 902.71 51.46 db block gets 97942 201.94 955.53 54.47 deferred (CURRENT) block cl 5224 10.77 50.97 2.91 dirty buffers inspected 2355 4.86 22.98 1.31 enqueue conversions 80 .16 . 78 .04 enqueue releases 29350 60.52 286.34 16.32 enqueue requests 29319 60.45 286.04 16.31 enqueue timeouts 6 .01 . 06 0 execute count 760148 1567.32 7416.08 422.77 free buffer inspected 6398 13.19 62.42 3.56 free buffer requested 4870521 10042.31 47517.28 2708.85 hot buffers moved to head o 602322 1241.9 5876.31 335 immediate (CR) block cleano 527 1.09 5.14 .29 immediate (CURRENT) block c 719 1.48 7.01 .4 index fast full scans (full 4 .01 . 04 0 index fetch by key 22111694 45591.12 215723.84 12297.94 index scans kdiixs1 14908898 30740 145452.66 8291.93 leaf node 90-10 splits 19 .04 . 19 .01 leaf node splits 124 .26 1.21 .07 logons cumulative 156 .32 1.52 .09 logons current 7 .01 . 07 0 messages received 4153 8.56 40.52 2.31 messages sent 4153 8.56 40.52 2.31 no work - consistent read g 50271488 103652.55 490453.54 27959.67 opened cursors cumulative 73980 152.54 721.76 41.15 opened cursors current 169 .35 1.65 .09 parse count (failures) 147 .3 1.43 .08 parse count (hard) 9984 20.59 97.4 5.55 parse count (total) 145995 301.02 1424.34 81.2 physical reads 4873242 10047.92 47543.82 2710.37 physical reads direct 5328 10.99 51.98 2.96 physical writes 18709 38.58 182.53 10.41 physical writes direct 13164 27.14 128.43 7.32 physical writes non checkpo 18703 38.56 182.47 10.4 pinned buffers inspected 3785 7.8 36.93 2.11 prefetched blocks 958405 1976.09 9350.29 533.04 prefetched blocks aged out 179 .37 1.75 .1 process last non-idle time 1797 3.71 17.53 1 recursive calls 1443176 2975.62 14079.77 802.66 recursive cpu usage 50082 103.26 488.6 27.85 redo blocks written 28594 58.96 278.97 15.9 redo buffer allocation retr 2 0 . 02 0 redo entries 45818 94.47 447 25.48 redo log space requests 2 0 . 02 0 redo size 13883700 28626.19 135450.73 7721.75 redo synch writes 611 1.26 5.96 .34 redo wastage 302472 623.65 2950.95 168.23 redo writes 1182 2.44 11.53 .66 rollback changes - undo rec 295 .61 2.88 .16 rollbacks only - consistent 124 .26 1.21 .07 rows fetched via callback 21750324 44846.03 212198.28 12096.95 session logical reads 106379278 219338.72 1037846.61 59165.34 session pga memory 13097796 27005.76 127783.38 7284.65 session pga memory max 19168416 39522.51 187008.94 10660.97 session uga memory 8597944504 17727720.63 83882385.4 4781949.11 session uga memory max 62722392 129324.52 611925.78 34884.53 shared hash latch upgrades 14783761 30481.98 144231.81 8222.34 shared hash latch upgrades 2071 4.27 20.2 1.15 sorts (disk) 11 .02 . 11 .01 sorts (memory) 1614111 3328.06 15747.42 897.73 sorts (rows) 3434505 7081.45 33507.37 1910.18 summed dirty queue length 26 .05 . 25 .01 switch current to new buffe 614 1.27 5.99 .34 table fetch by rowid 133067911 274366.83 1298223.52 74008.85 table fetch continued row 593492 1223.69 5790.17 330.08 table scan blocks gotten 2769428 5710.16 27018.81 1540.28 table scan rows gotten 159581878 329034.8 1556896.37 88755.22 table scans (long tables) 520 1.07 5.07 .29 table scans (short tables) 1214716 2504.57 11850.89 675.59 transaction rollbacks 4 .01 . 04 0 user calls 249521 514.48 2434.35 138.78 user commits 485 1 4.73 .27 user rollbacks 53 .11 . 52 .03 workarea executions - onepa 12 .02 . 12 .01 workarea executions - optim 46135 95.12 450.1 25.66
115 rows selected.
SQL> SQL> column "Event Name" format a32 trunc; SQL> set numwidth 13; SQL> Rem System wide wait events for non-background processes (PMON, SQL> Rem SMON, etc). Times are in hundreths of seconds. Each one of SQL> Rem these is a context switch which costs CPU time. By lookingat
SQL> Rem waiting for a specific event and the average time per wait on SQL> Rem that event. SQL> select n1.event "Event Name", 2 n1.event_count "Count", 3 n1.time_waited "Total Time", 4 round(n1.time_waited/n1.event_count, 2) "Avg Time" 5 from stats$event n1 6 where n1.event_count > 0 7 order by n1.time_waited desc; Event Name Count Total Time AvgTime
-------------------------------- ------------- ------------- ------------- SQL*Net message from client 240834 8773750 36.43 db file sequential read 3742459 216409 .06 rdbms ipc message 362 179038 494.58 queue messages 179 178797 998.87 virtual circuit status 60 178452 2974.2 dispatcher timer 30 175414 5847.13 jobq slave wait 127 37982 299.07 db file scattered read 166893 13092 .08 SQL*Net break/reset to client 881 12105 13.74 buffer busy waits 41906 4122 .1 SQL*Net more data to client 21316 122 .01 latch free 1092 65 .06 log file sync 585 34 .06 SQL*Net message to client 240967 29 0 direct path read 468 18 .04 SQL*Net more data from client 1685 15 .01 library cache pin 21 15 .71 SQL*Net message from dblink 161 14 .09 control file sequential read 69 10 .14 log file switch completion 2 6 3 db file parallel read 4 5 1.25 single-task message 1 3 3 direct path write 44 1 .02 rdbms ipc reply 2 1 .5 SQL*Net message to dblink 161 0 0 local write wait 1 0 0 library cache load lock 2 0 0
27 rows selected.
SQL> SQL> SQL> Rem System wide wait events for background processes (PMON, SMON,etc)
2 n1.event_count "Count", 3 n1.time_waited "Total Time", 4 round(n1.time_waited/n1.event_count, 2) "Avg Time" 5 from stats$bck_event n1 6 where n1.event_count > 0 7 order by n1.time_waited desc; Event Name Count Total Time AvgTime
-------------------------------- ------------- ------------- ------------- rdbms ipc message 6391 1486474 232.59 pmon timer 614 179445 292.26 smon timer 105 179082 1705.54 db file parallel write 1331 114 .09 log file sequential read 103 70 .68 control file sequential read 463 67 .14 control file parallel write 616 63 .1 db file sequential read 279 60 .22 log file parallel write 1182 7 .01 direct path read 38 5 .13 LGWR wait for redo copy 57 0 0 direct path write 38 0 0 log file single write 2 0 0 rdbms ipc reply 13 0 0 latch free 4 0 0 async disk IO 49 0 0
16 rows selected.
SQL> SQL> SQL> column latch_name format a18 trunc; SQL> set numwidth 11; SQL> Rem Latch statistics. Latch contention will show up as a largevalue for
SQL> Rem the 'latch free' event in the wait events above. SQL> Rem Sleeps should be low. The hit_ratio should be high. SQL> select name latch_name, gets, misses, 2 round((gets-misses)/decode(gets,0,1,gets),3) 3 hit_ratio, 4 sleeps, 5 round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS" 6 from stats$latches 7 where gets != 0 8 order by name; LATCH_NAME GETS MISSES HIT_RATIO SLEEPSSLEEPS/MISS
------------------ ----------- ----------- ----------- ----------- ----------- Consistent RBA 1183 0 1 0 0 FAL request queue 36 0 1 0 0 FIB s.o chain latc 10 0 1 0 0 FOB s.o list latch 1496 0 1 0 0 SQL memory manager 42177 0 1 0 0 active checkpoint 5413 2 1 0 0 alert log latch 4 0 1 0 0 archive control 63 0 1 0 0 archive process la 52 0 1 0 0 cache buffer handl 4407340 225 1 0 0 cache buffers chai 180254493 31059 1 85 .003 cache buffers lru 15557 12 .999 0 0 channel handle poo 616 0 1 0 0 channel operations 2758 3 .999 0 0 checkpoint queue l 441402 0 1 0 0 child cursor hash 102978 5 1 0 0 commit callback al 120 0 1 0 0 constraint object 28 0 1 0 0 dictionary lookup 1 0 1 0 0 dml lock allocatio 11444 0 1 0 0 dummy allocation 304 0 1 0 0 enqueue hash chain 58725 1 1 0 0 enqueues 51823 5 1 0 0 event group latch 154 0 1 0 0 global tx hash map 338 0 1 0 0 hash table column 348 0 1 0 0 job_queue_processe 32 0 1 0 0 ktm global data 109 0 1 0 0 lgwr LWN SCN 1409 0 1 0 0 library cache 4876926 3631 .999 48 .013 library cache load 17092 0 1 0 0 library cache pin 2833769 581 1 0 0 library cache pin 1133176 17 1 0 0 list of block allo 260 0 1 0 0 loader state objec 112 0 1 0 0 longop free list p 5 0 1 0 0 message pool opera 324 0 1 0 0 messages 19318 2 1 0 0 mostly latch-free 1417 8 .994 0 0 multiblock read ob 386610 92 1 0 0 ncodef allocation 31 0 1 0 0 object stats modif 19 0 1 0 0 post/wait queue 882 0 1 0 0 process allocation 300 0 1 0 0 process group crea 300 0 1 0 0 redo allocation 48388 3 1 0 0 redo writing 10154 0 1 0 0 row cache enqueue 1637776 358 1 0 0 row cache objects 1663298 294 1 0 0 sequence cache 6560 0 1 0 0 session allocation 780275 105 1 0 0 session idle bit 511589 1 1 0 0 session switching 232 0 1 0 0 session timer 614 0 1 0 0 shared pool 2543027 5586 .998 963 .172 simulator hash lat 5805854 2 1 0 0 simulator lru latc 247343 8 1 0 0 sort extent pool 1217 0 1 0 0 temporary table st 3 0 1 0 0 transaction alloca 369 0 1 0 0 transaction branch 224 0 1 0 0 undo global data 19239 0 1 0 0 user lock 1014 0 1 0 0
63 rows selected.
SQL> SQL> set numwidth 16 SQL> Rem Statistics on no_wait gets of latches. A no_wait get doesnot
2 immed_gets nowait_gets, 3 immed_miss nowait_misses, 4 round((immed_gets/(immed_gets+immed_miss)), 3) 5 nowait_hit_ratio 6 from stats$latches 7 where immed_gets + immed_miss != 0 8 order by name; LATCH_NAME NOWAIT_GETS NOWAIT_MISSESNOWAIT_HIT_RATIO
------------------ ---------------- ---------------- ---------------- SQL memory manager 599 0 1 cache buffers chai 5900251 332 1 cache buffers lru 6124904 3900 . 999 checkpoint queue l 5125 0 1 hash table column 753064 5 1 job workq parent l 4 1 . 8 library cache 47360 249 . 995 longop free list p 5 0 1 post/wait queue 587 0 1 process allocation 154 0 1 redo copy 45818 57 . 999 row cache objects 4618 0 1 session idle bit 3 0 1 simulator lru latc 317307 231
14 rows selected.
SQL>
SQL> Rem Buffer busy wait statistics. If the value for 'buffer busy
wait' in
SQL> Rem the wait event statistics is high, then this table will
identify
SQL> Rem which class of blocks is having high contention. If there
are high
SQL> Rem 'undo header' waits then add more rollback segments. If
there are
SQL> Rem high 'segment header' waits then adding freelists might help.
Check
SQL> Rem v$session_wait to get the addresses of the actual blocks
having
SQL> Rem contention.
SQL> select * from stats$waitstat
2 where count != 0
3 order by count desc;
CLASS COUNT
------------------ ---------------- ---------------- data block 41901 0 segment header 2 0 1st level bmb 1 0 undo header 1 0 2nd level bmb 1
SQL> SQL> SQL> set lines 159; SQL> set numwidth 19; SQL> Rem Waits_for_trans_tbl high implies you should add rollbacksegments.
UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES XACTS SHRINKS WRAPS ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- 0 106 0 0 385024 0 0 0 1 2786 0 1443900 10608640 0 1 3 2 873 0 348998 37937152 -1 0 0 3 1779 0 1529048 8511488 -1 1 2 4 978 0 432266 8511488 0 0 1 5 215 0 27596 10608640 -1 0 0 6 929 0 139108 6479872 1 0 0 7 477 0 269772 9560064 1 0 0 8 999 0 507498 5365760 0 0 0 9 804 0 90410 4317184 -1 0 0 10 212 0 17184 58843136 -1 0 0 11 890 0 535460 2220032 0 0 1
12 rows selected.
SQL> set lines 79; SQL> SQL> column name format a39 trunc; SQL> column value format a39 trunc; SQL> Rem The init.ora parameters currently in effect: SQL> select name, value from v$parameter where isdefault = 'FALSE'2 order by name;
NAME
VALUE
background_dump_dest D:\oracle\admin\PROD\bdump
control_files G:\ORACLE\ORADATA\PROD \CONTROL01.CTL, G core_dump_dest D:\oracle\admin\PROD\cdump
dispatchers (PROTOCOL=TCP)(SERVICE=PRODXDB)
ifile D:\oracle\admin\PROD\pfile\init.ora
log_archive_dest F:\ORACLE\oradata\PROD\arch
nls_date_format DD-MON-YYYY
user_dump_dest D:\oracle\admin\PROD \udump utl_file_dir D:\ORACLE\admin\PROD\error_log
43 rows selected.
SQL> SQL> column name format a15 trunc; SQL> column scan_reqs heading 'SCAN_REQ'; SQL> column scan_miss heading 'SCAN_MIS'; SQL> column cur_usage heading 'CUR_USAG'; SQL> set numwidth 8; SQL> Rem get_miss and scan_miss should be very low compared to therequests.
NAME GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS COUNT
CUR_USAG
--------------- -------- -------- -------- -------- -------- --------
dc_tablespaces 800 1 0 0 0 6 6 dc_segments 118105 323 0 0 1 1556 1556 dc_rollback_seg 4330 0 0 0 0 22 22 dc_users 264160 18 0 0 3 77 77 dc_user_grants 2942 10 0 0 0 69 69 dc_objects 83952 3524 0 0 1 6030 6030 dc_usernames 65463 13 0 0 0 127 127 dc_object_ids 244021 203 0 0 0 1408 1408 dc_sequences 3007 4 0 0 3007 8 8 dc_profiles 277 0 0 0 0 3 3 dc_database_lin 176 2 0 0 0 2 2 dc_histogram_de 39886 511 0 0 0 2210 2210 dc_global_oids 83 3 0 0 0 20 20 dc_table_scns 814 10 0 0 10 70 70
14 rows selected.
SQL> SQL> SQL> set lines 157; SQL> column table_space format a80 trunc; SQL> set numwidth 10; SQL> Rem Sum IO operations over tablespaces. SQL> select 2 table_space||' ' 3 table_space, 4 sum(phys_reads) reads, sum(phys_blks_rd) blks_read, 5 sum(phys_rd_time) read_time, sum(phys_writes) writes,6 sum(phys_blks_wr) blks_wrt, sum(phys_wrt_tim) write_time, 7 sum(megabytes_size) megabytes
TABLE_SPACE
READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME
MEGABYTES
19 rows selected.
SQL> SQL> SQL> set lines 196; SQL> column table_space format a48 trunc; SQL> column file_name format a48 trunc; SQL> set numwidth 10; SQL> Rem I/O should be spread evenly accross drives. A big differencebetween
4 megabytes_size megabytes, 5 round(decode(phys_blks_rd,0,0,phys_rd_time/phys_blks_rd),2) avg_rt, 6 round(decode(phys_reads,0,0,phys_blks_rd/phys_reads),2)"blocks/rd"
TABLE_SPACE
FILE_NAME READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME MEGABYTESAVG_RT blocks/rd
38 rows selected.
SQL> set lines 79; SQL> SQL> column start_time format a25; SQL> column end_time format a25; SQL> Rem The times that bstat and estat were run. SQL> select to_char(start_time, 'dd-mon-yy hh24:mi:ss') start_time, 2 to_char(end_time, 'dd-mon-yy hh24:mi:ss') end_time3 from stats$dates;
START_TIME
END_TIME
SQL> SQL> column banner format a75 trunc; SQL> Rem Versions SQL> select * from v$version;
BANNER
SQL> SQL> SQL> spool off;
On Jun 7, 2:51 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jun 7, 12:49 pm, beth.sto..._at_gmail.com wrote: > > > > > > > On May 25, 7:42 am, EscVector <J..._at_webthere.com> wrote: > > > > On May 24, 5:56 pm, beth.sto..._at_gmail.com wrote: > > > > > My apologies if this subject has been discussed. I searched the > > > > groups, and I couldn't find a good thread. > > > > > We've been having performance problems with our Client/Server > > > > application for months. Users contantly complain of slow response > > > > times to their queries. > > > > > Here's the environment: > > > > > Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core > > > > Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion > > > > CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are > > > > stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched > > > > backend. Users connect using FastEthernet. XP clients. All disks on > > > > the SAN are fibre channel. > > > > > CPU utilization is fine. RAM utilization is fine. Throughput on the > > > > NIC is fine -- maxes out at 50 Mbps for a short while when users first > > > > log in in the morning. Averages are 20 Mbps. > > > > > Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/ > > > > sec., here's what I see for averages: > > > > > Disk Reads/sec = 2200 Avg > > > > Disk Writes/sec = 10 Avg > > > > > Our reads/sec seem EXTREMELY high for only 80 users. > > > > > Can someone help me understand if this is truly a disk bottleneck? > > > > > Thanks in advance! > > > > Don't use the windows tools to check on SAN performance. Talk with > > > the storage admins. They will have a much better perspective on both > > > throughput and utilization. The perfmon tool might indicate an issue, > > > but I doubt it will tell much. I like the statspack suggestion if > > > done accurately and snap are scoped correctly. > > > > Do you have virus software running? This will typically bump up I/O > > > reads in perfmon.- Hide quoted text - > > > > - Show quoted text - > > > Thanks for all of the information and suggestions. > > > I did some comparison of Windows tools (Perfmon) and EMC tools > > (Navisphere analyzer). Perfmon was actually reporting the IO > > correctly. Average IO reads are 2000, Average Write IO is @ 10. > > There are some interesting metrics that I don't understand from Nav. > > Analyzer: The storage processors are showing peaks close to 100% at > > certain times of day, but the disks are not at 100%. I'm not sure > > what that means. LUN % utilization is the same, so it looks like the > > Storage processors are responsible for the high % utilization. > > > STATSPACK analysis shows something similar to Perfmon and Nav. > > Analyzer: Physical reads are 2552, physical writes are 52. There's a > > ton of information in the statspack report, so I'm not sure what else > > to look at. > > > There is anti-virus software on this machine, but I don't think it > > accounts for a significant part of the 2000+ IOPs per second. We use > > the same product on all of our servers. IO is not a problem on any > > other server. On this machine, there are peaks of 10,000 IOPs. This > > seems abnormally high. > > > We're a very small shop, so I'm the storage person. This is a new > > technology for us, so I'm in a learning curve here. > > > There don't appear to be many good SAN related groups that are active, > > so I'm hoping someone here has experience with SANs and ORACLE to help > > out. > > > EMC support is NOT what we were expecting. For the amount we pay for > > support, the response is very very disappointing. I've had a ticket > > open on this for MONTHS. Literally. > > > I guess the bottom line here is that I need to prove to my boss that > > this is a disk bottleneck and that giving the database more spindles > > will help. Also, I'd like to understand why IO reads are so high for > > Oracle. > > > Any information would be appreciated.- Hide quoted text - > > > - Show quoted text - > > On top of bdbafh's suggestions, looks like you should get Cary > Milsap's book and follow it through to determine the exact set of > problems. > > Besides that, you might want to post init.ora parameters that are not > default, 9 databases (assuming you are using the Oracle definition of > "database," it's different than the one many MS-oriented folk use - do > the terms ORACLE_HOME and ORACLE_SID mean anything to you?) could be a > major part of the problem. As Paul mentioned, the cache buffers may > be set too low, causing many reads to load them, where if there were > more, the buffers would already be loaded. If you just had one db > rather than nine, you could easily increase the buffer size. But they > are additive across databases. There is an Oracle product called OEM > (I've forgotten where it is, perhaps on a supplemental cd) that has > some decent visualizations of what is happening, and what will likely > happen with certain changes - but only per db. The data comes from > system tables named something like %ADVISOR%, which you can learn > about in the Performance manual, learning much about what's going on > while you are at it. > > Besides that, as you will no doubt discover as you work through these > things, most performance problems come from the application code. > > It's only after all that you can prove that more spindles will help. > Of course, proof to a boss may not need to be so rigorous, and many > people have no problem saying more spindles=better performance. It's > just a bit embarrassing after spending all that money to discover the > bottleneck was elsewhere, or some simple code change or adding an > index could have fixed 90% of the problem. The statspack may tell you > that, or it may mask the problem, depending... but you won't know by > not running it. There should be some "top" information in there you > may want to post. It's possible that you would be doing more writing > if you weren't doing so much reading, the wait info may be > informative. Some people in this group are willing to go through an > entire statspack if you post it. > > I'm guessing storage processor peaking while disks are not may > indicate loads satisfied by storage cache, if they were satisfied by > Oracle's cache instead you may see some improvement. Google about for > papers about reducing logical I/O, too. > > jg > -- > @home.com is bogus. > "There is no try in production." - bdbafh- Hide quoted text - > > - Show quoted text -Received on Thu Jun 07 2007 - 18:39:12 CDT