oracle 数据库变慢怎么分析

1、1、调整数据结构的设计。这一部分在开发信息系统之前完成，程序员需要考虑是否使用ORACLE数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。

2、2、调整应用程序结构设计。这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。

3、3、调整数据库SQL语句。应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了ORACLE数据库的性能。ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。

4、4、调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。需要注意的是，SGA区不是越大越好，SGA区过大会占用 *** 作系统使用的内存而引起虚拟内存的页面交换，这样反而会降低系统。

5、5、调整硬盘I/O，这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。

6、6、调整 *** 作系统参数，例如：运行在UNIX *** 作系统上的ORACLE数据库，可以调整UNIX数据缓冲池的大小，每个进程所能使用的内存大小等参数。

实际上，上述数据库优化措施之间是相互联系的。ORACLE数据库性能恶化表现基本上都是用户响应时间比较长，需要用户长时间的等待。但性能恶化的原因却是多种多样的，有时是多个因素共同造成了性能恶化的结果，这就需要数据库管理员有比较全面的计算机知识，能够敏感地察觉到影响数据库性能的主要原因所在。另外，良好的数据库管理工具对于优化数据库性能也是很重要的。

ORACLE数据库性能优化工具

常用的数据库性能优化工具有：

1、1、ORACLE数据库在线数据字典，ORACLE在线数据字典能够反映出ORACLE动态运行情况，对于调整数据库性能是很有帮助的。

2、2、 *** 作系统工具，例如UNIX *** 作系统的vmstat，iostat等命令可以查看到系统系统级内存和硬盘I/O的使用情况，这些工具对于管理员弄清出系统瓶颈出现在什么地方有时候很有用。

3、3、SQL语言跟踪工具（SQL TRACE FACILITY），SQL语言跟踪工具可以记录SQL语句的执行情况，管理员可以使用虚拟表来调整实例，使用SQL语句跟踪文件调整应用程序性能。SQL语言跟踪工具将结果输出成一个 *** 作系统的文件，管理员可以使用TKPROF工具查看这些文件。

4、4、ORACLE Enterprise Manager（OEM），这是一个图形的用户管理界面，用户可以使用它方便地进行数据库管理而不必记住复杂的ORACLE数据库管理的命令。

5、5、EXPLAIN PLAN——SQL语言优化命令，使用这个命令可以帮助程序员写出高效的SQL语言。

ORACLE数据库的系统性能评估

信息系统的类型不同，需要关注的数据库参数也是不同的。数据库管理员需要根据自己的信息系统的类型着重考虑不同的数据库参数。

1、1、在线事务处理信息系统（OLTP），这种类型的信息系统一般需要有大量的Insert、Update *** 作，典型的系统包括民航机票发售系统、银行储蓄系统等。OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度，这类系统使用的ORACLE数据库需要主要考虑下述参数：

l     l     数据库回滚段是否足够？

l     l     是否需要建立ORACLE数据库索引、聚集、散列？

l     l     系统全局区（SGA）大小是否足够？

l     l     SQL语句是否高效？

2、2、数据仓库系统（Data Warehousing），这种信息系统的主要任务是从ORACLE的海量数据中进行查询，得到数据之间的某些规律。数据库管理员需要为这种类型的ORACLE数据库着重考虑下述参数：

l     l     是否采用B*-索引或者bitmap索引？

l     l     是否采用并行SQL查询以提高查询效率？

l     l     是否采用PL/SQL函数编写存储过程？

l     l     有必要的话，需要建立并行数据库提高数据库的查询效率

SQL语句的调整原则

SQL语言是一种灵活的语言，相同的功能可以使用不同的语句来实现，但是语句的执行效率是很不相同的。程序员可以使用EXPLAIN PLAN语句来比较各种实现方案，并选出最优的实现方案。总得来讲，程序员写SQL语句需要满足考虑如下规则：

1、1、尽量使用索引。试比较下面两条SQL语句：

语句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN

(SELECT deptno FROM emp)

语句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS

(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno)

这两条查询语句实现的结果是相同的，但是执行语句A的时候，ORACLE会对整个emp表进行扫描，没有使用建立在emp表上的deptno索引，执行语句B的时候，由于在子查询中使用了联合查询，ORACLE只是对emp表进行的部分数据扫描，并利用了deptno列的索引，所以语句B的效率要比语句A的效率高一些。

