Oracle(10g+)常规诊断

Wesley13
• 阅读 270
-------------------Oracle(10g+)常规诊断-------------------
/*
数据库突然变慢,普通用户权限,常规诊断
1.检查数据库的等待事件
2.检查锁
3.查看当前会话连接数,是否属于正常范围
4.检查行链接/迁移
5.检查表空间使用情况

如果上面检查不出问题,建议申请权限做AWR报告分析。
权限:
GRANT ADVISOR TO user;
GRANT SELECT_CATALOG_ROLE TO user;
GRANT EXECUTE ON sys.dbms_workload_repository TO user;

创建快照:
exec sys.dbms_workload_repository.CREATE_SNAPSHOT;

执行脚本awrrpt.sql($ORACLE_HOME/rdbms/admin/)
--输入你想要的展现格式,html or text
--输入你想要查看多少天内的snap_id
Enter value for num_days: --这里过去几天
--输入begin_snapid
begin_snapid为显示过去几天信息中的Snap Id
--输入end_snapid
begin_snapid为显示过去几天信息中的Snap Id
--输入要保存的文件名

*/

-------------------Oracle对象状态-------------------
/*
检查Oracle控制文件状态
输出结果应该至少有2条,一般有3条以上(包含3条)的记录,“STATUS”应该为空。
状态为空表示控制文件状态正常。
*/
select status, name from v$controlfile;

/*
检查Oracle在线日志状态
输出结果应该有3条以上(包含3条)记录,“STATUS”应该为非“INVALID”,非“DELETED”。 
注:“STATUS”显示为空表示正常
*/
select group#, status, type, member from v$logfile;

/*
检查Oracle表空间的状态
输出结果中STATUS应该都为ONLINE。
*/
select tablespace_name, status from dba_tablespaces;

/*
检查Oracle所有数据文件状态
输出结果中“STATUS”应该都为“ONLINE”。或者输出结果中“STATUS”应该都为“AVAILABLE”。
*/
select name, status from v$datafile;

/*
检查无效对象
如果有记录返回,则说明存在无效对象。若这些对象与应用相关,那么需要重新编译生成这个对象
*/
select owner, object_name, object_type
  from dba_objects
 where status != 'VALID'
   and owner != 'SYS'
   and owner != 'SYSTEM';

/*
检索无效对象
*/
SELECT owner, object_name, object_type
  FROM dba_objects
 WHERE status = 'INVALID';

/*
检查所有回滚段状态
在10G中会根据事务数量自动调整OFFLINE,ONLINE
*/
select segment_name, status from dba_rollback_segs;

-------------------Oracle相关资源使用-------------------

/*
检查Oracle初始化文件中相关参数值
若LIMIT_VALU-MAX_UTILIZATION<=5,则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。
可以通过修改Oracle初始化参数文件$ORACLE_BASE/admin/CKDB/pfile/initORCL.ora来修改
*/
select resource_name, max_utilization, initial_allocation, limit_value
  from v$resource_limit;

/*
查看当前会话连接数,是否属于正常范围。
如果建立了过多的连接,会消耗数据库的资源,同时,对一些“挂死”的连接可能需要手工进行清理。
*/
select count(*) from v$session;

/*
如果用户使用的表空间空闲率%Free小于10%以上(包含10%),则注意要增加数据文件来扩展表空间而不要是用数据文件的自动扩展功能。
*/
select f.tablespace_name,
       a.total,
       f.free,
       round((f.free / a.total) * 100) "% Free"
  from (select tablespace_name, sum(bytes / (1024 * 1024)) total
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, round(sum(bytes / (1024 * 1024))) free
          from dba_free_space
         group by tablespace_name) f
 WHERE a.tablespace_name = f.tablespace_name(+)
 order by "% Free";

/*
检查一些扩展异常的对象
如果有记录返回,则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数。
*/
select Segment_Name,
       Segment_Type,
       TableSpace_Name,
       (Extents / Max_extents) * 100 Percent
  From sys.DBA_Segments
 Where Max_Extents != 0
   and (Extents / Max_extents) * 100 >= 95
 order By Percent;

/*
检查system表空间内的内容
如果记录返回,则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。
如果相关请把这些对象移到非System表空间,同时应该检查这些对象属主的缺省表空间值。
*/
select distinct (owner)
  from dba_tables
 where tablespace_name = 'SYSTEM'
   and owner != 'SYS'
   and owner != 'SYSTEM'
union
select distinct (owner)
  from dba_indexes
 where tablespace_name = 'SYSTEM'
   and owner != 'SYS'
   and owner != 'SYSTEM';

/*
检查对象的下一扩展与表空间的最大扩展值
如果有记录返回,则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值,需调整相应表空间的存储参数。
*/
select a.table_name, a.next_extent, a.tablespace_name
  from all_tables a,
       (select tablespace_name, max(bytes) as big_chunk
          from dba_free_space
         group by tablespace_name) f
 where f.tablespace_name = a.tablespace_name
   and a.next_extent > f.big_chunk
