常用sql巡检语句
1.查表空间占用率：
Sql代码 www.2cto.com
SELECT a.tablespace_name,
a.total || 'M' total_space,
(a.total - b.free) || 'M' used_space,
to_char((a.total - b.free) / a.total * 100, '99.99') || '%' pct_free
FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 free
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
2.查是否有锁表
Sql代码
SELECT object_name, s.sid, s.serial#
FROM v$locked_object o, v$session s, dba_objects c
WHERE o.session_id = s.sid
AND o.object_id = c.object_id;
3.查是否有失效索引
Sql代码
SELECT index_name, table_name, tablespace_name, status, owner
FROM dba_indexes
WHERE owner = 'WORKFLOW'
AND status <> 'VALID'
4.查是否有失效约束
Sql代码
SELECT constraint_type,
constraint_name,
table_name,
r_owner,
r_constraint_name,
status
FROM dba_constraints
WHERE owner = 'WORKFLOW'
AND status <> 'ENABLED'
5.查是否有失效触发器
Sql代码
SELECT trigger_name, table_name, status
FROM dba_triggers
WHERE owner = 'WORKFLOW'
AND status <> 'ENABLED'
6.查共享池
Sql代码
-- 如果命中率低于 90% 则需加大
数据库参数 db_cache_size
SELECT NAME,
1 - (physical_reads / (db_block_gets + consistent_gets)) hit_ratio
FROM v$buffer_pool_statistics
WHERE db_block_gets + consistent_gets > 0;
Sql代码
-- 如低于 95%，则需要调整应用程序使用绑定变量，或者调整
数据库参数 shared pool 的大小
SELECT SUM(pinhits) / SUM(pins) * 100 hit_radio FROM v$librarycache;
Sql代码
--共享SQL区的使用率,这个使用率应该在90％以上，否则需要增加共享池的大小
select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache
Sql代码
--其中: &TSP_IN_M是你的总的共享池的SIZE(M)
SELECT (1 - round(bytes / (&tsp_in_m * 1024 * 1024), 2)) * 100 || '%'
FROM v$sgastat
WHERE NAME = 'free memory'
AND pool = 'shared pool';
Sql代码
--查询空闲的共享池内存
SELECT *
FROM v$sgastat
WHERE NAME = 'free memory'
AND pool = 'shared pool';
--共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足。
Sql代码
SELECT NAME,
physical_reads,
db_block_gets,
consistent_gets,
1 - (physical_reads / (db_block_gets + consistent_gets)) "Hit Ratio"
FROM v$buffer_pool_statistics
WHERE NAME = 'DEFAULT';
---通常应在90%以上，否则，需要调整,加大DB_CACHE_SIZE
7.几个常用的检查语句
Sql代码
--查找排序最多的SQL:
SELECT HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS
FROM V$SQLAREA
ORDER BY SORTS DESC;
Sql代码
--查找磁盘读写最多的SQL:
SELECT *
FROM (SELECT sql_text,
disk_reads "total disk",
executions "total exec",
disk_reads / executions "disk/exec"
FROM v$sql
WHERE executions > 0
AND is_obsolete = 'N'
ORDER BY 4 DESC)
WHERE rownum < 11;
Sql代码
--查找工作量最大的SQL(实际上也是按磁盘读写来排序的):
SELECT substr(to_char(s.pct, '99.00'), 2) || '%' load,
s.executions executes,
p.sql_text
FROM (SELECT address,
disk_reads,
executions,
pct,
rank() over(ORDER BY disk_reads DESC) ranking
FROM (SELECT a