Performance Tuning Scripts for Oracle Database/Application

1. Script to find memory usage by INACTIVE Session

SELECT sess.USERNAME, value/(1024*1024) "Current session memory MB", sess.sid,sess.status,p.spid
 FROM v$session sess, v$sesstat stat, v$statname name,v$process p  WHERE sess.sid = stat.sid 
 AND sess.PADDR=p.ADDR
 AND stat.statistic# = name.statistic#
 AND name.name like '%memory%'
 AND sess.USERNAME = 'APPS'
 AND sess.STATUS='INACTIVE'
order by 2,4 asc
/
================================================

2. script to check INACTIVE sessions with HIGH DISK IO

select p.spid,s.username, s.sid,s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,s.machine cli_mach,s.process cli_process,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 5000
and s.status='INACTIVE'
--and s.process='1234'
order by S.PROGRAM;
============================================

3. Script to find which session or sql is having more processes

select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
–       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
 where
b.paddr = a.addr
and type=’USER’
order by spid;

===========================================

4. Query to find Active SQL’s in database

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;

/

============================================

5. Script to find locked objects in database

select  /*+ RULE */nvl(S.USERNAME,'Internal') username,

        nvl(S.TERMINAL,'None') terminal,

        L.SID||','||S.SERIAL# Kill,

        U1.NAME||'.'||substr(T1.NAME,1,20) tab,

        decode(L.LMODE,1,'No Lock',

                2,'Row Share',

                3,'Row Exclusive',

                4,'Share',

                5,'Share Row Exclusive',

                6,'Exclusive',null) lmode,

        decode(L.REQUEST,1,'No Lock',

                2,'Row Share',

                3,'Row Exclusive',

                4,'Share',

                5,'Share Row Exclusive',

                6,'Exclusive',null) request

from    V$LOCK L,

        V$SESSION S,

        SYS.USER$ U1,

        SYS.OBJ$ T1

where   L.SID = S.SID

and     T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)

and     U1.USER# = T1.OWNER#

and     S.TYPE != 'BACKGROUND'

order by 1,2,5;

=============================================

6. Query to find list of inactive forms session

set heading on

set line 9999

set pages 9999

col status for a8

col prog for a10

col username for a8

col module for a15

select p.sid,p.serial#, p.status,p.username,p.action,p.module,p.logon_time

from

(select distinct b.sid,b.serial# ,b.status,b.program,b.username,b.action,b.module,

to_char( b.logon_time, 'dd-MON-yyyy hh24:mi:ss' ) logon_time,

trunc( sysdate-b.logon_time ) "Dy",

trunc( mod( (sysdate-b.logon_time)*24, 24 ) )  "Hr",

trunc( mod( (sysdate-b.logon_time)*24*60, 60 ) )  "Mi",

trunc( mod( (sysdate-b.logon_time)*24*60*60, 60 ) ) "Sec"

from V$access a,v$session b, v$process c

where a.sid=b.sid

and b.paddr=c.addr

and b.status='INACTIVE'

and (b.action like '%FRM%' or b.action like '%frm%' or b.program like '%TOAD%' or b.program like '%toad%' or b.program like

'SQL%' or b.program like '%sql%' or b.program like '%FRM%'

or b.program like '%frm%' or b.action like 'SQL%' or b.action like 'sql%' or b.action like 'TOAD%' or b.action like 'toad%')

and (trunc( mod( (sysdate-b.logon_time)*24,24)) >=12 or trunc( sysdate-b.logon_time )>=1)) p order by p.logon_time;

===============================================

7. Script to check free space in tablespace

select a.TABLESPACE_NAME,

       round(a.bytes_used/(1024*1024*1024),2) TOTAL_SPACE_IN_GB,

       round(b.bytes_free/(1024*1024*1024),2) FREE_SPACE_IN_GB,

       round(b.smallest/(1024*1024*1024),2) min_size_in_GB,

       round(b.largest/(1024*1024*1024),2) max_size_in_GB,

       round(((a.bytes_used-b.bytes_free)/a.bytes_used)*100,2) percent_used

  from

  (

  select TABLESPACE_NAME, sum(bytes) bytes_used

    from dba_data_files

   group by TABLESPACE_NAME

  ) a,

  (

  select TABLESPACE_NAME, sum(BYTES) bytes_free, min(BYTES) smallest, max(BYTES) largest

    from dba_free_space

   group by TABLESPACE_NAME

  ) b

 where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)

   and a.tablespace_name = decode('&tablespace_name',null,a.tablespace_name,'&tablespace_name')

 order by ((a.BYTES_used-b.BYTES_free)/a.BYTES_used) desc;

=============================================

8. Script to check RUNNING Query in database

select sesion.sid,

 sesion.username,

 optimizer_mode,

 hash_value,

 address,

 cpu_time,

 elapsed_time,

 sql_text

 from v$sqlarea sqlarea, v$session sesion

where sesion.sql_hash_value = sqlarea.hash_value

 and sesion.sql_address = sqlarea.address

 and sesion.username is not null

/

============================================

9. Get the values of Row Fetched, if its chagning then it means query is processing

select b.name, a.value vlu

from v$sesstat a, v$statname b

where a.statistic# = b.statistic#

and sid =&sid

and a.value != 0

and b.name like '%row%'

/

===========================================

10. Script to find number of active users in R12 application

select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions from icx_sessions icx where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < limit_connects; 
============================================