oracle database check



 

1. Swap utilization

   #  free –m//查看交换分区大小

   # swapon –s 或者 #  cat/proc/swaps

    

2. Load average

   # top

3. Free disk space

   #:Df -h

4. Mount disk

   #  mount-t cifs //192.168.10.29/MES-LogBackup /remote-share/ -o username=admin

5.  

6.  

7.  

8. 查看表空间使用情况

    

   In OEM：服务器—〉表空间

    

   In putty：

   SELECT SUM(bytes) / (1024 * 1024) ASfree_space, tablespace_name

    FROMdba_free_space GROUP BY tablespace_name;

    

   SELECT a.tablespace_name, a.bytes total,b.bytes used,

   c.bytes free, (b.bytes * 100) / a.bytes"% USED ", (c.bytes * 100) / a.bytes "% FREE "

   FROM sys.sm$ts_avail a, sys.sm$ts_used b,sys.sm$ts_free c

   WHERE a.tablespace_name = b.tablespace_name

   AND a.tablespace_name = c.tablespace_name;

    

9. 数据库整体性能

    

   In OEM：性能

    

    

10. 高运行SQL语句

     

    In  OEM：性能—〉其他监视链接—〉SQL监视

In putty：

cpu_time占用top 10：

Select cpu_time,sql_text from (select sql_text,cpu_time, rank() over(order by cpu_time desc) exec_rank from v$sql ) where exec_rank <=10;

执行次数最多的top 10：

select sql_text,executions from (select sql_text,executions,rank() over (orderby executions desc) exec_rank from v$sql) where exec_rank <=10;

 

1. 阻塞会话

    

   In OEM：性能—〉其他监视链接—〉阻塞会话

 

In putty：

select r.root_sid, s.serial#,

      r.blocked_num, r.avg_wait_seconds,

      s.username,s.status,s.event,s.MACHINE,

      s.PROGRAM,s.sql_id,s.prev_sql_id

 from (select root_sid, avg(seconds_in_wait) as avg_wait_seconds,

              count(*) - 1 as blocked_num

         from (select CONNECT_BY_ROOT sid as root_sid, seconds_in_wait

                 from v$session

                start with blocking_session isnull

               connect by prior sid =blocking_session)

        group by root_sid

       having count(*) > 1) r,

      v$session s

 where r.root_sid = s.sid

 order by r.blocked_num desc,r.avg_wait_seconds desc;

 

1. 数据库等待时间

    

   In OEM：性能—〉I/O

    

   In putty:

    

   SELECT EVENT,TIME_WAITED_MICRO,ROUND(TIME_WAITED_MICRO*100/S.DBTIME,1)PCT_DB_TIME FROM V$SYSTEM_EVENT E, V$EVENT_NAME N,(SELECT VALUE DBTIME FROMV$SYS_TIME_MODEL WHERE STAT_NAME = 'DB time') S WHERE E.EVENT_ID = N.EVENT_IDAND N.WAIT_CLASS NOT IN ('Idle', 'System I/O') and TIME_WAITED_MICRO>1000000 ORDER BY PCT_DB_TIME ASC;

    

2. 数据库索引

    

   In putty：

   查看数据库Index信息

   SELECT A.OWNER, A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, A.INDEX_TYPE, B.COLUMN_POSITION,B.COLUMN_NAME, C.TABLESPACE_NAME, A.TABLESPACE_NAME, A.UNIQUENESS FROMDBA_INDEXES A, DBA_IND_COLUMNS B, DBA_TABLES C WHERE A.OWNER = UPPER ('schema_name')  AND A.OWNER = B.INDEX_OWNER AND A.OWNER =C.OWNER AND A.TABLE_NAME LIKE UPPER ('table_name') AND A.TABLE_NAME= B.TABLE_NAMEAND A.TABLE_NAME = C.TABLE_NAME AND A.INDEX_NAME = B.INDEX_NAME ORDER BY A.OWNER,A.TABLE_OWNER, A.TABLE_NAME A.INDEX_NAME, B.COLUMN_POSITION;

    

   查出没有建立index的表

   SELECT OWNER, TABLE_NAME FROM ALL_TABLES  WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')AND OWNER = UPPER ('schema_name') MINUS

   SELECT OWNER, TABLE_NAME  FROM ALL_INDEXES  WHERE OWNER NOT IN('SYS','SYSTEM','OUTLN','DBSNMP');

    

   查出建立了过量index的表

   SELECT  OWNER, TABLE_NAME, COUNT (*) "count" FROM ALL_INDEXES WHERE OWNER NOT IN('SYS','SYSTEM','OUTLN','DBSNMP') AND OWNER = UPPER ('schema_name') GROUP BYOWNER, TABLE_NAME HAVING COUNT (*) >('4');

    

    

   索引是否被使用

    

   SQL> ALTER INDEX  index_name MONITORING USAGE;

   SQL> SELECT table_name, index_name,monitoring, used FROM v$object_usage;

    

   索引是否重建 （dd〉20%,则考虑重建索引）

    

   select del_lf_rows * 100 /decode(lf_rows,0,1,lf_rows)  dd from index_stats

   where name = 'index_ name';