select * from locked_objects_info_vu where (owner like 'OA_CH_%' OR owner like 'OA_VIS_%')
--and blocking_session is not null
order by object_name, sid_serial#
/
select * from blocked_objects_hist
where trunc(crtd_dt)=trunc(sysdate)
/
select to_char(logon_time, 'DD-MON-YYYY HH24:MI:SS') from v$session where sid||','||serial# = '1905,30488'
/
alter system kill session '1905,30488'
/
/
SELECT * FROM V$SQL
WHERE SQL_ID = '6t4ds9ggj005u'
/
SELECT TRUNC(CREATION_DATE), COUNT(*) FROM PS_TXN GROUP BY TRUNC(CREATION_DATE)
/
sELECT * FROM PS_TXN
-- check resources
/
select * from v$resource_limit
/
select to_char(crtd_dt,'yyyymmdd hh24:mi:ss') dtime, current_utilization, max_utilization, limit_value
from v$resource_limit_test
where resource_name ='processes'
and crtd_dt>trunc(sysdate)
order by current_utilization desc,crtd_dt desc
/
-- Check TOP_SESSIONS by CPU Usage
Select * from TOP_SESSIONS
WHERE username like 'OA_%'
and status ='ACTIVE'
--and sql_id is not null
/
-- Check for BLOCKING_SESSION
select * from cpu_usage_vu
/
select * from locked_objects_info_vu
--where BLOCKING_SESSION is not null
order by SECONDS_IN_WAIT desc
/
select * from v$session
where status='ACTIVE'
/
-- KILL SESSION
--alter system kill session ' ' immediate
--/
-- Check TOP_SQLS BIG per execution time
SELECT * FROM TOP_SQLS
WHERE MODULE like 'JDBC%' --w3wp
AND per_exec > 3 -- >2SECS
--and LAST_LOAD_TIME>trunc(sysdate)
ORDER BY per_exec desc --executions
/
-- Check TOP_SQLS by execution time
SELECT * FROM TOP_SQLS
WHERE MODULE like 'JDBC%'
AND per_exec > 0.5 -- >2SECS
--and sql_text like '%FIRST_ROWS%'
--and executions>9
ORDER BY executions desc --
/
select * from dba_constraints
where constraint_name ='SYS_C0025355'
/
select * from DBA_CONS_COLUMNS
where constraint_name ='SYS_C0025355'
/
SELECT sql_fulltext FROM v$sqlarea
WHERE sql_id ='g6px76dmjv1jy'
/
select * from dba_hist_sqltext
/
-- accessed between a given time
select function_desc,Count(*)
from FUNCTION_HIST f, log_hist l
where f.log_id=l.log_id
and l.accessed_link like '%7011'
--and l.accessed_link like '%7013'
--and to_date(Substr(start_time,1,11),'dd-Mon-yyyy')=trunc(sysdate)
and to_date(start_time,'dd-Mon-yyyy hh24:mi;ss')> sysdate-5/1440 -- 5 min
--and to_date(start_time,'dd-Mon-yyyy hh24:mi;ss') between to_date('20-jul-2020 18:30:00','dd-mon-yyyy hh24:mi:ss') and to_date('20-jul-2020 19:20:00','dd-mon-yyyy hh24:mi:ss')
group by function_id,function_desc
order by 2 desc
/
select to_char(sysdate-5/1440,'dd-mon-yyyy hh24:mi:ss') dt from dual
/
-- total active users connected on each port
select accessed_link,count(*)
from (
select distinct user_id,accessed_link from log_hist
where to_date(log_in_time,'dd-Mon-yyyy hh24:mi:ss') >= trunc(sysdate)
and user_id>0
and log_out_time is null -- enable for today active users
)
group by accessed_link
order by 2 desc
/
-- pga
select name, value/1024/1024 in_mb from v$pgastat
where name in (
'aggregate PGA auto target',
'maximum PGA allocated',
'total PGA allocated',
'total PGA inuse',
'total freeable PGA memory'
)
/
-- Temporary tablespace free
SELECT TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024/1024 "TBS Size(GB)" ,
ALLOCATED_SPACE/1024/1024/1024"Used Size(GB)",FREE_SPACE/1024/1024/1024" Free Size(GB)"
FROM dba_temp_free_space
/
select tablespace_name , (free_blocks*8)/1024/1024 FreeSpaceInGB,
(used_blocks*8)/1024/1024 UsedSpaceInGB,
(total_blocks*8)/1024/1024 TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'
/
-- undo tablespace free -- takes around 29/50 secs
select a.tablespace_name, SIZEGB, USAGEGB, (SIZEGB - USAGEGB) FREEGB
from (select sum(bytes) / 1024 / 1024 /1024 SIZEGB, b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
group by b.tablespace_name) a,
(select c.tablespace_name, sum(bytes) / 1024 / 1024 /1024 USAGEGB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name
/
-- total memory sizes
SELECT component, current_size, min_size, max_size
FROM v$memory_dynamic_components
WHERE current_size != 0
/
-- Active sessions PGA used
select s.inst_id, s.sid,s.username,s.status, s.logon_time, s.program, PGA_USED_MEM/1024/1024 PGA_USED_MEM, PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MEM, s.sql_id
from gv$session s
, gv$process p
Where s.paddr = p.addr
and s.inst_id = p.inst_id
and s.status ='ACTIVE'
and PGA_USED_MEM/1024/1024 > 20 -- pga_used memory over 20mb
order by PGA_USED_MEM desc
/
-- session wise pga used
select total_size/1024/1024 PGA_GB,awr_flush_emergency_count from v$ash_info;
/
-- uga & Pga Memmory
select name, sum(value/1024/1024) "Value - MB"
from v$statname n,
v$session s,
v$sesstat t
where s.sid=t.sid
and n.statistic# = t.statistic#
and s.type = 'USER'
and s.username is not NULL
and n.name in ('session pga memory', 'session pga memory max',
'session uga memory', 'session uga memory max')
group by name
/
/
select * from locked_objects_info_vu
/
-- pga advisor
select pga_target_for_estimate, pga_target_factor, estd_time
from v$pga_target_advice
/
-- SID wise PGA used
SELECT SID,SUM(VAL) VALINMB FROM (
select sid,name,value/1024/1024 VAL
from v$statname n,v$sesstat s
where n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%max')
GROUP BY SID
order by 2 desc
/
No comments:
Post a Comment