Yes, you are right. However I said it because of some
bugs that can be mostly find on 8.0.5. The sympton is
that when you issue a query over v$bh or v$cache it
gives you no rows selected.
Regards.
--- Brian MacLean <[EMAIL PROTECTED]> wrote:
> Works fine for me on Solaris 2.6 and Oracle
> v8.1.7.0.0
>
> -----Original Message-----
> Sent: Monday, August 06, 2001 2:56 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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).
>
=====
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).
