Script to find archivelog generation per hour

set pagesize 120;
set linesize 200;
col day for a8;
spool archivelog.lst

PROMPT Archive log distribution per hours on each day …
  

select
  to_char(first_time,’YY-MM-DD’) day,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’00’,1,0)),’999′) “00”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’01’,1,0)),’999′) “01”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’02’,1,0)),’999′) “02”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’03’,1,0)),’999′) “03”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’04’,1,0)),’999′) “04”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’05’,1,0)),’999′) “05”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’06’,1,0)),’999′) “06”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’07’,1,0)),’999′) “07”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’08’,1,0)),’999′) “08”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’09’,1,0)),’999′) “09”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’10’,1,0)),’999′) “10”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’11’,1,0)),’999′) “11”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’12’,1,0)),’999′) “12”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’13’,1,0)),’999′) “13”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’14’,1,0)),’999′) “14”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’15’,1,0)),’999′) “15”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’16’,1,0)),’999′) “16”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’17’,1,0)),’999′) “17”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’18’,1,0)),’999′) “18”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’19’,1,0)),’999′) “19”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’20’,1,0)),’999′) “20”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’21’,1,0)),’999′) “21”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’22’,1,0)),’999′) “22”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’23’,1,0)),’999′) “23”,
  COUNT(*) TOT
from v$log_history
group by to_char(first_time,’YY-MM-DD’)
order by day ;

Script to identify segments generating redologs

SELECT to_char(begin_interval_time,’YY-MM-DD HH24′) snap_time, 
        dhso.object_name, 
        sum(db_block_changes_delta) BLOCK_CHANGED 
  FROM dba_hist_seg_stat dhss, 
       dba_hist_seg_stat_obj dhso, 
       dba_hist_snapshot dhs 
  WHERE dhs.snap_id = dhss.snap_id 
    AND dhs.instance_number = dhss.instance_number 
    AND dhss.obj# = dhso.obj# 
    AND dhss.dataobj# = dhso.dataobj# 
    AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)  
                                AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’) 
  GROUP BY to_char(begin_interval_time,’YY-MM-DD HH24′), 
           dhso.object_name 
  HAVING sum(db_block_changes_delta) > 0 
ORDER BY sum(db_block_changes_delta) desc ;

Script to know which SQL’s are generating redo

SELECT when, sql, SUM(sx) executions, sum (sd) rows_processed 
FROM ( 
      SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24′) when, 
             dbms_lob.substr(sql_text,4000,1) sql, 
             dhss.instance_number inst_id, 
             dhss.sql_id, 
             sum(executions_delta) exec_delta, 
             sum(rows_processed_delta) rows_proc_delta 
        FROM dba_hist_sqlstat dhss, 
             dba_hist_snapshot dhs, 
             dba_hist_sqltext dhst 
        WHERE upper(dhst.sql_text) LIKE ‘%Z_PLACENO%’ 
          AND ltrim(upper(dhst.sql_text)) NOT LIKE ‘SELECT%’
          AND dhss.snap_id=dhs.snap_id 
          AND dhss.instance_Number=dhs.instance_number 
          AND dhss.sql_id = dhst.sql_id  
          AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)  
                                      AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’) 
        GROUP BY to_char(begin_interval_time,’YYYY_MM_DD HH24′), 
            dbms_lob.substr(sql_text,4000,1), 
              dhss.instance_number, 
             dhss.sql_id 
) 
group by when, sql;

Script to find PGA memory allocation to BG processes

*********************************************

PGA Memory allocation to background process

*********************************************

SELECT spid, program,

pga_max_mem max,

pga_alloc_mem alloc,

pga_used_mem used,

pga_freeable_mem free

FROM V$PROCESS

WHERE spid = 2587;

Script to find memory usage by BG processes

**************************************

Memory usage for backgroung processes

**************************************

SELECT p.program,

p.spid,

pm.category,

pm.allocated,

pm.used,

pm.max_allocated

FROM V$PROCESS p, V$PROCESS_MEMORY pm

WHERE p.pid = pm.pid

AND p.spid = 2587;

Script to show active distributed tx’s in database

***************************************

script to show active distributed tx’s

***************************************

REM distri.sql

column origin format a13

column GTXID format a35

column LSESSION format a10

