请教大虾:ORACLE服务性能问题,I/O busy高,WAIT多，如何解决

请教大虾:ORACLE服务性能问题,I/O busy高,WAIT多，如何解决
环境：aix5304+oracle92, 6CPU+12内存，ORACLE内存6G
TOPAS:
  wait 23.9
  Idle 43.6DISK     BUSY%
hdisk0    100.2
hdisk1    100.2MEMORY   
Real,MB  11455
Comp   95.5
Noncomp  3.3
client  3.3PAGING   SPACE
Used     82
Free     18STACKPACK如下：
STATSPACK report forDB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
PROD            11733427 PROD                1 9.2.0.6.0   NO      jxdb1              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:      1231 04-Sep-09 08:14:46      755 #########
  End Snap:      1250 04-Sep-09 11:28:55      673 #########
   Elapsed:              194.15 (mins)Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:     7,168M      Std Block Size:          8K
           Shared Pool Size:     1,232M          Log Buffer:     10,240KLoad Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             36,731.82              3,868.20
              Logical reads:             42,082.32              4,431.66
              Block changes:                190.06                 20.02
             Physical reads:                172.13                 18.13
            Physical writes:                 13.22                  1.39
                 User calls:                768.06                 80.88
                     Parses:                219.61                 23.13
                Hard parses:                  3.70                  0.39
                      Sorts:                132.63                 13.97
                     Logons:                  0.36                  0.04
                   Executes:              1,032.45                108.73
               Transactions:                  9.50  % Blocks changed per Read:    0.45    Recursive Call %:     66.53
 Rollback per transaction %:    3.14       Rows per Sort:     17.90Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:     99.97
            Buffer  Hit   %:   99.59    In-memory Sort %:    100.00
            Library Hit   %:   99.73        Soft Parse %:     98.32
         Execute to Parse %:   78.73         Latch Hit %:     99.90
Parse CPU to Parse Elapsd %:    3.04     % Non-Parse CPU:     93.15 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   80.43   67.50
    % SQL with executions>1:   60.08   59.10
  % Memory for SQL w/exec>1:   49.05   56.83Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free                                        633,919     149,312    89.09
CPU time                                                       13,207     7.88
enqueue                                             1,303       1,699     1.01
db file scattered read                            349,770       1,051      .63
log file sync                                     110,021         723      .43
          -------------------------------------------------------------
Wait Events for DB: PROD  Instance: PROD  Snaps: 1231 -1250
-> s  - second
-> cs - centisecond -     100th of a second
-> ms - millisecond -    1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
latch free                        633,919     20,243    149,312    236      5.7
enqueue                             1,303        344      1,699   1304      0.0
db file scattered read            349,770          0      1,051      3      3.2
log file sync                     110,021        369        723      7      1.0
db file sequential read           170,774          0        451      3      1.5
buffer busy waits                   1,068        228        294    276      0.0
log file switch completion            346        139        176    507      0.0
library cache pin                     119         39        151   1270      0.0
db file parallel write              5,224          0        144     28      0.0
process startup                       288        130        137    476      0.0
row cache lock                        132         34        120    908      0.0
LGWR wait for redo copy             6,455      3,200         34      5      0.1
log file sequential read              720          0         25     35      0.0
direct path read (lob)             12,708          0         15      1      0.1
control file parallel write         5,122          0         10      2      0.0
SQL*Net break/reset to clien       28,365          0          9      0      0.3
log file parallel write           118,696          0          8      0      1.1
async disk IO                         586          0          7     12      0.0
SQL*Net more data to client       173,346          0          3      0      1.6
library cache lock                      1          0          3   2681      0.0
control file sequential read        7,259          0          3      0      0.1
direct path read                    3,510          0          1      0      0.0
library cache load lock                 1          0          1    717      0.0
log file single write                 180          0          0      2      0.0
direct path write                   2,616          0          0      0      0.0
db file parallel read                   7          0          0     16      0.0
local write wait                       26          0          0      2      0.0
wait list latch free                    3          0          0     10      0.0
direct path write (lob)               688          0          0      0      0.0
db file single write                    1          0          0      1      0.0
buffer deadlock                       186        184          0      0      0.0
undo segment extension                 47         47          0      0      0.0
SQL*Net message from client     9,209,887          0  3,173,931    345     83.3
pipe get                            8,431      8,431     33,683   3995      0.1
queue messages                      2,296      2,296     22,402   9757      0.0
jobq slave wait                     3,604      3,519     10,452   2900      0.0
wakeup time manager                   364        364     10,071  27668      0.0
SQL*Net message to client       9,209,910          0         17      0     83.3
SQL*Net more data from clien       11,725          0          1      0      0.1
          -------------------------------------------------------------