2、2、选择联合查询的联合次序。考虑下面的例子：

SELECT stuff FROM taba a, tabb b, tabc c

WHERE a.acol between :alow and :ahigh

AND b.bcol between :blow and :bhigh

AND c.ccol between :clow and :chigh

AND a.key1 = b.key1

AMD a.key2 = c.key2

这个SQL例子中，程序员首先需要选择要查询的主表，因为主表要进行整个表数据的扫描，所以主表应该数据量最小，所以例子中表A的acol列的范围应该比表B和表C相应列的范围小。

3、3、在子查询中慎重使用IN或者NOT IN语句，使用where (NOT) exists的效果要好的多。

4、4、慎重使用视图的联合查询，尤其是比较复杂的视图之间的联合查询。一般对视图的查询最好都分解为对数据表的直接查询效果要好一些。

5、5、可以在参数文件中设置SHARED_POOL_RESERVED_SIZE参数，这个参数在SGA共享池中保留一个连续的内存空间，连续的内存空间有益于存放大的SQL程序包。

6、6、ORACLE公司提供的DBMS_SHARED_POOL程序可以帮助程序员将某些经常使用的存储过程“钉”在SQL区中而不被换出内存，程序员对于经常使用并且占用内存很多的存储过程“钉”到内存中有利于提高最终用户的响应时间。

CPU参数的调整

CPU是服务器的一项重要资源，服务器良好的工作状态是在工作高峰时CPU的使用率在90％以上。如果空闲时间CPU使用率就在90％以上，说明服务器缺乏CPU资源，如果工作高峰时CPU使用率仍然很低，说明服务器CPU资源还比较富余。

使用 *** 作相同命令可以看到CPU的使用情况，一般UNIX *** 作系统的服务器，可以使用sar –u命令查看CPU的使用率，NT *** 作系统的服务器，可以使用NT的性能管理器来查看CPU的使用率。

数据库管理员可以通过查看v$sysstat数据字典中“CPU used by this session”统计项得知ORACLE数据库使用的CPU时间，查看“OS User level CPU time”统计项得知 *** 作系统用户态下的CPU时间，查看“OS System call CPU time”统计项得知 *** 作系统系统态下的CPU时间， *** 作系统总的CPU时间就是用户态和系统态时间之和，如果ORACLE数据库使用的CPU时间占 *** 作系统总的CPU时间90％以上，说明服务器CPU基本上被ORACLE数据库使用着，这是合理，反之，说明服务器CPU被其它程序占用过多，ORACLE数据库无法得到更多的CPU时间。

数据库管理员还可以通过查看v$sesstat数据字典来获得当前连接ORACLE数据库各个会话占用的CPU时间，从而得知什么会话耗用服务器CPU比较多。

出现CPU资源不足的情况是很多的：SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足。

1、数据库管理员可以执行下述语句来查看SQL语句的解析情况：

SELECT * FROM V$SYSSTAT

WHERE NAME IN

('parse time cpu', 'parse time elapsed', 'parse count (hard)')

这里parse time cpu是系统服务时间，parse time elapsed是响应时间，用户等待时间

waite time = parse time elapsed – parse time cpu

由此可以得到用户SQL语句平均解析等待时间＝waite time / parse count。这个平均等待时间应该接近于0，如果平均解析等待时间过长，数据库管理员可以通过下述语句

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA

ORDER BY PARSE_CALLS

来发现是什么SQL语句解析效率比较低。程序员可以优化这些语句，或者增加ORACLE参数SESSION_CACHED_CURSORS的值。

2、数据库管理员还可以通过下述语句：

SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA

查看低效率的SQL语句，优化这些语句也有助于提高CPU的利用率。

3、3、数据库管理员可以通过v$system_event数据字典中的“latch free”统计项查看ORACLE数据库的冲突情况，如果没有冲突的话，latch free查询出来没有结果。如果冲突太大的话，数据库管理员可以降低spin_count参数值，来消除高的CPU使用率。

内存参数的调整

内存参数的调整主要是指ORACLE数据库的系统全局区（SGA）的调整。SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。

1、  1、   共享池由两部分构成：共享SQL区和数据字典缓冲区，共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。数据库管理员通过执行下述语句：

