Simple Performance tuning for freshers – Easy steps

1. To list long-running forms user sessions

select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ and
p.addr=s.paddr ;

2. To list inactive Sessions respective username

SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS=’INACTIVE’
group by username
order by num_inv_sess DESC;

SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS=’INACTIVE’;

SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS=’INACTIVE’;

3. To find session-id with a set of SPIDs

select sid from v$session, v$process where addr=paddr and spid in (‘11555′,’26265′,’11533’);

4. To find SQL Text given SQLHASH & SQLADDR

select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS =’&addr’ order by piece;
select piece,sql_text from v$sqltext where ADDRESS =’&addr’ order by piece;

5.Checking for Active Transactions SID

select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;

6. Session details from Session longops

select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;

7. Session details with SPID

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,’yyyy-mm-dd hh24:mi:ss’)
from v$session where paddr in (select addr from v$process where spid = ‘&spid’)
/

8. To find Undo Generated For a given session

select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid=’&sid’;

9. To list the count of connections from other machines

select count(1),machine from gv$session where inst_id=’&inst_id’ group by machine;

10. To get the total count of sessions and processes

select count(*) from v$session;

select count(*) from v$process;

select (select count() from v$session) sessions, (select count() from v$process) processes from dual;

11. To find SQL text through SQL address

select sql_address from v$session where sid=1999;

select sql_text from v$sqltext where ADDRESS=’C00000027FF00AF0′ order by PIECE;

12. To find SQL text for different SQL hash value

select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)

13. Session details associated with SID and Event waiting for

set pages 50000 lines 32767
col EVENT for a40

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, ‘dd-mon-yy hh24:mi’) LOGON_TIME,to_char(Sysdate, ‘dd-mon-yy-hh24:mi’) CURRENT_TIME, (a.last_call_et/3600) “Hrs connected” from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;

14. Active Sessions running for more than 1 hour

set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40

SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,’dd-mm-yy hh:mi:ss AM’)”Logon Time”,
ROUND((SYSDATE-LOGON_TIME)(2460),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS=’ACTIVE’
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)(2460),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;

15.SQLs Running from longtime

alter session set nls_date_format = ‘dd/mm/yyyy hh24:mi’;
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
,opname
,target
,round(sofar/totalwork*100,2) as percent_done
,start_time
,last_update_time
,time_remaining
from v$session_longops;

16. Last/Latest Running SQL

set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc;

17. Current Running SQLs

set pages 50000 lines 32767
col program format a20
col sql_text format a50

select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id;

18. Current Running SQLs

set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,’DD-MON-RR HH24:MI’) login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
— and S.status=’ACTIVE’
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like ‘select S.USERNAME,S.seconds_in_wait%’;

Please find out all of our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8