【摘】数据库巡检

1. 检查数据库字符集

select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.ZHS16GBK

一致，请将客户端环境变量修改为同server端一致。

2.1 检查shared_pool的缓存命中率

Shared pool主要由两部分组成，分别是Dictionary cache(包括数据字典的定义，如表结构、权限等),Library cache（包括共享的sql游标，sql原代码以及执行计划、存储过程和会话信息）因此对shared_pool的检查应该分为两部分。

2.2 检查shared_pool的使用率

shared_pool的作用是缓存已经被解析过的SQL，使其能被重用，不用再解析。它对应用程序的执行效率有重要影响，如果shared_pool不足，将会引起应用程序报错，因此有必要检查shared_pool的使用情况。

以下sql将报告shared_pool的使用：

2.4 检查redo log buffer的命中率

redo log buffer（重做日志文件缓冲区）也是SGA中一块重要的内存区域。对数据库的任何修改都按顺序被记录在该缓冲,然后由LGWR进程将它写入磁盘.

以下SQL可以查出redo log buffer的命中情况：

select name,value from v$sysstat

where name in ('redo entries','redo buffer allocation retries')；

2.5 检查内存排序性能

oracle数据库当需要进行排序操作时，优先使用sort_area_size分配的内存做排序；当内存不足时，使用临时表空间做排序。一个运行情况理想的数据库应该是绝大部分排序操作都在内存里完成。

 以下SQL可以查出oracle数据库的排序效率：

SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

输出类似如下：

NAME                                                                 VALUE

---------------------------------------------------------------- ----------

sorts (memory)                                                       31676

sorts (disk)                                                            10

要求sorts(disk)/sort(memory)比值小于0.0001(万分之一)。如果该比值偏大，建议适当增加sort_area_size的大小。

（从oracle 9i之后，oracle修改了对排序区的管理。当workarea_size_policy参数被设置为auto时，pga_aggregate_target参数指定连接到例程的所有服务器进程的目标 PGA 总内存，即在SGA区之外专门分配出pga_aggregate_target大小的内存用于排序等操作，此时sort_area_size参数不起作用。）

3 检查是否使用了MTS

MTS（Multi- Threaded Server，共享服务器）是ORACLE SERVER的一个可选的配置选择，是相对DEDICATE方式（专有连接）而言，它利用队列的方式管理连接池，最大的优点是在不用增加物理资源（内存） 的前提下支持更多的并发的连接。一般来说，如果并发连接数不超过500，不建议使用MTS。MTS本身并不十分稳定，经常会发生应用断连、CPU过载等问 题，应当避免使用。

以下SQL可以查询系统当前是否使用了MTS：

SQL>select sid,username,server from v$session;

结果应该返回类似如下：

SID       SERIAL#   USERNAME                      SERVER

---------- ---------- ------------------------------ ---------

        1         1                               DEDICATED

        2         1                               DEDICATED

        3         1                               DEDICATED

        4         1                               DEDICATED

        5         1                               DEDICATED

        6         1                               DEDICATED

        7         1                               DEDICATED

        8         1                               DEDICATED

        9     15314 SYS                           DEDICATED

 

4 检查redo logfile

用户对数据库进行的任何插入、删除、修改等操作都会首先被记录到oracle的redo logfile里，因此数据库redo logfile文件的重要性极高，在某种意义上甚至超过了数据文件。我们要求每个数据库实例至少要有两组redo logfile，每组redo logfile应该有两个以上的成员。

以下sql可以查询出redo logfile的位置、大小等信息。

ttitle -

 center 'Redo Log Summary' skip 2 

col group# format 999     heading 'Group' 

col member format a40      heading 'Member' justify c

col thread#   format 999     heading ‘Thread’ justify c

col status format a10      heading 'Status' justify c        

col archivedformat a10      heading 'Archived' justify c      

col fsize  format 999     heading 'Size|(MB)'  

select l.group#,

       l.thread#,

       member,

    archived,

       l.status,

       (bytes/1024/1024) fsize

from   v$log l,

    v$logfile f

where f.group# = l.group#

order by 1 /

ttitle off

如果每个instance对应的日志文件组少于2个，每个组内的日志成员少于2个，请增加日志组或日志成员个数。