column s format a1

column waiting format a15

Select /*+ ORDERED */

substr(s.ksusemnm,1,10)||’-‘|| substr(s.ksusepid,1,10) “ORIGIN”,

substr(g.K2GTITID_ORA,1,35) “GTXID”,

substr(s.indx,1,4)||’.’|| substr(s.ksuseser,1,5) “LSESSION” ,

substr(decode(bitand(ksuseidl,11),

1,’ACTIVE’,

0, decode(bitand(ksuseflg,4096),0,’INACTIVE’,’CACHED’),

2,’SNIPED’,

3,’SNIPED’, ‘KILLED’),1,1) “S”,

substr(event,1,10) “WAITING”

from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w

— where g.K2GTeXCB =t.ktcxbxba <= use this if running in Oracle7

where g.K2GTDXCB =t.ktcxbxba — comment out if running in Oracle8 or later

and g.K2GTDSES=t.ktcxbses

and s.addr=g.K2GTDSES

and w.sid=s.indx;

REM distri_details.sql

set headin off

select /*+ ORDERED */

‘—————————————-‘||’

Curent Time : ‘|| substr(to_char(sysdate,’dd-Mon-YYYY HH24.MI.SS’),1,22) ||’

‘||’GTXID=’||substr(g.K2GTITID_EXT,1,10) ||’

‘||’Ascii GTXID=’||g.K2GTITID_ORA ||’

‘||’Branch= ‘||g.K2GTIBID ||’

Client Process ID is ‘|| substr(s.ksusepid,1,10)||’

running in machine : ‘||substr(s.ksusemnm,1,80)||’

Local TX Id =’||substr(t.KXIDUSN||’.’||t.kXIDSLT||’.’||t.kXIDSQN,1,10) ||’

Local Session SID.SERIAL =’||substr(s.indx,1,4)||’.’|| s.ksuseser ||’

is : ‘||decode(bitand(ksuseidl,11),1,’ACTIVE’,0,

decode(bitand(ksuseflg,4096),0,’INACTIVE’,’CACHED’),

2,’SNIPED’,3,’SNIPED’, ‘KILLED’) ||

‘ and ‘|| substr(STATE,1,9)||

‘ since ‘|| to_char(SECONDS_IN_WAIT,’9999′)||’ seconds’ ||’

Wait Event is :’||’

‘|| substr(event,1,30)||’ ‘||p1text||’=’||p1

||’,’||p2text||’=’||p2

||’,’||p3text||’=’||p3 ||’

Waited ‘||to_char(SEQ#,’99999′)||’ times ‘||’

Server for this session:’ ||decode(s.ksspatyp,1,’Dedicated Server’,

2,’Shared Server’,3,

‘PSE’,’None’) “Server”

from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w

— where g.K2GTeXCB =t.ktcxbxba <= use this if running Oracle7

where g.K2GTDXCB =t.ktcxbxba — comment out if running Oracle8 or later

and g.K2GTDSES=t.ktcxbses

and s.addr=g.K2GTDSES

and w.sid=s.indx;

set headin on

— end script

Query to find Active SQL’s in database

set feedback off

set serveroutput on size 9999

column username format a20

column sql_text format a55 word_wrapped

begin

for x in

(select username||'(‘||sid||’,’||serial#||’) ospid = ‘|| process ||

‘ program = ‘ || program username,

to_char(LOGON_TIME,’ Day HH24:MI’) logon_time,

to_char(sysdate,’ Day HH24:MI’) current_time,

sql_address,

sql_hash_value

from v$session

where status = ‘ACTIVE’

and rawtohex(sql_address) <> ’00’

and username is not null ) loop

for y in (select sql_text

from v$sqlarea

where address = x.sql_address ) loop

if ( y.sql_text not like ‘%listener.get_cmd%’ and

y.sql_text not like ‘%RAWTOHEX(SQL_ADDRESS)%’ ) then

dbms_output.put_line( ‘——————–‘ );

dbms_output.put_line( x.username );

dbms_output.put_line( x.logon_time || ‘ ‘ || x.current_time || ‘ SQL#=’ || x.sql_hash_value);

dbms_output.put_line( substr( y.sql_text, 1, 250 ) );

end if;

end loop;

end loop;

end;

/