Oracle监控Sql脚本

spool d:\10-10.log
Prompt 192.168.10.10 WCM03 分析
Prompt 分析时间
SELECT SYSDATE FROM DUAL;
Prompt 数据库实例
SELECT NAME FROM V$DATABASE;
Prompt 检查数据文件的状态记录状态不是"online"的数据文件
Select file_name
from dba_data_files
where status='OFFLINE';
Prompt 检查表空间的使用情况 如果表空间使用超过80%，添加新的数据文件或者加大数据文件的大小
SELECT tablespace_name,
to_char(100*sum_free_m/sum_m, '99.99') || '%' AS pct_free,
max_m,
count_blocks free_blk_cnt,
sum_free_m
FROM ( SELECT tablespace_name,
sum(bytes)/1024/1024 AS sum_m
FROM dba_data_files
GROUP BY tablespace_name),
(SELECT tablespace_name AS fs_ts_name,
max(bytes)/1024/1024 AS max_m,
count(blocks) AS count_blocks,
sum(bytes/1024/1024) AS sum_free_m
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name;
Prompt 统计数据库的剩余的使用空间
SELECT tablespace_name,
trunc ( sum (bytes ) / (1024*1024) ) as free_m,
sum ( blocks ) as free_blk ,
max ( bytes ) / (1024) as big_chunk_k,
count (*) as num_chunks
FROM dba_free_space
GROUP BY tablespace_name ;
Prompt 检查无效的数据库对象
SELECT owner||' '||object_name||' '||object_type
FROM dba_objects
WHERE status='INVALID';
Prompt 检查不起作用的约束
SELECT owner||' '||constraint_name||' '||table_name||' '||constraint_type||' '||status
FROM dba_constraints
WHERE status = 'DISABLED'
AND constraint_type = 'P';
Prompt 检查无效的trigger
SELECT owner||' '||trigger_name||' '||table_name||' '|| status
FROM dba_triggers
WHERE status = 'DISABLED';
Prompt 自由范围的碎片
column FSFI format 999,99
select tablespace_name,
sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name
order by 1;
Prompt 数据缓冲命中率
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' ;
Prompt 语句的重载率
SELECT SUM(pins) total_pins,
SUM(reloads) total_reloads,
SUM(reloads)/SUM(pins)*100 libcache_reload_ratio
FROM v$librarycache;
Prompt 用户锁状态
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK',NULL) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
s.terminal,
s.machine,
s.program,
s.osuser
FROM v$session s,
v$lock l,
dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL ;
Prompt 数据字典高速缓存
SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100
FROM v$rowcache;

Prompt 物理文件的使用频率
SELECT name||' '||phyrds||'/'||phywrts
FROM v$datafile df,
v$filestat fs
WHERE df.file# =fs.file#;
Prompt 调度进程的竞争
SELECT network,
sum(busy)/sum(busy)+sum(idle)
FROM v$dispatcher
GROUP BY network;
Prompt 减少Free List竞争
SELECT class,
count
FROM v$waitstat
WHERE class='free list';
SELECT sum(value)
FROM v$sysstat
WHERE name IN ('db block gets','consistent gets');
spool off