Hanlaysis releted sql

定位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