ORACLE 数据库变得非常慢解决方案一例 数据库变得非常慢[/b][/b] 接到客户报告最近数据库速度慢了好多,同样的应用慢了 4,5 倍. 我做了个 statspack,没发现什么问题,主要信息: Cache Sizes (end) ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ Buffer Cache: 3,104M Std Block Size: 8K Shared Pool Size: 400M Log Buffer: 3,072K Load Profile ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ Per Second Per Transaction --------------- --------------- Redo size: 394,918.91 68,578.62 Logical reads: 40,718.26 7,070.82 Block changes: 3,516.66 610.68 Physical reads: 1,673.37 290.59 Physical writes: 63.58 11.04 User calls: 122.83 21.33 Parses: 22.04 3.83 Hard parses: 2.23 0.39 Sorts: 10.36 1.80 Logons: 0.38 0.07 Executes: 54.35 9.44 Transactions: 5.76 % Blocks changed per Read: 8.64 Recursive Call %: 46.61 Rollback per transaction %: 0.00 Rows per Sort: 1601.81 Instance Efficiency Percentages (Target 100%) ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ Buffer Nowait %: 99.99 Redo NoWait %: 100.00 Buffer Hit %: 95.91 In-memory Sort %: 99.99 Library Hit %: 98.08 Soft Parse %: 89.90 Execute to Parse %: 59.45 Latch Hit %: 99.88 Parse CPU to Parse Elapsd %: % Non-Parse CPU: Shared Pool Statistics Begin End Memory Usage %: 86.59 86.84 % SQL with executions>1: 88.19 88.59 % Memory for SQL w/exec>1: 94.34 94.96 Top 5 Timed Events ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- db file scattered read 585,373 5,110 36.24 latch free 38,146 3,331 23.63 db file sequential read 328,881 2,096 14.87 PX Deq: Txn Recovery Start 645 1,124 7.97 db file parallel write 3,840 754 5.35 **********************...
