Memory monitoring queries

 

Moreover use following query to calculate the memory used by the each session

======================================================================

SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||

nvl(lower(ssn.machine), ins.host_name) "SESSION",

to_char(prc.spid, '999999999') "PID/THREAD",

to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",

to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE"

FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,

v$instance ins, v$statname stat1, v$statname stat2

WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'

AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'

AND se1.sid = ssn.sid

AND se2.sid = ssn.sid

AND ssn.paddr = bgp.paddr (+)

AND ssn.paddr = prc.addr (+);

/

Show SQL Workarea Memory Usage Statistics

===========================================

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

 

COLUMN sql FORMAT A10

COLUMN operation FORMAT A15

COLUMN op_id FORMAT 99

COLUMN policy FORMAT A7

COLUMN estd_opt_size FORMAT 9999

COLUMN estd_one_size FORMAT 9999

COLUMN last_mem_usd FORMAT 9999

COLUMN last_exec FORMAT A9

COLUMN tot_exec FORMAT 9

COLUMN opt_exec FORMAT 9

COLUMN one_exec FORMAT 9

COLUMN multi_exec FORMAT 10

COLUMN sec FORMAT 999

COLUMN max_tmp_sz FORMAT 999

COLUMN last_tmp_sz FORMAT 999

 

SELECT sql_text as sql, 

         operation_type as operation, 

         operation_id as op_id, 

         policy,

         round(estimated_optimal_size/1024/1024,2) as estd_opt_size, 

         round(estimated_onepass_size/1024/1024,2) as estd_one_size,

         round(last_memory_used/1024/1024,2) as last_mem_usd, 

         last_execution as last_exec,

         total_executions as tot_exec, 

         optimal_executions as opt_exec, 

         onepass_executions as one_exec, 

         multipasses_executions as multi_exec,

         round(active_time/1000000,2) as sec, 

         round(max_tempseg_size/1024/1024,2) as max_tmp_sz, 

         round(last_tempseg_size/1024/1024,2) as last_tmp_sz 

from   v$sql_workarea swa, 

         v$sql sq

where swa.address = sq.address 

and    swa.hash_value = sq.hash_value

and    sql_text like '&part_of_sql_statement%' order by sql

/

CPU Usage

=========

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;