 |
|
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.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
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: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
 |
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. |
 |
|