rem =========================================================================
rem 
rem                     genshpool8.sql
rem 
rem     Copyright (C) Oriole Software, 1998-2000
rem 
rem     Downloaded from http://www.oriolecorp.com
rem 
rem     This script for Oracle database administration is free software; you
rem     can redistribute it and/or modify it under the terms of the GNU General
rem     Public License as published by the Free Software Foundation; either
rem     version 2 of the License, or any later version.
rem 
rem     This script is distributed in the hope that it will be useful,
rem     but WITHOUT ANY WARRANTY; without even the implied warranty of
rem     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
rem     GNU General Public License for more details.
rem 
rem     You should have received a copy of the GNU General Public License
rem     along with this program; if not, write to the Free Software
rem     Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem 
rem =========================================================================
rem
rem # Stored procedures most reloaded in the shared pool (Oracle8i version)
rem
rem This script lists, by decreasing number of times they were reloaded,
rem the stored procedures and packages which are most moved in and out
rem of memory - this can lead to shared pool fragmentation and the big
rem objects should be kept in memory (see genshkeep.sql)
rem
rem Must be run by INTERNAL (SYS)
rem 
rem =========================================================================
rem
column name format A50
column K    format 9990
set numwidth 8
select /*+ ORDERED */ o.kglnaown || '.' || o.kglnaobj name,
       round((o.kglobhs0 + o.kglobhs1 + o.kglobhs2 + o.kglobhs3
              + o.kglobhs4 + o.kglobhs5 + o.kglobhs6) / 1024) K,
       o.kglhdldc loads,
       sum(c.kglhdexc) executions,
       decode(o.kglhdkmk, 0, 'NO', 'YES') kept
from x$kglob o,
     x$kgldp d,
     x$kglcursor c
where o.kglnahsh = d.kglrfhsh
  and o.kglhdadr = d.kglrfhdl                                              
  and o.kglobtyp in (7, 8, 9)
  and o.kglhdldc > 1
  and d.kglhdpar = c.kglhdpar
  and d.kglnahsh = c.kglnahsh
  and c.kglhdadr != c.kglhdpar
  and c.kglobt02 != 0                                  
group by o.kglnaown || '.' || o.kglnaobj,
         round((o.kglobhs0 + o.kglobhs1 + o.kglobhs2 + o.kglobhs3
                + o.kglobhs4 + o.kglobhs5 + o.kglobhs6) / 1024),
         o.kglhdldc,
         decode(o.kglhdkmk, 0, 'NO', 'YES')
order by 3 desc
/