性能监控之监控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