Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Performance Tuning’ Category

Script to Analyze schema for Unix (but with analyze command)

Posted by Pavan DBA on March 8, 2012


######################

ANALYZE SCHEMAS IN UNIX

#######################

#!/bin/ksh

ORACLE_SID=ORVIT8QA

export ORACLE_SID

ORACLE_HOME=/d001/oracle/9.2.0.8-64

export ORACLE_HOME

export EXPORT_FILE

LD_LIBRARY_PATH=/d001/oracle/9.2.0.8-64/bin:/d001/oracle/9.2.0.8-64/network/lib:/usr/openwin/lib:/usr/dt/lib

export LD_LIBRARY_PATH

PATH=/d001/oracle/9.2.0.8-64/bin:/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:/usr/local/bin:/usr/ucb:/usr/openwin/bin:

export PATH

SQL_DIR=/d002/oracle/$ORACLE_SID/sql

export SQL_DIR

LOG_FILE=$SQL_DIR/gen_analyze_tables.log

export LOG_FILE

SQL_FILE=$SQL_DIR/gen_analyze_tables.sql

export SQL_FILE

 

rm $LOG_FILE

 

gen_analyze_tables.sql Script

______________________________

set termout off

set echo off

set feedback off

set heading off

set linesize 150

set pagesize 0

set space 0

spool analyze_tables.sql

select

‘analyze table ‘||owner||’.’||table_name||’ compute statistics;’

from dba_tables

where owner not like ‘SYS%’ and owner <> ‘MASTER_LOOKUP’ and owner <> ‘PRICING’

and table_name <> ‘MANAGETAX’

order by owner, table_name;

spool analyze_tables.log

@analyze_tables.sql

spool off

exit

Posted in Performance Tuning, Scripts | Tagged: , , , | Leave a Comment »

Query to find Active SQL’s in database

Posted by Pavan DBA on March 8, 2012


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;

/

Posted in Performance Tuning, Scripts | Tagged: , , | Leave a Comment »

Script to show active distributed tx’s in database

Posted by Pavan DBA on February 23, 2012


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

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

 

Posted in Performance Tuning, Scripts | Leave a Comment »

Script to find memory usage by BG processes

Posted by Pavan DBA on February 23, 2012


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

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;

Posted in Performance Tuning, Scripts | Leave a Comment »

Script to find PGA memory allocation to BG processes

Posted by Pavan DBA on February 23, 2012


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

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;

Posted in Performance Tuning, Scripts | Leave a Comment »

 
%d bloggers like this: