202 lines
7.9 KiB
MySQL
202 lines
7.9 KiB
MySQL
|
-- +----------------------------------------------------------------------------+
|
||
|
|
-- | Jeffrey M. Hunter |
|
||
|
|
-- | jhunter@idevelopment.info |
|
||
|
|
-- | www.idevelopment.info |
|
||
|
|
-- |----------------------------------------------------------------------------|
|
||
|
|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
|
||
|
|
-- |----------------------------------------------------------------------------|
|
||
|
|
-- | DATABASE : Oracle |
|
||
|
|
-- | FILE : dba_object_cache.sql |
|
||
|
|
-- | CLASS : Database Administration |
|
||
|
|
-- | PURPOSE : Summary of objects in the shared pool cache. |
|
||
|
|
-- | NOTE : As with any code, ensure to test this script in a development |
|
||
|
|
-- | environment before attempting to run it in production. |
|
||
|
|
-- +----------------------------------------------------------------------------+
|
||
|
|
|
||
|
|
SET TERMOUT OFF;
|
||
|
|
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
|
||
|
|
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
|
||
|
|
SET TERMOUT ON;
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT +------------------------------------------------------------------------+
|
||
|
|
PROMPT | Report : Summary of Objects in the Shared Pool Cache |
|
||
|
|
PROMPT | Instance : ¤t_instance |
|
||
|
|
PROMPT +------------------------------------------------------------------------+
|
||
|
|
|
||
|
|
SET ECHO OFF
|
||
|
|
SET FEEDBACK 6
|
||
|
|
SET HEADING ON
|
||
|
|
SET LINESIZE 180
|
||
|
|
SET PAGESIZE 50000
|
||
|
|
SET TERMOUT OFF
|
||
|
|
SET TIMING OFF
|
||
|
|
SET TRIMOUT ON
|
||
|
|
SET TRIMSPOOL ON
|
||
|
|
SET VERIFY OFF
|
||
|
|
|
||
|
|
CLEAR COLUMNS
|
||
|
|
CLEAR BREAKS
|
||
|
|
CLEAR COMPUTES
|
||
|
|
|
||
|
|
COLUMN inst_id HEAD "Inst.|ID"
|
||
|
|
COLUMN owner FORMAT a10 HEAD "Owner"
|
||
|
|
COLUMN name FORMAT a30 HEAD "Name"
|
||
|
|
COLUMN db_link FORMAT a7 HEAD "DB Link"
|
||
|
|
COLUMN namespace FORMAT a25 HEAD "Namespace"
|
||
|
|
COLUMN type FORMAT a18 HEAD "Type"
|
||
|
|
COLUMN sharable_mem FORMAT 99,999,999,999 HEAD "Sharable|Memory"
|
||
|
|
COLUMN loads HEAD "Loads"
|
||
|
|
COLUMN executions FORMAT 99,999,999 HEAD "Executions"
|
||
|
|
COLUMN locks HEAD "Locks"
|
||
|
|
COLUMN pins HEAD "Pins"
|
||
|
|
COLUMN kept FORMAT a5 HEAD "Kept?"
|
||
|
|
COLUMN child_latch HEAD "Child Latch"
|
||
|
|
COLUMN hash_value HEAD "Hash Value"
|
||
|
|
COLUMN address HEAD "Address"
|
||
|
|
COLUMN paddress HEAD "Paddress"
|
||
|
|
COLUMN crsr_plan_hash_value HEAD "Cursor Plan|Hash Value"
|
||
|
|
COLUMN kglobt02 HEAD "kglobt02"
|
||
|
|
|
||
|
|
BREAK ON report
|
||
|
|
|
||
|
|
COMPUTE sum OF sharable_mem ON report
|
||
|
|
|
||
|
|
DEFINE spool_file=shared_pool_object_cache.lst
|
||
|
|
|
||
|
|
SPOOL &spool_file
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
inst_id inst_id
|
||
|
|
, kglnaown owner
|
||
|
|
, kglnaobj name
|
||
|
|
-- , kglnadlk db_link
|
||
|
|
, DECODE( kglhdnsp
|
||
|
|
, 0 , 'CURSOR'
|
||
|
|
, 1 , 'TABLE/PROCEDURE'
|
||
|
|
, 2 , 'BODY'
|
||
|
|
, 3 , 'TRIGGER'
|
||
|
|
, 4 , 'INDEX'
|
||
|
|
, 5 , 'CLUSTER'
|
||
|
|
, 6 , 'OBJECT'
|
||
|
|
, 13, 'JAVA SOURCE'
|
||
|
|
, 14, 'JAVA RESOURCE'
|
||
|
|
, 15, 'REPLICATED TABLE OBJECT'
|
||
|
|
, 16, 'REPLICATION INTERNAL PACKAGE'
|
||
|
|
, 17, 'CONTEXT POLICY'
|
||
|
|
, 18, 'PUB_SUB'
|
||
|
|
, 19, 'SUMMARY'
|
||
|
|
, 20, 'DIMENSION'
|
||
|
|
, 21, 'APP CONTEXT'
|
||
|
|
, 22, 'STORED OUTLINE'
|
||
|
|
, 23, 'RULESET'
|
||
|
|
, 24, 'RSRC PLAN'
|
||
|
|
, 25, 'RSRC CONSUMER GROUP'
|
||
|
|
, 26, 'PENDING RSRC PLAN'
|
||
|
|
, 27, 'PENDING RSRC CONSUMER GROUP'
|
||
|
|
, 28, 'SUBSCRIPTION'
|
||
|
|
, 29, 'LOCATION'
|
||
|
|
, 30, 'REMOTE OBJECT'
|
||
|
|
, 31, 'SNAPSHOT METADATA'
|
||
|
|
, 32, 'JAVA SHARED DATA'
|
||
|
|
, 33, 'SECURITY PROFILE'
|
||
|
|
, 'INVALID NAMESPACE'
|
||
|
|
) namespace
|
||
|
|
, DECODE ( BITAND(kglobflg, 3)
|
||
|
|
, 0, 'NOT LOADED'
|
||
|
|
, 2, 'NON-EXISTENT'
|
||
|
|
, 3, 'INVALID STATUS'
|
||
|
|
, DECODE ( kglobtyp
|
||
|
|
, 0 , 'CURSOR'
|
||
|
|
, 1 , 'INDEX'
|
||
|
|
, 2 , 'TABLE'
|
||
|
|
, 3 , 'CLUSTER'
|
||
|
|
, 4 , 'VIEW'
|
||
|
|
, 5 , 'SYNONYM'
|
||
|
|
, 6 , 'SEQUENCE'
|
||
|
|
, 7 , 'PROCEDURE'
|
||
|
|
, 8 , 'FUNCTION'
|
||
|
|
, 9 , 'PACKAGE'
|
||
|
|
, 10, 'NON-EXISTENT'
|
||
|
|
, 11, 'PACKAGE BODY'
|
||
|
|
, 12, 'TRIGGER'
|
||
|
|
, 13, 'TYPE'
|
||
|
|
, 14, 'TYPE BODY'
|
||
|
|
, 15, 'OBJECT'
|
||
|
|
, 16, 'USER'
|
||
|
|
, 17, 'DBLINK'
|
||
|
|
, 18, 'PIPE'
|
||
|
|
, 19, 'TABLE PARTITION'
|
||
|
|
, 20, 'INDEX PARTITION'
|
||
|
|
, 21, 'LOB'
|
||
|
|
, 22, 'LIBRARY'
|
||
|
|
, 23, 'DIRECTORY'
|
||
|
|
, 24, 'QUEUE'
|
||
|
|
, 25, 'INDEX-ORGANIZED TABLE'
|
||
|
|
, 26, 'REPLICATION OBJECT GROUP'
|
||
|
|
, 27, 'REPLICATION PROPAGATOR'
|
||
|
|
, 28, 'JAVA SOURCE'
|
||
|
|
, 29, 'JAVA CLASS'
|
||
|
|
, 30, 'JAVA RESOURCE'
|
||
|
|
, 31, 'JAVA JAR'
|
||
|
|
, 32, 'INDEX TYPE'
|
||
|
|
, 33, 'OPERATOR'
|
||
|
|
, 34, 'TABLE SUBPARTITION'
|
||
|
|
, 35, 'INDEX SUBPARTITION'
|
||
|
|
, 36, 'REPLICATED TABLE OBJECT'
|
||
|
|
, 37, 'REPLICATION INTERNAL PACKAGE'
|
||
|
|
, 38, 'CONTEXT POLICY'
|
||
|
|
, 39, 'PUB_SUB'
|
||
|
|
, 40, 'LOB PARTITION'
|
||
|
|
, 41, 'LOB SUBPARTITION'
|
||
|
|
, 42, 'SUMMARY'
|
||
|
|
, 43, 'DIMENSION'
|
||
|
|
, 44, 'APP CONTEXT'
|
||
|
|
, 45, 'STORED OUTLINE'
|
||
|
|
, 46, 'RULESET'
|
||
|
|
, 47, 'RSRC PLAN'
|
||
|
|
, 48, 'RSRC CONSUMER GROUP'
|
||
|
|
, 49, 'PENDING RSRC PLAN'
|
||
|
|
, 50, 'PENDING RSRC CONSUMER GROUP'
|
||
|
|
, 51, 'SUBSCRIPTION'
|
||
|
|
, 52, 'LOCATION'
|
||
|
|
, 53, 'REMOTE OBJECT'
|
||
|
|
, 54, 'SNAPSHOT METADATA'
|
||
|
|
, 55, 'IFS'
|
||
|
|
, 56, 'JAVA SHARED DATA'
|
||
|
|
, 57, 'SECURITY PROFILE'
|
||
|
|
, 'INVALID TYPE'
|
||
|
|
)
|
||
|
|
) type
|
||
|
|
, kglobhs0 +
|
||
|
|
kglobhs1 +
|
||
|
|
kglobhs2 +
|
||
|
|
kglobhs3 +
|
||
|
|
kglobhs4 +
|
||
|
|
kglobhs5 +
|
||
|
|
kglobhs6 sharable_mem
|
||
|
|
, kglhdldc loads
|
||
|
|
, kglhdexc executions
|
||
|
|
, kglhdlkc locks
|
||
|
|
, kglobpc0 pins
|
||
|
|
, DECODE( kglhdkmk
|
||
|
|
, 0 ,'NO'
|
||
|
|
, 'YES'
|
||
|
|
) kept
|
||
|
|
-- , kglhdclt child_latch
|
||
|
|
-- , kglnahsh hash_value
|
||
|
|
-- , kglhdadr address
|
||
|
|
-- , kglhdpar paddress
|
||
|
|
-- , kglobt30 crsr_plan_hash_value
|
||
|
|
-- , kglobt02 kglobt02
|
||
|
|
FROM x$kglob
|
||
|
|
/
|
||
|
|
|
||
|
|
SPOOL OFF
|
||
|
|
|
||
|
|
SET TERMOUT ON
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT Report written to &spool_file
|
||
|
|
PROMPT
|