top 10 cpu sql


select * from ( select p.spid "ospid",ss.INST_ID, (se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,30) "program", ss.module,ss.osuser,ss.MACHINE,ss.status, se.VALUE/100 cpu_usage_sec from gv$session ss,gv$sesstat se, gv$statname sn,gv$process p where se.STATISTIC# = sn.STATISTIC# and NAME like '%CPU used by this session%' and se.SID = ss.SID and ss.username !='SYS' and ss.status='ACTIVE' and ss.username is not null and ss.paddr=p.addr and value > 0 and ss.INST_ID=3 order by se.VALUE desc);



select rownum, a.* from ( SELECT v.sid,sess.Serial# ,v.INST_ID,sess.SQL_ID,program, v.value / (100 * 60) CPUMins FROM gv$statname s , gv$sesstat v, gv$session sess WHERE s.name = 'CPU used by this session' and sess.sid = v.sid and v.statistic#=s.statistic# and v.value>0 and v.INST_ID=3 ORDER BY v.value DESC) a where rownum < 11;



Memeory Usage



SELECT a.username, a.osuser,a.INST_ID, a.program, b.spid,b.pga_used_mem/1024/1024/1024, a.sid, a.serial# ,a.module,a.logon_time,a.terminal FROM gv$session a, gv$process b WHERE a.paddr = b.addr and a.USERNAME='XXXXXXXXX' order by b.pga_used_mem desc

Tables used for sqlid and its stats

SELECT a.object_owner, a.object_name, a.object_type, b.stale_stats FROM v$sql_plan a JOIN dba_tab_statistics b ON a.object_owner = b.owner AND a.object_name = b.TABLE_NAME WHERE b.object_type = 'TABLE' AND a.sql_id = TRIM ('&1') AND (stale_stats IS NULL OR stale_stats = 'YES') UNION ALL SELECT a.object_owner, a.object_name, a.object_type, b.stale_stats FROM v$sql_plan a JOIN dba_tab_statistics b ON a.object_owner = b.owner AND a.object_name = b.PARTITION_NAME WHERE b.object_type = 'PARTITION' AND a.sql_id = TRIM ('&1') AND (stale_stats IS NULL OR stale_stats = 'YES') UNION ALL SELECT a.object_owner, a.object_name, b.object_type, b.stale_stats FROM v$sql_plan a JOIN dba_ind_statistics b ON a.object_owner = b.owner AND a.object_name = b.index_name WHERE b.object_type!='SUBPARTITION' and a.sql_id = TRIM ('&1') AND (stale_stats IS NULL OR stale_stats = 'YES');

find out a SQL how many times its running in a min and avg response time/cpu time/cluster activity/io time

SET SERVEROUTPUT ON
DECLARE a INT := 0; a1 FLOAT := 0; a2 FLOAT := 0; a3 FLOAT := 0; b INT := 0; b1 FLOAT := 0; b2 FLOAT := 0; b3 FLOAT := 0; c INT := 0; d INT := 1; BEGIN SELECT SUM (executions), SUM (elapsed_time) / 1000, SUM (CONCURRENCY_WAIT_TIME) / 1000, SUM (USER_IO_WAIT_TIME) / 1000 INTO a, a1, a2, a3 FROM gv$sql WHERE sql_id = 'gnf46h863mwh3'; DBMS_LOCK.Sleep (60); SELECT SUM (executions), SUM (elapsed_time) / 1000, SUM (CONCURRENCY_WAIT_TIME) / 1000, SUM (USER_IO_WAIT_TIME) / 1000 INTO b, b1, b2, b3 FROM gv$sql WHERE sql_id = 'gnf46h863mwh3'; DBMS_OUTPUT.put_line ( 'gnf46h863mwh3 .. .. count=' || (b - a) || '..........elasped_time_ms=' || (b1 - a1) / (b - a) || '.......conc_time_ms' || (b2 - a2) / (b - a) || '....userwait_time_ms' || (b3 - a3) / (b - a)); end;

Daily scripts

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 ;

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’;
--- 3600 sec
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’);

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;

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;

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

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’)

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’;

To list count of connections from other machines
select count(1),machine from gv$session where inst_id=’&inst_id’ group by machine;

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;

To find SQL text through SQL address
select sql_address from v$session where sid=11023;

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

To find SQL text for different SQL hash value
select hash_value,sql_text from v$sql where hash_value in ('XXXXXXXXXXX','XXXXXXXXX')
Session details associated with SID and Event waiting for
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;

Active Sessions running for more than 1 hour
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;

SQLs Running from longtime
alter session set nls_date_format = ‘dd/mm/yyyy hh24:mi’; select sid ,opname ,target ,round(sofar/totalwork*100,2) as percent_done ,start_time ,last_update_time ,time_remaining from v$session_longops;

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;
Current Running SQLs
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;
Current Running SQLs
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%’;