Improper Memory Configuration Oracle Tips by Mike Ault

If you put too-small a carburetor on a car then even though the engine may be able to do 200 MPH, you are constraining it to much less performance. Likewise if you do not give enough memory to Oracle you will prevent it from reaching its full performance potential.

In this section we will discuss two major areas of memory, the database buffer area and the shared pool area. The PGA areas are discussed in a later section.

The Database Buffer Area 

Just like the old adage you can’t fly anywhere unless you go through Atlanta, you aren’t going to get data unless you go through the buffer. Admittedly there are some direct-read scenarios, but for the most part anything that goes to users or gets into the database must go through the database buffers.

Gone are the days of a single buffer area (the default) now we have 2, 4, 8,, 16, 32 K buffer areas, keep and recycle buffer pools on top of the default area. Within these areas we have the consistent read, current read, free, exclusive current, and many other types of blocks that are used in Oracle’s multi-block consistency model.

The V$BH view (and it’s parent the X$BH table) are the major tools used by the Remote DBA to track block usage, however, you may find that the data in the V$BH view can be misleading unless you also tie in block size data. Look at Figure 10.

rem vbh_status.sql
rem
rem Mike Ault -- Burleson
rem
col dt new_value td noprint
select to_char(sysdate,'ddmmyyyyhh24miss') dt from dual;
@title80 'Status of DB Block Buffers'
spool rep_out\&db\vbh_status&&td
select status,count(*) number_buffers from v$bh group by status;
spool off
ttitle off
clear columns 

Figure 10: Simple V$BH Report

In the report in Figure 10 we see a simple version of a V$BH query. Figure 10 assumes only one buffer is in play, the default buffer, and doesn’t account for any of the multiple blocksize areas or the recycle or keep areas. By not accounting for other types of buffers that may be present the report in Figure 10 can overstate the number of free buffers available. Look at Figure 11.

Date: 12/13/05                                              Page:   1
Time: 10:38 PM           Status of DB Block Buffers         PERFSTAT                                   whoville database                                                                                                       

STATU NUMBER_BUFFERS
----- --------------                                                       cr             33931
free           15829
xcur          371374     

Figure 11: Simple V$BH report listing

From the results in Figure 11 we would conclude we had plenty of free buffers, however we would be mistaken. Look at the report in Figure 12.

Date: 12/13/05                                              Page:   1
Time: 10:39 PM               All Buffers Status             PERFSTAT                                     whoville database                                                                                                             

STATUS           NUM                                                      
--------- ----------                                                      
32k cr          2930                                                      
32k xcur       29064                                                      
8k cr           1271                                                      
8k free            3                                                      
8k read            4                                                       
8k xcur       378747                                                      
free           10371               

Figure 12: Detailed V$BH Status report

As you can see, while there are free buffers, only 3 of them are available to the 8k, default area and none are available to our 32K area. The free buffers are actually assigned to a keep or recycle pool area (hence the null value for the blocksize) and are not available for normal usage. The script to generate this report is shown in Figure 13.

set pages 50
@title80 'All Buffers Status'
spool rep_out\&&db\all_vbh_status
select
  '32k '||status as status,
  count(*) as num
from
 v$bh
where file# in(
   select file_id
     from Remote DBA_data_files
     where tablespace_name in (
       select tablespace_name
        from Remote DBA_tablespaces
        where block_size=32768))
group by '32k '||status
union
select
  '16k '||status as status,
   count(*) as num
from
 v$bh
where
  file# in(
   select file_id
    from Remote DBA_data_files
    where tablespace_name in (
      select tablespace_name
       from Remote DBA_tablespaces
       where block_size=16384))
group by '16k '||status
union
select
  '8k '||status as status,
  count(*) as num
from
  v$bh
where
  file# in(
   select file_id
    from Remote DBA_data_files
    where tablespace_name in (
      select tablespace_name
       from Remote DBA_tablespaces
       where block_size=8192))
group by '8k '||status
union
select
  '4k '||status as status,
  count(*) as num
from
 v$bh
where
 file# in(
  select file_id
   from Remote DBA_data_files
   where tablespace_name in (
     select tablespace_name
     from Remote DBA_tablespaces
     where block_size=4096))
group by '4k '||status
union
select
  '2k '||status as status,
  count(*) as num
from
  v$bh
where
 file# in(
  select file_id
   from Remote DBA_data_files
   where tablespace_name in (
    select tablespace_name
    from Remote DBA_tablespaces
    where block_size=2048))
group by '2k '||status
union
select
  status,
  count(*) as num
from
  v$bh
where status='free'
group by status
order by 1
/
spool off
ttitle off

Figure 13: Script to get all Buffer Pool Status

As you can see, the script is wee bit more complex than the simple V$BH script. No doubt there is a clever way to simplify the script using X and K$ tables, but then we would have to use the SYS user to run it and I prefer to use lower powered users when I go to client sites.