另外，请检查alert日志中的日志切换频率，最好是每15-20分钟切换一次。如果日志切换频率太高，建议增加redo logfile的大小。

5 控制文件（control file）检查 

select name,status from v$controlfile;

要求control file数量应该为3个。如果control file数量不足3个，考虑将control file文件数量增加到3个。

6 回滚段检查

回滚段在oracle数据库内主要有三个作用：

事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。ITPUB个人空间/vZz^F
事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在重做日志文件中，ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。ITPUB个人空间Zud.S}%w6N_w#V
读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。而且，当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致）。ORACLE利用回滚段的数据前影像来构造一个读一致性视图。

6.3 oracle 9i回滚段检查

从oracle 9i开始,oracle引入了undo tablespace，大幅度简化了回滚段的管理。但undo tablespace也不能完全解决所有和回滚段相关的问题，因此仍然需要对它进行监控。

From v$undostat

7 检查表空间使用状况及碎片程度

创建一个数据库时，需要多个表空间（Tablespace）， 表空间由逻辑段（Segment）组成，如系统（System）表空间,临时（Temporary）表空间等。一个逻辑段可以包含多个数据范围 （Extent）和一个或多个自由范围块，即自由空间（Free Space）。表空间是存储数据的逻辑单位，它的使用状况对oracle数据库的正常工作意义重大。

以下sql可以检查出当前表空间的使用情况及碎片程度：

col Tablespace format a24

select df.tablespace_name "Tablespace",

      df.bytes/(1024*1024) "Total Size",

      sum(fs.bytes)/(1024*1024) "Free Size",

      round(sum(fs.bytes)*100/df.bytes) "Pct Free",

      round((df.bytes-sum(fs.bytes))*100/df.bytes) "Pct Used",

      ROUND(100*SQRT(MAX(fs.bytes)/SUM(fs.bytes))*

      (1/SQRT(SQRT(COUNT(fs.bytes)))) ,2) FSFI

from dba_free_space fs, (select tablespace_name, sum(bytes) bytes

                from dba_data_files

                group by tablespace_name ) df         

where fs.tablespace_name = df.tablespace_name

group by df.tablespace_name, df.bytes；

 

输出结果类似如下：

Tablespace              Total Size Free Size  Pct Free   Pct Used      FSFI

------------------------ ---------- ---------- ---------- ---------- ----------

XDB                     88.125    50.125        57        43      83.93

INDX           　      25        24.9375       100       0       100

RMAN                    50        43.9375       88        12      100

DRSYS           　     20        15.1875       76        24      100

TOOLS                   10        9.9375        99        1       100

USERS                   25        24.9375       100       0       100

SYSTEM                  430       91.25         21        79      72.88

最后一列FSFI 的含义是Free Space Fragmentation Index（自由空间碎片索引），FSFI的最大可能值为100（一个理想的单文件表空间）。随着范围的增加，FSFI值缓慢下降，而随着最大范围尺寸的 减少，FSFI值会迅速下降。通常来说，表空间的FSFI值不应该低于30%。

 

对表空间的检查结果应该是每个表空间的使用率不超过90%，FSFI值不应低于30。

（若某表空间的FSFI值低于30%，可以手工合并表空间碎片。

SQL> alter tablespace 表空间名 coalesce;

   上述检查结果中如果表空间剩余空间不足10%，请扩展相应的表空间。

 9 检查job状态是否正常

为完成某些例行工作，我们的应用程序往往定义了很多job。但有时因为种种原因，有些job不能按时执行，因此需要检查job的状态。

以下SQL可以查出job的内容、状态、执行时间等信息：

SQL>select job,what,next_date,broken from dba_jobs;

 11 检查是否有因为表空间不足而不能扩展的对象

表和索引的存储参数next_extent和pct_increase确定了某张表的下一个扩展范围（extent），有时尽管表空间还有很多剩余空间，但因为表或索引的存储参数设置不当，也会导致对象无空间扩展下一个范围的问题。

以下SQL可以查出无法扩展下一个extent的对象：

 12 检查失效的存储过程、函数、包等对象

为保证业务的连续性，需要定时检查数据库内有哪些失效对象，避免不必要的业务中断。

以下sql脚本可以按输入参数（用户名）检查该用户下有哪些对象失效。

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

转载于:http://blog.itpub.net/563971/viewspace-414982/