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 -> How to improve oracle IO performance ?

How to improve oracle IO performance ?

From: <jshen.cad_at_gmail.com>
Date: 25 Feb 2007 06:14:33 -0800
Message-ID: <1172412873.801137.114860@p10g2000cwp.googlegroups.com>


hi,

    Our oracle8.1.7 runs on HP-UX 11i with HP disk-array xp-1024. The disk array is raid-0.

   there are about 3 million accout mainteined in the DB which has accouting data input into the db. There are three snapshots on four tables of the DB. The refreshing interval is 5min. the four tables which are snapshoted mainteins the most part of information in DB.

   The max session number of DB is 600, and the peak connection number recorded is 571.

   we experience DB performance problem in past months. And, there happened three times of DB crash.

    I'm new to this area. Is there anyone would like to share knowledge and exprience with such problem ? ( e.g. where should I start ? what should be the key point of analysis? is there any mustread   on DB optimization? )

   any word will be highly appreciated.

  Joe

ps. the following is tracefile of DB crash , and disk io on DB server.

  1. DB crash trace file

/oracle/app/oracle/admin/pin65db/bdump/pmon_8454_pin65db.trc Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production ORACLE_HOME = /oracle/app/oracle/product/8.1.7 System name: HP-UX

Node name:      cm3-dcb2
Release:        B.11.11
Version:        U
Machine:        9000/800

Instance name: pin65db
Redo thread mounted by this instance: 1
Oracle process number: 2
Unix process pid: 8454, image: oracle_at_cm3-dcb2 (PMON)

[snip]

                                publishers/subscribers: 0/292,
                                messages published: 0
    ----------------------------------------
    SO: c0000001293f6100, type: 2, owner: c0000001291c6ec0, pt: 0, flag: INIT/-/-/0x00

    (call) sess: cur c000000129269870, rec 0, usr c000000129269870; depth: 0



CURRENT SESSION'S INSTANTIATION STATE

current session=c000000129269870
********************   Cursor Dump   ************************
Current cursor: 0, pgadep: 0
Cursor Dump:
End of cursor dump
END OF PROCESS STATE
********************   Cursor Dump   ************************
Current cursor: 0, pgadep: 0
Cursor Dump:
End of cursor dump
ssexhd: crashing the process...
Background_Core_Dump = partial

