#!/usr/bin/ksh
# whatlibrarycache ()
#------------------------------------------------------------------------------#
#    Module Name:  whatlibrarycache                                            #
#                                                                              #
#    Purpose:      Report on the Database System Global Area (SGA).            #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
	clear
	echo
	echo  " #--------------------------------------------------------#"
	echo  " # whatlibrarycache: Oracle Apps System Global Area (SGA) #"
	echo  " #--------------------------------------------------------#"
	echo
	echo  " Connecting as SYSDBA"
	sleep 2

	clear
	ORANAM=${ORASID}
	
#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

	CONNECT /  AS SYSDBA

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        set     feedback        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        999

        SPOOL /tmp/whatlibrarycache_$$.txt

        BREAK   ON  REPORT
        COMPUTE SUM LABEL 'Total'       OF vgets      ON REPORT
        COMPUTE SUM LABEL 'Total'       OF vpins      ON REPORT
        COMPUTE SUM LABEL 'Total'       OF vload      ON REPORT
        COMPUTE SUM LABEL 'Total'       OF vinvl      ON REPORT

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR
        COLUMN  vname   FORMAT  A15       Heading 'Lib-Cache Name' 	-
				          WORD_WRAPPED
        COLUMN  vgets	FORMAT  999999999 Heading 'Lib-Cache|Gets'
        COLUMN  vghit	FORMAT  999       Heading 'GetHit|Ratio'
        COLUMN  vpins	FORMAT  999999999 Heading 'Lib-Cache|Pins'
        COLUMN  vphit	FORMAT  999       Heading 'PinsHit|Ratio'
        COLUMN  vload	FORMAT  999999999 Heading 'Lib-Cache|Reloads'
        COLUMN  vinvl	FORMAT  999999999 Heading 'Lib-Cache|Invalids'

        TTITLE	CENTER  'Oracle Library Cache (v\$LibraryCache)' 	-
		LEFT    DATEVAR                                         -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1	-
                CENTER  'whatlibrarycache -- Sorted by LibCache-Name'	-
                LEFT    TIMEVAR					skip 2	

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')		datevalue,
 		TO_CHAR(sysdate, 'HH24:MI:SS')		timevalue,
		namespace				vname,
		gets					vgets,
		gethitratio				vghit,
		pins					vpins,
		pinhitratio				vphit,
		reloads					vload,
		invalidations				vinvl
	FROM	V\$LIBRARYCACHE 
	ORDER	BY  namespace;

        EXIT;
END_FILE

print
print "Please see /tmp/whatlibrarycache_$$.txt"
print
}
