AIX系统月维护查什么（二）

缓冲区命中率

Statckspack的Buffer Hit

缓冲区的命中率显示相对磁盘读取的数据内存的百分比。

数据字典缓存命中率

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;

数据字典缓存命中率显示了对数据字典和其它对象的内存读取操作所占的百分比

库缓存命中率

select Sum(Pins)/(Sum(Pins)+Sum(Reloads))* 100 "Hit Ratio" from V$LibraryCache;

库缓存命中率显示了对实际语句和PL/SQL对象的内存读操作所占的百分比

内存排序命中率

select a.value "Disk Sorts", b.value "Memory Sorts",      round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) "Pct Memory Sorts"
from   v$sysstat a, v$sysstat b
where  a.name ='sorts (disk)'
and    b.name ='sorts (memory)';

根据初始化参数PGA_AGGREGATE_TARGET(或者SORT_AREA_SIZE)的值，用户的排序可能在内存中执行或磁盘上的特定的临时表空间执行

最浪费内存的前十个语句占所有语句的比例

select sum(pct_bufgets)
from (select rank() over(order by buffer_gets desc) as rank_bufgets,
     to_char(100*ratio_to_report(buffer_gets) over(), '999.99')
     pct_bufgets
       from v$sqlarea)
where rank_bufgets < 11;

前十个语句占所有语句的比例过高，将影响Oracle的整体性能，也意味着前十个语句也许需要进行SQL优化

表空间使用情况

select tablespace_name,

       total_gb,

       free_gb,

       used_gb,

       used_ratio || '%' used_ratio,

       round((used_gb / 90 * 100) - total_gb) "to-90%",

       round((used_gb / 85 * 100) - total_gb) "to-85%",

       round((used_gb / 80 * 100) - total_gb) "to-80%",

       round((used_gb / 75 * 100) - total_gb) "to-75%",

       round((used_gb / 70 * 100) - total_gb) "to-70%"

  from (select ttt.*

          from (select df.tablespace_name,

                       df.total_gb,

                       nvl(dfs.free_gb, 4) free_gb,

                       df.total_gb - nvl(dfs.free_gb, 4) used_GB,

                       round((df.total_gb - nvl(dfs.free_gb, 4)) /

                             decode(df.total_gb, 0, 1, df.total_gb),

                             2) * 100 used_ratio

                         from (select tablespace_name,

                               round(sum(bytes) / 1024 / 1024 / 1024, 4) total_gb

                          from dba_data_files

                         group by tablespace_name) df,

                       (select tablespace_name,

                               round(sum(bytes) / 1024 / 1024 / 1024, 4) free_gb

                          from dba_free_space

                         group by tablespace_name) dfs

                 where df.tablespace_name = dfs.tablespace_name(+)

                union all

                select tt.tablespace_name,

                       tt.total_gb,

                       round(dt.block_size * ss.fb / 1024 / 1024 / 1024, 4) free_gb,

                       tt.total_gb -

                       round(dt.block_size * ss.fb / 1024 / 1024 / 1024, 4) used_gb,

                       round((dt.block_size * ss.ub / 1024 / 1024 / 1024) /

                             decode(tt.total_gb, 0, 1, tt.total_gb),

                             2) * 100 used_ratio

                  from (select tablespace_name,

                               sum(used_blocks) ub,

                               sum(free_blocks) fb

                          from gv$sort_segment

                         group by tablespace_name) ss,

                       dba_tablespaces dt,

                       (select tablespace_name,

                               round(sum(bytes / 1024 / 1024 / 1024), 4) total_gb

                          from dba_temp_files

                         group by tablespace_name) tt

                 where tt.tablespace_name = ss.TABLESPACE_NAME

                   and tt.tablespace_name = dt.tablespace_name) ttt

         order by 5 desc);

使用率是否在阀值之内

数据文件状态

所有数据文件是否online

监听运行情况

lsnrctl -status

数据库Alert日志报警情况 

是否有ORA开头的关键字