2. disk io

                   tty             cpu
                 tin tout        us  ni  sy  id
                   0 1326         6   0   3  91

  device    bps     sps    msps

  c0t6d0      7     2.4     1.0
  c2t6d0      9     2.4     1.0
 c22t2d2    1376    41.0     1.0
 c22t3d3    1187    90.0     1.0
 c22t3d4    881    75.1     1.0
 c22t3d5    893    83.1     1.0
 c22t3d6    975    81.1     1.0
 c22t3d7    1066    88.8     1.0
 c22t4d0    1041    88.4     1.0
 c22t4d1    961    76.7     1.0
 c22t4d2    896    78.1     1.0
 c27t3d0    894    84.3     1.0
 c27t3d1    968    72.9     1.0
 c27t3d2    1237    89.0     1.0
 c27t3d3    1276    80.9     1.0
 c27t3d4    1277    88.8     1.0
 c27t3d5    1450    90.4     1.0
 c27t3d6    1336    89.0     1.0
 c27t3d7    1237    84.5     1.0
 c19t3d3      0     0.0     1.0
 c19t3d4      0     0.0     1.0
 c19t3d5      0     0.0     1.0
 c19t3d6      0     0.0     1.0
 c19t3d7      0     0.0     1.0
 c19t4d0      0     0.0     1.0
 c19t4d1      0     0.0     1.0
 c19t4d2      0     0.0     1.0
 c21t3d0      0     0.0     1.0
 c21t3d1      0     0.0     1.0
 c21t3d2      0     0.0     1.0
 c21t3d3      0     0.0     1.0
 c21t3d4      0     0.0     1.0
 c21t3d5      0     0.0     1.0
 c21t3d6      0     0.0     1.0
 c21t3d7      0     0.0     1.0
 c22t4d3     63    15.9     1.0
 c22t4d4     60    15.1     1.0
 c22t4d5     77    19.1     1.0
 c22t4d6     53    13.1     1.0
 c22t4d7     53    12.9     1.0
 c22t5d0     45    11.2     1.0
 c22t5d1     58    14.3     1.0
 c22t5d2     50    12.4     1.0
 c27t4d0     62    15.3     1.0
 c27t4d1     47    11.6     1.0
 c27t4d2     59    14.7     1.0
 c27t4d3     55    13.5     1.0
 c27t4d4     49    12.2     1.0
 c27t4d5     37     9.2     1.0
 c27t4d6     50    12.5     1.0
 c27t4d7     51    12.7     1.0
 c19t4d3      0     0.0     1.0
 c19t4d4      0     0.0     1.0
 c19t4d5      0     0.0     1.0
 c19t4d6      0     0.0     1.0
 c19t4d7      0     0.0     1.0
 c19t5d0      0     0.0     1.0
 c19t5d1      0     0.0     1.0
 c19t5d2      0     0.0     1.0
 c21t4d0      0     0.0     1.0
 c21t4d1      0     0.0     1.0
 c21t4d2      0     0.0     1.0
 c21t4d3      0     0.0     1.0
 c21t4d4      0     0.0     1.0
 c21t4d5      0     0.0     1.0
 c21t4d6      0     0.0     1.0
 c21t4d7      0     0.0     1.0
 c22t5d3    434    108.8     1.0
 c22t5d4    400    100.2     1.0
 c22t5d5    430    106.8     1.0
 c22t5d6    410    101.8     1.0
 c22t5d7    415    103.2     1.0
 c22t6d0    422    105.4     1.0
 c22t6d1    434    108.8     1.0
 c22t6d2    400    100.0     1.0
 c27t5d0    436    108.8     1.0
 c27t5d1    419    104.8     1.0
 c27t5d2    438    109.8     1.0
 c27t5d3    420    105.0     1.0
 c27t5d4    426    107.4     1.0
 c27t5d5    403    101.4     1.0
 c27t5d6    436    109.6     1.0
 c27t5d7    418    104.4     1.0
 c19t5d3      0     0.0     1.0
 c19t5d4      0     0.0     1.0
 c19t5d5      0     0.0     1.0
 c19t5d6      0     0.0     1.0
 c19t5d7      0     0.0     1.0
 c19t6d0      0     0.0     1.0
 c19t6d1      0     0.0     1.0
 c19t6d2      0     0.0     1.0
 c21t5d0      0     0.0     1.0
 c21t5d1      0     0.0     1.0
 c21t5d2      0     0.0     1.0
 c21t5d3      0     0.0     1.0
 c21t5d4      0     0.0     1.0
 c21t5d5      0     0.0     1.0
 c21t5d6      0     0.0     1.0
 c21t5d7      0     0.0     1.0
 c22t6d3    541    134.3     1.0
 c22t6d4    524    131.1     1.0
 c27t6d0    540    136.7     1.0
 c27t6d1    518    128.9     1.0
 c31t3d3    521    130.1     1.0
 c31t3d4    545    136.1     1.0
 c31t3d5    538    134.7     1.0
 c31t3d6    549    137.1     1.0
 c31t5d3    521    131.7     1.0
 c19t6d3      0     0.0     1.0
 c19t6d4      0     0.0     1.0
 c21t6d0      0     0.0     1.0
 c21t6d1      0     0.0     1.0
 c26t3d3      0     0.0     1.0
 c26t3d4      0     0.0     1.0
 c26t3d5      0     0.0     1.0
 c26t3d6      0     0.0     1.0
 c26t5d3      0     0.0     1.0
 c22t7d5    5398    236.1     1.0
 c19t7d5      0     0.0     1.0
 c22t7d3    301    75.7     1.0
 c27t7d0    252    62.4     1.0
 c19t7d3      0     0.0     1.0
 c21t7d0      0     0.0     1.0
 c22t2d3    958    28.7     1.0
 c27t2d2    918    27.7     1.0
 c27t2d3    960    67.7     1.0
 c27t2d4    933    40.8     1.0
 c27t2d5    918    25.9     1.0
 c27t2d6    918    27.9     1.0
 c27t2d7    1235    36.5     1.0
 c19t2d2      0     0.0     1.0
 c19t2d3      0     0.0     1.0
 c21t2d2      0     0.0     1.0
 c21t2d3      0     0.0     1.0
 c21t2d4      0     0.0     1.0
 c21t2d5      0     0.0     1.0
 c21t2d6      0     0.0     1.0
 c21t2d7      0     0.0     1.0
 c22t0d0      0     0.0     1.0
 c22t0d1      0     0.0     1.0
 c22t0d2      0     0.0     1.0
 c22t0d3      0     0.0     1.0
 c22t0d4      0     0.0     1.0
 c22t0d5      0     0.0     1.0
 c22t0d6      0     0.0     1.0
 c22t1d0      0     0.0     1.0
 c22t1d1      0     0.0     1.0
 c22t1d2      0     0.0     1.0
 c22t1d3      0     0.0     1.0
 c22t1d7      0     0.0     1.0
 c27t0d0      0     0.0     1.0
 c27t0d1      0     0.0     1.0
 c27t0d2      0     0.0     1.0
 c27t0d3      0     0.0     1.0
 c27t0d4      0     0.0     1.0
 c27t0d5      0     0.0     1.0
 c27t0d6      0     0.0     1.0
 c27t1d0      0     0.0     1.0
 c27t1d1      0     0.0     1.0
 c27t1d2      0     0.0     1.0
 c27t1d3      0     0.0     1.0
 c27t1d7      0     0.0     1.0
 c19t0d0      0     0.0     1.0
 c19t0d1      0     0.0     1.0
 c19t0d2      0     0.0     1.0
 c19t0d3      0     0.0     1.0
 c19t0d4      0     0.0     1.0
 c19t0d5      0     0.0     1.0
 c19t0d6      0     0.0     1.0
 c19t1d0      0     0.0     1.0
 c19t1d1      0     0.0     1.0
 c19t1d2      0     0.0     1.0
 c19t1d3      0     0.0     1.0
 c19t1d7      0     0.0     1.0
 c21t0d0      0     0.0     1.0
 c21t0d1      0     0.0     1.0
 c21t0d2      0     0.0     1.0
 c21t0d3      0     0.0     1.0
 c21t0d4      0     0.0     1.0
 c21t0d5      0     0.0     1.0
 c21t0d6      0     0.0     1.0
 c21t1d0      0     0.0     1.0
 c21t1d1      0     0.0     1.0
 c21t1d2      0     0.0     1.0
 c21t1d3      0     0.0     1.0
 c21t1d7      0     0.0     1.0



3. sar result

22:07:00    %usr    %sys    %wio   %idle
22:07:05       6       3      76      16
22:07:10       6       2      77      15
22:07:15       5       2      70      23
22:07:20       5       2      62      31
22:07:25       6       2      74      17
22:07:30       6       2      74      18
22:07:35       5       3      75      17
22:07:40       5       3      72      20
22:07:45       6       2      70      22
22:07:50       5       2      65      28
22:07:55       5       2      68      24
22:08:00       6       3      69      22
22:08:05       6       3      75      17
22:08:10       5       2      65      28
22:08:15       6       3      71      20
22:08:20       6       2      74      19
22:08:25       6       2      77      15
22:08:30       5       3      71      21
22:08:35       6       3      75      16
22:08:40       5       2      69      24
22:08:45       5       2      75      18
22:08:50       6       3      72      20
22:08:55       5       2      71      21

although 'sar' report above, the info shown on diskarray hardware monitor (xp-1024's control panel) show less than 15% io wait. Received on Sun Feb 25 2007 - 08:14:33 CST

Original text of this message

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