Background Wait Events for DB: PROD  Instance: PROD  Snaps: 1231 -1250
-> ordered by wait time desc, waits desc (idle events last)                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write              5,223          0        144     28      0.0
latch free                            577         36        120    209      0.0
LGWR wait for redo copy             6,455      3,200         34      5      0.1
log file sequential read              720          0         25     35      0.0
buffer busy waits                      79         19         23    294      0.0
control file parallel write         5,115          0         10      2      0.0
log file parallel write           118,688          0          8      0      1.1
async disk IO                         585          0          7     12      0.0
db file sequential read               628          0          5      8      0.0
rdbms ipc reply                       135          0          3     23      0.0
control file sequential read        5,799          0          2      0      0.1
db file scattered read                112          0          2     14      0.0
direct path read                    3,510          0          1      0      0.0
log file single write                 180          0          0      2      0.0
direct path write                   2,610          0          0      0      0.0
rdbms ipc message                 128,194     15,580    163,535   1276      1.2
pmon timer                          4,272      3,903     11,324   2651      0.0
smon timer                            371         14     11,276  30394      0.0
          -------------------------------------------------------------

解决方案 »

  1.   

    第一，这个基本上要从磁盘着手了，把读写比较频繁的文件放在独立的物理磁盘上，比如log和data文件分开，data文件根据热度分开。
    如果有条件做0＋1，条块化，那是比较好的方式
    第二，你的系统绑定变量的工作做的不够，需要加强
      

  2.   

    另外，我估计你的系统是个OLTP系统，要处理的数据量很大，你的内存可能偏小了，把你的SGA和PGA的数据贴出来看看，说一下你的系统的大概要处理的数据量，说说用户规模，你的undo的retention时间等等系统的情况
      

  3.   


    谈谈我的看法
    提高share pool检查存储设备，做这个统计的时候，你的系统在做什么样的操作，transaction量不大，但是redo比较多。
      

  4.   

    Top 5 Timed Events 
    ~~~~~~~~~~~~~~~~~~                                                    % Total 
    Event                                              Waits    Time (s) Ela Time 
    -------------------------------------------- ------------ ----------- -------- 
    latch free                                        633,919    149,312    89.09 
    查看内存分配：
    Buffer Cache:    7,168M      Std Block Size:          8K 
              Shared Pool Size:    1,232M          Log Buffer:    10,240K 会不会是shared pool 小了点？
      

  5.   

    lz能不能传个完整的sp？引起这个现象的原因很多，热点块、磁盘故障、低效sql等等。如果不能，则：
    1.select * from v$session_wait where event='latch free';
    2.select * from v$latchname where latch#=x;
    根据具体latch位置再做进一步处理
      

  6.   

    今天还有不少朋友上来讨论，好开心哟。看来csdn真是个学习的好地方。
      

  7.   

    谢谢各个大虾，请问如何上传附近与图片?我想把整个STACKPACK上传请大家分析:
      

  8.   

    补充1点: 系统数据是放在存储上,储存做了RAID5
         现系统有个数据文件已达到32G,这个数据文件对应的表空间现扩充了数据文件,繁忙时访问的并发用户为300人,基本访问的相同的模块
      

  9.   

    把你的sp的结果，贴到空间里，要看看sp的内容来判断哟。
      

  10.   

    csdn很不方便,附件没办法上传.令人不爽啊.不过我建议楼主先看看你的top sql,
      

  11.   


    同意，可以传到其他空间里，比如QQ，google
      

  12.   

    谢谢，各位，今天的TOPAS
    Topas Monitor for host:    jxdb1                EVENTS/QUEUES    FILE/TTY
    Tue Sep 15 11:10:32 2009   Interval:  2         Cswitch    1185  Readch     7891
                                                    Syscall     819  Writech    3300
    Kernel    0.7   |#                           |  Reads        34  Rawin         0
    User      9.4   |###                         |  Writes       27  Ttyout     1164
    Wait     34.9   |##########                  |  Forks         0  Igets         0
    Idle     55.1   |################            |  Execs         0  Namei        16
                                                    Runqueue    0.5  Dirblk        0
    Network  KBPS   I-Pack  O-Pack   KB-In  KB-Out  Waitqueue   7.5
    en2       7.5     37.1    27.1     3.3     4.1
    en5       0.7      8.0     1.0     0.6     0.1  PAGING           MEMORY
    en0       0.0      0.0     0.0     0.0     0.0  Faults        0  Real,MB   11455
    lo0       0.0      0.0     0.0     0.0     0.0  Steals        0  % Comp     96.6
                                                    PgspIn        0  % Noncomp   3.3
    Disk    Busy%     KBPS     TPS KB-Read KB-Writ  PgspOut     296  % Client    3.3
    hdisk0  100.2   1189.5   190.1     2.0  1187.5  PageIn        0
    hdisk1  100.2   1187.5   193.1     0.0  1187.5  PageOut     296  PAGING SPACE
    dac0utm   0.0      0.0     0.0     0.0     0.0  Sios         90  Size,MB   10240
    dac1      0.0      0.0     0.0     0.0     0.0                   % Used     83.3
    dac0      0.0      0.0     0.0     0.0     0.0  NFS (calls/sec)  % Free     17.7
    dac1utm   0.0      0.0     0.0     0.0     0.0  ServerV2       0
    cd0       0.0      0.0     0.0     0.0     0.0  ClientV2       0   Press:
    hdisk2    0.0      0.0     0.0     0.0     0.0  ServerV3       0   "h" for help
    hdisk3    0.0      0.0     0.0     0.0     0.0  ClientV3       0   "q" to quitName            PID  CPU%  PgSp Owner
    topas        585870   0.2  11.3 root   
    oracle      2155504   0.2   6.6 oraprod
    oracle       250426   0.2   5.6 oraprod
    oracle      1348314   0.2   4.6 oraprod
    oracle       516642   0.2   7.1 oraprod
    oracle       876742   0.2   5.6 oraprod
    oracle       753992   0.2   5.7 oraprod
    oracle       987694   0.2   5.4 oraprod
    oracle       717016   0.2   5.6 oraprod
    VMO -a
      large_page_heap_size = 0
              lgpg_regions = 0
                 lgpg_size = 0
           low_ps_handling = 1
           lru_file_repage = 0
         lru_poll_interval = 10
                 lrubucket = 131072
                maxclient% = 20
                   maxfree = 1088
                   maxperm = 562665
                  maxperm% = 20
                    maxpin = 2368007
                   maxpin% = 80
           mbuf_heap_psize = 4096
           memory_affinity = 1
             memory_frames = 2932735
             memplace_data = 2
      memplace_mapped_file = 2
    memplace_shm_anonymous = 2
      

  13.   

    Top 5 Timed Events 
    ~~~~~~~~~~~~~~~~~~                                                    % Total 
    Event                                              Waits    Time (s) Ela Time 
    -------------------------------------------- ------------ ----------- -------- 
    latch free                                        633,919    149,312    89.09 
    CPU time                                                      13,207    7.88 
    enqueue                                            1,303      1,699    1.01 
    db file scattered read                            349,770      1,051      .63 
    log file sync                                    110,021        723      .43 
    谈谈个人观点：
    1、在繁忙时，latch 等待主要是在做什么操作？
    2、这几个top5都是对性能开销很大的东西，最好有完整的sp报告。
    3、日志文件切换太频繁，是否太小，或者分散磁盘。
    4、fts估计比较多，在第一点的基础上，对longops的语句可先分析优化。
    另外enqueue的东西不好说了，要看具体的业务和系统设计。
      

  14.   


    shared pool如果不够，是否碎片太多了。这个系统的配置，1232M已经不小了。
      

  15.   

    我用delph写了一个数据库转换的程序。从oracle 11g 的一个用户导入到另一个用户下。 一共有1000多张表，主要有10几张比较大的表，平均有30W条数据，刚开始导入的时候挺快的， 导入到几张表以后就非常的慢了。开始是cpu占用率80%以上，后来就低下来了只10%左右同时导入速度也极度慢了，找不到原因，请各位大虾们帮帮忙找找原因吧。 是个多线程的程序， oracle会话多时有40多个。等待时间很长。服务器配置绝对够用，cpu 8核2.0的，内存6G,
      

  16.   

    实际上把你的FTS解决就好了， 这个帖子几个月了。楼主也没个回音。