Objetos utilizados no Library Cache

Mais um pouco de contribuição para esse laboratório :

set lin 150 pages 60

col gethitratio heading 'Get|HitRatio'
col pinhitratio heading 'Pin|HitRatio'
col name noprint new_value dbname
select name from v$database;

spool d:\Pro-atividade\libdet.log
ttitle left "Database:"dbname" - Relatório de Detalhes sobre a Library Cache" skip 2

select namespace,
 gets,
 round(gethitratio*100,2) gethitratio,
 pins,
 round(pinhitratio*100,2) pinhitratio,
 reloads,
 invalidations

from v$librarycache order by 1;

spool off
E mais um :

set pagesize 80
set verify off
set heading off
set feedback off
set termout off

col sp_size format 999,999,999 justify right
col x_sp_used format 999,999,999 justify right
col sp_used_shr format 999,999,999 justify right
col sp_used_per format 999,999,999 justify right
col sp_used_run format 999,999,999 justify right
col sp_avail format 999,999,999 justify right
col sp_sz_pins format 999,999,999 justify right
col sp_no_pins format 999,999 justify right
col sp_no_obj format 999,999 justify right
col sp_no_stmts format 999,999 justify right
col sp_sz_kept_chks format 999,999,999 justify right
col sp_no_kept_chks format 999,999 justify right

col val2 new_val x_sp_size noprint
select value val2
from v$parameter
where name='shared_pool_size'
/
col val2 new_val x_sp_used noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val2
from v$sqlarea
/
col val2 new_val x_sp_used_shr noprint
col val3 new_val x_sp_used_per noprint
col val4 new_val x_sp_used_run noprint
col val5 new_val x_sp_no_stmts noprint

select sum(sharable_mem) val2,
 sum(persistent_mem) val3,
 sum(runtime_mem) val4,
 count(*) val5

from v$sqlarea
/
col val2 new_val x_sp_no_obj noprint
select count(*) val2 from v$db_object_cache
/
col val2 new_val x_sp_avail noprint
select &x_sp_size-&x_sp_used val2
from dual
/
col val2 new_val x_sp_no_kept_chks noprint
col val3 new_val x_sp_sz_kept_chks noprint

select decode(count(*),'',0,count(*)) val2,
 decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3

from v$db_object_cache
where kept='YES'
/
col val2 new_val x_sp_no_pins noprint
select count(*) val2
from v$session a, v$sqltext b
where a.sql_address||a.sql_hash_value = b.address||b.hash_value
/
col val2 new_val x_sp_sz_pins noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val2
from v$session a,
v$sqltext b,
v$sqlarea c
where a.sql_address||a.sql_hash_value = b.address||b.hash_value and
b.address||b.hash_value = c.address||c.hash_value
/

set termout on
set heading off

ttitle -
center 'Shared Pool`s Library Cache Information' skip 2

select 'Size : '
 ||&x_sp_size sp_size,
 'Used (total) : '
 ||&x_sp_used,
 ' sharable : '
 ||&x_sp_used_shr sp_used_shr,
 ' persistent : '
 ||&x_sp_used_per sp_used_per,
 ' runtime : '
 ||&x_sp_used_run sp_used_run,
 'Available : '
 ||&x_sp_avail sp_avail,
 'Number of SQL statements : '
 ||&x_sp_no_stmts sp_no_stmts,
 'Number of programmatic constructs : '
 ||&x_sp_no_obj sp_no_obj,
 'Kept programmatic construct chunks : '
 ||&x_sp_no_kept_chks sp_no_kept_chks,
 'Kept programmatic construct chunks size : '
 ||&x_sp_sz_kept_chks sp_sz_kept_chks,
 'Pinned statements : '
 ||&x_sp_no_pins sp_no_pins,
 'Pinned statements size : '
 ||&x_sp_sz_pins sp_sz_pins

from dual /

ttitle off
set heading on
set feedback on
Abraços, até mais!!!!