union
select a.index_name, a.next_extent, a.tablespace_name
  from all_indexes a,
       (select tablespace_name, max(bytes) as big_chunk
          from dba_free_space
         group by tablespace_name) f
 where f.tablespace_name = a.tablespace_name
   and a.next_extent > f.big_chunk;

-------------------Oracle数据库性能-------------------

/*
检查数据库的等待事件
如果数据库长时间持续出现大量像latch free,enqueue,buffer busy waits,
db file sequential read,db file scattered read等等待事件时,需要对其进行分析,可能存在问题的语句。
建议做AWR报告分析。
*/
select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT
  from v$session_wait
 where event not like 'SQL%'
   and event not like 'rdbms%';

/*
查找前十条性能差的sql
注:仅供参考,v$视图提供的不一定准确,以AWR报告分析为准。
*/
SELECT *
  FROM (SELECT PARSING_USER_ID EXECUTIONS,
               SORTS,
               COMMAND_TYPE,
               DISK_READS,
               SQL_TEXT
          FROM V$SQLAREA
         ORDER BY DISK_READS DESC)
 WHERE ROWNUM < 10;

/*
等待时间最多的5个系统等待事件的获取
*/
SELECT *
  FROM (SELECT *
          FROM V$SYSTEM_EVENT
         WHERE EVENT NOT LIKE 'SQL%'
         ORDER BY TOTAL_WAITS DESC)
 WHERE ROWNUM <= 5;

/*
检查碎片程度高的表
*/
SELECT segment_name table_name, COUNT(*) extents
  FROM dba_segments
 WHERE owner NOT IN ('SYS', 'SYSTEM')
 GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                     FROM dba_segments
                    GROUP BY segment_name);

/*
检查表空间的 I/O 比例
*/
SELECT DF.TABLESPACE_NAME NAME,
       DF.FILE_NAME       "FILE",
       F.PHYRDS           PYR,
       F.PHYBLKRD         PBR,
       F.PHYWRTS          PYW,
       F.PHYBLKWRT        PBW
  FROM V$FILESTAT F, DBA_DATA_FILES DF
 WHERE F.FILE# = DF.FILE_ID
 ORDER BY DF.TABLESPACE_NAME;

