Mit folgendem SQL-Statement kann der Inhalt der Buffer-Caches angezeigt werden:
\n—
\nset pages 999
\nset lines 131
\nttitle ‚Data Buffer‘
\ncolumn owner\u00a0 heading „Owner“ format a10
\ncolumn objname heading „Object|Name“ format a25
\ncolumn subobjname heading „Subobject|Name“ format a25
\ncolumn objtype heading „Object|Type“ format a10
\ncolumn bufferblocks heading „Blocks in|Buffer“ format 999,999,999
\ncolumn totalblocks heading „Total|Blocks“ format 999,999,999
\ncolumn bufferpercent heading „Percentage|in Buffer“ format 999.99
\ncolumn memkb heading „Memory|in KB“ format 999,999,999
\ncolumn blockkb heading „Block|Size|KB“ format 99
\nselect
\n\u00a0s.owner owner,
\n\u00a0object_name objname,
\n\u00a0subobject_name subobjname,
\n\u00a0substr(object_type,1,10) objtype,
\n\u00a0ts.block_size \/ 1024 blockkb,
\n\u00a0buffer.blocks blocks,
\n\u00a0s.blocks totalblocks,
\n\u00a0(buffer.blocks * ts.block_size \/ 1024) memkb,
\n\u00a0(buffer.blocks\/decode(s.blocks, 0, .001, s.blocks))*100 bufferpercent
\nfrom
\n\u00a0(select o.owner, o.object_name, o.subobject_name,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 o.object_type object_type, count(*) blocks
\n\u00a0 from dba_objects o, v$bh bh
\n\u00a0 where o.object_id = bh.objd and o.owner not in (‚SYS‘,’SYSTEM‘)
\n\u00a0 group by o.owner, o.object_name, o.subobject_name, o.object_type) buffer,
\n\u00a0 dba_segments s,
\n\u00a0dba_tablespaces ts
\nwhere s.tablespace_name = ts.tablespace_name
\n\u00a0 and s.owner = buffer.owner
\n\u00a0 and s.segment_name = buffer.object_name
\n\u00a0 and s.SEGMENT_TYPE = buffer.object_type
\n\u00a0 and (s.PARTITION_NAME = buffer.subobject_name or buffer.subobject_name is null)
\norder by memkb asc
\n;
\n\u00a0<\/p>\n
—
\n— allgemeine Cache hit ratio
\nselect value, name from v$sysstat
\nwhere name in (‚physical reads‘, ‚db block gets‘, ‚consistent gets‘)
\n;<\/p>\n
select ‚database buffer cache hit ratio‘ „Ratio“
\n, round ((1-
\n\u00a0\u00a0 ((select sum(value) from v$sysstat where name = ‚physical reads‘)
\n\u00a0 \/((select sum(value) from v$sysstat where name = ‚db block gets‘)
\n\u00a0 + (select sum(value) from v$sysstat where name = ‚consistent gets‘)
\n\u00a0))) * 100) || ‚%‘ „Prozent“
\nfrom dual;
\n\u00a0
\n\u00a0<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Mit folgendem SQL-Statement kann der Inhalt der Buffer-Caches angezeigt werden: — set pages 999 set lines 131 ttitle ‚Data Buffer‘ column owner\u00a0 heading „Owner“ format a10 column objname heading „Object|Name“ format a25 column subobjname heading „Subobject|Name“ format a25 column objtype … Weiterlesen