ORACLE数据库变得非常慢解决方案一例ORACLE数据库变得非常慢解决方案一例
ORACLE数据库变得非常慢解决方案一例
数据库变得非常慢[/b][/b]接到客户报告最近数据库速度慢了好多,同样的应用慢了4,5倍.我做了个statspack,没发现什么问题,主要信息:Cache Sizes (end)~~~~~~~~~~~~~~~~~Buffer Cache: 3,104M Std Block Size:8KShared Pool Size: 400M Log Buffer:3,072KLoad Profile~~~~~~~~~~~~ Per Second Per Transaction--------------- ---------------Redo size: 394,918.91 68,578.62Logical reads: 40,718.26 7,070.82Block changes: 3,516.66 610.68Physical reads: 1,673.37 290.59Physical writes: 63.58 11.04User calls: 122.83 21.33Parses: 22.04 3.83Hard parses: 2.23 0.39Sorts: 10.36 1.80Logons: 0.38 0.07Executes: 54.35 9.44Transactions: 5.76% Blocks changed per Read: 8.64 Recursive Call %: 46.61Rollback per transaction %: 0.00 Rows per Sort: 1601.81Instance Efficiency Percentages (Target 100%)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Buffer Nowait %: 99.99 Redo NoWait %: 100.00Buffer Hit %: 95.91 In-memory Sort %: 99.99Library Hit %: 98.08 Soft Parse %: 89.90Execute to Parse %: 59.45 Latch Hit %: 99.88Parse CPU to Parse Elapsd %: % Non-Parse CPU:Shared Pool Statistics Begin EndMemory Usage %: 86.59 86.84% SQL with executions>1: 88.19 88.59% Memory for SQL w/exec>1: 94.34 94.96Top 5 Timed Events~~~~~~~~~~~~~~~~~~ % TotalEvent Waits Time (s) Ela Time-------------------------------------------- ------------ ----------- --------db file scattered read 585,373 5,110 36.24latch free 38,146 3,331 23.63db file sequential read 328,881 2,096 14.87PX Deq: Txn Recovery Start 645 1,124 7.97db file parallel write 3,840 754 5.35****************************AIX系统:***********************************#iostat 5 5tty: tin tout avg-cpu: % user % sys % idle % iowait0.0 2.5 16.4 2.8 69.7 11.1Disks: % tm_act Kbps tps Kb_read Kb_wrtnhdisk0 20.9 141.9 33.0 1176852937 1037907344hdisk1 20.4 136.6 31.3 1136554209 996356546hdisk2 12.6 140.3 56.6 702195893 1487895240hdisk3 10.6 249.1 39.5 1887071251 2001871356cd0 0.0 0.0 0.0 0 0tty: tin tout avg-cpu: % user % sys % idle % iowait0.0 339.4 22.1 6.6 19.0 52