Posted by Pavan DBA on March 30, 2012
#################################################
redo generated by current sessions
#################################################
select v$session.sid, username, value redo_size
from v$sesstat, v$statname, v$session
where v$sesstat.STATISTIC# = v$statname.STATISTIC#
and v$session.sid = v$sesstat.sid
and name = ‘redo size’
and value > 0
and username is not null
order by value
/
Posted in Performance Tuning, Scripts | Tagged: redo generated by sessions, script to find redo generated, script to find redo generated by current sessions | 10 Comments »
Posted by Pavan DBA on March 30, 2012
#######################
TO FIND SQL TEXT FROM SID
#######################
select a.sid,a.program,b.sql_text
from v$session a, v$sqltext b
where a.sql_hash_value = b.hash_value
and a.sid=429
order by a.sid,hash_value,piece;
Posted in Performance Tuning, Scripts | Tagged: how to find sql, how to find sql text, how to find sql text with sid, how to find sql with sid, script to find sql text with sid of a session, script to get sql for a session, script to get sql for a sid, script to get sql text, script to get sql text for a session | Leave a Comment »
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: analyze script, schema analyze script, script to analyze schema, script to analyze schema in unix | Leave a Comment »
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: how to find active sql statemetns, query to find active sql's, script to find active sql in database | Leave a Comment »
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 »
