|
Introduction
In my 16 years of working with Oracle I have
seen the database grow and change as new features are added and old
ones removed or changed. However, even as things change, they remain
the same in many respects. We still must index properly, set memory
settings correctly and specify proper hardware.
Welcome to
My Nightmare - The Common Performance Errors in Oracle
Databases
Improper Memory Configuration
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 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.
For complete scripts, see my complete Oracle script
collection at
www.dba-oracle.com/oracle_scripts.htm.
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.
For complete scripts, see my complete Oracle
script collection at
www.dba-oracle.com/oracle_scripts.htm.
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 dba_data_files
where tablespace_name in (
select tablespace_name
from 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 dba_data_files
where tablespace_name in (
select tablespace_name
from 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 dba_data_files
where tablespace_name in (
select tablespace_name
from 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 dba_data_files
where tablespace_name in (
select tablespace_name
from 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 dba_data_files
where tablespace_name in (
select tablespace_name
from 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.
For
complete scripts, see my complete Oracle script collection at
www.dba-oracle.com/oracle_scripts.htm.
-- 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 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.
|
and include the URL for the page.
