Oracle调优实战.doc

Oracle调优实战1 文档结构主要从性能调优的各个方面阐述分析思路，文中如果出现蓝色带下划线的部分，请点击，可以查看详细的操作步骤。阅读文档的时候建议打开文档结构图，便于快速获得自己所需要的内容2 必备工具工欲善其事，必先利其器2.1 SQLDeveloper2.2 XManager系列工具3 了解一下服务器大致运行的情况3.1 CPU和内存情况cat /proc/cpuinfofree3.2 Top如果4-5个Oracle进程老是居高不下，说明语句或者Oracle内存配置还是有很大问题3.3 Vmstat观察一段时间内，是否线程堵塞，SWAP是否在使用、CPU和Memory使用情况如何3.4 确认是否以SMP方式运行#cat /proc/cpuinfo 可查看系统CPU状况#uname a可看系统的运行方式3.5 确认操作系统的共享内存大小最好将Oracle的使用内存都扔到一个共享内存段中，便于提高性能；以下内容主要还是针对Linux，AIX好像是自己管理共享内存，无法设置查看方法：more /proc/sys/kernel/shmmax 查看共享内存大小，默认为32M修改方法：vi /etc/sysctl.conf然后加入fs.file-max = 65536kernel.shmmax = 2147483648需要将Oracle的SGA内存都放在一个共享内存段使用sysctl -p命令生效3.6 IO故障分析确认文件打开数目，ulimit n 建议调到65535在IO故障分析还是缺少经验，希望有实战的同事补充4 从TOPSQL开始入手Oracle一般按照以前提供的文档进行设置，基本上不会出现太多问题，问题很多方面与垃圾数据、SQL语句不合理、索引创建得不太合适有关。4.1 获取TOPSQL按照后面相关知识点中的获取TOPSQL章节描述的方法，根据看到的情况，获取系统的前1020条TOPSQL4.2 逐条分析这些SQL，找到具体的原因位于TOPSQL主要的可能性原因主要可能有以下几个方面：1) 几个大表进行关联查询（比如WCMChnlDoc和WCMDocument）2) 某些表数据太大，过多冗余3) 索引建立得不合理，存在Table Full Scan5 了解WCM的一些信息5.1 检查一些表的冗余数据是否过多需要检查的表：1. WCMLog(日志)2. 消息相关的表：WCMMessage、WCMMsgQueue、WCMMSGRECEIVER3. 如果安装了Blog：XWCMCLIENTUSER4. 如果使用了IDS：IDSLOG表可能也会很大检查、删除和备份的方法：1. Select count(*) from ;2. 如果超过1W，建议执行truncate table ;3. 如果感觉表对于用户还有用，可以先备份a) SQLServer：select * into from ;b) Oracle：create table as select * from ;5.2 确定是单表还是多表查询如果是WCM52，默认都是多表；Alter table WCMChnlDoc nologging;Update WCMChnlDoc set DocTitle=(select WCMDocument.DocTitle from WCMDocument where WCMDocument.DocId=WCMChnlDoc.DocId);5.3 栏目检索条件、模板中是否指定了多表检查垃圾数据是否多，如果超过1W，建议truncateselect count(*) from WCMLog;select count(*) from WCMMessage; -WCM6需要关注select count(*) from wcmmsgqueue; -WCM6需要关注select count(*) from wcmpublishtask;检查WCMChnlDoc上的索引（RecId是否有主键、）6 了解Oracle基本配置6.1 获取配置文件1) 登录服务器，切换到Oracle用户2) 相关知识点：Oracle的SID一般定义在：$ORACLE_SID变量中配置文件一般配置在$ORACLE_HOME/dbs中3) cd $ORACLE_HOME/dbs，配置文件一般配置在cd $ORACLE_HOME/dbs中4) 获取Oracle配置文件，查看各项配置，获取方法见Oracle配置文件的获取和修改方法6.2 确认是否为共享模式(shared mode)ps -ef |grep s000如果有进程，说明是Shared Mode，需要修改配置文件将如下一行注释掉#*.dispatchers=(PROTOCOL=TCP) (SERVICE=mossXDB)关于共享模式的相关文档：/post/681/2141886.3 查看内存配置情况，看是否与我发的文档一致系统运行中可以直接通过alter system set XXX=XX scope=SPFile修改参数内存设置根据操作系统不一样会有些差别，32位操作系统最大只能设置到4G，更多内存只能在64位操作系统才能支持查看是否是32位还是64位的方法：file /sbin/init6.4 了解几张关键表的分析时间几张关键的表：WCMDocument、WCMChnldoc、WCMMetaTableXXX如果没有分析需要执行分析。方法见：了解Oracle指定表的分析时间和分析方法7 相关知识点7.1 获取TOPSQL方法1. 打开SQL Developer2. 点击ReportsData Dictionary ReportsDatabase AdministrationTOP SQL，可以按照多种方式统计TOPSQLStep1：Step2：7.2 Oracle配置文件的获取和修改方法Oracle配置文件现在一般采用SPFile维护，但是他是二进制文件，需要转换成PFile1) 确认系统是PFile还是SPFile：Select isspecified,count(*) from v$spparameter group by isspecified;True表示为spfile2) 从SPfile转换成Pfile文件，便于修改：oracledatabase db_1$ sqlplus /as sysdba SQL create pfile from spfile;3) 将$ORACLE_HOME/dbs/init.ora下载下来4) 修改配置文件，在服务器上备份以前的文件，上传文件，将spfile.ora移走5) 重新启动Oracle，让新的配置生效：（RAC的启动方式可能有很大不同）sqlplus /as sysdba shutdown immediate 如果没有带参数的停止时间可能会过长，千万要注意startupexit7.3 了解Oracle指定表的分析时间和分析方法了解分析的最后时间：对指定表进行分析7.4 操作系统共享内存的相关知识shmmax内核参数定义单个共享内存段的最大值，如果该参数设置小于Oracle SGA设置，那么SGA就会被分配多个共享内存段。这在繁忙的系统中可能成为性能负担，带来系统问题。Linux上该参数的缺省值通常为32M。rootneirong root# more /proc/sys/kernel/shmmax33554432可以通过ipcs命令查看此设置下共享内存的分配，我们可以看到Oracle分配了多个共享内存段以满足SGA设置的需要:rootneirong root# ipcs -sa- Shared Memory Segments -key shmid owner perms bytes nattch status 0x00000000 884736 oracle 640 4194304 14 0x00000000 917505 oracle 640 33554432 14 0x00000000 950274 oracle 640 33554432 14 0x00000000 983043 oracle 640 33554432 14 0x00000000 1015812 oracle 640 33554432 14 0x00000000 1048581 oracle 640 33554432 14 0x00000000 1081350 oracle 640 33554432 14 0x00000000 1114119 oracle 640 33554432 14 0x00000000 1146888 oracle 640 33554432 14 0x00000000 1179657 oracle 640 33554432 14 0x00000000 1212426 oracle 640 33554432 14 0x00000000 1245195 oracle 640 33554432 14 0x00000000 1277964 oracle 640 33554432 14 0x00000000 1310733 oracle 640 33554432 14 0x00000000 1343502 oracle 640 33554432 14 0x00000000 1376271 oracle 640 33554432 14 0x00000000 1409040 oracle 640 33554432 14 0x00000000 1441809 oracle 640 33554432 14 0x00000000 1474578 oracle 640 33554432 14 0x00000000 1507347 oracle 640 33554432 14 0x00000000 1540116 oracle 640 33554432 14 0x00000000 1572885 oracle 640 33554432 14 0x00000000 1605654 oracle 640 33554432 14 0x00000000 1638423 oracle 640 33554432 14 0x00000000 1671192 oracle 640 33554432 14 0x00000000 1703961 oracle 640 33554432 14 0x7a9c9900 1736730 oracle 640 4194304 56 - Semaphore Arrays -key semid owner perms nsems 0xfcf02e10 229376 oracle 640 154 - Message Queues -key msqid owner perms used-bytes messages 使用pmap我们可以看到每个共享内存段的地址空间rootneirong root# ps -ef|grep 3102oracle 3102 1 0 09:27 ? 00:00:26 ora_dbw0_hsmktroot 3447 3397 0 10:39 pts/4 00:00:00 grep 3102rootneirong root# pmap 3102ora_dbw0_hsmkt310208048000 (37308 KB) r-xp (68:06 1525072) /opt/oracle/product/9.2.0/bin/oracle0a4b7000 (8804 KB) rw-p (68:06 1525072) /opt/oracle/product/9.2.0/bin/oracle0ad50000 (380 KB) rw-p (00:00 0) 50000000 (4096 KB) rw-s (00:04 884736) /SYSV0000000051000000 (32768 KB) rw-s (00:04 917505) /SYSV0000000053000000 (32768 KB) rw-s (00:04 950274) /SYSV0000000055000000 (32768 KB) rw-s (00:04 983043) /SYSV0000000057000000 (32768 KB) rw-s (00:04 1015812) /SYSV0000000059000000 (32768 KB) rw-s (00:04 1048581) /SYSV000000005b000000 (32768 KB) rw-s (00:04 1081350) /SYSV000000005d000000 (32768 KB) rw-s (00:04 1114119) /SYSV000000005f000000 (32768 KB) rw-s (00:04 1146888) /SYSV0000000061000000 (32768 KB) rw-s (00:04 1179657) /SYSV0000000063000000 (32768 KB) rw-s (00:04 1212426) /SYSV0000000065000000 (32768 KB) rw-s (00:04 1245195) /SYSV0000000067000000 (32768 KB) rw-s (00:04 1277964) /SYSV0000000069000000 (32768 KB) rw-s (00:04 1310733) /SYSV000000006b000000 (32768 KB) rw-s (00:04 1343502) /SYSV000000006d000000 (32768 KB) rw-s (00:04 1376271) /SYSV000000006f000000 (32768 KB) rw-s (00:04 1409040) /SYSV0000000071000000 (32768 KB) rw-s (00:04 1441809) /SYSV0000000073000000 (32768 KB) rw-s (00:04 1474578) /SYSV0000000075000000 (32768 KB) rw-s (00:04 1507347) /SYSV0000000077000000 (32768 KB) rw-s (00:04 1540116) /SYSV0000000079000000 (32768 KB) rw-s (00:04 1572885) /SYSV000000007b000000 (32768 KB) rw-s (00:04 1605654) /SYSV000000007d000000 (32768 KB) rw-s (00:04 1638423) /SYSV000000007f000000 (32768 KB) rw-s (00:04 1671192) /SYSV0000000081000000 (32768 KB) rw-s (00:04 1703961) /SYSV0000000083000000 (4 KB) r-s (00:04 1736730) /SYSV7a9c990083001000 (644 KB) rw-s (00:04 1736730) /SYSV7a9c9900830a2000 (4 KB) r-s (00:04 1736730) /SYSV7a9c9900830a3000 (3444 KB) rw-s (00:04 1736730) /SYSV7a9c9900b6ec2000 (44 KB) r-xp (68:03 32811) /lib/libnss_files-2.3.2.sob6ecd000 (4 KB) rw-p (68:03 32811) /lib/libnss_files-2.3.2.sob6ece000 (512 KB) rw-p (68:03 40360) /dev/zerob6f4e000 (1140 KB) rw-p (00:00 0) b706b000 (1224 KB) r-xp (68:03 114692) /lib/tls/libc-2.3.2.sob719d000 (12 KB) rw-p (68:03 114692) /lib/tls/libc-2.3.2.sob71a0000 (12 KB) rw-p (00:00 0) b71a3000 (72 KB) r-xp (68:03 32795) /lib/libnsl-2.3.2.sob71b5000 (4 KB) rw-p (68:03 32795) /lib/libnsl-2.3.2.sob71b6000 (8 KB) rw-p (00:00 0) b71b8000 (52 KB) r-xp (68:03 114696) /lib/tls/libpthread-0.60.sob71c5000 (4 KB) rw-p (68:03 114696) /lib/tls/libpthread-0.60.sob71c6000 (8 KB) rw-p (00:00 0) b71c8000 (132 KB) r-xp (68:03 114694) /lib/tls/libm-2.3.2.sob71e9000 (4 KB) rw-p (68:03 114694) /lib/tls/libm-2.3.2.sob71ea000 (8 KB) r-xp (68:03 32791) /lib/libdl-2.3.2.sob71ec000 (4 KB) rw-p (68:03 32791) /lib/libdl-2.3.2.sob71ed000 (2940 KB) r-xp (68:06 491677) /opt/oracle/product/9.2.0/lib/libjox9.sob74cc000 (1088 KB) rw-p (68:06 491677) /opt/oracle/product/9.2.0/lib/libjox9.sob75dc000 (12 KB) rw-p (00:00 0) b75df000 (4 KB) r-xp (68:06 491685) /opt/oracle/product/9.2.0/lib/libskgxn9.sob75e0000 (8 KB) rw-p (68:06 491685) /opt/oracle/product/9.2.0/lib/libskgxn9.sob75e2000 (4 KB) r-xp (68:06 491567) /opt/oracle/product/9.2.0/lib/libskgxp9.sob75e3000 (4 KB) -p (68:06 491567) /opt/oracle/product/9.2.0/lib/libskgxp9.sob75e4000 (4 KB) rw-p (68:06 491567) /opt/oracle/product/9.2.0/lib/libskgxp9.sob75e5000 (4 KB) r-xp (68:06 491688) /opt/oracle/product/9.2.0/lib/libodmd9.sob75e6000 (4 KB) rw-p (68:06 491688) /opt/oracle/product/9.2.0/lib/libodmd9.sob75e7000 (4 KB) r-xp (68:03 101245) /etc/libcwait.sob75e8000 (4 KB) rw-p (68:03 101245) /etc/libcwait.sob75ea000 (4 KB) rw-p (00:00 0) b75eb000 (84 KB) r-xp (68:03 32778) /lib/ld-2.3.2.sob7600000 (4 KB) rw-p (68:03 32778) /lib/ld-2.3.2.sobfff8000 (32 KB) rwxp (00:00 0) mapped: 881332 KB writable/private: 12056 KB shared: 827392 KB为了避免多个共享内存段，我们可以修改shmmax内核参数，使SGA存在于一个共享内存段中。通过修改/proc/sys/kernel/shmmax参数可以达到此目的。rootneirong root# echo 1073741824 /proc/sys/kernel/shmmaxrootneirong root# more /proc/sys/kernel/shmmax1073741824这里设为1G。对于shmmax文件的修改，系统重新启动后会复位。可以通过修改 /etc/sysctl.conf 使更改永久化。在该文件内添加以下一行这个更改在系统重新启动后生效kernel.shmmax = 1073741824 重起数据库使更改生效:SQL shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL !oracleneirong oracle$ ipcs -sa- Shared Memory Segments -key shmid owner perms bytes nattch status - Semaphore Arrays -key semid owner perms nsems - Message Queues -key msqid owner perms used-bytes messages oracleneirong oracle$ exitexitSQL startupORACLE instance started.Total System Global Area 839980852 bytesFixed Size 452404 bytesVariable Size 201326592 bytesDatabase Buffers 637534208 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL ! ipcs -sa- Shared Memory Segments -key shmid owner perms bytes nattch status 0x7a9c9900 1769472 oracle 640 859832320 35 - Semaphore Arrays -key semid owner perms nsems 0xfcf02e10 360448 oracle 640 154 - Message Queues -key msqid owner perms used-bytes messages 此时进程的pmap映射显示为：oracleneirong bdump$ pmap 4178ora_lgwr_hsmkt417808048000 (37308 KB) r-xp (68:06 1525072) /opt/oracle/product/9.2.0/bin/oracle0a4b7000 (8804 KB) rw-p (68:06 1525072) /opt/oracle/product/9.2.0/bin/oracle0ad50000 (3320 KB) rw-p (00:00 0) 50000000 (835584 KB) rw-s (00:04 1835008) /SYSV7a9c990083000000 (4 KB) r-s (00:04 1835008) /SYSV7a9c990083001000 (644 KB) rw-s (00:04 1835008) /SYSV7a9c9900830a2000 (4 KB) r-s (00:04 1835008) /SYSV7a9c9900830a3000 (3444 KB) rw-s (00:04 1835008) /SYSV7a9c9900b6bb7000 (4112 KB) rw-p (00:00 0) b6fbb000 (44 KB) r-xp (68:03 32811) /lib/libnss_files-2.3.2.sob6fc6000 (4 KB) rw-p (68:03 32811) /lib/libnss_files-2.3.2.sob6fc7000 (512 KB) rw-p (68:03 40360) /dev/zerob7047000 (144 KB) rw-p (00:00 0) b706b000 (1224 KB) r-xp (68:03 114692) /lib/tls/libc-2.3.2.sob719d000 (12 KB) rw-p (68:03 114692) /lib/tls/libc-2.3.2.sob71a0000 (12 KB) rw-p (00:00 0) b71a3000 (72 KB) r-xp (68:03 32795) /lib/libnsl-2.3.2.sob71b5000 (4 KB) rw-p (68:03 32795) /lib/libnsl-2.3.2.sob71b6000 (8 KB) rw-p (00:00 0) b71b8000 (52 KB) r-xp (68:03 114696) /lib/tls/libpthread-0.60.sob71c5000 (4 KB) rw-p (68:03 114696) /lib/tls/libpthread-0.60.sob71c6000 (8 KB) rw-p (00:00 0) b71c8000 (132 KB) r-xp (68:03 114694) /lib/tls/libm-2.3.2.sob71e9000 (4 KB) rw-p (68:03 114694) /lib/tls/libm-2.3.2.sob71ea000 (8 KB) r-xp (68:03 32791) /lib/libdl-2.3.2.sob71ec000 (4 KB) rw-p (68:03 32791) /lib/libdl-2.3.2.sob71ed000 (2940 KB) r-xp (68:06 491677)