Linux操作系统下Oracle9i数据库SQL语句跟踪、发现、优化方法

Linux操作系统下Oracle9i数据库

SQL语句跟踪、发现、优化方法

1 前言

以前看了很多关于SQL语句的优化方法的次料，可就是没有看到过关于怎样发现程序中的问题SQL语句类的资料（可能是我没找到正地方）。

由于本人刚来到这家公司不长时间，所以对程序也不熟悉，业务也不熟悉，我的职位是数据库管理员，所以，好多以前遗留的问题都跑到我这来了，也可说只要用户反应系统漫的问题，都会跑到我这来（通过实践证明，确实应该跑到我这来，呵呵~~）。说实在，我是第一次做这么专业的程序优化（主要是写在程序里的语句优化）工作。

刚开始的时候我想用优化数据库参数方式来解决问题，可是参数（SGA）已经无法在优化了。后来还是从SQL语句上下手，通过一个非常笨的办法（上用户那调查明确具体漫的功能模块；查看程序源码找程序中相关的语句，采用一条一条的在PL/SQL中测试，来找到问题所在；分析优化办法，主要就是建索引，调整语句的书写格式）进行问题语句的查找，也解决了一些问题，就是操作起来太麻烦了，语句的性能评估也不专业，只是看执行时间来断定语句的性能。

有一次很好的机会和一个比较老道的DBA接触上了，哈哈~~不能浪费机会，多问几个问题，学到了不少知识。一是知道了在10G的EM中看顶级会话就可以知道那些语句执行的漫，太好了，以后不用在找程序了；二是知道在PL/SQL中把语句粘进去，按F5可以对语句性能进行分析（以前自己真按过这个键，但是不知道什么意思），能查看到用没用索引、是不是全表扫描、使用了多少系统资源（COST越小越好）；三是索引问题，因为我们有的表上有8个索引，而且大多都是复合索引，而且还有多个字段在不同的索引中，自己感觉这样也是不合理的，又和他确认了一下，还真就不合理；四是……其实，在这之前我刚刚学会用ORACLE自带的工具autotrace进行分析。

刚学会一个省事的捕获方法，难题又来了。呜呜呜~~ORACLE是9i的，没有EM，这可怎么办啊！

其实，这次不是去给优化的，而是排除服务器故障的，用户说以前用的很好，最近总是用两周左右服务器中的数据库就无法连接了，重启操作系统就好了，用一段时间就又不行了，应用一起来硬盘灯闪的也很频繁。

这我那处理过啊！到底是什么问题呢！不像是硬件故障啊！无意中top了一下，发现问题了，CPU使用率非常高（80%以上是常事），空闲内存只有3m多，有一些浮动但是很中，几乎就是总是这么少。感觉找到服务器故障原因了，是不是因为长时间高资源的使用导致的啊！仔细的看了看进程，发现有的ORACLE进程占用CPU为30%、40%、60%的，而且经常出现，在群里咨询了一下，可以确认这种情况是不正常的，在那位兄弟的指导下，又学到了一些知识（有东西学，可真爽啊！）。硬盘灯闪的那频繁可能是I/O太频繁了。

问题确认了，那就开始优化吧！光有进程也不行啊，得知道执行的是什么才能优化啊！还好以前看的资料多，记得是可以通过进程的PID找到SID，通过SID找到语句，网上一搜，果真有。哈哈~~

2 服务器环境

2.1 硬件配置

CPU：2.4

内存：2G

2.2 软件配置

操作系统：Red Hat Enterprise Linux AS release 4 (Nahant Update 2)

          Kernel 2.6.9-22.EL on an i686

    数据库： Oracle9i Enterprise Edition Release 9.2.0.4.0

3 相关工具

Telnet：telnet-server-0.17-31.EL4.3

Ftf：tftp-server-0.39-1

PL/SQL Developer：Version 7.0.2.1076 (MBCS)

4 操作步骤

4.1 远程登录服务器

由于服务器不在公网上，也没有现成的SSH工具可安装，所就使用服务器上已经安装好的Telnet了。

一、操作机：开始——》运行——》使用Telnet登录远程服务器

    二、输入用户名和密码，输入top系统命令

三、观查操作系统的性能指标

发现CPU使用率非常高，已经达到了92%，在进程列表中可以看到有一个进程占用了44%，记录一下这个进程的PID，在之后的SQL语句查找中我们可以用到。

4.2 查看与进程对应的会话

    在PL/SQL中使用下面语句，可以查到会话信息。记得要修改查询条件Spid中的值啊！

Select Sid,        Serial#,        Username,        Osuser,        Machine,        Program,        Process,        Logon_Time   From V$session  Where Paddr In (Select Addr                    From V$process                   Where Spid In ('28821'))

 

4.3 查找会话执行的语句

    在PL/SQL中新开一个窗口，把下面语句粘进去，把查询条件Sid的值改成与上面结果中的值相同。按一下F8，如果有查询结果，正明我们已经捕获到了那个执行比较漫的SQL语句。如果没有，那就一直按，按到有为只，如果用户频繁的使用的这个功能，你会很容易得到结果的。

Select Sql_Text   From V$sqltext a  Where a.Hash_Value =        (Select Sql_Hash_Value From V$session s Where s.Sid = '30')  Order By Piece Asc

4.4 分析语句

    在新开一个窗口，把上面捕获到的语句粘进来，自己把语句调整一下，直到他可以执行为止。在执行的时候，你会发现，这条语句执行的速度还真就漫（漫就对了，不漫就没问题了）。

语句可以执行了，我们在按F5，看一下这条件语句的性能分析结果，主要就是看看是不是全表扫描了、COST值是不是很高（只要是全表扫描，就一定高）。再往下我就不多说了，问题找到了，是修改语句的书写规则啊，还是建索引啊，那就自己分析吧！在网上有很多关与SQL语句优化方法的资料，自己查吧！

 

5 总结

是能跟踪到问题语句了，建索引的也好解决，在数据库里操作就行了，可是有是需要修改语句书写的，可是语句又在哪个功能里，得让程员修改啊，这都成了疑问。没有办法，还得个操作员那走一圈，把操作漫的功能模块详细的记录一下，回去在跟踪确认一下，确认后协调程员修改程序，更新程序，测试，给各用户更新，啊啊啊~~操作起来确实快了，呵呵~~没白忙乎。

经过我这么一折腾，服务器CPU的使用率明显示下降，基本就在10%以内，也有高的时候，但只是偶尔才出现。空闲内存也有索引变化，不总是3M、4M了，而是30M~3M之间来回徘徊。

终于可以向用户有个交代了，又是各操作员那跑一圈，让他们签个字，确认比较快了，问题解决了，签字也省事，咋地都行。光操作员们签完字不行，还得向用户领导汇报啊，说明服务器故障原因（说的比较含蓄，不会让用户感到是我们程序有问题，哈哈~~还好领导不技术），领导听了也非常高兴。我心里这个美啊！任务完成，可以回家了，嘻嘻~~

一周以后，电话与用户确认了一下服务器运行情况，一切正常。快两周的时候，还得问题一下，这样我心才有底。

（2010-5-10 早上8点左右，又接到用户电话，服务器又死了，还是数据库连接不上，重启服务器后就可以了，迷糊啊~~呵呵~~2010-5-12又到用户这来了）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14769796/viewspace-662006/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14769796/viewspace-662006/