监控数据库性能的SQL
查看tablespace的空間使用情況
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name
1. 监控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
2. 回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3. 监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4. 监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 监控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
7. 监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
8. 监控 SGA 中共享缓存区的命中率,应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
9. 显示所有数据库对象的类别和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
10. 监控 SGA 中重做日志缓存区的命中率,应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
11. 监控内存和硬盘的排序比率,最好使它小于 .10,增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
12. 监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13. 监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
14. 找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
15. 监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时,参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时,参数需加大
16. 碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
17. 表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
select segment_name,count(*) from dba_extents where segment_type='INDEX' and ōwner='caisit'
group by segment_name;
18、找使用CPU多的用户session
是cpu used by this session
19﹑找使用cpu和內存多的sql語句.
select sql_text from v$sqltext a where a.hashvalue=(select sql_hash_value from v$session b where b.paddr=(select
addr from v$process where spid=&pid))
20﹑ 捕獲等待的sql語句.
select w.sid,w.event,t.sql_text from v$session_wait w,v$sqltext t,v$session s
where t.hashvalue=s.sql_hash_value and s.sid=w.sid;
21﹑捕獲等待的sql語句和用戶
select user#,audsid,s.osuser,w.sid,w.event,t.sql_text from v$session_wait w,v$sqltext t,v$session s
where t.hashvalue=s.sql_hash_value and s.sid=w.sid;
22. cache hit ratio
SELECT (sum(getmisses)/sum(gets)) * 100 as "cache hit ratio" FROM v$rowcache
23. buffer cache hit ratio
SELECT (1 - (sum(decode(name, 'physical reads', value, 0)) /(sum(decode(name, 'db block gets', value, 0)) +
sum(decode(name, 'consistent gets', value, 0))))) * 100 as "Buffer cache hit ratio" FROM v$sysstat
24.查數據字典v$datafile,及v$filestat了解數據中是否存在I/O競爭等﹕
select substr(a.file#,1,2) "File#",a.status,a.bytes,b.phyrds,b.phywrts,substr(a.name,1,50) "File Name"
from v$datafile a,v$filestat b where a.file#=b.file#;
<PHYRDS,PHYWRTS分別表示物理磁盤讀寫的數據量>
25.日志緩沖區過小﹐會增加日志寫盤的次數﹐增加了系統的I/O負擔﹐對于日志緩沖區而言﹐可以計算失敗率﹐使用數據字典v$LATCH計算日志緩沖區的失敗率﹐應該小于1%
select name,gets,misses,immediate_gets,immediate_misses,decode(gets,0,0,misses/gets*100) ratioone,
decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratiotwo
from v$latch where name in ('redo allocation','redo copy');
<gets表示成功等待日志緩沖區的次數。immediate_gets表示成功立即得到日志緩沖區的次數。immediate_misses表示失敗立即得到日志緩沖區的次數。日志緩沖區的失敗率表示了日志緩沖區是否足夠大﹐應該小于1%>
1、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
3、查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
4、查看控制文件
select name from v$controlfile;
5、查看日志文件
select member from v$logfile;
6、查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_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;
7、查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
8、查看数据库的版本
Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle';
9、查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;
分享到:
相关推荐
oracle性能监控语句:有很多都不太清楚是做什么用的,如果高手看到,请帮忙做个批注!谢谢
一键即运行的图形化Oracle数据库性能监控软件 无需安装、部署,不占用服务器资源 支持从Oracle 11.2.0.1开始的所有版本 支持Oracle RAC 非植入式,对Oracle只读,不在数据库内创建任何对象 资源占用极低,内存...
Oracle性能监控与分析系统,对于搞这方面的论文有帮助,提供参考!
oracle 性能 监控 日常维护必须准备
spotlight on oracle 性能监控教程、详细讲解性能测试中如何使用spotlight进行oracle数据库的监控分析
oracle性能监控常用语句集合 因文件较长,故分三小节展现。 请下载后自行解压。
1.分析表 2.监控事例的等待 3.查看碎片程度高的表 4.找使用CPU多的用户session 5.回滚段的争用情况 6.在某个用户下找所有的索引 7..... etc.
Tomcat和MySql和Jvm和Oracle性能监控和调优
Ooracle 数据库性能监控常用sql 监控当前会话、执行的sql及io等信息
Oracle性能监控常用sql,需要的看看哦
常用的几个ORACLE性能监控SQL,需要的就下吧。
增加了对大量服务器监控配置 增加了监控内容 1.0功能: 1.查询Oracle实例名、状态、版本等 2.Oracle连接数 3.SGA信息 4.PGA信息 5.数据库使用 6.表空间使用情况 7.临时文件信息 8.临时文件使用情况 9.表死锁情况 10....
OraGlance 是由 parnassusdata.com 开发的轻量级 Oracle 性能监控工具。 它只会关注最重要的指标。 您无需任何先决条件即可轻松运行它。 它会要求很少的权限:将 gv_$active_session_history 上的选择授予 $USER_...
NULL 博文链接:https://lwl860116.iteye.com/blog/705262
LOadrunner进行压力测试时,需要对数据库进行监控,spotliht on oracle工具可以实时监控oracle数据库,定位耗时,执行效率低的sql,便于对数据库进行调优提供参考依据。
利用SQL语句实现数据库性能监控.pdf
ORACLE日常监控与性能问题处理脚本,非常适用,几百个脚本完全满足需要