ABMSBATCH, KILL SESSION, QUERY, MONITOR
--#########################################################
-- Monitor the Current running SQL
--#########################################################
select * from gv$session where username ='ABMSBATCH';
alter system flush shared_pool;
select distinct inst_id, plan_hash_value,sql_id,executions exec,rows_processed rows_ ,round(ELAPSED_TIME/1000000) TIME_SEC,round(ELAPSED_TIME/60000000) Min ,
buffer_gets bufr_gets,
parsing_schema_name schema, sql_text
from gv$sql
where sql_id in
(
select sql_id
from gv$session s
WHERE s.username ='ABMSBATCH'
)
and sql_text not like '%DBMS%APP%'
order by TIME_SEC desc;
--#########################################################
-- Monitor the Current running SQL - Via SQLMonitor
--#########################################################
select
inst_id, status, sql_id,
to_char(sql_exec_start,'DD-Mon HH:MI') SQL_START,
to_char(last_refresh_time,'DD-Mon HH:MI') last_refresh_time,
round((sysdate - sql_exec_start)*(24*60)) Min,
refresh_count,sql_plan_hash_value, sid,session_serial# serial#,
sql_text
from gv$sql_monitor
where status not like '%DONE%'
order by elapsed_time desc;
select 'alter system kill session ''' || sid ||','||serial# ||',@'|| inst_id ||''' immediate;'
from gv$session
where username ='TESTUSER'
and sql_id='5mdjfv8594zj8';
--#########################################################
-- How may ABMSBATCH Sessions in Database
select count(*) from gv$session where username ='TESTUSER' ;
-- How may ABMSBATCH Sessions per instance
select inst_id,sql_id,count(*) from gv$session where username ='TESTUSER' group by inst_id,sql_id ;
-- ABMSBATCH with SID
select inst_id,sql_id,sid,count(*) from gv$session where username ='TESTUSER' group by inst_id,sql_id,sid ;
-- ABMSBATCH active sessions with SQL ID
select inst_id,sql_id,status,count(*) from gv$session where username ='TESTUSER' and status ='ACTIVE' group by inst_id,sql_id,status ;
-- ABMSBATCH active sessions with SQL ID - Spacific sql
select inst_id,sql_id,status,count(*) from gv$session where username ='TESTUSER' where sql_id ='&SQLID' group by inst_id,sql_id,status ;
-- Longops all ABMSBATCH
select * from gv$session_longops where username ='TESTUSER' ;
-- Longops ABMSBATCH specific SQL
select * from gv$session_longops where username ='TESTUSER' and sql_id ='&SQLID';
---------------------------------------
-- SQL Area Stats - One ABMSBATCH SQL
---------------------------------------
select inst_id,rows_processed,hash_value,plan_hash_value, sql_profile from gv$sqlarea where sql_id ='&SQLID' order by 1;
---------------------------------------
-- SQL Area Stats - ALL ABMSBATCH SQL
----------------------------------------
select distinct inst_id, sql_id,executions,rows_processed,round(ELAPSED_TIME/1000000) ELAPSED_TIME,sql_text
from gv$sql
where sql_id in
(
select sql_id
from gv$session s
WHERE s.username ='TESTUSER'
)
and sql_text not like '%DBMS%APP%'
order by elapsed_time desc;
select * from gV$ACTIVE_SESSION_HISTORY where sql_id ='&SQLID';
SELECT optimizer_mode_mismatch, reason
FROM gv$sql_shared_cursor
WHERE sql_id ='&SQLID'
AND child_number = 0;
=====================================
-- Find Who is Blocking Session
=====================================
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' Serial=' || S1.SERIAL# || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2
order by 1;
=====================================
-- Find Blocking Objects - Query1
=====================================
alter system kill session 'SID,SERERIAL,@INST' immediate;
SELECT d.OBJECT_ID, substr(OBJECT_NAME,1,20), l.SESSION_ID, l.ORACLE_USERNAME, l.LOCKED_MODE
from gv$locked_object l, dba_objects d
where d.OBJECT_ID=l.OBJECT_ID;
SELECT XIDUSN,OBJECT_ID,SESSION_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS from gv$locked_object;
select * from gv$locked_object;
select * from sys.obj$ where obj#=&OBJID;
=====================================
-- Find Blocking Objects - Query2
=====================================
select
sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from
v$locked_object lo,
dba_objects ao,
v$session sess
where
ao.object_id = lo.object_id
and
lo.session_id = sess.sid;
#################################
-- BLOCKING
--------------
select inst_id,sid,sql_id,username,status,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE, BLOCKING_SESSION_STATUS ,
BLOCKING_INSTANCE ,
BLOCKING_SESSION ,
FINAL_BLOCKING_SESSION_STATUS ,
FINAL_BLOCKING_INSTANCE ,
FINAL_BLOCKING_SESSION
from gv$session
where
username in ('TESTUSER')
;
-- SPID
SELECT s.inst_id,s.sid,p.spid ,s.osuser,s.status, s.sql_id,s.prev_sql_id,s.*
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.username ='TESTUSER'
and s.status ='ACTIVE'
;
select
distinct s.snap_id,to_char(s.begin_interval_time,'mm-dd hh24') c1,
sql.sql_id c2,
sql.executions_delta exe_delta,
sql.buffer_gets_delta buffer_gets_delta,
sql.disk_reads_delta disk_reads_delta,
sql.iowait_delta iowait_delta,
sql.apwait_delta apwait_delta,
sql.ccwait_delta ccwait_delta
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
sql_id = '68p9v83zkp64h'
order by
c1 desc,
c2
;
---------------------------------------
-- My Session
---------------------------------------
select sys_context('USERENV','SID') from dual;
select sid from v$mystat where rownum <=1;
select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;
---------------------------------------
-- Sessions
---------------------------------------
select * from gv$instance;
select inst_id,count(*) from gv$session group by inst_id;
select inst_id,machine,count(*) from gv$session group by inst_id,machine order by machine;
select username,count(*) from gv$session group by username order by username;
select inst_id,username,count(*) from gv$session group by username,inst_id order by username,inst_id;
select username,sql_id,count(*)
from gv$session
where status='ACTIVE'
and username is not null
group by username,sql_id order by 1;
SELECT *
FROM gv$session
WHERE type != 'BACKGROUND'
and status ='ACTIVE'
and username is not null
order by username ;
-- List All Logged Users info1
SELECT inst_id ,username, osuser, status , count(*)
FROM gv$session
WHERE type != 'BACKGROUND'
and username is not null
--and status ='ACTIVE'
group by inst_id ,username,osuser, status
order by 2,1 ;
---------------------------------------
-- Kill Query list all Shared Global Sessions
---------------------------------------
SELECT s.inst_id,s.sid,p.spid spid_to_kill ,s.osuser,s.status , s.sql_id,s.prev_sql_id, s.*
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
--and s.sid=9264
--and s.inst_id=2
and s.username ='SHARED_GLOBAL'
;
---------------------------------------
-- list Blocking Sessions
---------------------------------------
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
gv$session
where
blocking_session is not NULL
order by
blocking_session;
select
sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from
v$locked_object lo,
dba_objects ao,
v$session sess
where
ao.object_id = lo.object_id
and
lo.session_id = s.sid;
select sys_context('USERENV','SID') rom dual;
select sid from v$mystat where rownum <=1;
select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;
---------------------------------------
-- Sessions
---------------------------------------
select * from gv$instance;
select inst_id,count(*) from gv$session group by inst_id;
select inst_id,machine,count(*) from gv$session group by inst_id,machine order by machine;
select username,count(*) from gv$session group by username order by username;
select inst_id,username,count(*) from gv$session group by username,inst_id order by username,inst_id;
show parameter global;
-- List Summary of Total Sessions in the Database Instance
SELECT inst_id , status , count(*)
FROM gv$session
WHERE type != 'BACKGROUND'
group by inst_id ,status
order by 1 ;
---------------------------
-- ABMSBATCH Related
---------------------------
SELECT optimizer_mode_mismatch, reason
FROM gv$sql_shared_cursor
WHERE sql_id ='2h224cxcq878r'
AND child_number = 0;
select distinct inst_id, sql_id,executions,rows_processed,round(ELAPSED_TIME/1000000) ELAPSED_TIME,sql_text
from gv$sql
where sql_id in
(
select prev_sql_id
from gv$session s
WHERE s.username ='TESTUSER'
)
and sql_text not like '%user$%' and sql_text not like '%role$%' and sql_text not like '%DBMS%APP%'
and sql_text not like '%tab$%'
order by elapsed_time desc;
order by sql_id;
select s.inst_id,s.username,s.status, sql_id,prev_sql_id,count(*)
from gv$session s
WHERE s.username ='TESTUSER'
group by s.inst_id,s.username,s.status,sql_id,prev_sql_id;
select * from gv$sql;
select * from gv$session;
select distinct inst_id, sql_id,executions,rows_processed,ELAPSED_TIME,sql_text from gv$sql where sql_id in
(
select prev_sql_id
from gv$session s
WHERE s.username ='TESTUSER'
)
order by sql_id;
select distinct inst_id, sql_id,sql_text from gv$sql where sql_id in
(
select prev_sql_id
from gv$session s
WHERE s.username ='TESTUSER'
--and status ='ACTIVE'
);
select sql_id, sum(executions),sum(ROWS_PROCESSED), sql_text from gv$sql where sql_id in
(
select prev_sql_id
from gv$session s
WHERE s.username ='TESTUSER'
--and status ='ACTIVE'
)
group by sql_id,sql_text;
SELECT s.inst_id,s.sid,p.spid ,s.osuser,s.status, s.*
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.username ='TESTUSER'
and s.status ='ACTIVE'
;
---------------------------------------
-- Kill Query list all Shared Global Sessions
---------------------------------------
SELECT s.inst_id,s.sid,p.spid spid_to_kill ,s.osuser,s.status, s.*
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
--and s.sid=9264
--and s.inst_id=2
and s.username ='SHARED_GLOBAL'
;
---------------------------------------
-- list all Sessions
---------------------------------------
select username,machine,count(*),status, service_name
from gv$session
where username is not null
and username not in ('TESTUSER','DBSNMP')
and osuser not in ('oracle')
group by username,machine,osuser,status,module, service_name order by 1,2,3;
select username,status,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE, BLOCKING_SESSION_STATUS ,
BLOCKING_INSTANCE ,
BLOCKING_SESSION ,
FINAL_BLOCKING_SESSION_STATUS ,
FINAL_BLOCKING_INSTANCE ,
FINAL_BLOCKING_SESSION
from gv$session
where
username in ('TESTUSER')
;
---------------------------------------
-- list Blocking Sessions
---------------------------------------
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
gv$session
where
blocking_session is not NULL
order by
blocking_session;
select
sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from
v$locked_object lo,
dba_objects ao,
v$session sess
where
ao.object_id = lo.object_id
and
lo.session_id = s.sid;
2k2a2ys5mjhkf
