Learn IT

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,

;

---------------------------------------

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

Learn IT

Monday, July 18, 2022

Pages

About Me