select (sum(pins - reloads)) / sum(pins) "Lib Cache"  from v$librarycache

来查看共享SQL区的使用率。这个使用率应该在90％以上，否则需要增加共享池的大小。数据库管理员还可以执行下述语句：

select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache

查看数据字典缓冲区的使用率，这个使用率也应该在90％以上，否则需要增加共享池的大小。

2、  2、   数据缓冲区。数据库管理员可以通过下述语句：

SELECT name, value  FROM v$sysstat  WHERE name IN ('db block gets', 'consistent gets','physical reads')

来查看数据库数据缓冲区的使用情况。查询出来的结果可以计算出来数据缓冲区的使用命中率＝1 - ( physical reads / (db block gets + consistent gets) )。

这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。

3、  3、   日志缓冲区。数据库管理员可以通过执行下述语句：

select name,value from v$sysstat where name in ('redo entries','redo log space requests')查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败率：

申请失败率＝requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区。

数据库慢一般有三种情况

逐渐变慢

突然变慢

不定时变慢

第一种情况 逐渐变慢 要建立一个长期的监控机制 比如 写个shell脚本每天的忙时(通常 ~ etc )定时收集os neork db的信息 每个星期出report对收集到的信息进行分析 这些数据的积累 可以决定后期的优化决策 并且可以是DBA说服manager采用自己决策的重要数据 DBA的价值 就在每个星期的report中体现

第二种情况 突然变慢 也是最容易解决的 先从业务的角度看是DB的使用跟以前有何不同 然后做进一步判断 硬件/网络故障通常也会引起DB性能的突然下降

第一步: 察看DB/OS/NEORK的系统log 排除硬件/网络问题

第二步 察看数据库的等待事件 根据等待事件来判断可能出问题的环节 如果 没有等待事件 可以排除数据库的问题 如果有等待时间 根据不同的等待事件 来找引起这些事件的根源

比如latch free等跟SQL parse有关系的等待事件 OS的表现是CPU 的占用率高

db file scattered read等跟SQL disk read有关系的等待时间 OS的表现是iostat可以看到磁盘读写量增加

第三步: 察看os的信息 CPU/IO/MEMORY等

a Cpu 的占用率

CPU占用率与数据库性能不成反比 CPU占用率高 不能说明数据库性能慢 通常情况 一个优化很好 而且业务量确实很大的数据库 CPU的占用率都会高 而且会平均分布在每个进程上 反过来 CPU的占用率都会高也不代表数据库性能就好 要结合数据库的等待事件来判断CPU占用率高是否合理

如果某个进程的cpu占用高 肯定是这个进程有问题 如果 不是oracle的进程 可以让application察看是否程序有死循环等漏洞 如果 是oracle的进程 可以根据pid查找oracle数据字典看看这个进程的发起程序 正在执行的sql语句 以及等待事件 然后 不同情况使用不同的方法来解决

b IO

排除硬件的IO问题 数据库突然变慢 一般来说 都是一个或几个SQL语句引起的

如果IO很频繁 可以通过优化disk reads高的TOP SQL来解决 当然这也是解决IO问题的最笨也是最有效的办法

OS以及存储的配置也是影响IO的一个重要的原因

比如 最常见的HP unix下异步IO的问题 如果DBA GROUP没有MLOCK的权限 ORACLE是不使用AIO的 偏偏OS与DB的两方的admin如果配合不够好地话 这个配置就很容易给漏掉了

c Memory

第二种情况与memory的关系比较小 只要SGA区配置合理没有变化 一般来说 只要不是Application Memory leak 不会引起突然变慢的现象

第三种情况 不定时变慢 是最难解决的 现场出现的问题原因也是五花八门千奇百怪 最重要的是 出现慢的现象时 以最快的速度抓取到最多的信息以供分析 先写好抓取数据的shell 脚本 并在现象发生时及时按下回车键

一个例子

数据库突然变慢

背景: 一个新应用上线后 数据库突然变慢

第一步 调查新应用

据开发人员讲新应用访问的都是新建立的表 表的数据量很小 没有复杂的SQL查询

查询 v$sqlarea 分别按照disk_reads / buffer_gets / executions 排序 TOP SQL 中没有新应用的SQL 排除新应用数据库访问照成的性能问题

