定位wait event主要等待
快照id
col take_time for a20
select instance_number,snap_id,to_char(END_INTERVAL_TIME,'mm-dd hh24') "take_time" from dba_hist_snapshot order by 2,1;
快照时间
select min(begin_interval_time) min, max(end_interval_time) max
from dba_hist_snapshot where snap_id between 204 and 228;
136930
按等待事件大类统计
select wait_class_id, wait_class, count(*) cnt
from dba_hist_active_sess_history
where snap_id between 100720 and 100721
group by wait_class_id, wait_class
order by 3;
1740759767
3871361733
按等待事件小类统计
SELECT event_id, event, COUNT (*) cnt
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 100720 and 100721 AND wait_class_id = 3875070507
GROUP BY event_id, event
ORDER BY 3;
具体等待事件对应sql统计
SELECT sql_id, COUNT (*) cnt
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 100720 and 100721 AND event_id IN (1729366244)
GROUP BY sql_id
HAVING COUNT (*) > 1
ORDER BY 2;
------------------直接查session的信息
select sql_id,EVENT,p1,p1text,p2,p3
from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time > to_date('2020-04-24 09:30:00','yyyy-mm-dd hh24:mi:ss')
and sample_time < to_date('2021-01-04 09:45:00','yyyy-mm-dd hh24:mi:ss')
and event ='row cache lock';
order by 5,4
select parameter,gets,getmisses,MODIFICATIONS from v$rowcache where cache#=2
select event,count(*)
from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time > to_date('2020-10-10 04:00:00','yyyy-mm-dd hh24:mi:ss')
and sample_time < to_date('2020-10-10 08:30:00','yyyy-mm-dd hh24:mi:ss')
and session_id=2731
group by event;
select blocking_session,event,count(session_id)
from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time > to_date('2020-07-28 11:00:00','yyyy-mm-dd hh24:mi:ss') and sample_time < to_date('2020-07-28 11:30:00','yyyy-mm-dd hh24:mi:ss')
group by blocking_session,event
select session_id,sql_id,event
from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time > to_date('2020-07-28 11:00:00','yyyy-mm-dd hh24:mi:ss') and sample_time < to_date('2020-07-28 11:30:00','yyyy-mm-dd hh24:mi:ss')
where session_id=
select sql_text from DBA_HIST_sqltext where sql_id=
SELECT event,count(*)
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 137367 and 137368
and sql_id='f71ny8h8s04z9'
group by event;
SELECT event,count(*)
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 137319 and 137320
and sql_id='f71ny8h8s04z9'
group by event;
Generate hanganalysis:
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all hanganalyze 3
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug -g all dump systemstate 283;
Statement processed.
Wait for 3 mins
SQL> oradebug -g all dump systemstate 283;
SQL> oradebug tracefile_name;
SQL> oradebug -g all dump systemstate 258;
Statement processed.
Wait for 2mins
SQL> oradebug -g all dump systemstate 258;
Statement processed.
SQL> oradebug tracefile_name;
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 266
--Wait for 2 mins
SQL> oradebug dump systemstate 266
--Wait for 2 mins
SQL> oradebug dump systemstate 266
--Wait for 2 mins
SQL> oradebug tracefile_name
set echo on
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
-- For DB, set level 266
oradebug -g all dump systemstate 266
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
Hang related with RMAN:
select SID ,SERIAL# , INST_ID , USERNAME, OSUSER || '@' || MACHINE OSINFO, SUBSTR(PROGRAM,0,20) PROGRAM,TO_CHAR(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') LOGON_TIME, EVENT, SECONDS_IN_WAIT SIW from gv$session where type <> 'BACKGROUND' and PROGRAM like 'rman%' ORDER BY USERNAME, INST_ID, SID;
Check RMAN progress:
When it shows 100%, means RMAN work is complete.
SELECT opname, round(sofar/totalwork*100) "% Complete"
FROM gv$session_longops
WHERE opname LIKE 'RMAN%'
AND totalwork != 0
AND sofar <> totalwork
ORDER BY 1;
cat library_cache_lock.sql
col user_name for a10;
col kglnaobj for a10;
select b.sid,a.user_name,a.kglnaobj from x$kgllk a, v$session b where a.kgllkhdl in (select p1raw from v$session_wait where wait_time=0 and event ='library cache lock') and a.kgllkmod <> 0 and b.saddr=a.kgllkuse;
cat hang_latch.sql
select * from v$latchholder;
cat hang_wait.sql
col mode_held for a10;
col mode_requested for a10
col lock_type for a10;
set lines 1000;
select * from dba_waiters;
select * from gv$lock where block=1;
col user_name format a10
col owner format a10
col object_name format a15
col sid format 999999
col serial# format 999999
col spid format a6
select /*+ rule */ lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username user_name,
o.owner, o.object_name, o.object_type, s.sid, s.serial#, p.spid
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr
order by o.object_id, xidusn desc;
SQL> set lines 350 trimspool on pages 300
SQL>
SQL> select sid, state, event, seconds_in_wait, blocking_session
2 from v$session
3 where blocking_session is not null
4 or sid in (select blocking_session
5 from v$session
6 where blocking_session is not null)
7 order by sid;
SID STATE EVENT SECONDS_IN_WAIT BLOCKING_SESSION
---------- ------------------- ---------------------------------------------------------------- --------------- ----------------
128 WAITING enq: DD - contention 542311 1702
380 WAITING enq: DD - contention 2159938 1702
442 WAITING enq: DD - contention 2159098 1702
506 WAITING enq: DD - contention 2158258 1702
569 WAITING enq: DD - contention 713509 1702
632 WAITING enq: DD - contention 2156578 1702
695 WAITING enq: DD - contention 2155738 1702
758 WAITING enq: DD - contention 1108924 1702
821 WAITING enq: DD - contention 2142990 1702
884 WAITING enq: DD - contention 2139901 1702
947 WAITING enq: DD - contention 2136286 1702
1010 WAITING enq: DD - contention 2078621 1702
1073 WAITING enq: DD - contention 1845020 1702
1135 WAITING GPnP Get Item 2161258
1136 WAITING enq: DD - contention 1844671 1702
1199 WAITING enq: DD - contention 1844394 1702
1263 WAITING enq: DD - contention 1844065 1702
1325 WAITING enq: DD - contention 1843549 1702
1388 WAITING enq: DD - contention 1471932 1702
1451 WAITING enq: DD - contention 1641694 1702
1514 WAITING enq: DD - contention 1641426 1702
1577 WAITING enq: DD - contention 1572189 1702
1640 WAITING enq: DD - contention 1501765 1702
1702 WAITING rdbms ipc reply 0 1135
1703 WAITING enq: DD - contention 896552 1702
1765 WAITING enq: DD - contention 2161018 1702
1766 WAITING enq: DD - contention 893474 1702
1829 WAITING enq: DD - contention 11782 1702
1892 WAITING enq: DD - contention 5041 1702
1955 WAITING enq: DD - contention 3357 1702
30 rows selected.
Reference with https://www.cnblogs.com/jyzhao/p/8379720.html
Wait Eventsfor 11g
V$SESSION
V$WAITCLASSMETRIC
V$WAITCLASSMETRIC_HISTORY
V$WAITSTAT
V$WAIT_CHAINS
Oracle 11g Backup and Recovery
Backup and Recoveryis the most important function of the serious Oracle DBA and can mean the difference between life and death in terms of corporate safety and job security. The following v$ views provide details for Oracle 11g Backup and Recovery.
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_JOB_DETAILS
V$BACKUP_SET_DETAILS
V$BACKUP_PIECE_DETAILS
V$BACKUP_COPY_DETAILS
$BACKUP
V$RECOVERY_STATUS
V$RECOVERY_FILE_STATUS
V$BACKUP_SET
V$BACKUP_PIECE
V$BACKUP_DATAFILE
V$BACKUP_REDOLOG
V$BACKUP_CORRUPTION
V$BACKUP_DEVICE
V$BACKUP_SPFILE
V$BACKUP_SYNC_IO
V$BACKUP_ASYNC_IO
V$RECOVER_FILE
V$RMAN_STATUS
V$RMAN_OUTPUT
V$BACKUP_DATAFILE_DETAILS
V$BACKUP_CONTROLFILE_DETAILS
V$BACKUP_ARCHIVELOG_DETAILS
V$BACKUP_SPFILE_DETAILS
V$BACKUP_DATAFILE_SUMMARY
V$BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_ARCHIVELOG_SUMMARY
V$BACKUP_SPFILE_SUMMARY
V$BACKUP_SET_SUMMARY
V$RECOVERY_PROGRESS
V$RMAN_BACKUP_TYPE
V$RMAN_CONFIGURATION
V$ Views-Oracle 11g NFS New Features
Oracle 11g provides a new feature to setup NFS based files within Oracle 11g. The following v$ views provide the details for NFS configurationwithin Oracle 11g.
V$NFS_CLIENTS
V$NFS_OPEN_FILES
V$NFS_LOCKS
V$IOSTAT_NETWORK
The 11g health monitor (HM) is linked to the following v$ views listed below:
V$HM_CHECK
V$HM_CHECK_PARAM
V$HM_RUN
V$HM_FINDING
V$HM_RECOMMENDATION
V$HM_INFO
For the new 11g feature for the Incident Repair component of the 11g ADR, there are the following v$ views:
V$IR_FAILURE
V$IR_REPAIR
V$IR_MANUAL_CHECKLIST
V$IR_FAILURE_SET