Objetos utilizados no Library Cache

[alphamek 2]

Caros,

 

Segue um script que dá mais detalhes sobre os objetos que estão no Library Cache.

 

SELECT SUBSTR(owner,1,10) Owner,
      SUBSTR(type,1,12)  Type,
      SUBSTR(name,1,20)  Name,
      executions,
      sharable_mem       Mem_used,
      SUBSTR(kept||' ',1,4)   "Kept?"
FROM v$db_object_cache WHERE TYPE IN ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
ORDER BY EXECUTIONS DESC

 

Abraços, http://forum.imasters.com.br/public/style_emoticons/default/clap.gif

[davidr 0]

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!!!!