/*
检查文件系统的I/O比例
*/
SELECT SUBSTR(A.FILE#, 1, 2) "#",
       SUBSTR(A.NAME, 1, 30) "NAME",
       A.STATUS,
       A.BYTES,
       B.PHYRDS,
       B.PHYWRTS
  FROM V$DATAFILE A, V$FILESTAT B
 WHERE A.FILE# = B.FILE#;

/*
检测回滚段争用
SUM(waits)值应小于SUM(gets)值的1%
*/
select sum(gets), sum(waits), sum(waits) / sum(gets) from v$rollstat;

/*
回卷段的竟争会降低系统的性能。如果GETS与WAITS的比大于2%表示存在竟争问题
*/
select rn.name,
       rs.gets as 被访问次数,
       rs.waits as 等待回退段块的次数,
       (rs.waits / rs.gets) * 100 as 命中率
  from v$rollstat rs, v$rollname rn;

/*
检查锁
*/
select sid,
       serial#,
       username,
       SCHEMANAME,
       osuser,
       MACHINE,
       terminal,
       PROGRAM,
       owner,
       object_name,
       object_type,
       o.object_id
  from dba_objects o, v$locked_object l, v$session s
 where o.object_id = l.object_id
   and s.sid = l.session_id;

/*
查看是否有僵死进程
*/
select spid from v$process where addr not in (select paddr from v$session);

/*
检查行链接/迁移
*/
select table_name, num_rows, chain_cnt
  From dba_tables
 Where owner = 'CTAIS2'
   And chain_cnt <> 0;

/*
检查缓冲区命中率
如果命中率低于90% 则需加大数据库参数db_cache_size
*/
SELECT a.VALUE + b.VALUE logical_reads,
       c.VALUE phys_reads,
       round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio
  FROM v$sysstat a, v$sysstat b, v$sysstat c
 WHERE a.NAME = 'db block gets'
   AND b.NAME = 'consistent gets'
   AND c.NAME = 'physical reads';

/*
检查共享池命中率
如低于95%,则需要调整应用程序使用绑定变量,或者调整数据库参数shared pool的大小。
*/
select sum(pinhits) / sum(pins) * 100 from v$librarycache;

/*
检查排序区
如果disk/(memoty+row)的比例过高,则需要调整sort_area_size(workarea_size_policy=false)
或pga_aggregate_target(workarea_size_policy=true)。
*/
select name, value from v$sysstat where name like '%sort%';

/*
检查日志缓冲区
如果redo buffer allocation retries/redo entries 超过1% ,则需要增大log_buffer。
*/
select name, value
  from v$sysstat
 where name in ('redo entries', 'redo buffer allocation retries');

-------------------Oracle数据库其他检查-------------------

/*
检查失效的索引
注:分区表上的索引status为N/A是正常的,如有失效索引则对该索引做rebuild,
如:alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;
*/
select index_name, table_name, tablespace_name, status
  From dba_indexes
 Where owner = 'CTAIS2'
   And status <> 'VALID';

/*
检查不起作用的约束
如有失效约束则启用,如:
alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;
*/
SELECT owner, constraint_name, table_name, constraint_type, status
  FROM dba_constraints
 WHERE status = 'DISABLE'
   and constraint_type = 'P';

/*
检查无效的trigger
alter Trigger TRIGGER_NAME Enable;
*/
SELECT owner, trigger_name, table_name, status
  FROM dba_triggers
 WHERE status = 'DISABLED';
点赞
收藏
评论区
推荐文章
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Easter79 Easter79
1年前
swap空间的增减方法
(1)增大swap空间去激活swap交换区:swapoff v /dev/vg00/lvswap扩展交换lv:lvextend L 10G /dev/vg00/lvswap重新生成swap交换区:mkswap /dev/vg00/lvswap激活新生成的交换区:swapon v /dev/vg00/lvswap
Wesley13 Wesley13
1年前
MySQL查询按照指定规则排序
1.按照指定(单个)字段排序selectfromtable_nameorderiddesc;2.按照指定(多个)字段排序selectfromtable_nameorderiddesc,statusdesc;3.按照指定字段和规则排序selec
SPDK QOS机制解析
本文关键词:intelspdkbdevqos序:intelspdk软件在存储领域应用广泛。因其可以高效管理linux系统的nvmessd盘,又支持vhostuser协议可以对接qemu虚拟机,在云计算领域通常被用来做本地盘云主机的存储管理软件。如此优秀的一款软件,有必要仔细分析其内部的实现机制,本篇文章主要介绍spdkqos机制。spdk
3A网络 3A网络
4个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
开发一个不需要重写成HiveQL的大数据SQL引擎学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。各种知识表象看杂乱无章,若只是学习
SPDK对接Ceph性能优化
关键词:SPDK、NVMeOF、Ceph、CPU负载均衡SPDK是intel公司主导开发的一套存储高性能开发套件,提供了一组工具和库,用于编写高性能、可扩展和用户态存储应用。它通过使用一些关键技术实现了高性能:1.将所有必需的驱动程序移到用户空间,以避免系统调用并且支持零拷贝访问2.IO的完成通过轮询硬件而不是依赖中断,以降低时延3.使用消息传递,以避免IO
3A网络 3A网络
4个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
理解virt、res、shr之间的关系(linux系统篇)前言想必在linux上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题——你的程序在运行时占用了多少内存(物理内存)?通常我们可以通过t
初识DevOps
基本概念和延伸的思考DevOps,是Development(开发)和Operations(运维)组成的复合词,一般译为“开发运维一体化”。看到这个概念,首先会产生几个问题:开发是什么,哪些环节是开发?运维是什么,哪些环节是运维?开发人员写好代码在本地调试,环境出问题了自己来调整,这是开发工作还是运维工作?系统故障后,运维人员发现是配置文件内容出错了就改成了正
一个关于SDWAN单臂部署方案验证的实验
假设有这样一张网络,其中RTA和PCA表示某公司的A分支,通过中国电信CT路由器接入互联网ISP;RTB和PCB表示某公司的B分支,通过中国联通CU路由器接入互联网ISP。DNS(8.8.8.8)表示某互联网应用。为实现A分支私网192.168.2.0/24和B分支私网192.168.3.0/24的互通,现计划使用某厂商的SDWAN方案进打通两个内网,像下图
天翼云高可用虚拟IP(HAVIP)实践
(一)产品概述天翼云高可用虚拟IP(HighAvailabilityVirtualIPAddress,简称HAVIP)是一种可用独立创建和删除的私有网络IP地址资源。通过在VIPCIDR中申请一个私有网络IP地址,然后与高可用软件(如高可用软件Keepalived)配合使用,可用在VPC中搭建高可用的主备集群服务,提高VPC中服务的可用性。限制和说明
高性能API网关Kong介绍
本文关键词:高性能、API网关、Kong、微服务1.Introduction是随着微服务(Microservice)概念兴起的一种架构模式。原本一个庞大的单体应用(Allinone)业务系统被拆分成许多微服务(Microservice)系统进行独立的维护和部署,服务拆分带来的变化是API的规模成倍增长,API的管理难度也在日益增加,使用API网关发布和管