当前位置:首页 >> 计算机软件及应用 >>

ORACLE数据库常见问题诊断方法(非OPS篇)-20021224-A2


ORACLE 数据库常见问题诊断方法(非 OPS 篇)
一、 ORACLE 数据库系统常见问题
1) 空间方面问题
? 现象:

随着数据库使用时间的增长, 数据库系统存储的数据就越多, 不断增长的数据可能导致空间 不足、目标的范围分配数太大、SQL 语句的性能下降等问题,因此应该经常检查空间使用 情况。

?
1.

解决思路
定期检查主要表空间的使用情况,执行表空间的剩余空间检查脚本(语句 4) ,剩余空 间应该保持在 20%以上,否则需要备份、增加数据文件或清理历史数据; 注意:空间的标志位。有时空间高位标志位远远大于实际使用的空间数量,此时应截断 高位标志位。 表或索引的 Extents 数(语句 1)应该小于 50,核心表数据表 Extents 数应该小于 100, 否则要参考集成案例来调整表的存储参数。由于索引对 EXTENT 比较敏感,所以,对 于有大量 UPDATE、INSERT 操作的索引,EXTENT 应更小。 空间碎片问题:对于 ICD 业务来讲,由于 DDL 操作较少,所以,表空间碎片问题基本 不存在,但表、索引的空间碎片问题普遍存在,例如 COMMONINFOMATION 表及其 索引。此时需要对表数据备份、TRUNCATE、导入操作,对索引需要 REBUILD。

2.

3.

2) 性能方面问题
? 现象:

座席端的调用慢甚至死机,应用服务器队列全忙、不断重连并最终断连。在数据库服务器上 的现象是 CPU 资源或者 IO 资源消耗很大。

?
1.

解决思路
ORACLE 需要设置的参数较多,尤其是关于 SGA 的尺寸的设置对性能影响较大,当出 现以上性能问题时,首先应该按照《ORACLE 数据库安装配置检查要求》逐项检查, 调整不合理的参数。 操作系统资源检查:包括 CPU、IO、内存、队列等,具体检查方法请参照小型机日常 维护检查手册。 在确认参数设置及系统资源没有问题后就需要查找应用本身的问题, 由于数据库中数据

2. 3.

的数量、分布的变化,可能导致 SQL 语句的性能变得较差,进而导致性能问题出现。 这种情况需要找出性能较差的语句并进行分析、 优化。 性能较差的语句一般分为下列几 种情况: 3.1. 物理读比较大,这种情况很常见,在操作系统命令行下执行 vmstat 2 10,可以看到 pi,po 项数值大于 0,表示内存中有换入换出,执行语句 12 查找物理读最多的前 20 条 语句, 并查看这些语句的查询计划 (PL/SQL DEVELOP 工具中将 SQL 语句拷贝到 SQL Window 中按 F5) ,分析是否可优化。 3.2. 逻辑读比较大,这种情况下一般 CPU 占用较高, 可以执行语句 13 查找逻辑读最多 的前 20 条语句,并查看这些语句的查询计划(方法同上) ,分析是否可优化。 如现场无法优化可将上述语句涉及的表、 存储过程的脚本发给优化部姚理或者相关人员 处理。 3.3. 没有合适的索引或没有用到合适索引,需要用上述方法分析 3.4. 应用设计存在缺陷,需要用上述方法分析

3) 锁争用方面问题
? 现象:

这种情况下的现象也是座席端的调用慢甚至死机, 应用服务器队列全忙、 不断重连并最终断 连。 但与上节中性能问题不同的是, 在数据库服务器上的现象是 CPU 资源或者 IO 资源消耗 并不大。

?

解决思路

当系统中有会话修改数据后长时间不提交事务或者修改数据的 SQL 语句本身运行时间就较 长时,该会话会保持所修改数据的琐,并阻塞其他试图修改这部分数据的会话,有时甚至会 话之间相互阻塞,导致死锁出现。 1. 可以运行语句 9 查找出所有的锁, 正常情况下应该没有锁或者锁的持续时间很短, 如果 锁的持续时间较长(大于 10 秒)则可能有问题。 2. 执行语句 8 查看持有该锁的会话 ID 的等待事件。 3. 执行语句 10 查看持有该锁的会话 ID 当前正在执行的 SQL 语句。 将以上执行语句的结果以及相关 SQL 语句涉及的表、存储过程的脚本发给优化部姚理 或者相关人员处理。如果某会话持有锁时间太长且阻塞其他会话,可以使用语句 14 杀 死该会话。

4) 内存方面问题
? 现象:

内存方面问题目前有两种:1、ORACLE 本身内存泄露;2、SGA 中剩余内存不足,生成大 量 TRACE 文件,严重占用 IO 资源。

?
1.

