========================================================
Constraints Queries========================================================
select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
from all_constraints a, all_constraints b
where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and b.table_name = 'Table Name Here'
/
-- generic
select CONSTRAINT_NAME,OWNER,TABLE_NAME,STATUS
from DBA_CONSTRAINTS
where R_CONSTRAINT_NAME
in
( select CONSTRAINT_NAME
from dba_constraints
where TABLE_NAME in ('MASTER')
)
/
-- Get Columns (Generic Query)
select * from DBA_CONS_COLUMNS
where (CONSTRAINT_NAME,OWNER,TABLE_NAME)
in
( select CONSTRAINT_NAME,OWNER,TABLE_NAME
from DBA_CONSTRAINTS
where R_CONSTRAINT_NAME
in
( select CONSTRAINT_NAME
from dba_constraints
Where TABLE_NAME in ('MASTER')
)
)
/
========================================================
-- session/process info
========================================================
select *
from gv$session s, gv$process p
where s.paddr = p.addr
and s.inst_id = p.inst_id
--and s.sid='5552'
;
select s.inst_id
,s.STATUS
,s.sid
,p.spid
,substr(s.username,1,15) usernm
,substr(s.program,1,15) program
,substr(s.MACHINE,1,15) machine
,substr(s.TERMINAL,1,15) terminal
,substr(s.MODULE,1,15) modul
,substr(s.ACTION,1,15) action
,s.LOGON_TIME
--,p.PGA_USED_MEM
--,p.PGA_MAX_MEM
,s.SQL_HASH_VALUE
,s.BLOCKING_SESSION
,command
from
gv$session s,
gv$process p
where s.paddr = p.addr
and s.inst_id =p.inst_id
--and s.username is not null
--and s.username='ADARSHKUMAR'
--and s.module not like '%Apol%'
--and status ='ACTIVE'
--and blocking_session is not null
--and s.MACHINE not like '%web%'
--and s.MACHINE not like '%beta%'
--and s.MACHINE not like '%seci%'
--and s.sid =4
order by 3
;
=======================================================
-- Wait Event Queries
========================================================
select wait_class_id, wait_class, count(*) cnt
from dba_hist_active_sess_history
where snap_id between 21277 and 21285
group by wait_class_id, wait_class
order by 3;
- Use Classid and Look the Enets whit Higher Count
select event_id, event, count(*) cnt
from dba_hist_active_sess_history
where snap_id between 21281 and 21285
and wait_class_id=3871361733
group by event_id, event
order by 3;
------------------------------
- Now Get The SQL Statement
------------------------------
select sql_id, count(*) cnt
from dba_hist_active_sess_history
where snap_id between 21281 and 21285
and event_id in (1478861578, 2701629120)
group by sql_id
having count(*) > 1
order by 2;
- Go for SQl
--- Max cnt 871
select sql_text
from dba_hist_sqltext
where sql_id='$id';
========================================================
-- Hot Block Query
========================================================
SELECT SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_EXTENTS
WHERE FILE_ID = &fileid AND &block BETWEEN BLOCK_ID AND
BLOCK_ID + BLOCKS - 1;
-----------------------------------------
-- Check who is blocking and How may sessions are affected
-- This session needed to be killed
-----------------------------------------
SELECT blocking_sid, num_blocked
FROM ( SELECT blocking_sid, SUM(num_blocked) num_blocked
FROM ( SELECT l.id1, l.id2,
MAX(DECODE(l.block, 1, i.instance_name||'-'||l.sid,2,i.instance_name||'-'||l.sid, 0 )) blocking_sid,
SUM(DECODE(l.request, 0, 0, 1 )) num_blocked
FROM gv$lock l, gv$instance i
WHERE ( l.block!= 0 OR l.request > 0 ) AND
l.inst_id = i.inst_id
GROUP BY l.id1, l.id2
)
GROUP BY blocking_sid
ORDER BY num_blocked DESC
)
WHERE num_blocked != 0;
-----------------------------------------------------------
-- check who has locked the object
-----------------------------------------------------------
select *
from gv$locked_object
where object_id in ( select object_id from dba_objects where object_name='TEST');
-- Detail of above Query
select p.spid,s.*
from gv$session s,
gv$process p
where s.paddr=p.addr
and s.inst_id = p.inst_id
and s.sid in ( select k.session_id
from gv$locked_object k
where k.object_id in ( select object_id from dba_objects where object_name='&TABLE_NAME')
and s.inst_id = k.inst_id
);
-- Find SQL Associated
select * from gv$sqlarea where sql_id='&SQL_ID';
===================================
Transaction Queries
===================================
Check who is using most REDO and If it is still Rolling Back
SELECT s.inst_id,s.username,s.schemaname,s.osuser,s.machine,s.program,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM gv$transaction t,
gv$session s,
gv$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.inst_id=s.inst_id
and s.inst_id=r.inst_id
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC
/
-- Find when Transaction started for a specific table
-- and Check the Current Status
select * from
GV$TRANSACTION
where (XIDUSN,XIDSQN,XIDSLOT) in
(
select XIDUSN,XIDSQN,XIDSLOT
from gv$locked_object
where object_id in ( select object_id from dba_objects where object_name='&table_name_here')
);
SELECT start_timestamp,commit_timestamp,logon_user,table_owner,table_name,operation, undo_sql
FROM flashback_transaction_query ftq,v$transaction vt,v$session vs
WHERE ftq.xid = vt.xid and vt.ses_addr=vs.saddr AND vs.sid =&SID;
-- chk spid and other info
select s.inst_id
,s.STATUS
,s.sid
,p.spid
,substr(s.username,1,15) usernm
,substr(s.program,1,15) program
,substr(s.MACHINE,1,15) machine
,substr(s.TERMINAL,1,15) terminal
,substr(s.MODULE,1,15) modul
,substr(s.ACTION,1,15) action
,s.LOGON_TIME
,p.PGA_USED_MEM
,p.PGA_MAX_MEM
,s.SQL_HASH_VALUE
,s.BLOCKING_SESSION
,command
from
gv$session s,
gv$process p
where s.paddr = p.addr
and s.inst_id =p.inst_id
--and s.username='ADARSHKUMAR'
--and s.module not like '%Apol%'
--order by status,inst_id,sid
order by PGA_USED_MEM desc
;
========================================================
-- Temp Space Usage Queries
========================================================
select * FROM gV$SORT_SEGMENT;
select a.inst_id,b.sid, a.session_num serial# , a.username,
a.segtype,
to_char(a.blocks*16/1024,'999,999,999,999') space_used_MB ,
a.SQLHASH ,a.SQL_ID ,a.TABLESPACE,
trunc((blocks*16*1024/tot) *100,1) || '%' as pct_used
from gv$sort_usage a,gv$session b,(select sum(blocks*16*1024) tot
from gv$sort_usage su
) n
where a.inst_id = b.inst_id
and a.session_num = b.serial#
order by a.blocks desc;
select b.machine,a.inst_id, a.username, a.segtype,
to_char(a.blocks*16/1024,'999,999,999,999') space_used_MB ,
a.SQLHASH ,a.SQL_ID ,a.TABLESPACE,
trunc((blocks*16*1024/tot) *100,1) || '%' as pct_used
from gv$sort_usage a,gv$session b,(select sum(blocks*16*1024) tot
from gv$sort_usage su
) n
where a.inst_id = b.inst_id
and a.session_num = b.serial#
order by a.blocks desc;
========================================================
Redo Space Usage Queries
to_char(trunc(SUM(BLOCKS * BLOCK_SIZE)/(1024*1024)),'999,999,999') SIZE_in_MB ,count(*) cnt
from gV$ARCHIVED_LOG
--where COMPLETION_TIME > sysdate -10
where inst_id =1
group by dest_id,trunc (COMPLETION_TIME)
order by 2 desc,1;
(
SELECT * FROM ( SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00" ,
SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
FROM GV$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM') )
ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC )
WHERE ROWNUM <15;
from gv$sysstat
where name like 'redo size'
order by 2,1;
from gv$loghist where first_time > sysdate - 10/1440
group by inst_id,to_char(first_time,'yy-mm-dd hh24:mi')
order by inst_id,to_char(first_time,'yy-mm-dd hh24:mi');
substr(s.MACHINE,1,20) machin , s.program, t.value "redo Size"
FROM gv$session s, gv$sesstat t
WHERE s.sid = t.sid
AND s.inst_id = t.inst_id
AND t.value != 0
AND t.statistic# = (select statistic# from v$statname where name = 'redo size')
order by t.value desc
/
SELECT s.inst_id, s.sid,osuser,s.SCHEMANAME,s.status ,s.machine,s.service_name Service ,
LOGON_TIME,s.sql_exec_start,s.sql_id,sql_hash_value sql_hash ,
substr(s.module,1,30) module ,name,value
FROM gv$session s,
gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND name in ('redo size', 'redo blocks written')
and s.inst_id = ss.inst_id
and ss.inst_id = sn.inst_id and value > 0
--and osuser ='hhhhhhhhhhh'
ORDER BY s.inst_id,value desc
/
--------------------------------
select *
from gv$sqlarea
where (inst_id,hash_value)
in
(
SELECT s.inst_id ,sql_hash_value
FROM gv$session s, gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND name in ('redo size', 'redo blocks written')
and osuser ='jhhhhhhhhhh'
and s.inst_id = ss.inst_id
and ss.inst_id = sn.inst_id
)
/
-----------------------------------------------------------------------
-- Current Redo generating rate by Instance per second
---------------------------------------------------------------------
set serveroutput on
declare
lv_redo_start number;
lv_redo_end number; x number := 0;
begin
loop x:= x + 1;
select value
into lv_redo_start
from v$sysstat
where name='redo size';
sys.dbms_lock.sleep(1);
select value
into lv_redo_end
from v$sysstat
where name='redo size';
dbms_output.put_line('Redo per second: ' || (lv_redo_end - lv_redo_start) / 5);
exit when x >= 10;
end loop;
end;
/
---------------------------------------------------------------------------
Top UNDO geration Session ( Must login to correct instance in RAC )
--------------------------------------------------------------------------
select a.process, a.program, a.module, a.machine,
b.USED_UREC, c.sql_text
from v$sql c,
v$session a,
v$transaction b
where b.addr = a.taddr
and a.sql_address = c.address
and a.sql_hash_value = c.hash_value
order by b.USED_UREC desc
/
SELECT s.sid , s.username , t.used_ublk
FROM v$transaction t ,
v$session s
WHERE 1 = 1
AND t.ses_addr = s.saddr
order by t.used_ublk desc
/
from gv$sqlarea
where (inst_id,hash_value)
in
(
SELECT s.inst_id ,sql_hash_value
FROM gv$session s, gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND name in ('redo size', 'redo blocks written')
and osuser ='jhhhhhhhhhh'
and s.inst_id = ss.inst_id
and ss.inst_id = sn.inst_id
)
/
declare
lv_redo_start number;
lv_redo_end number; x number := 0;
begin
loop x:= x + 1;
select value
into lv_redo_start
from v$sysstat
where name='redo size';
sys.dbms_lock.sleep(1);
select value
into lv_redo_end
from v$sysstat
where name='redo size';
dbms_output.put_line('Redo per second: ' || (lv_redo_end - lv_redo_start) / 5);
exit when x >= 10;
end loop;
end;
/
b.USED_UREC, c.sql_text
from v$sql c,
v$session a,
v$transaction b
where b.addr = a.taddr
and a.sql_address = c.address
and a.sql_hash_value = c.hash_value
order by b.USED_UREC desc
/
FROM v$transaction t ,
v$session s
WHERE 1 = 1
AND t.ses_addr = s.saddr
order by t.used_ublk desc
/
round((t.used_ublk*16)/1024 ) size_in_MB_16kb_Block_size
FROM v$transaction t ,
v$session s
WHERE 1 = 1
AND t.ses_addr = s.saddr
/