So, if you see buffer busy waits, db block waits and the like and you run the above report and see no free buffers it is probably a good bet you need to increase the number of available buffers for the area showing no free buffers. You should not immediately assume you need more buffers because of buffer busy waits as these can be caused by other problems such as row lock waits, itl waits and other issues.

Luckily Oracle10g has made it relatively simple to determine if we have these other types of waits. Look at Figure 14.

-- Crosstab of object and statistic for an owner
--
col "Object" format a20
set numwidth 12
set lines 132
set pages 50
@title132 'Object Wait Statistics'
spool rep_out\&&db\obj_stat_xtab
select * from(
select DECODE(GROUPING(a.object_name), 1, 'All Objects',
      a.object_name) AS "Object",
sum(case when a.statistic_name = 'ITL waits' then a.value else
null end) "ITL Waits",
sum(case when a.statistic_name = 'buffer busy waits' then a.value
else null end) "Buffer Busy Waits",
sum(case when a.statistic_name = 'row lock waits' then a.value else null end) "Row Lock Waits",
sum(case when a.statistic_name = 'physical reads' then a.value else null end) "Physical Reads",
sum(case when a.statistic_name = 'logical reads' then a.value else null end) "Logical Reads"from v$segment_statistics a
where a.owner like upper('&owner')
group by rollup(a.object_name)) b
where (b."ITL Waits">0 or b."Buffer Busy Waits">0)
/
spool off
clear columns
ttitle off
 

Figure 14: Object Statistic Crosstab Report

Figure 14 shows an object statistic cross tab report based on the V$SEGMENT_STATISTICS view. The cross tab report generates a listing showing the statistics of concern as headers across the page rather than listings going down the page and summarizes them by object. This allows us to easily compare total buffer busy waits to the number of ITL or row lock waits. This ability to compare the ITL and row lock waits to buffer busy waits lets us see what objects may be experiencing contention for ITL lists, which may be experiencing excessive locking activity and through comparisons, which are highly contended for without the row lock or ITL waits. An example of the output of the report, edited for length, is shown in Figure 15.

Date: 12/09/05                                      Page:   1     
Time: 07:17 PM           Object Wait Statistics     PERFSTAT      
                            whoville database                                                                                                                        
                       ITL   Buffer Busy Row Lock  Physical      Logical
Object         Waits       Waits    Waits     Reads        Reads
-------------------- ----- ----------- -------- ---------- -----BILLING            0       63636    38267    1316055   410219712
BILLING_INDX1      1       16510       55     151085    21776800
..
DELIVER_INDX1   1963       36096    32962    1952600    60809744
DELIVER_INDX2     88       16250     9029   18839481   342857488 DELIVER_PK      2676       99748    29293   15256214   416206384
DELIVER_INDX3   2856      104765    31710    8505812   467240320
...

All Objects    12613    20348859  1253057 1139977207 20947864752
 

243 rows selected.

Figure 15: Example Object Cross Tab Report

In the above report the BILLING_INDX1 index has a large number of  buffer busy waits but we can’t account for them from the ITL or Row lock waits, this indicates that the index is being constantly read and the blocks then aged out of memory forcing waits as they are re-read for the next process. On the other hand, almost all of the buffer busy waits for the DELIVER_INDX1 index can be attributed to ITL and Row Lock waits.

In situations where there are large numbers of ITL waits we need to consider the increase of the INITRANS setting for the table to remove this source of contention. If the predominant wait is row lock waits then we need to determine if we are properly using locking and cursors in our application (for example, we may be over using the SELECT…FOR UPDATE type code.) If, on the other hand all the waits are un-accounted for buffer busy waits, then we need to consider increasing the amount of database block buffers we have in our SGA.

As you can see, this object wait cross tab report can be a powerful addition to our tuning arsenal.

By knowing how our buffers are being used and seeing exactly what waits are causing our buffer wait indications we can quickly determine if we need to tune objects or add buffers, making sizing buffer areas fairly easy.

But what about the Automatic Memory Manager in 10g? It is a powerful tool for Remote DBAs with systems that have a predictable load profile, however if your system has rapid changes in user and memory loads then AMM is playing catch up and may deliver poor performance as a result. In the case of memory it may be better to hand the system too much rather than just enough, just in time (JIT). As many companies have found when trying the JIT methodology in their manufacturing environment it only works if things are easily predictable.

The AMM is utilized in 10g by setting two parameters, the SGA_MAX_SIZE and the SGA_TARGET. The Oracle memory manager will size the various buffer areas as needed within the range between base settings or SGA_TARGET and SGA_MAX_SIZE using the SGA_TARGET setting as an “optimal” and the SGA_MAX_SIZE as a maximum with the manual settings used in some cases as a minimum size for the specific memory component.

---

For more information on this topic, I recommend Don Burleson's latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.