第二步 察看数据库log/ OS log

数据库log中可以看到大量的ORA 错误 以及大量的dump文件 分析dump文件(时间久了 没有dump文件可参考 具体细节没法描述下来 ) 发现是新应用通过dblink访问remote DB时生成的dump文件 应用开发人说没法修改 Oracle也没有相应的patch解决

OS log中没有错误信息

第三步 察看statspack report

从wait events中看到 Top event是 buffer busy waits db file parallel write 等于IO相关的等待事件

从buffer busy waits 的统计信息来看 是等待data block

还有些physical reads等信息与从前比没有太多的异常

Tablespace 的IO reads/writes也没有异常 但是wait明显增加

初步确定是IO问题

第四步 察看OS的信息

top 命令(输出为实验室数据 仅作格式参考)

load averages: : :

processes: sleeping zombie stopped on cpu

CPU states: % idle % user % kernel % iowait % swap

Memory: M real M free M swap in use M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU MAND

a K K cpu/ : % top

mpgj M K sleep : % view_server

当时现场数据显示 iowait 值与以前相比大很多 没有异常进程

sar –d (输出为实验室数据 仅作格式参考)

SunOS sc Generic_ sun u / /

: : device %busy avque r+w/s blks/s avwait avserv

sd

sd a

sd b

sd c

sd g

当时现场数据显示 放数据文件的设备 avwait avque blks/s值偏大

第五步 察看数据库的等待事件

一个大业务量的数据库如果性能不好的话 一般来说都会有大量的等待事件 上百个等待事件很常见 我通常会按照EVENT进行group

Select count(*) event from v$session_wait where event not in ( *** on timer pmon timer rdbms ipc message SQL*Net message from client ) group by event order by desc

输出结果显示最多的等待事件是buffer busy waits

进一步分析 找出等待的原因

Select count(*) p p p from v$session_wait where event = buffer busy waits group by p p p

在buffer busy waits等待事件中

P = file#

P = block#

P = id ( 此id对应为等待的原因)

按照p p p group是为了明确buffer busy waits的等待集中在哪些对象上

Metalink对buffer busy waits等待事件的描述有如下一段话

If P shows that the buffer busy wait is waiting for a block read to plete then the blocking session is likely to be waiting on an IO wait (eg: db file sequential read or db file scattered read for the same file# and block#

输出结果显示 等待分布在多个不同的对象上 等待原因为 waiting for a block read to plete 进一步分析为IO的问题

如果 buffer busy waits等待集中在某个对象上 说明有hot block 通过重新rebuild这个对象增加freelist来解决 RAC环境增加freelist group

通过以下SQL可以找到具体的object

Select owner segment_name segment_type from dba_extents where file_id=P and P beeen block_id and block_id+blocks

P P 是上面v$session_wait查出的具体的值

第六步 明确原因 找出解决步骤

分析

磁盘的IO流量增加

磁盘的IO等待增加

DB的IO流量没有增加

DB的IO等待增加

由 可以推出 有数据库以外的IO访问磁盘

察看磁盘配置 该VG只存放了数据库数据文件和数据库系统文件 排除数据文件 产生IO的是数据库系统文件

数据库系统文件一般来说不会产生IO 有IO读写的地方只有log和dump文件

结论 ora 产生的大量core dump文件堵塞IO

解决办法

消除ora (应用不改的情况下 无法解决)

把dump目录指向别的VG

让oracle尽量少的去写core dump文件

background_core_dump = partial

lishixinzhi/Article/program/Oracle/201311/18969

这个 速度慢 是指 使用数据库过程中 查询数据缓慢 还是 数据库运行过程中各项功能缓慢？ 如果是查询数据缓慢的话，基本上是因为 与 查询语句逻辑、表结构、执行计划相关，可以根据数据库具体执行计划优化数据库查询，比如 要查询的表没有对应的索引，可以添加索引提高查询速度；或者 要查询的表有过大量的删除与更新，则需要更新表的统计信息以提高查询速度。如果是数据库本身各项功能运行缓慢，那大多是数据库系统各项参数设置不合理，与服务器本身的系统的硬件和系统有关，可以更改数据库相关的系统参数，调整数据库运行所需要的参数以提高数据库运行质量。

---