Memory
==========================
How much memory is Oracle using
SELECT DECODE( grouping(nm), 1, 'total', nm ) MEMORY_AREA, round(sum(val/1024/1024)) mb
FROM
(
SELECT 'sga' nm, SUM(value) val
FROM V$SGA
UNION ALL
SELECT 'pga', SUM(SESSTAT.value)
FROM V$SESSTAT SESSTAT,
V$STATNAME STATNAME
WHERE STATNAME.name = 'session pga memory'
AND SESSTAT.statistic# = STATNAME.statistic#
)
GROUP BY ROLLUP(nm);
MEMORY_AREA MB
----------- ----------
pga 122
sga 2039
total 2161
============================
Memory usage per Oracle pool
============================
SELECT * FROM
V$SGASTAT
ORDER BY bytes DESC
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 449705088
shared pool sql area 253625648
buffer_cache 167772160
shared pool CCursor 120926648
shared pool PCursor 56403168
streams pool free memory 33442512
shared pool ASH buffers 30408704
shared pool KGLS heap 24977696
shared pool KGL handle 20724288
java pool free memory 16777216
large pool free memory 15701216
shared pool Heap0: KGL 14306280
shared pool XDB Schema Cac 13986128
shared pool KQR L PO 12177896
shared pool PL/SQL MPCODE 11592448
shared pool sql area:KOKA 11294048
shared pool kglsim object batch 10100496
shared pool Cursor Stats 9731984
shared pool KQR M PO 9256480
shared pool row cache 7560184
shared pool kglsim heap 6346368
shared pool KGL buckets 5242880
============================
V$RESOURCE_LIMIT Table
============================
View resources max and init values since instance startup.
SELECT *
FROM V$RESOURCE_LIMIT
ORDER BY resource_name.
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
--------------------- ------------------- --------------- ------------------- -----------
branches 2 17 247 UNLIMITED
cmtcallbk 0 16 247 UNLIMITED
dml_locks 1 64 988 UNLIMITED
enqueue_locks 128 227 3030 3030
enqueue_resources 131 198 1208 UNLIMITED
gcs_resources 0 0 0 0
gcs_shadows 0 0 0 0
ges_big_msgs 0 0 0 UNLIMITED
ges_cache_ress 0 0 0 UNLIMITED
ges_locks 0 0 0 UNLIMITED
ges_procs 0 0 0 0
ges_reg_msgs 0 0 0 UNLIMITED
ges_ress 0 0 0 UNLIMITED
ges_rsv_msgs 0 0 0 0
max_rollback_segments 21 28 247 65535
max_shared_servers 0 0 UNLIMITED UNLIMITED
parallel_max_servers 17 42 80 3600
processes 76 117 200 200
sessions 74 131 225 225
sort_segment_locks 8 21 UNLIMITED UNLIMITED
temporary_table_locks 0 8 UNLIMITED UNLIMITED
transactions 2 77 247 UNLIMITED
22 rows selected
SQL to provide memory usage per transaction
REM LOCATION: Application Tuning\Reports
REM FUNCTION: Generate value for current session memory usage
REM TESTED ON: 7.3.3.5, 8.0.4.1, 8.1.5, 8.1.7, 9.0.1, 10.2.0.3, 11.1.0.6
REM PLATFORM: non-specific
REM REQUIRES: v$session, v$sesstat, v$statname
REM
REM This is a part of the Knowledge Xpert for Oracle Administration library.
REM Copyright (C) 2008 Quest Software
REM All rights reserved.
REM
REM******************** Knowledge Xpert for Oracle Administration ********************
SET pages 66 lines 132 verify off feedback off echo off
COLUMN sess_mem heading "Current|session|memory|bytes" format 9,999,999,999,999
SET pages 66 lines 132 verify off feedback off
TTITLE left _date center 'Current Session Memory' skip 2
SELECT NVL (username, 'SYS-BKGD') username, sess.SID, SUM (VALUE) sess_mem
FROM v$session sess, v$sesstat stat, v$statname NAME
WHERE sess.SID = stat.SID
AND stat.statistic# = NAME.statistic#
AND NAME.NAME LIKE 'session % memory'
GROUP BY username, sess.SID
/
No comments:
Post a Comment