-------------------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';