Daily Scripts – Part 1

Below some useful scripts to help on daily tasks.
--- Wait Events:
 col event format a80;
 select count(1), decode(event,'db file scattered read',event || ' - **** FULL TABLE SCAN ****',EVENT) Event
 from gv$session_wait
 group by event
 order by 1 desc;
-- Wait Events per user:
 select distinct a.inst_id,
 B.EVENT, d.spid OSPID, A.SID, A.SERIAL#, A.USERNAME, A.STATUS, A.MACHINE, A.PROGRAM, A.MODULE, c.SQL_TEXT, c.address, b.p1,b.p1raw, b.p2, b.p2raw, b.p3, round((a.last_call_et / 60),2) Minutos_Executando
 from gv$session A, gV$SESSION_WAIT B, gv$sql c, gv$process d
 where
 A.SID=B.sid AND
 b.EVENT = '&Event' and
 a.SQL_ADDRESS=c.address(+) and
 a.paddr = d.addr
 order by username, machine;
-- Session per IO
 SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
 FROM v$session s, v$sess_io i
 WHERE s.sid = i.sid
 ORDER BY 5 desc ;
SELECT s.sid, s.serial#, s.username, s.program, i.block_changes, c.object, c.type, e.statistic_name, e.value
 FROM v$session s, v$sess_io i, v$access c, dba_objects d, v$segment_statistics e
 WHERE s.sid = i.sid and
 i.sid=c.sid and i.sid=&sid and
 c.object=d.object_name and
 c.type=d.object_type and
 e.obj#=d.OBJECT_ID and
 e.value>0
 ORDER BY 9 desc;
-- Latches
 select distinct a.inst_id,
 B.EVENT, d.name "Tipo do Latch", A.SID, A.SERIAL#, A.USERNAME, A.STATUS, A.MACHINE, A.PROGRAM, A.MODULE,
 B.P1, B.P2, c.SQL_TEXT, c.address,
 round(a.last_call_et/(60),0) Minutes
 from gv$session A, gV$SESSION_WAIT B, gv$sql c, gV$LATCH D
 where
 A.SID=B.sid AND
 b.EVENT like '%latch%' and
 a.SQL_ADDRESS=c.address(+) AND
 b.p2=d.LATCH#
 order by username, machine;
-- Free Space on Tablespaces:
 select a.tablespace_name, round((a.free_mb / b.df_size) * 100,2) free_perc , a.free_mb, b.df_size
 from
 (
 SELECT ROUND(SUM(BYTES) / (1024*1024),0) free_mb, TABLESPACE_NAME
 FROM DBA_FREE_SPACE
 GROUP BY TABLESPACE_NAME
 ORDER BY 1 ASC, 2 ASC
 ) a,
 (SELECT ROUND(SUM(BYTES) / (1024*1024),0) df_size, TABLESPACE_NAME
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME
 ) b
 where a.tablespace_name = b.tablespace_name
 order by 2 asc
 /
--Archives
 select count(*), round(sum(blocks*block_size)/ (1024*1024),0)  size_MB, to_char(first_time,'hh24') HOUR
 from v$archived_log
 where trunc(first_time)=trunc(sysdate) and
 dest_id=1
 group by to_char(first_time,'hh24')
 order by 3;
select thread#, max(sequence#)
 from gv$archived_log
 where trunc(first_time)=trunc(sysdate)
 group by thread#
 /
--Archive MAP
 set pages 200
 set lines 199
 set linesize 132
 alter session set nls_date_format='dd-mon-rr';
 select * from (
 SELECT TO_DATE(first_time,'dd-mon-rr') day,
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
 FROM gV$LOG_HISTORY
 where thread# = inst_id
 GROUP BY   to_Date(first_time,'dd-mon-rr')
 order by 1 desc)
 where rownum < 25;
-- Background Process
 select vb.name NOME, vp.program PROCESSNAME,
 vp.spid THREADID, vs.sid SID
 from v$session vs, v$process vp, v$bgprocess vb
 where vb.paddr <> '00' and
 vb.paddr = vp.addr and
 vp.addr = vs.paddr;
--MySession
 select username, sid, serial#, program, osuser, paddr, status
 from v$session
 where audsid
 in
 (select userenv('SESSIONID')  from dual);
--Checking PSU applied
 COLUMN   ACTION FORMAT   a20;
 COLUMN   NAMESPACE FORMAT   a11;
 COLUMN   COMMENTS FORMAT   a40;
 COLUMN   ACTION_TIME FORMAT   a30;
 COLUMN   BUNDLE_SERIES FORMAT   a30;
 COLUMN   VERSION FORMAT   a15;
prompt "Execute as SYS"
 select * from registry$history;
--Redo Information
 col member format a50
select a.thread#, a.group#, b.member,  round(bytes/1024,0) kb, a.members, a.status, b.status ST_LogFile
 from v$log a, v$logfile b
 where
 a.group#=b.group#
 order by 1,2;
SELECT name, value
 FROM v$sysstat
 WHERE name = 'redo log space requests';
prompt "Contention if > 1%";
 SELECT
 substr(ln.name, 1, 20), gets, misses, immediate_gets, immediate_misses ,
 round((misses / gets) * 100,2) taxa,
 round(immediate_misses / decode((immediate_gets+immediate_misses),0,1,(immediate_gets+immediate_misses)),2) taxa_immediate
 FROM v$latch l, v$latchname ln
 WHERE ln.name in ('redo allocation', 'redo copy')
 and ln.latch# = l.latch#;
--Active Sessions
 select distinct a.inst_id, a.server, c.spid OSPID, a.sid,a.serial#, a.username, a.osuser, d.event, a.machine, a.program, a.module, b.sql_text, b.address, round(a.last_call_et / 60) minutes
 from
 gv$session a, gv$sql b, gv$process c, gv$session_wait d
 where a.status = 'ACTIVE' and
 a.sql_address=b.address and
 a.inst_id=b.inst_id and
 a.paddr = c.addr and
 a.inst_id=c.inst_id and
 a.sid=d.sid(+) and
 a.inst_id=d.inst_id(+)
 /
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s