oracle备份恢复与监控.ppt

磁盘读最多的sql set serveroutput on size 1000000 declare execution number; top25 number; text1 varchar2(4000); x number; len1 number; cursor c1 is select executions, disk_reads, substr(sql_text, 1, 4000) from v$sqlarea order by disk_reads desc; begin dbms_output.put_line(Exec || || Reads || || Text); dbms_output.put_line(----- || || -------- || || -------------); open c1; for i in 1 .. 25 loop fetch c1 into execution, top25, text1; dbms_output.put_line(rpad(to_char(execution), 5) || || rpad(to_char(top25), 8) || || substr(text1, 1, 66)); len1 := length(text1); x := 66; while len1 x - 1 loop dbms_output.put_line(- || substr(text1, x, 66)); x := x + 66; end loop; end loop; end; / 查看dba_waiters select * from dba_waiters 发现有大量的等待session。如果无输出，数据库应没有问题 查看等待事件情况 select p.spid pid,s.sid,s.SERIAL#,s.username,event,w.p1,w.P1TEXT,w.p2,w.P2TEXT,w.p3,w.P3TEXT,sq.SQL_TEXT,w.WAIT_TIME,w.SECONDS_IN_WAIT,w.STATE from v$session_wait w, v$session s, v$process p, v$sql sq where event not like SQL% and w.sid = s.sid and s.paddr = p.addr and s.SQL_ADDRESS = sq.ADDRESS and s.SQL_HASH_VALUE = sq.HASH_VALUE 查看锁等待情况 SELECT lpad( , DECODE(request, 0, 0, 1)) || sid sess,id1,id2,lmode,request,type FROM V$LOCK WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0) ORDER BY id1, request 检查是否存在lmode为3的记录 捕获耗费性能的SQL语句 --获取会话 select sid, serial#, username, program, to_char(logon_time, yyyy-mm-dd hh24:mi:ss) as logon_time from v$session where paddr in(select addr from v$process where spid = $1); --获取该会话运行的SQL语句 select hash_value,sql_text from v$sqltext_with_newlines where hash_value in (select sql_hash_value from v$session where paddr in (select addr from v$process where spid = $1)) order by piece; 获取现有语句的执行计划 --先根据hash值获得SQL语句 select sql_text from v$sqltext_with_newlines where