57 lines
3.2 KiB
SQL
57 lines
3.2 KiB
SQL
@@header
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display SGA breakdown history from statspack data
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Aug-07 Vishal Gupta First Draft
|
|
*/
|
|
set term on
|
|
|
|
|
|
|
|
COLUMN snap_time HEADING "Snap Time" FORMAT a18
|
|
COLUMN total HEADING "TotalSGA||(MB)" FORMAT 9,999,999
|
|
COLUMN buffercache HEADING "BufferCache||(MB)" FORMAT 9,999,999
|
|
COLUMN sharedpooltotal HEADING "SharedPool|Allocated|(MB)" FORMAT 9,999
|
|
COLUMN sharedpoolfree HEADING "SharedPool|Free|(MB)" FORMAT 9,999
|
|
COLUMN javapooltotal HEADING "JavaPool|Allocated|(MB)" FORMAT 9,999
|
|
COLUMN javapoolfree HEADING "JavaPool|Free|(MB)" FORMAT 9,999
|
|
COLUMN largepooltotal HEADING "LargePool|Allocated|(MB)" FORMAT 9,999
|
|
COLUMN largepoolfree HEADING "LargedPool|Free|(MB)" FORMAT 9,999
|
|
COLUMN streamspooltotal HEADING "StreamsPool|Allocated|(MB)" FORMAT 9,999
|
|
COLUMN streamspoolfree HEADING "StreamsPool|Free|(MB)" FORMAT 9,999
|
|
COLUMN fixedsga HEADING "FixedSGA||(MB)" FORMAT 9,999
|
|
COLUMN logbuffer HEADING "LogBuffer||(MB)" FORMAT 9,999
|
|
|
|
SELECT TO_CHAR(A.snap_time,'DD-MON-YY HH24:MI:SS') snap_time
|
|
, ROUND(sum(bytes)/1024/1024) total
|
|
, ROUND(sum(DECODE(pool,null, DECODE(name,'buffer_cache',bytes,0),0))/1024/1024) buffercache
|
|
, ROUND(sum(DECODE(pool,'shared pool', bytes,0))/1024/1024) sharedpooltotal
|
|
, ROUND(sum(DECODE(pool,'shared pool', DECODE(name,'free memory',bytes,0),0))/1024/1024) sharedpoolfree
|
|
, ROUND(sum(DECODE(pool,'java pool', bytes,0))/1024/1024) javapooltotal
|
|
, ROUND(sum(DECODE(pool,'java pool', DECODE(name,'free memory',bytes,0),0))/1024/1024) javapoolfree
|
|
, ROUND(sum(DECODE(pool,'large pool', bytes,0))/1024/1024) largepooltotal
|
|
, ROUND(sum(DECODE(pool,'large pool', DECODE(name,'free memory',bytes,0),0))/1024/1024) largepoolfree
|
|
, ROUND(sum(DECODE(pool,'streams pool', bytes,0))/1024/1024) streamspooltotal
|
|
, ROUND(sum(DECODE(pool,'streams pool', DECODE(name,'free memory',bytes,0),0))/1024/1024) streamspoolfree
|
|
, ROUND(sum(DECODE(pool,null, DECODE(name,'fixed_sga',bytes,0),0))/1024/1024) fixedsga
|
|
, ROUND(sum(DECODE(pool,null, DECODE(name,'log_buffer',bytes,0),0))/1024/1024) logbuffer
|
|
FROM stats$SGAstat B, stats$SNAPSHOT A
|
|
WHERE A.db_unique_name = B.db_unique_name
|
|
AND A.instance_name = B.instance_name
|
|
AND A.SNAP_ID = B.SNAP_ID
|
|
and A.snap_time > sysdate - &&1
|
|
group by A.db_unique_name
|
|
, A.instance_name
|
|
, A.snap_time
|
|
ORDER BY SNAP_TIME asc;
|
|
|
|
set echo off
|
|
@@footer
|