Giám sát tải ACTIVE SESION, LOCK trong Oracle Database

/************************************************ ACTIVE, LOCK ************************************************/
SELECT /*1.ActiveSession*/ distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE  S.STATUS = 'ACTIVE' AND  S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'
and s.username  NOT in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE')
--AND username in 'BINHTV'
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
--and lower(ss.sql_text) like lower('%parallel%')
--and s.sid=4588
--and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY username,sql_id;


select /* 2.Total_ActiveSessioins*/ USERNAME,count(*) from gv$session where  status='ACTIVE' group by USERNAME order by count(*) desc;

select /* 3.Total_Session*/ username,status, count(*) from gv$session group by username,status order by count(*) desc;

Select /*4.Blocking_session*/ inst_id,blocking_session, sid, serial#, sql_id, wait_class, seconds_in_wait, username,STATUS,SCHEMANAME,OSUSER,MACHINE,PROGRAM,TYPE,LOGON_TIME
From gv$session where blocking_session is not NULL and type not like 'BACKGROUND' order by inst_id;

SELECT /*5.SID*/  'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr 
AND a.inst_id=b.inst_id
--and b.inst_id=2
AND a.sid in (
4588
)
and type='USER'
order by inst_id;

select /*6.SQL_Detail*/  sql_id,sql_fulltext from gv$sql where  sql_id in ('67bm8d2ah3xhk');

select sql_id,sql_fulltext,loaded_versions,executions,loads,invalidations,parse_calls from gv$sql  where inst_id=4 and sql_id='cn7m7t6y5h77g';

SELECT *
    FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
    WHERE s.type != 'BACKGROUND' AND S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND     AND S.TYPE = 'USER'
    AND s.sql_id <> (select sql_id from v$session where sid=(select sid from v$mystat where rownum=1)) and username NOT in ('SYS')
    and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') IN ('direct path read','db file scattered read','PX Deq Credit: send blkd') ;
 
SELECT *
    FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
    WHERE s.type != 'BACKGROUND' AND S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND AND S.TYPE = 'USER' and s.username  not in ('SYS','SYSMAN')
    and s.event in ('library cache lock','gc buffer busy acquire')
    and s.username NOT in ('SYS');

/******************************************* SUM *****************************************/
select /* count , status*/ username,status, count(*) from gv$session group by username,status order by count(*) desc;

select /* Active theo user*/ USERNAME,count(*) from gv$session where  status='ACTIVE' group by USERNAME order by count(*) desc;

select status, count(*) from gv$session  group by status order by status;

select count(*) from gv$session ;

select USERNAME,count(*) from gv$session group by USERNAME order by count(*) desc;

select machine,count(*) from v$session group by machine order by count(*) desc;

select inst_id, count(*) from gv$session group by inst_id;

select /*Thong ke theo status*/  username,status,count(*) from gv$session where username like 'BINHTV%' group by username,status order by count(*) desc;

select /*Thong ke theo inst_id*/ inst_id,count(*), username from gv$session where username like 'BINHTV%' group by inst_id, username order by username;

select /* user theo machine */ machine,count(*), username from gv$session where username like 'BINHTV%' group by machine, username order by username;

/****************************************** LOCK BANG, PKG ************************************************/
--Table
SELECT c.owner, c.object_name, c.object_type, b.SID,b.SQL_ID, b.serial#, b.status,b.osuser, b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id
AND a.object_id = c.object_id
--and lower(object_name) like lower('%TEST%');

SELECT s.inst_id,s.sid, s.serial#,s.sql_id,username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser, s.machine,
DECODE(l.block,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
  DECODE(v.locked_mode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
  ) MODE_HELD,
  decode(l.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',l.type) lock_type
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
and object_name like upper('%TEST_TABLE')
--and username like upper('trieunv')
ORDER BY username, session_id;

-- Cac session dang truy cap vao object theo owner --> De kill
select /*+ parallel(8) */ distinct owner from gv$access where lower(object) like lower('%test_table%');

***************************************************** SQL DETAIL *******************************************************************/
select sql_id,sql_fulltext from gv$sql where  sql_id in ('67bm8d2ah3xhk');

SELECT /* Tim cau lenh sql */ b.inst_id,b.sid, a.SQL_TEXT, b.username, b.machine, b.blocking_session, B.TYPE   
FROM gV$SQLAREA a,gV$SESSION b
WHERE a.ADDRESS = b.SQL_ADDRESS
AND upper(SQL_TEXT) LIKE '%TEST%';

select machine,username,count(*) from gv$session where sql_id='48hfqhs6n2gak'
group by machine,username order by count(*) desc;

/************************************************** DISTRIBIUTED TRANSACTION *********************************************************/
---///////ORA-01591: lock held by in-doubt distributed transaction 10.1.10741505, xy ly tren sqlplus
select * from sys.pending_trans$;

select * from DBA_2PC_PENDING;

select * from DBA_2PC_NEIGHBORS;

743.30.1421878
1896.9.233248
2780.32.722288
3127.12.110519

commit force '3127.12.110519'
--rollback force '75.1.3697342'
execute dbms_transaction.purge_lost_db_entry('3127.12.110519');
commit;

SELECT   local_tran_id, state
              FROM   DBA_2PC_PENDING
              where (retry_time-fail_time)*24*60>1.5;
     

@ Trần Văn Bình - Founder of Oracle DBA AZ
#học oracle #oracle database #khóa học oracle online #khóa học oca #học oca ở đâu #oca là gì #oca oracle #BossData #OraAz #OracleDBAAz #OracleTutorial #Quản_trị_cơ_sở_dữ_liệu_Oracle #OracleDBA #OracleDatabaseAdministration