rem
rem FUNCTION: Report Stored Object Statistics
rem
column owner format a11 heading Schema
column name format a30 heading Object|Name
column namespace heading Name|Space
column type heading Object|Type
column kept format a4 heading Kept
column sharable_mem format 999,999 heading Shared|Memory
column executions format 999,999 heading Executes
set lines 132 pages 47 feedback off
@title132 'Oracle Objects Report'
break on owner on namespace on type
--spool rep_out/&db/o_stat
select
OWNER,
NAMESPACE,
TYPE,
NAME,
SHARABLE_MEM,
LOADS,
EXECUTIONS,
LOCKS,
PINS,
KEPT
from
v$db_object_cache
where
type not in (
'NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE')
and executions>0 and loads>1 and kept='NO'
order by owner,namespace,type,executions desc;
--spool off
set lines 80 pages 22 feedback on
clear columns
clear breaks
ttitle off
执行结果如下：
Loads和executes值高的package和procedures都应该pin在shared pool里。注意对于package，只要pin package head就够了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/207/viewspace-814214/ ，如需转载，请注明出处，否则将追究法律责任。