解决思路
为尽早发现内存泄露问题,应该定期检查小型机上剩余内存,周期为一周或者半个月,

2.

方法为使用 vmstat 命令查看 memory free 字段,单位为页(每页大小一般为 4K) ,如果 剩余内存持续减少则存在内存泄露,请报告优化部姚理或者相关人员处理。 执行语句 15 和语句 16 查看 SGA 的信息,应该只有 NAME 字段为 db_block_buffers 或 sql area 的记录内存字节数(BYTES 字段)较大,其他项的大小应该不到 10%,如果有 其他项占用异常的大请将上述语句执行结果报告优化部姚理或者相关人员处理。

二、 ORACLE 问题分析脚本
1. 查看当前用户下指定表已分配的空间以及扩展次数,例如查看表 SERVICEINFO

SELECT SUBSTRB(SEGMENT_NAME, 1, 30) "SEGMENT_NAME",

SUM(BYTES) / 1024 / 1024 "Alloc(MB)",
COUNT(*) "Extents" FROM USER_EXTENTS WHERE SEGMENT_NAME = 'SERVICEINFO' GROUP BY SEGMENT_NAME;

2.

查看当前用户下占用空间最多的对象

SELECT SUBSTRB(SEGMENT_NAME, 1, 30) "SEGMENT_NAME", SUM(Bytes) / 1024 / 1024 "Alloc(MB)", COUNT(*) "Extents" FROM USER_EXTENTS GROUP BY SEGMENT_NAME HAVING SUM(Bytes) / 1024 / 1024 >= 10 ORDER BY 2 DESC;

3.

查看当前用户下区间扩展最多的对象

SELECT SUBSTRB(SEGMENT_NAME, 1, 30) "SEGMENT_NAME", SUM(Bytes) / 1024 / 1024 "Alloc(MB)", COUNT(*) "Extents" FROM USER_EXTENTS GROUP BY SEGMENT_NAME HAVING COUNT(*) >= 10 ORDER BY 3 DESC;

4.

查看系统所有表空间的空间使用情况

SELECT D.TABLESPACE_NAME "Name", D.STATUS "Status", TO_CHAR((A.BYTES / 1024 / 1024), '99,990.9') "Size (M)", TO_CHAR(((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024),'99,990.9') "Used (M)", TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'99,990.9') "Free (M)" FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME;

5.

查看所有表空间的数据文件使用情况

SELECT d.file_name "Name", d.tablespace_name "Tablespace", v.status "Status", TO_CHAR((d.bytes / 1024 / 1024), '99999990.000') "Size (M)", NVL(TO_CHAR(((d.bytes - SUM(s.bytes)) / 1024 / 1024), '99999990.000'), TO_CHAR((d.bytes / 1024 / 1024), '99999990.000')) "Used (M)" FROM sys.dba_data_files d, sys.dba_free_space s, sys.v_$datafile v WHERE (s.file_id (+)= d.file_id) AND (d.file_name = v.name) GROUP BY d.file_name, d.tablespace_name, v.status, d.bytes;

6.

查看表空间碎片情况, 以下语句得到的结果中 FSFI 字段小于 30%的表空间为碎片比较 严重的表空间

SELECT TABLESPACE_NAME, SQRT(MAX(BLOCKS) / SUM(BLOCKS)) * (100 / SQRT(SQRT(COUNT(BLOCKS)))) FSFI FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY 1;

7.

查看所有会话的信息,包括会话 ID、用户名、状态、客户端机器名、运行的程序以及 登陆时间

SELECT sid,serial#,username,status,machine,terminal,program,logon_time FROM V$SESSION;

8.

查看所有会话的等待信息

SELECT sid,event,seconds_in_wait,state FROM v$session_wait;

9.

查看所有会话的锁

SELECT sid,TYPE,id1,id2,lmode,request,ctime,block FROM v$lock WHERE TYPE IN ('TM','TX')

一般情况下返回记录数应为 0,如果有记录应该检查等待时间(字段 ctime,单位秒) ,如果 等待时间较长则需要检查该会话是否被其他会话的锁阻塞,字段 request 不为 0 表示该会话 在等待其他会话释放锁而被阻塞,字段 request 为 0 表示该会话已获得锁,block 不为 0 表示 该会话阻塞了其他会话。

10. 查看指定会话正在执行的 SQL 语句
SELECT SQL_Text FROM V$SQLTEXT A, V$SESSION B WHERE A.Hash_Value = B.SQL_Hash_Value AND B.SID = 29 ORDER BY Piece;

11. 查看被锁住的对象
SELECT b.OWNER "Owner",b.OBJECT_NAME "Loked Object",a.SESSION_ID "Session

