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

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

 

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

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;

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

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;

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

To know used blocks information

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

set serveroutput on

 

declare

TOTAL_BLOCKS number;

TOTAL_BYTES number;

UNUSED_BLOCKS number;

UNUSED_BYTES number;

LAST_USED_EXTENT_FILE_ID number;

LAST_USED_EXTENT_BLOCK_ID number;

LAST_USED_BLOCK number;

 

begin dbms_space.unused_space(‘SYSTEM’, ‘TEST’, ‘TABLE’,

TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,

LAST_USED_EXTxENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,

LAST_USED_BLOCK);

 

dbms_output.put_line(‘OBJECT_NAME = CUSTOMER’);

dbms_output.put_line(‘———————-’);

dbms_output.put_line(‘TOTAL_BLOCKS = ||TOTAL_BLOCKS);

dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);

end;

/

 

 

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

Keep and Recycle cache advices

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

SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS

FROM V$DB_CACHE_ADVICE

WHERE NAME = ‘KEEP’

AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘db_block_size’)

AND ADVICE_STATUS = ‘ON’;

 

 

SELECT ds.BUFFER_POOL,

Substr(do.object_name,1,9) object_name,

ds.blocks object_blocks,

Count(* ) cached_blocks

FROM dba_objects do,

dba_segments ds,

v$bh v

WHERE do.data_object_id = v.objd

AND do.owner = ds.owner (+)

AND do.object_name = ds.segment_name (+)

AND do.object_type = ds.segment_type (+)

AND ds.BUFFER_POOL IN (‘KEEP’,'RECYCLE’)

GROUP BY ds.BUFFER_POOL,

do.object_name,

ds.blocks

ORDER BY do.object_name,

ds.BUFFER_POOL;