22 lines
1.1 KiB
SQL
22 lines
1.1 KiB
SQL
@@header
|
|
|
|
SELECT TO_CHAR(A.END_INTERVAL_TIME,'DD-Mon-YY HH24:MI:SS') "Time"
|
|
, ROUND(sum(b.bytes)/1024/1024) total
|
|
, ROUND(sum(DECODE(b.pool,null, DECODE(b.name,'buffer_cache',b.bytes,0),0))/1024/1024) buffercache
|
|
, ROUND(sum(DECODE(b.pool,'shared pool', b.bytes,0))/1024/1024) sharedpooltotal
|
|
, ROUND(sum(DECODE(b.name,'free memory',b.bytes,0))/1024/1024) sharedpoolfree
|
|
, ROUND(sum(DECODE(b.pool,'java pool', b.bytes,0))/1024/1024) javapool
|
|
, ROUND(sum(DECODE(b.pool,'large pool', b.bytes,0))/1024/1024) largepool
|
|
, ROUND(sum(DECODE(b.pool,'streams pool', b.bytes,0))/1024/1024) streamspool
|
|
, ROUND(sum(DECODE(b.pool,null, DECODE(b.name,'fixed_sga',b.bytes,0),0))/1024/1024) fixedsga
|
|
, ROUND(sum(DECODE(b.pool,null, DECODE(b.name,'log_buffer',b.bytes,0),0))/1024/1024) logbuffer
|
|
FROM v$database d
|
|
JOIN DBA_HIST_SNAPSHOT A ON A.DBID = d.DBID
|
|
JOIN DBA_HIST_SGAstat B ON A.DBID = B.DBID AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER AND A.SNAP_ID = B.SNAP_ID
|
|
WHERE 1=1
|
|
and a.END_INTERVAL_TIME > sysdate - 3
|
|
group by a.end_interval_time
|
|
ORDER BY a.end_interval_time ASC;
|
|
|
|
@@footer
|