ID",a.ORACLE_USERNAME "Oracle User",a.OS_USER_NAME "OS User",a.PROCESS "OS Process ID",a.LOCKED_MODE "Locked Mode" FROM v$locked_object a,dba_objects b WHERE a.OBJECT_ID = b.OBJECT_ID

12. 查看 SQL 缓冲池中物理读最多的语句,一般平均读硬盘数大于 10000 则性能很差
SELECT SQL_Text, disk_reads/executions "Avg Disk_Reads", executions FROM V$SQLArea WHERE executions > 0 ORDER BY disk_reads/executions DESC;

13. 查看 SQL 缓冲池中逻辑读最多的语句,一般平均读内存数大于 10000 则性能很差
SELECT SQL_Text, buffer_gets/executions "Avg Disk_Reads", executions FROM V$SQLArea t WHERE executions > 0 ORDER BY buffer_gets/executions DESC;

14. 杀死指定的会话
ALTER SYSTEM KILL SESSION '<SID>, <SERIAL#>';

其中字段 SID 和 SERIAL#的值可以从 V$SESSION 中得到(见上一条语句) 。
注意: 使用杀死会话语句前先运行以下语句得到要杀死语句的操作系统进程 ID, 因为有可能会话已经僵死, 杀会话的语句无法杀死该会话, 只能将其状态标记为’KILLED’, 这时需要在操作系统的命令行下使用 KILL 或 KILL -9 根据得到的操作系统进程 ID 来杀死操作系统进程。 SELECT B.spid FROM V$SESSION A, V$PROCESS B WHERE A.PAddr = B.Addr AND A.SID = <SID>;

15. 查看 SGA 大小
SELECT * FROM v$sga

16. 查看 SGA 详细信息, 注意: 应该只有 NAME 字段为 db_block_buffers 或 sql area 的记 录内存字节数(BYTES 字段)较大,其他项应该比较小,此外 free memory 应该大于 20M
SELECT * FROM v$sgastat

17. 查看定时任务
SELECT job,log_user,last_date,this_date,next_date,total_time,broken,INTERVAL,what FROM DBA_JOBS

18. 查看正在运行的定时任务
SELECT * FROM dba_jobs_running


相关文章:
ORACLE数据库常见问题诊断方法(非OPS篇)-20021224-A2.doc
ORACLE数据库常见问题诊断方法(非OPS篇)-20021224-A2_计算机
ORACLE数据库常见问题诊断方法(常见错误篇)-20021224-A2.doc
ORACLE数据库常见问题诊断方法(常见错误篇)-20021224-A2_计算机软件及应用_IT/计算机...典型的 案例如 OPS 方式时,回滚段不能公有; (3)删除回滚段时,回滚段中有...
ORACLE数据库常见问题诊断方法(内部错误篇)-20021224-A2.doc
ORACLE数据库常见问题诊断方法(内部错误篇)-20021224-A2_计算机软件及应用_IT/计算机...ORACLE 数据库常见问题诊断方法 (内部错误篇) ORACLE 的错误各种各样,包括应用...
Oracle数据库常见问题诊断方法.pdf
Oracle数据库常见问题诊断方法Oracle数据库常见问题诊断方法隐藏>> Oracle 数据库常见...7 1.4 OPS 或 RAC 篇 ......
ORACLE数据库常见问题诊断方法.doc
ORACLE数据库常见问题诊断方法 - 1 ORA-12571、ORA-0311
ORACLE数据库常见问题(常见错误).doc
ORACLE数据库常见问题(常见错误) - RACLE 数据库常见问题诊断方法 ---(常见错误篇) (常见错误篇) 1 ORA-12571、ORA-03113、ORA-03114、ORA-01...
13 Oracle数据库常见问题诊断方法.pdf
9 5 非 OPS 篇......29 iii 华为产品维护资料 Oracle 数据库常见问题诊断方法 2004/10/15 Oracle 数据库常见问题诊断方法 1 常见错误篇 ORACLE 的这类错误...
Oracle数据库操作常见错误及解决方案.doc
Oracle数据库操作常见错误及解决方案_IT/计算机_专业...使用 OPS(当然 OPS 从某种意义 上说并不是一种好...
Oracle数据库的DevOps实践_图文.ppt
Oracle数据库的DevOps实践_计算机软件及应用_IT/计算机_专业资料。
详细讲解Oracle数据库管理员认证方法.doc
详细讲解Oracle数据库管理员认证方法_计算机软件及应用_IT/计算机_专业资料。详细...非管理员级别用户 OS 认证方法: 关键字: os_authent_prefix=ops$ 注意: ...
oracle数据库参数.doc
oracle数据库参数_IT/计算机_专业资料。oracle数据库...诊断与统计 用于控制是否检查事务处理管理块有无...NULL gc_defer_time OPS 一个并行服务器参数,用于...
更多相关标签: