进了新公司，老板要求把E-Bussiness Suite11.5.7升级到11.5.10.2。复杂度不是很大，以前做过类似的，从11.5.9到11.5.10.2。

其中的一步，就是把database从9.0.1.4升级到9.2.0.8。准备把Application升级到11.5.10.2后再升级Tech Stack到10g。

但是呢，发现数据库 startup migrate竟然超过10个小时。我就知道有问题，但是我忍了，想升级完以后再检查需要优化的参数。但是utlrp.sql竟然也运行了超过20个小时。。。。

忍不住，就打开另外一个session，看看utlrp.sql的那个session到底在做什么。

SQL> SELECT a.event, a.total_waits,
2 a.total_timeouts, a.time_waited, a.average_wait
3 FROM v$session_event a, v$session b
4 WHERE a.SID = b.SID
5 AND A.SID= 9
6 AND a.event NOT IN
7 ('pmon timer',
8 'rdbms ipc message',
9 'i/o slave wait',
10 'wait for unread message on broadcast channel',
11 'wait for unread message on multiple broadcast channels',
12 'class slave wait',
13 'KSV master wait',
14 'watchdog main loop',
15 'DIAG idle wait',
16 'ges remote message',
17 'gcs remote message',
18 'SGA: MMAN sleep for component shrink',
19 'LNS ASYNC archive log',
20 'LNS ASYNC dest activation',
21 'LNS ASYNC end of log',
22 'PX Deq: Par Recov Reply',
23 'PX Deq: Par Recov Execute',
24 'PX Deq: Par Recov Change Vector',
25 'LogMiner: slave waiting for activate message',
26 'LogMiner: wakeup event for builder',
27 'LogMiner: wakeup event for preparer',
28 'LogMiner: wakeup event for reader',
29 'LogMiner: client waiting for transaction',
30 'parallel recovery coordinator waits for cleanup of slaves',
31 'smon timer',
'PX Deq: Txn Recovery Start',
32 33 'PX Deq: Txn Recovery Reply',
34 'PX Deq: Index Merge Reply',
35 'PX Deq: Index Merge Execute',
36 'PX Deq: Index Merge Close',
37 'PX Deq: kdcph_mai',
38 'PX Deq: kdcphc_ack',
39 'virtual circuit status',
40 'dispatcher timer',
41 'jobq slave wait',
42 'pipe get',
43 'PX Deque wait',
44 'PX Idle Wait',
45 'PX Deq: Join ACK',
46 'PX Deq Credit: need buffer',
47 'PX Deq: Msg Fragment',
'PX Deq: Parse Reply',
48 'PX Deq: Execute Reply',
49 'PX Deq: Execution Msg',
50 51 'PX Deq: Table Q Normal',
'PX Deq: Table Q Sample',
52 'Streams fetch slave: waiting for txns',
53 54 'single-task message',
55 'SQL*Net message from client',
56 'SQL*Net message from dblink',
'PL/SQL lock timer',
57 'EMON idle wait',
58 'Streams AQ: waiting for messages in the queue',
59 60 'Streams AQ: waiting for time management or cleanup tasks',
61 'Streams AQ: delete acknowledged messages',
62 'Streams AQ: deallocate messages from Streams Pool',
63 'Streams AQ: qmn coordinator idle wait',
64 'Streams AQ: qmn slave idle wait',
65 'Streams AQ: RAC qmn coordinator idle wait',
66 'HS message to agent',
67 'ASM background timer',
68 'JS external job'
)
69 70 and time_waited > 0
71 ORDER BY username, time_waited desc
72
SQL> /

EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
-------------------- ----------- -------------- ----------- ------------
log buffer space 40525 31864 3549906 88
db file sequential r 633317 0 3163564 5
ead

log file switch comp 779 533 64085 82
letion

log file sync 157 143 14635 93
db file scattered re 806 0 3855 5
ad

EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
-------------------- ----------- -------------- ----------- ------------
rdbms ipc reply 32 15 3531 110
local write wait 39 32 3332 85
direct path write 395 0 2609 7
enqueue 3 2 716 239
direct path read 473 0 418 1
latch free 431 95 318 1
buffer busy waits 20 1 145 7
control file sequent 34 0 39 1
ial read

library cache lock 4 0 28 7

14 rows selected.

晕，我很等log_buffer_space花了3549906/100=35499秒，大概是10个小时！看来，应该是log_buffer太小了/或者log写入disk太慢。

确认下：

SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_log_buffers_corrupt boolean FALSE
_log_buffers_debug boolean FALSE
log_buffer integer 1048576
SQL>

1M，不算小，那就可能是硬盘写的慢。

iostat -xnp，发现asvc_t>200, wsvc_t>60，看来，写的慢才是真的慢。

没有办法，测试环境。把log_buffer改成10M，发现情况有好转。