性能监控之监控SQL语句Oracle认证考试
文章作者 100test 发表时间 2009:12:11 18:09:52
来源 100Test.Com百考试题网
"mkhgigh">
监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece.
分析表
analyze table tablename compute statistics for all indexes.
analyze table tablename compute statistics for all indexed columns.
analyze table tablename compute statistics for table.
监控事例的等待
0select 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).
表、索引的存储情况检查
0select 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.
0select 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