-
性能监控之监控SQL语句
-
-
分析表
-
analyze TABLE tablename compute statistics FOR ALL indexes;
-
analyze TABLE tablename compute statistics FOR ALL indexed COLUMNS;
-
analyze TABLE tablename compute statistics FOR TABLE;
-
-
监控事例的等待
-
-
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;
-
-
查看碎片程度高的表
-
-
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);
-
-
表、索引的存储情况检查
-
-
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 owner=&owner
-
GROUP BY segment_name;
-
-
找使用CPU多的用户session
-
-
12是cpu used BY this session
-
-
SELECT a.sid,spid,STATUS,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
-
FROM v$session a,v$process b,v$sesstat c
-
WHERE c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
-
-
监控表空间的 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;
-
-
-
回滚段的争用情况
-
-
SELECT name, waits, gets, waits/gets "Ratio"
-
FROM v$rollstat a, v$rollname b
-
WHERE a.usn = b.usn;
-
-
在某个用户下找所有的索引
-
-
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;
-
-
-
监控文件系统的 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#;
-
-
监控 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;
-
-
监控 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;
-
-
监控 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;
-
-
监控 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);
-
-
显示所有数据库对象的类别和大小
-
-
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,增加 sort_area_size
-
-
SELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk));
-
-
-
监控当前数据库谁在运行什么SQL语句
-
-
SELECT osuser, username, sql_text FROM v$session a, v$sqltext b
-
WHERE a.sql_address =b.address ORDER BY address, piece;
-
-
监控字典缓冲区
-
-
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
-
-
监控 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时,参数需加大
-
-
碎片程度
-
-
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;
相关推荐
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日常监控与性能问题处理脚本,非常适用,几百个脚本完全满足需要