High cpu consuming SQL + Sessions Info + Long & Blocking Sessions

How to To Find sessions / SQL’s which consuming High CPU in Oracle Database.

Statspack + AWR + Below quries:
===============================
1) Get the Process ID (PID) from TOP command which consume high CPU Usages.

So the query to get the session details (SID) from OS PID (SPID) will be as per following.

select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid = (PID) ;

3) Once we get the session ID, base on this information we can get the actual SQL statement which is causing
HIGH CPU usage on database server.

We can use the following query to get the actual SQL STATEMENT.

SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE
= (select sql_hash_value from v$session
where SID = (SID_WITCH_CAPTURED_IN_STEP_2) ;


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

--# from below query you will findout sid:-

SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage"
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;

#
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;

#
select sql_hash_value, count(*) from v$session
where status = 'ACTIVE' group by sql_hash_value order by 2 desc;

#
select sql_text,users_executing from v$sql where hash_value = <give hash value here which is out put of above query>;




================== Long Running sessions (>60 mins) ============


LongRunning Session   GV$

set echo off  linesize 200  pages 1000  head on  feedback on
 col username format a10
 col start_time format a15
 col curr_time format a15
 col osuser format a10
 col opname format a10
 col target format a25
 col tremain format 999999.99
 col elamin format 999999.99
 select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,to_char(a.start_time,'dd-mon:hh24:mi:ss') start_time,
 to_char(sysdate,'dd-mon:hh24:mi:ss') curr_time,a.opname,a.target,a.totalwork,a.sofar,(a.elapsed_Seconds)/60 elamin,
 a.time_remaining/60 tremain
 from v$session_longops a,
 v$session b
 where a.totalwork<>a.sofar
 and a.sid=b.sid
 order by 3
 /


 ========>  Exact Query <===========
 set lines 300
 SELECT S.SID,S.SERIAL#,S.USERNAME,S.STATUS, s.sql_id,S.LAST_cALL_ET/60 "Min",S.OSUSER,S.MACHINE
FROM V$SESSION S,V$PROCESS P
WHERE S.PADDR=P.ADDR
AND S.USERNAME IS NOT NULL
AND S.STATUS='ACTIVE'
and S.LAST_cALL_ET/60>60 order by S.LAST_cALL_ET/60;

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

 select sql_text from v$sqlarea where SQL_ID='&sqlid';

 *******
 alter system kill session '1421,14693';

=========RAMN LONG RUNNING =======

  select b.sid, b.serial#, a.spid, b.client_info,b.LOGON_TIME from v$process a, v$session b  where a.addr=b.paddr and client_info  like 'rman%';

=====> SQL TEXT FROM SQL ID ::

 set lines 300
 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=487
 order by a.sid,hash_value,piece;

 SQL> select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='76';

  select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR ,WAIT_TIME from v$session where sid='344';

SQL>  select SQL_ID,sql_text from v$sqltext where address='00000004EB782658';




*************** Sessions Info ***********
No.of connections:
 =================
 show parameter sessions

 SQL> SELECT COUNT(*) FROM v$session WHERE STATUS = 'ACTIVE';

 >select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');

 > select count(*),sum(decode(status, 'ACTIVE',1,0)) from v$session where type= 'USER'

######################## BLOCKING SESSIONS ########################

   col WAIT_CLASS for a20
 select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session where blocking_session is not NULL order by blocking_session ;

 Select (select username from v$session where sid=a.sid) blocker,
a.sid,' is blocking ',(select username from v$session where sid=b.sid) blockee,b
.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;

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