ORACLE数据库变得非常慢解决方案一例

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 **************************** AIX系统:

*********************************** #iostat 5 5

tty: tin tout avg-cpu: % user % sys % idle % iowait 0.0 2.5 16.4 2.8 69.7 11.1

Disks: % tm_act Kbps tps Kb_read Kb_wrtn hdisk0 20.9 141.9 33.0 1176852937 1037907344 hdisk1 20.4 136.6 31.3 1136554209 996356546 hdisk2 12.6 140.3 56.6 702195893 1487895240 hdisk3 10.6 249.1 39.5 1887071251 2001871356 cd0 0.0 0.0 0.0 0 0

tty: tin tout avg-cpu: % user % sys % idle % iowait 0.0 339.4 22.1 6.6 19.0 52.4

Disks: % tm_act Kbps tps Kb_read Kb_wrtn hdisk0 95.6 615.2 152.0 2112 964 hdisk1 96.0 612.0 150.8 2156 904 hdisk2 83.0 2470.4 226.4 11524 828 hdisk3 7.0 60.0 12.6 300 0 cd0 0.0 0.0 0.0 0 0

tty: tin tout avg-cpu: % user % sys % idle % iowait 0.0 460.4 31.3 7.7 13.1 47.9

Disks: % tm_act Kbps tps Kb_read Kb_wrtn hdisk0 95.4 648.0 158.4 1844 1396 hdisk1 98.4 679.2 163.4 1896 1500 hdisk2 89.6 2595.2 260.2 12088 888 hdisk3 5.8 44.8 10.2 212 12 cd0 0.0 0.0 0.0 0 0

tty: tin tout avg-cpu: % user % sys % idle % iowait 0.0 364.5 21.9 7.4 14.9 55.9

Disks: % tm_act Kbps tps Kb_read Kb_wrtn hdisk0 96.6 750.9 177.9 1152 2604

hdisk1 95.2 773.3 177.7 1176 2692 hdisk2 70.2 2002.5 205.1 9740 276 hdisk3 5.0 132.8 8.4 152 512 cd0 0.0 0.0 0.0 0 0

************ FZYC1/#sar 5 5

AIX FZYC1 3 4 000177DF4C00 06/06/06 10:15:40 %usr %sys %wio %idle 10:15:45 15 2 63 19 10:15:50 19 3 56 22 10:15:55 22 5 54 20 10:16:00 20 10 51 20 10:16:05 25 9 51 15 Average 20 6 55 19

****************** FZYC1/#ps -ef |pg

UID PID PPID C STIME TTY TIME CMD root 1 0 0 Dec 07 - 29:33 /etc/init

root 4158 1 0 Dec 07 - 0:00 /usr/lib/methods/ssa_daemon -l ssa0

root 4446 11096 0 Dec 07 - 182:03 /usr/dt/bin/dtsession root 5014 1 0 Dec 07 - 1577:46 /usr/sbin/syncd 10

root 5958 10322 0 Dec 07 - 24:57 /usr/lpp/X11/bin/X -x abx -x db e -x GLX -D /usr/lib/X11//rgb -T -force :0 -auth /var/dt/A:0-aWkfia root 10322 1 0 Dec 07 - 0:01 /usr/dt/bin/dtlogin -daemon root 10610 1 0 Dec 07 - 0:00 /usr/lib/errdemon root 10926 4446 0 Dec 07 - 0:00 /usr/dt/bin/dtterm root 11096 10322 0 Dec 07 - 0:00 dtlogin -daemon root 11364 17626 0 Dec 07 pts/0 0:00 /bin/ksh

root 11620 1 0 Dec 07 - 0:00 imqsmdem imqsrv.ini /etc/IMNSea rch/dbcshelp/ ...

我发现iowait太高了,怀疑是这个系统同步进程

root 5014 1 0 Dec 07 - 1577:46 /usr/sbin/syncd 10

照成的,aix系统不怎么熟,会不会是这个照成数据库变慢呢? 要不要喀嚓掉它? 我怀疑不是数据库的问题,因为应用没什么变化,定期维护也有做.

问题情况简要叙述:

客户报告数据库越来越慢,业务快无法进行了,近期并没有上什么新的应用和大的改变. 收集信息:

statspack 发现top5等待首位为 db scatter read

aix topas 和iostat 发现iowait 严重,超过50% busy保持90% 分析:

查询top sql,v$sesstat+v$statname发现awms 应用进程db session logical reads等磁盘读写严重awms应用为实时查询(很频繁),查询sql的执行路径为,主要sql为对一些基表进行全表扫描.基表数据量不大(5-8w)左右,理论上查询io并不应该太髙.进一步查发现,该表dml较多,照成表不断扩大,碎片增多,对该表的full scan代价越来越大.仅仅凭112m的大小并不会照成太严重的io,但是该表被用于实查询,并且由于是full scan,在查询完成后会被至到LRU列表的least used 端.因此照成频繁的读写.内存不够用,引起了过多的swap.(这里为推测的原因,并不能肯定是正确的) 解决:

