About Developer Oracle 11g Technology Information Sitemap

Oracle Database CPU and PGA Analysis
-- Oracle database PGA (Program Global Area) analysis query 
SELECT sn.name, ms.value
FROM  v$statname sn INNER JOIN v$mystat ms 
ON  sn.statistic# = ms.statistic#
WHERE INSTR(sn.name,'pga')>0;

=> Query Output (11gXEr2)

-- Active sessions details
SELECT  s.sid,   --s.username, s.osuser,
 CAST(s.p1||'='||s.p1text AS VARCHAR2(20)) p1_text,
 CAST(s.p2||'='||s.p2text AS VARCHAR2(20)) p2_text,
 CAST(s.p3||'='||s.p3text AS VARCHAR2(20)) p3_text,
 s.last_call_et,  --t.sql_text,
FROM  v$session s INNER JOIN v$sqltext t
ON  s.sql_address = t.address
WHERE  s.status = 'ACTIVE'
AND    s.username IS NOT NULL;

=> Query (v$session/v$sqltext) Output

-- CPU usage data query
SELECT * FROM v$sysmetric_summary
WHERE  INSTR(lower(metric_name),'usage') >0;

=> Query (v$sysmetric_summary) Output

-- CPU usage data query for specific dates
SELECT * FROM dba_hist_sysmetric_summary
WHERE  INSTR(lower(metric_name),'cpu') >0
AND    begin_time > SYSDATE-1;

Oracle 11gXEr2 - Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: March 09, 2015