That doesn't work on 8i. You should change the scripts
using x$bh instead of v$cache and v$bh.
Regards.
--- Brian MacLean <[EMAIL PROTECTED]> wrote:
> As already stated you get v$bh and v$cache views.
>
> Here is a script that uses them to tell you WTF is
> using your
> db_block_buffers. Sample output follows the script
> (have fun).
>
>
>
> REM
> REM Brian P. Mac Lean 01-JUL-01
> REM
> REM SGA DB Block Hogs and Activity
> REM
> REM Must Be Run As SYS or Internal
> REM
> REM tool_db_buffer_map2.sql
> REM
> set verify off
> set pagesize 36
> set linesize 132
> set pause off
> set pause 'Hit enter to continue'
> set feedback off
> set showmode off
> set echo off
>
> REM
>
----------------------------------------------------------------------------
>
> col db_block_size new_value nv_db_block_size
> noprint
> select value db_block_size from v$parameter where
> name = 'db_block_size';
>
> REM
>
----------------------------------------------------------------------------
>
> ttitle "V$BH, Ordered by Status and Count"
> col status format a10 heading
> "Block|Status|Types"
> col status2 format a25 heading
> "Block|Status|Description"
> col block_status format a32 heading "------Block
> Status
> Details------|Dirty-Temp-Ping-Stale-Direct-New"
> col cnt format 99999999 heading "Blocks"
> col sga_kbytes format 99999999 heading "Kbytes"
> col sga_mbytes format 99999999 heading "Mbytes"
> compute sum of cnt on report
> compute sum of sga_kbytes on report
> compute sum of sga_mbytes on report
> break on report
> select count(*) cnt,
> count(*) * (&nv_db_block_size / 1024)
> sga_kbytes,
> trunc((count(*) * (&nv_db_block_size / 1024))
> / 1024) sga_mbytes,
> status,
> decode(upper(status), 'FREE', 'not currently
> in use',
> 'XCUR', 'exclusive',
> 'SCUR', 'shared
> current',
> 'CR', 'consistent
> read',
> 'READ', 'being read
> from disk',
> 'MREC', 'in media
> recovery mode',
> 'IREC', 'in instance
> recovery mode', 'UNKNOWN')
> status2,
> dirty || ' ' || temp || ' ' || ping ||
> ' ' || stale || '
> ' || direct || ' ' || new block_status
> from v$bh
> group by status,
> dirty || ' ' || temp || ' ' || ping
> || ' ' || stale || '
> ' || direct || ' ' || new
> order by 2,1;
> clear break
> clear compute
>
> REM
>
----------------------------------------------------------------------------
>
> ttitle off
> set pause off
>
> PROMPT
> PROMPT Sort BY Selections
> prompt For DB Buffer Cache Map
> PROMPT ------------------------
>
> PROMPT 1 = Object Owner
> PROMPT 2 = Object Name
> PROMPT 3 = Object Type
> PROMPT 4 = SGA Blocks
> PROMPT 5 = SGA KBytes
> PROMPT 6 = Segment KBytes
> PROMPT 7 = Percent in SGA
> ACCEPT USER_INPUT1 NUMBER PROMPT 'Please enter one
> now:>'
> col sort_by_number new_value sort_by_number_value
> noprint
> col sort_by_text new_value sort_by_text_value
> noprint
> select decode(&USER_INPUT1, 1,1, 2,2, 3,3, 4,4, 5,5,
> 6,7, 7,9, 4)
> sort_by_number,
> decode(&USER_INPUT1, 1, 'Object Owner',
> 2,'Object Name',
> 3,'Object Type',
> 4,'SGA Blocks',
> 5,'SGA KBytes',
> 6,'Segment KBytes',
> 7,'Percent in SGA',
> 'SGA Blocks')
> sort_by_text
> from dual;
>
> REM
>
----------------------------------------------------------------------------
>
> PROMPT
> PROMPT Sort ORDER Selections
> PROMPT ---------------------
>
> PROMPT 1 = Descending
> PROMPT 2 = Ascending
> ACCEPT USER_INPUT2 NUMBER PROMPT 'Please enter one
> now:>'
> col order_by_text new_value order_by_text_value
> noprint
> select decode(&USER_INPUT2, 1,'Desc', 2,'Asc',
> 'Desc') order_by_text
> from dual;
>
> REM
>
----------------------------------------------------------------------------
>
> ACCEPT USER_INPUT3 CHAR PROMPT 'Include SYS objects
> (Y/N):>'
> col include_sys new_value nv_include_sys noprint
> select decode(upper('&USER_INPUT3'), 'Y','Y', 'N')
> include_sys
> from dual;
>
> REM
>
----------------------------------------------------------------------------
>
> set feedback on
>
> create table tmp_v$cache
> as select owner#, name, kind, partition_name,
> count(*) sga_cnt, count(*) *
> (&nv_db_block_size / 1024) sga_kb
> from v$cache
> group by owner#, name, kind, partition_name;
> create index tmp_v$cache_idx
> on tmp_v$cache(owner#, name, kind, partition_name,
> sga_cnt, sga_kb);
> analyze table tmp_v$cache
> compute statistics
> for table
> for all indexes
> for all indexed columns;
>
> create table tmp_dba_users
> as select user_id, username
> from dba_users;
> create index tmp_dba_users_idx
> on tmp_dba_users(user_id, username);
> analyze table tmp_dba_users
> compute statistics
> for table
> for all indexes
> for all indexed columns;
>
> create table tmp_dba_segments
> as select owner, segment_name, segment_type,
> partition_name, blocks *
> (&nv_db_block_size / 1024) seg_kb
> from dba_segments;
> create index tmp_dba_segments_idx
> on tmp_dba_segments(owner, segment_name,
> segment_type, partition_name,
> seg_kb);
> analyze table tmp_dba_segments
> compute statistics
> for table
> for all indexes
> for all indexed columns;
>
> REM
>
----------------------------------------------------------------------------
>
> PROMPT spooling output to
> /tmp/tool_db_buffer_map2_&1..lst
> PROMPT
> PROMPT Working, please wait...
>
> set feedback off termout off
> spool /tmp/tool_db_buffer_map2_&1..lst
>
> ttitle 'Sga Usage, Ordered by &sort_by_text_value
> &order_by_text_value '
> col username heading 'Object Owner' format a20
> col name heading 'Object Name' format a40
> col kind heading 'Object Type'
> col sga_cnt heading 'SGA|Blocks' format
> 9999999
> col sga_kb heading 'SGA|KBytes' format
> 999999
> col sga_mb heading 'SGA|MBytes' format
> 99999
> col seg_kb heading 'Segment|KBytes' format
> 9999999
> col seg_mb heading 'Segment|MBytes' format
> 99999
> col pct_load heading 'Pct In|SGA' format
> 999.999
>
> select /*+ RULE */
> B.username
> username,
> nvl(substr(A.name ||
> decode(A.partition_name,NULL,NULL,'.' ||
> A.partition_name
> ),1,40
> ), 'FREE BLOCKS'
> )
> name ,
> A.kind
> kind ,
> A.sga_cnt
> sga_cnt ,
> A.sga_kb
> sga_kb ,
> trunc(A.sga_kb / 1024)
> sga_mb ,
> C.seg_kb
> seg_kb ,
> trunc(C.seg_kb / 1024)
> seg_mb ,
> (A.sga_kb / C.seg_kb) * 100
> pct_load
> from tmp_dba_segments C, tmp_dba_users B,
> tmp_v$cache A
> where A.owner# = B.user_id
> and ((B.username != 'SYS'
> ) or
> (B.username = 'SYS' and '&nv_include_sys' =
> 'Y')
> )
> and B.username = C.owner
> and A.name = C.segment_name
> and A.kind = C.segment_type
> and nvl(A.partition_name,'NULL') =
> nvl(C.partition_name,'NULL')
> union all
> select /*+ RULE */
> C.owner
> username,
> nvl(substr(C.segment_name ||
> decode(C.partition_name,NULL,NULL,'.' ||
> C.partition_name
> ),1,40
> ), 'FREE BLOCKS'
> )
> name ,
> C.segment_type
> kind ,
> 0
> sga_cnt ,
> 0
> sga_kb ,
> 0
> sga_mb ,
> C.seg_kb
> seg_kb ,
> trunc(C.seg_kb / 1024)
> seg_mb ,
> 0
> pct_load
> from tmp_dba_segments C
> where ((C.owner != 'SYS'
> ) or
> (C.owner = 'SYS' and '&nv_include_sys' =
> 'Y')
> )
> and (C.owner || C.segment_name || C.segment_type
> || C.partition_name) not
> in
> (select B.username || A.name || A.kind ||
> A.partition_name
> from tmp_dba_users B, tmp_v$cache A
> where A.owner# =
> B.user_id
> and ((B.username != 'SYS'
> ) or
> (B.username = 'SYS' and
> '&nv_include_sys' = 'Y')
> )
> )
> order by &sort_by_number_value
> &order_by_text_value, 1,2;
>
> spool off
> ttitle off
> set feedback on termout on
> drop table tmp_dba_segments;
> drop table tmp_dba_users;
> drop table tmp_v$cache;
>
> exit
>
> REM ================================ END OF FILE
> ===============================
>
>
>
>
>
> Fri Aug 03
> page 1
>
> V$BH, Ordered by Status and
> Count
>
> Block Block
> Status Status
> ------Block Status Details------
> Blocks Kbytes Mbytes Types Description
> Dirty-Temp-Ping-Stale-Direct-New
> --------- --------- --------- ----------
> -------------------------
> --------------------------------
> 1 8 0 xcur exclusive
> Y Y
> N N N N
> 6 48 0 cr consistent
> read N N
> N N N N
> 92 736 0 cr consistent
> read Y N
> N N N N
> 731 5848 5 xcur exclusive
> Y N
> N N N N
> 149170 1193360 1165 xcur exclusive
> N N
> N N N N
> --------- --------- ---------
> 150000 1200000 1170
>
> Sort BY Selections
> For DB Buffer Cache Map
> -----------------------
> 1 = Object Owner
> 2 = Object Name
> 3 = Object Type
> 4 = SGA Blocks
> 5 = SGA KBytes
> 6 = Segment KBytes
> 7 = Percent in SGA
> Please enter one now:>4
>
>
>
>
> Sort ORDER Selections
> --------------------
> 1 = Descending
> 2 = Ascending
> Please enter one now:>1
>
>
>
> Include SYS objects (Y/N):>n
>
> Fri Aug 03
> page 1
> Sga
> Usage, Ordered by SGA
> Blocks Desc
>
>
> SGA SGA SGA Segment Segment Pct In
> Object Owner Object Name
> Object Type
> Blocks KBytes MBytes KBytes MBytes SGA
> --------------------
> ----------------------------------------
> ------------------ -------- ------- ------ --------
> ------- --------
> PROD VEND_PROD
> TABLE
> 39652 317216 309 671744 656 47.223
> PROD DISP_PRODUCTS
> TABLE
> 33771 270168 263 1007616 984 26.813
> PROD SO_HISTORY
> TABLE
> 29001 232008 226 245760 240 94.404
> PROD DS_ORDERS
> TABLE
> 12122 96976 94 110592 108 87.688
> PROD DS_ORDER_LINES
> TABLE
> 7561 60488 59 102400 100 59.070
> PROD AK_VP_MODIFIED
> INDEX
> 4708 37664 36 40960 40 91.953
> PROD AK_VENP_ID
> INDEX
> 2870 22960 22 69632 68 32.973
>
> -----Original Message-----
> Sent: Thursday, July 26, 2001 12:06 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Is there any sense to run catparr.sql if I do not
> use OPS?
>
> Alex Hillman
> --
>
=====
Eng. Christian Trassens
Senior DBA
Systems Engineer
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : 541149816062
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christian Trassens
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