把表重建(truncate掉重插数据,或者move表然后

重建索引),这些基表不大,并且频繁使用,由于这些特性,所以将其keep到buffer中-- alter table table_name storage (buffer_pool keep) 观察了些时间,数据性能恢复了.

http://bbs.51cto.com/archiver/tid-248860.html

小记：不明原因的解决了ORACLE慢的问题

近来发现ORACLE服务器超级慢，而且慢并不是由应用程序性能导致的，就连运行proc预编译程序都很慢，可见问题还是出在ORACLE服务器 本身。

首先查看了一下ORACLE的主要内存参数：

SELECT \/1024 AS \\

\ FROM v$parameter

WHERE NAME IN ('db_block_size', 'db_cache_size', 'java_pool_size', 'large_pool_size', 'pga_aggregate_target', 'shared_pool_size', 'sort_area_size')

除了大池(large_pool_size)为16MB以外，每项内存都配置得不小。 于是怀疑是共享池满造成的，在网上找了篇文章查了查共享池的占用情况： 《如何计算oracle共享池的大小》

http://blog.sina.com.cn/s/blog_3ebdf8ad010006bk.html

执行了一下，共享池竟然占用了113%。居然溢出了！！！搞不懂！

于是尝试着修改参数： 1.在linux下：su - oracle 2. sqlplus \

3. CREATE PFILE='ahfu.ora' FROM SPFILE; 4. 在LINUX下：cd /oracle/ora9/product/9.2/dbs/ 5. vi ahfu.ora 6. 修改参数如下：

db_cache_size (数据缓存) 256MB -> 512MB java_pool_size (java池） 84MB -> 0MB large_pool_size (大池) 16MB -> 64MB

query_rewrite_enabled (查询重写) FALSE -> TRUE 7. 保存，关闭数据库：shutdown immediate;

8. 从新的pfile启动：startup pfile='/oracle/ora9/product/9.2/dbs/ahfu.ora';

9. 重新连接数据库，发现连接和查询都变得很快了。反复重启数据库多次，仍然很快。

猜测一方面是因为共享池满导致了数据库慢，另一方面是大池设置得过小。但是重启后变快并不能说明真正解决了问题，要多运行一段时间

才知道。

==================================================== 附1：对共享池的分析

共享池缓存了系统中执行过的SQL。如果出现大量的不同的SQL，就可能导致共享池满。共享池满后，每次执行SQL都会扫描共享池，寻找相

同的SQL，找不到后又扫描整个共享池将长时间为执行的SQL设置为过期，为新的SQL分配空间。因此，哪怕是很简单的SQL，每次执行都会反复

扫描共享池，使得整个系统都很慢。共享池满的问题并不是立即发生的，需要一定的积累过程。所以数据库刚刚启动的时候，共享池是空的，

执行总是很快。数据库运行一段时间后，共享池满了，才会发现数据库很慢。大量不同的

SQL会导致共享池问题：

比如：select user_name from webuser.user_info where user_id=:UserID; 与select user_name from webuser.user_info where user_id=1

上面两条SQL语句，第一条使用参数绑定，在共享池中会多次被命中，性能很高；而第二条，参数采用常量，很难被命中，每次都要重新解

析，并且会占用共享池的空间。

附2：对查询重写的分析

查询重写功能是指数据库引擎强制将所有SQL中的参数修改成参数绑定的格式。 例如，数据库强制将select user_name from webuser.user_info where user_id=1这条SQL

强制修改成select user_name from webuser.user_info where user_id=:UserID 查询重写能够有效避免共享池满的问题，但是会增加SQL的解析时间。

http://blog.csdn.net/ah__fu/article/details/2336165

SQL会导致共享池问题：

比如：select user_name from webuser.user_info where user_id=:UserID; 与select user_name from webuser.user_info where user_id=1

上面两条SQL语句，第一条使用参数绑定，在共享池中会多次被命中，性能很高；而第二条，参数采用常量，很难被命中，每次都要重新解

析，并且会占用共享池的空间。

附2：对查询重写的分析

查询重写功能是指数据库引擎强制将所有SQL中的参数修改成参数绑定的格式。 例如，数据库强制将select user_name from webuser.user_info where user_id=1这条SQL

强制修改成select user_name from webuser.user_info where user_id=:UserID 查询重写能够有效避免共享池满的问题，但是会增加SQL的解析时间。

http://blog.csdn.net/ah__fu/article/details/2336165

本文来源：https://www.bwwdw.com/article/hb53.html