 |
|
Oracle Tips by Burleson |
Using V$BH and X$BH to
Monitor Buffer Use
In versions prior to Oracle, you may have
to run the catparr.sql script, located in ORACLE_HOME/rdbms/admin,
to create the v$bh view. The v$bh view, and its parent x$bh, are
very important for monitoring buffer usage. Rather then depending on
hit ratio, which is prone to miscalculation, problems with
nonselective indexes, and other woes, the v$bh and x$bh tables can
be used to tell you exactly what is happening with your buffer
areas. Look at the report in Source 13.14.
SOURCE
13.14 Block usage script.
rem
block_usage.sql
rem
rem Mike
Ault
rem
@title80
'Block Usage Inside SGA Block Buffers'
spool
rep_out\&db\block_usage
SELECT
decode(c.name,null,'UNUSED',c.name) ts_name,
a.file# file_number,
COUNT(a.block#) Blocks,
COUNT (DISTINCT a.file# || a.block#) Distinct_blocks
FROM
V$BH a, file$ b, ts$ c
See Code Depot
The script in Source 13.14 used the
v$bh SYS view to show which tablespaces have blocks inside the SGA
and how many blocks are free. An example block usage report is shown
in Listing 13.15.
LISTING
13.15 Example block usage report.
Date:
11/09/01 Page: 1
Time:
06:05 PM
Block Usage Inside SGA Block Buffers SYS
aultdb1 databas
TS_NAME
FILE_NUMBER BLOCKS DISTINCT_BLOCKS
------------------------------ ----------- ------ ---------------
UNUSED 0
7177 3791
SYSTEM 1
3149 3138
RBS 2
707 707
TOOLS 5
441 225
PERFSTAT
8 333
333
TEST_2K 9
2 2
This is an excerpt by
Mike Ault’s book “Oracle
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
