Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Performance -- Possible Disk Bottleneck

Re: Oracle Performance -- Possible Disk Bottleneck

From: <beth.stover_at_gmail.com>
Date: Thu, 07 Jun 2007 23:39:12 -0000
Message-ID: <1181259552.918564.300180@q66g2000hsg.googlegroups.com>


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 be
high.
SQL> select namespace library,
  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

  9 from stats$lib;

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 the
time
SQL> Rem bstat was run and the time estat was run. Note that the estat
SQL> Rem script logs on to the instance so the per_logon statistics will
SQL> Rem always be based on at least one logon. SQL> select 'Users connected at ',to_char(start_time, 'dd-mon-yy hh24:mi:ss'),':',start_users from stats$dates;

'USERSCONNECTEDAT' TO_CHAR(START_TIME '
START_USERS

------------------- ------------------ -


Users connected at 18-apr-07 15:00:02 : 99

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

------------------- ------------------ -


Users connected at 18-apr-07 15:30:00 : 106

SQL> select 'avg # of connections: ',((start_users+end_users)/2) from stats$dates;

'AVG#OFCONNECTIONS:' ((START_USER




avg # of connections:
102.5

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,

'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
Per Second
--------------------------- ------------ ------------ ------------
------------
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 c 122136830 251828.52 1191578.83 67929.27
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 looking
at
SQL> Rem the Total Time you can often determine what is the bottleneck SQL> Rem that processes are waiting for. This shows the total time spent
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      Avg
Time
-------------------------------- ------------- -------------
-------------
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)
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$bck_event n1
  6  	where n1.event_count > 0
  7  	order by n1.time_waited desc;

Event Name                               Count    Total Time      Avg
Time
-------------------------------- ------------- -------------
-------------
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 large
value 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      SLEEPS
SLEEPS/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 does
not
SQL> Rem wait for the latch to become free, it immediately times out. SQL> select name latch_name,
  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_MISSES
NOWAIT_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               23
1

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

TIME
------------------ ----------------
----------------
data block                    41901
0
segment header                    2
0
1st level bmb                     1
0
undo header                       1
0
2nd level bmb                     1

0
SQL>
SQL>
SQL> set lines 159;
SQL> set numwidth 19;
SQL> Rem Waits_for_trans_tbl high implies you should add rollback
segments.
SQL> select * from stats$roll;
       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
compatible
9.2.0.0.0
control_files                           G:\ORACLE\ORADATA\PROD
\CONTROL01.CTL, G
core_dump_dest                          D:\oracle\admin\PROD
\cdump
db_block_size
8192
db_cache_size
209715200
db_domain
NORCALMUTUAL.COM
db_file_multiblock_read_count
8
db_files
254
db_name
PROD
db_writer_processes
4
dispatchers                             (PROTOCOL=TCP)
(SERVICE=PRODXDB)
dml_locks
500
enqueue_resources
129000
hash_join_enabled
FALSE
ifile                                   D:\oracle\admin\PROD\pfile
\init.ora
instance_name
PROD
job_queue_processes
10
large_pool_size
100663296
log_archive_dest                        F:\ORACLE\oradata\PROD
\arch
log_archive_start
TRUE
log_buffer
12288000
log_checkpoint_interval
10000
max_dump_file_size
UNLIMITED
nls_date_format                         DD-MON-
YYYY
open_cursors
310
optimizer_index_caching
90
optimizer_index_cost_adj
20
optimizer_mode
RULE
pga_aggregate_target
50000000
processes
200
query_rewrite_enabled
TRUE
query_rewrite_integrity
ENFORCED
remote_login_passwordfile
EXCLUSIVE
resource_limit
TRUE
sga_max_size
1665379432
shared_pool_size
209715200
sort_area_size
5120000
undo_management
AUTO
undo_retention
10800
undo_tablespace
RBS
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 the
requests.
SQL> Rem cur_usage is the number of entries in the cache that are being used.
SQL> select * from stats$dc
  2 where get_reqs != 0 or scan_reqs != 0 or mod_reqs != 0;

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
  8 from stats$files
  9 group by table_space
 10 order by table_space;

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 difference
between
SQL> Rem phys_reads and phys_blks_rd implies table scans are going on. SQL> select table_space, file_name,
  2 phys_reads reads, phys_blks_rd blks_read, phys_rd_time read_time,
  3 phys_writes writes, phys_blks_wr blks_wrt, phys_wrt_tim write_time,
  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"
  7 from stats$files order by table_space, file_name;

TABLE_SPACE

FILE_NAME                                             READS
BLKS_READ  READ_TIME     WRITES   BLKS_WRT WRITE_TIME  MEGABYTES
AVG_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_time
  3 from stats$dates;

START_TIME
END_TIME




18-apr-07 15:00:02 18-apr-07
15:30:00
SQL>
SQL> column banner format a75 trunc;
SQL> Rem Versions
SQL> select * from v$version;

BANNER



Oracle9i Release 9.2.0.7.0 -
Production
PL/SQL Release 9.2.0.7.0 -
Production
CORE 9.2.0.7.0
Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 -
Production
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US