Friday, January 17, 2014

SQL History of an Oracle Session from ASH

Query help to find out the SQL history of an oracle session from Active Session History(ASH) . Here we can see the historical  Data available from AWR too.

SELECT SQL_ID,PLAN_HASH_VALUE,inst_id, sum(execs) "Execution", round(sum(tot_time)/1000000,2) "Elapsed_time",
(case when sum(execs) > 0 then sum(tot_time)/ sum(execs)/1000000 else 0 end) Average_exe_Second
FROM (SELECT C.SQL_ID ,c.PLAN_HASH_VALUE,b.inst_id,
        c.executions execs ,c.elapsed_time tot_time
 FROM gv$ACTIVE_SESSION_HISTORY A,gv$session b,
        gv$SQLAREA C where
        a.sql_id=c.sql_id and b.sid=a.session_id and b.sql_id=c.sql_id and b.sid=131
    union all
SELECT C.SQL_ID  ,c.PLAN_HASH_VALUE ,b.inst_id ,c.executions_delta Execs,c.elapsed_time_delta tot_time
 from dba_hist_active_sess_history a, gv$session b, dba_hist_sqlstat c where
a.sql_id=c.sql_id and b.sid=a.session_id and b.sql_id=c.sql_id and b.sid=12 )
GROUP BY SQL_ID,PLAN_HASH_VALUE,inst_id;

Thanks,
Jyothish

PGA Allocation for an Oracle Process

Query help to find out how much PGA allocated for an Oracle Process.

SELECT
  s.sid,
  p.spid,
  DECODE(s.program, NULL, p.program, s.program) AS "Program",
  pga_used_mem,
  pga_alloc_mem,
  pga_max_mem
FROM gv$process p, gv$session s
WHERE s.paddr = p.addr
ORDER BY s.sid;

Thanks,
Jyothish

PGA Allocated Memory for an Oracle Session

Below Query help to find out PGA Allocated Memory for an Oracle Session.

select
    to_char(ssn.sid, '9999') || ' – ' || nvl(ssn.username,
    nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) "Session_Details",
    to_char(prc.spid, '999999999')                       pid_thread,
    to_char((se1.value / 1024) / 1024, '999g999g990d00') current_size_mb,
    to_char((se2.value / 1024) / 1024, '999g999g990d00') maximum_size_mb
from
    gv$statname  stat1,
    gv$statname  stat2,
    gv$session   ssn,
    gv$sesstat   se1,
    gv$sesstat   se2,
    gv$bgprocess bgp,
    gv$process   prc,
    gv$instance  ins
where stat1.name = 'session pga memory'
and stat2.name = 'session pga memory max'
and se1.sid = ssn.sid
and se2.sid = ssn.sid
and se2.statistic# = stat2.statistic#
and se1.statistic# = stat1.statistic#
and ssn.paddr = bgp.paddr(+)
and ssn.paddr = prc.addr(+) ;

Thanks,
Jyothish

Sort Area Usage/Temp Tablespace Usage of an Oracle Session

Below query will picture of Temp Usage of an Oracle Session.


set lines 132
set feedback on
set pages 99

select s.osuser, s.sid, s.serial#,s.username, s.program,
s.client_info,(sum(u.blocks)*vp.value/1024/1024/1024) AS GB_USED, a.sql_text as sql_t
from sys.gv_$session s, sys.gv_$sort_usage u, sys.gv_$parameter vp, sys.gv_$sqlarea a, sys.gv_$process b
where
s.saddr = u.session_addr and
vp.name = 'db_block_size' and
a.address = s.sql_address and
b.addr = s.paddr
group by s.osuser, s.sid, s.serial#,s.username, s.program,s.client_info, vp.value, a.sql_text;


Thanks,
Jyothish

CPU usage of an Oracle session


Below query help to  find out the CPU usage(Second) for a  oracle session session.

select
   ss.sql_id "SQL_ID",ss.username,ss.status,ss.machine,b.sql_text,b.PLAN_HASH_VALUE,
   VALUE/100 cpu_usage_seconds
from
   gv$session ss,
   gv$sesstat se,
   gv$statname sn,
   gv$sql b
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID and ss.sql_id=b.sql_id
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc;

Thanks,
Jyothish