-
Swap utilization
# free –m//查看交换分区大小
# swapon –s 或者 # cat/proc/swaps
-
Load average
# top
-
Free disk space
#:Df -h
-
Mount disk
# mount-t cifs //192.168.10.29/MES-LogBackup /remote-share/ -o username=admin
-
-
-
-
查看表空间使用情况
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;
-
数据库整体性能
In OEM:性能
-
高运行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;
-
阻塞会话
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;
-
数据库等待时间
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;
-
数据库索引
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';