REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_tab_load.sql
REM
REM <SQLDIR_GRP>TAB INDX</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(www.dbatoolz.com)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports tables that need to be cached      
REM	allows to cache tables with high LOAD value
REM	----------------------------------------------
REM	These loads can also indicate that tables
REM	need to be indexed.
REM	---------------------------
REM	USES v$db_object_cache VIEW
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tab_load.sql NUMBER_OF_HIGH_LOADS
REM 
REM Example:
REM	s_tab_load.sql 3
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set feedback on
set term on
ttitle off
btitle off

col object_owner format a35
col type         format a15

accept 1 prompt "Enter value for LOADs:"
prompt +--------------------------------+
prompt | Tables with high "LOAD" values |
prompt +--------------------------------+
prompt

select owner||'.'||name     object_owner
,      type
,      loads
from   v$db_object_cache
where  type = 'TABLE'
and    loads > &&1
/


prompt +------------------------------------+
prompt | Tables created with "CACHE" clause |
prompt +------------------------------------+
prompt

select owner||'.'||table_name "Owner.Table Name"
from dba_tables
where ltrim(rtrim(cache)) = 'Y'
/

accept dummy prompt "Do you want to cache tables with high LOAD values? [Enter - YES; CTL-C - NO]:"

set term off
set verify off
set pages 0
set feedback off
set lines 130
col n_line format a130

select    'prompt CACHING table :'||owner||'.'||name||' ...'  n_line
,         'alter table '||owner||'.'||name||' CACHE;'         n_line
from   v$db_object_cache
where  type = 'TABLE'
and    loads > &&1

spool tab_cache.tmp
prompt PROMPT
prompt PROMPT
/
spool off

set term on
@tab_cache.tmp


prompt +------------------------------------+
prompt | Tables created with "CACHE" clause |
prompt +------------------------------------+
prompt

select owner||'.'||table_name "Owner.Table Name"
from dba_tables
where ltrim(rtrim(cache)) = 'Y'
/