By Mike Ault
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.
You would think that by now everyone would have
a pretty good idea what an average configuration for an Oracle
small, medium and large database would be, but unfortunately these
classifications also undergo mutation. It wasn't too long ago that
if you used several hundred megabytes of disks you had a large
database. Now of course large databases are in the terabyte and
larger size range. This makes setting tuning guidelines for Oracle
databases somewhat of a moving target where the rules change almost
as fast as you can derive them.
Given a changing environment for tuning Oracle
is the only constant I will try to present in this paper the major
issues I have found in the last few years while tuning several dozen
different client databases. I will try to present a method or
methods for determining set points that take into account this
changing beast that is Oracle.
Major Issues of Concern
From a review of the tuning reports I have
generated over the last couple of years I have come up with a list
of 6 major areas that the DBA or tuning expert needs to pay
attention to in an Oracle environment:
?
Non-use of bind variables
?
Improper Index usage
?
Improper memory configuration
?
Improper disk setup
?
Improper initialization parameter usage
?
Improper PGA setup
Each of the above areas can have a profound
impact on the ability of an Oracle database to function properly. In
the following sections we will cover how to measure the affect of
these on your system, how to correct them once found and how to
prevent them from being issues in the future. Each of the above can
be, and probably is somewhere at this conference, a full hour topic,
so we have a lot of ground to cover, let's get started!
Non-Use of Bind Variables
You?ll hear it from PL/SQL tuners, you?ll hear
from SQL tuners and you?ll hear from shared pool tuners, the biggest
problem in many applications is the non-use of bind variables.
Why is this an issue? Well, Oracle uses a
signature generation algorithm to assign a hash value to each SQL
statement based on the characters in the SQL statement. Any change
in a statement (generally speaking) will result in a new hash and
thus Oracle assumes it is a new statement. Each new statement must
be verified, parsed and have an execution plan generated and stored.
The activities needed to parse a statement and
generate an execution plan are CPU intensive and generate recursive
SQL against the data dictionary which may result in physical IO as
well. The added statement and parse tree takes up space in the
shared pool. I have seen several databases where the shared pool was
over a gigabyte in size (one were it was 4 gig!) until bind
variables were introduced, reducing the size to a couple of hundred
megabytes at most.
A quick method of seeing whether code is being
reused (a key indicator of proper bind variable usage) is to look at
the values of reusable and non-reusable memory in the shared pool. A
SQL for determining this comparison of reusable to non-reusable code
is shown in figure 1.
For complete scripts, see my complete Oracle script
collection at
www.dba-oracle.com/oracle_scripts.htm.
ttitle 'Shared Pool Utilization'
spool sql_garbage
select 1 nopr,
to_char(a.inst_id) inst_id,
a.users users,
to_char(a.garbage,'9,999,999,999') garbage,
to_char(b.good,'9,999,999,999') good,
to_char((b.good/(b.good+a.garbage))*100,'9,999,999.999')
good_percent
from (select
a.inst_id,
b.username users,
sum(a.sharable_mem+a.persistent_mem)
Garbage,
to_number(null) good
from
sys.gv_$sqlarea a,
dba_users b
where
(a.parsing_user_id = b.user_id and
a.executions<=1)
group by a.inst_id, b.username
union
select distinct
c.inst_id,
b.username users,
to_number(null) garbage,
sum(c.sharable_mem+c.persistent_mem) Good
from
dba_users b,
sys.gv_$sqlarea c
where
(b.user_id=c.parsing_user_id and
c.executions>1)
group by c.inst_id, b.username
) a, (select
a.inst_id,
b.username users,
sum(a.sharable_mem+a.persistent_mem)
Garbage,
to_number(null) good
from
sys.gv_$sqlarea a,
dba_users b
where
(a.parsing_user_id = b.user_id and
a.executions<=1)
group by a.inst_id,b.username
union
select distinct
c.inst_id,
b.username users,
to_number(null) garbage,
sum(c.sharable_mem+c.persistent_mem) Good
from
dba_users b,
sys.gv_$sqlarea c
where
(b.user_id=c.parsing_user_id and
c.executions>1)
group by c.inst_id, b.username
) b
where a.users=b.users
and a.inst_id=b.inst_id
and a.garbage is not null and b.good is not
null
union
select 2 nopr,
'-------' inst_id,'-------------'
users,
'--------------' garbage,'--------------' good,
'--------------' good_percent from dual
union
select 3 nopr,
to_char(a.inst_id,'999999'),
to_char(count(a.users)) users,
to_char(sum(a.garbage),'9,999,999,999')
garbage,
to_char(sum(b.good),'9,999,999,999') good,
to_char(((sum(b.good)/(sum(b.good)+sum(a.garbage)))*100),'9,999,999.999')
good_percent
from (select
a.inst_id,
b.username users,
sum(a.sharable_mem+a.persistent_mem)
Garbage,
to_number(null) good
from
sys.gv_$sqlarea a,
SEE CODE DEPOT FOR FULL
SCRIPT
where
(a.parsing_user_id = b.user_id and
a.executions<=1)
group by a.inst_id,b.username
union
select distinct
c.inst_id,
b.username users,
to_number(null) garbage,
sum(c.sharable_mem+c.persistent_mem) Good
from
dba_users b,
sys.gv_$sqlarea c
where
(b.user_id=c.parsing_user_id and
c.executions>1)
group by c.inst_id,b.username
) a, (select
a.inst_id,
b.username users,
sum(a.sharable_mem+a.persistent_mem)
Garbage,
to_number(null) good
from
sys.gv_$sqlarea a,
dba_users b
where
(a.parsing_user_id = b.user_id and
a.executions<=1)
group by a.inst_id,b.username
union
select distinct
c.inst_id,
b.username users,
to_number(null) garbage,
sum(c.sharable_mem+c.persistent_mem) Good
from
dba_users b,
SEE CODE DEPOT FOR FULL
SCRIPT
where
(b.user_id=c.parsing_user_id and
c.executions>1)
group by c.inst_id, b.username
) b
where a.users=b.users
and a.inst_id=b.inst_id
and a.garbage is not null and b.good is not
null
group by a.inst_id
order by 1,2 desc
/
spool off
ttitle off
set pages 22
Figure 1: SQL Code to Show Shared verses
Non-shared code
An example report is shown in Figure 2 for an
instance with poor code reuse characteristics. The names have been
changed to protect the innocent.
Date: 03/25/05
Page: 1
Time: 17:51 PM Shared Pool
Utilization SYSTEM
whoville
database
users Non-Shared SQL Shared
SQL Percent Shared
--------------------
-------------- -------------- --------------
WHOAPP
532,097,982 1,775,745 .333
SYS
5,622,594 5,108,017 47.602
DBSNMP
678,616 219,775 24.463
SYSMAN
439,915 2,353,205 84.250
SYSTEM
425,586 20,674 4.633
------------- --------------
-------------- --------------
5 541,308,815
9,502,046 1.725
Figure 2: Example report output for poor code
reuse
As you can see from Figure 2 the majority owner
in this application, WHOAPP is only showing 0.3 percent of reusable
code by memory usage and is tying up an amazing 530 megabytes with
non-reusable code! Let's look at a database with good reuse
statistics. Look at Figure 3.
Date:
11/13/05 Page: 1
Time: 03:15 PM Shared Pool
Utilization PERFSTAT
dbaville
database
users Non-Shared SQL Shared
SQL Percent Shared
-------------------- --------------
-------------- --------------
DBAVILLAGE 9,601,173
81,949,581 89.513
PERFSTAT 2,652,827
199,868 7.006
DBASTAGER 1,168,137
35,468,687 96.812
SYS 76,037
5,119,125 98.536
------------- --------------
-------------- --------------
4 13,498,174
122,737,261 90.092
Figure 3: Example of Good Sharing of Code
Notice in Figure 3 how the two application
owners, DBAVILLAGE and DBASTAGER show 89.513 and 96.812 reuse
percentage by memory footprint for code.
So what else can we look at to see about code
reusage, the above reports give us a gross indication, how about
something with a bit more usability to correct the situation? The
V$SQLAREA and V$SQLTEXT views give us the capability to look at the
current code in the shared pool and determine if it is using, or not
using bind variables. Look at Figure 4.
For complete scripts, see my complete Oracle script
collection at
www.dba-oracle.com/oracle_scripts.htm.
set lines 140 pages 55 verify off feedback
off
col num_of_times heading 'Number|Of|Repeats'
col SQL heading 'SubString - &&chars
Characters'
col username format a15 heading 'User'
@title132 'Similar SQL'
spool rep_out\&db\similar_sql&&chars
select
b.username,substr(a.sql_text,1,&&chars) SQL,
count(a.sql_text)
num_of_times from v$sqlarea a, dba_users b
where a.parsing_user_id=b.user_id
group by
b.username,substr(a.sql_text,1,&&chars) having count(a.sql_text)>&&num_repeats
order by count(a.sql_text) desc
/
spool off
undef chars
undef num_repeats
clear columns
set lines 80 pages 22 verify on feedback on
ttitle off
Figure 4: Similar SQL report code
Figure 4 shows a simple script to determine,
based on the first x characters (input when the report is
executed) the number of SQL statements that are identifical up to
the first x characters. This shows us the repeating code in
the database and helps us to track down the offending statements for
correction.
As you can see from Figure 5, the SQL text is
pinpointed that needs fixing. Using a substring from the above SQL
the V$SQLTEXT view can be used to pull an entire listing of the
code.
Some may be asking: ?What is a bind variable??
simply put, a bind variable is a variable inserted into the SQL code
in the place of literal values. For example:
SELECT *
FROM whousers WHERE first_name=?ANNA?;
Is not using bind variables. If we issued a
second query:
SELECT *
FROM whousers WHERE first_name=?GRINCH?;
Even though the queries are identical until the
last bit where we specify the name, the Oracle query engine would
treat them as two different queries. By using bind variables, as
shown below, we allow Oracle to parse the statement once and reuse
it many times.
SELECT *
FROM whousers WHERE first_name=:whoname;
The colon in front of the variable ?whoname?
tells Oracle this is a bind variable that will be supplied at run
time.
So, the proper fix for non-bind variable usage
is to re-write the application to use bind variables. This of course
can be an expensive and time consuming process, but ultimately it
provides the best fix for the problem. However, what if you can't
change the code? Maybe you have time, budget or vendor constraints
that prevent you from being able to do the ?proper? thing. What are
your options?
Oracle has provided the CURSOR_SHARING
initialization variable that will automatically replace the literals
in your code with bind variables. The settings for CURSOR_SHARING
are EXACT (the default), FORCE, and SIMILAR.
?
EXACT - The statements have to match exactly to
be reusable
?
FORCE - Always replace literals
?
SIMILAR - Perform literal peeking and replace
when it makes sense
We usually suggest the use of the SIMILAR option
for CURSOR_SHARING. You can tell if cursor sharing is set to FORCE
or SIMILAR by either using the SHOW PARAMETER CURSOR_SHARING command
or by looking at the code in the shared pool, if you see code that
looks like so:
SELECT
USERNAME FROM whousers WHERE first_name=:"SYS_B_0"
This tells you that CURSOR_SHARING is set to
either FORCE or SIMILAR because of the replacement variable :'sYS_B_O?.
Improper Index Usage
In the good old days Oracle followed the rule based
optimizer (RBO) and the rule based optimizer followed essentially
one simple premise from which it's rules were devised:
INDEXES
GOOD! FULL TABLE SCANS BAD!
Unfortunately this simple rule basis led to many
less than optimal execution plans so SQL tuners spent a lot of time
doing things such as null value concatenation or 0/1 math (add zero,
or multiple by 1) to eliminate index usage. Of course now we have
the cost based optimizer (CBO) which always gives us the correct
path?not!
In essence we now have to look for full table
scans and examine the table size, available indexes and other
factors to determine if the CBO has made the proper choice. In most
cases where improper full table scans are occurring I have generally
found that missing or improper indexes were the cause, not the
optimizer.
Pre-9i determining full table scans was either
done live by looking for full table scan related waits and
backtracking to the objects showing the waits (as shown in Figure 6)
or by periodically stripping the SQL from the V$SQLTEXT or V$SQLAREA
views and performing explain plan commands on them into a table. The
table was then searched for the plans that showed full table
accesses. Neither of these were particularly user friendly.
For complete scripts, see my complete Oracle
script collection at
www.dba-oracle.com/oracle_scripts.htm.
rem fts_rep.sql
rem FUNCTION: Full table scan report
rem MRA
rem
col sid format 99999
col owner format a15
col segment_name format a30
col td new_value rep_date noprint
select to_char(sysdate,'ddmonyyhh24mi') td
from dual;
@title80 'Full Table Scans &rep_date'
spool rep_out\&db\fts_rep_&rep_date
SELECT DISTINCT A.SID,
C.OWNER,
C.SEGMENT_NAME
FROM SYS.V_$SESSION_WAIT A,
SYS.V_$DATAFILE B,
SYS.DBA_EXTENTS C
WHERE A.P1 = B.FILE# AND
B.FILE# = C.FILE_ID AND
A.P2 BETWEEN C.BLOCK_ID AND
(C.BLOCK_ID + C.BLOCKS) AND
A.EVENT = 'db file scattered read';
spool off
ttitle off
Figure 6: Example Realtime report to obtain
object undergoing full table scans
You will be happy to know that starting with
Oracle9i there is a new view that keeps the explain plans for all
current SQL in the shared pool, this view, appropriately named
V$SQL_PLAN allows DBAs to determine exactly what statements are
using full table scans and more importantly how often the particular
SQL statements are being executed. An example report against the
V$SQL_PLAN table is shown in Figure 7.
For complete scripts, see my complete Oracle
script collection at
www.dba-oracle.com/oracle_scripts.htm.
rem fts report
rem based on V$SQL_PLAN table
col operation format a15
col object_name format a32
col object_owner format a15
col options format a20
col executions format 999,999,999
set pages 55 lines 132 trims on
@title132 'Full Table/Index Scans'
spool rep_out\&&db\fts
select a.object_owner,a.object_name,
rtrim(a.operation) operation,
a.options, b.executions from
v$sql_plan a, v$sqlarea b
where
SEE CODE DEPOT FOR FULL
SCRIPT
and a.operation IN ('TABLE ACCESS','INDEX')
and a.options in ('FULL','FULL SCAN','FAST
FULL SCAN','SKIP SCAN','SAMPLE FAST FULL SCAN')
and a.object_owner not in ('SYS','SYSTEM','PERFSTAT')
group by object_owner,object_name,
operation, options
order by object_owner, operation, options,
object_name
/
spool off
set pages 20
ttitle off
Figure 7: Example SQL to get full table scan
data from database
Notice that I didn't limit myself to just full
table scans, I also looked for expensive index scans as well. An
example excerpt from this report is shown in Figure 8.
Date:
11/09/04
Page: 1
Time: 08:31
PM Full Table/Index Scans
PERFSTAT
whoemail database
HASH_VALUE
OWNER OBJECT_NAME OPERATION OPTIONS
EXECUTIONS BYTES FTS_MEG
----------
------ ------------------------- ------------- --------------
---------- -------- -------
4278137387
SDM DB_STATUS TABLE ACCESS FULL
30,303 1048576 30303
1977943106
SDM DB_STATUS TABLE ACCESS FULL
1,863 1048576 1863
3391889070
SDM FORWARD_RECIPIENTS TABLE ACCESS FULL
29,785 4194304 119140
1309516963
SDM FORWARD_RECIPIENTS TABLE ACCESS FULL
3,454 4194304 13816
4017881007
SDM GLOBAL_SUPPRESSION_LIST TABLE ACCESS FULL
168,020 1048576 168020
3707567343
SDM ORGANIZATION2 TABLE ACCESS FULL
6,008 1048576 6008
1705069780
SDM SP_CAMPAIGN_MAILING TABLE ACCESS FULL
1,306 10485760 13060
1047433976
SDM SS_LAST_SENT_JOB TABLE ACCESS FULL
572,896 1048576 572896
3556187438
SDM SS_LAST_SENT_JOB TABLE ACCESS FULL
572,896 1048576 572896
3207589632
SDM SS_SEND TABLE ACCESS FULL
32,275 20971520 645500
788044291
SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN
25,655 20971520 513100
1417625610
SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN
11,802 20971520 236040
2719565392
SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN
11,379 20971520 227580
1533235337
SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN
10,981 20971520 219620
3273441234
SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN
10,594 20971520 211880
1823729862
SDM SS_TASK_OWNER_NNDX INDEX FULL SCAN
3,992 10485760 39920
3673286081
SDM SYSTEM_SEED_LIST TABLE ACCESS FULL
81,249 1048576 81249
4712038
SDM TRACK_OPEN_MRRJ_LOCK_NNDX INDEX FULL SCAN
11,159 62914560 669540
3797121012
SDM TRACK_OPEN_MRRJ_LOCK_NNDX INDEX FULL SCAN
11,159 62914560 669540
1624438202
SDM TRACK_RAW_OPEN TABLE ACCESS FULL
29,786 31457280 893580
4232130615
SDM TRACK_RAW_OPEN TABLE ACCESS FULL
29,786 31457280 893580
3109457251
SDM TRACK_RAW_OPEN TABLE ACCESS FULL
29,785 31457280 893550
3391889070
SDM TRACK_RAW_OPEN TABLE ACCESS FULL
29,785 31457280 893550
1309516963
SDM TRACK_RAW_OPEN TABLE ACCESS FULL
3,454 31457280 103620
3685251647
SDM TR_M_TOP_DOMAINS TABLE ACCESS FULL
5,925 10485760 59250
2318926907
SDM TR_R_CLICKSTREAM TABLE ACCESS FULL
1,155 10485760 11550
804017134
SDM TR_R_OPTOUT TABLE ACCESS FULL
2,375 10485760 23750
3707567343
SDM USER_INFO TABLE ACCESS FULL
6,008 10485760 60080
3328028760
SDM VIRTUAL_MTA_LOOKUP_PK INDEX FAST FULL SCAN
43,265 1048576 43265
1150816681
SDM VIRTUAL_MTA_LOOKUP_PK INDEX FAST FULL SCAN
20,193 1048576 20193
Figure 8: Example output from FTS Report.
Notice instead of trying to capture the full SQL
statement I just grab the HASH value. I can then use the hash value
to pull the interesting SQL statements using SQL similar to:
select sql_text
from v$sqltext
where
hash_value=&hash
order by piece;
Once I see the SQL statement I use SQL similar
to this to pull the table indexes:
For complete scripts, see my complete Oracle
script collection at
www.dba-oracle.com/oracle_scripts.htm.
set lines 132
col index_name form a30
col table_name form a30
col column_name format a30
select
a.table_name,a.index_name,a.column_name,b.index_type
from dba_ind_columns a, dba_indexes b
where a.table_name =upper('&tab')
and a.table_name=b.table_name
and a.index_owner=b.owner
and a.index_name=b.index_name
order by
a.table_name,a.index_name,a.column_position
/
set lines 80
Once I have both the SQL and the indexes for the
full scanned table I can usually quickly come to a tuning decision
if any additional indexes are needed or, if an existing index should
be used. In some cases there is an existing index that could be used
of the SQL where rewritten. In that case I will usually suggest the
SQL be rewritten. An example extract from a SQL analysis of this
type is shown in Figure 9.
SQL> @get_it
Enter value for hash: 605795936
SQL_TEXT
----------------------------------------------------------------
DELETE FROM BOUNCE WHERE UPDATED_TS <
SYSDATE - 21
1 row selected.
SQL> @get_tab_ind
Enter value for tab: bounce
TABLE_NAME INDEX_NAME
COLUMN_NAME INDEX_TYPE
------------ --------------------------
-------------- ----------
BOUNCE BOUNCE_MAILREPRECJOB_UNDX
MAILING_ID NORMAL
BOUNCE BOUNCE_MAILREPRECJOB_UNDX
RECIPIENT_ID NORMAL
BOUNCE BOUNCE_MAILREPRECJOB_UNDX
JOB_ID NORMAL
BOUNCE BOUNCE_MAILREPRECJOB_UNDX
REPORT_ID NORMAL
BOUNCE BOUNCE_PK
MAILING_ID NORMAL
BOUNCE BOUNCE_PK
RECIPIENT_ID NORMAL
BOUNCE BOUNCE_PK
JOB_ID NORMAL
7 rows selected.
As you can see here there is no index on
UPDATED_TS
SQL> @get_it
Enter value for hash: 3347592868
SQL_TEXT
----------------------------------------------------------------
SELECT VERSION_TS, CURRENT_MAJOR,
CURRENT_MINOR, CURRENT_BUILD,
CURRENT_URL, MINIMUM_MAJOR, MINIMUM_MINOR,
MINIMUM_BUILD, MINIMU
M_URL, INSTALL_RA_PATH, HELP_RA_PATH FROM
CURRENT_CLIENT_VERSION
4 rows selected.
Here there is no WHERE clause, hence a FTS
is required.
SQL> @get_it
Enter value for hash: 4278137387
SQL_TEXT
----------------------------------------------------------------
SELECT STATUS FROM DB_STATUS WHERE DB_NAME =
'ARCHIVE'
1 row selected.
SQL> @get_tab_ind
Enter value for tab: db_status
no rows selected
Yep, no indexes will cause a FTS everytime?
Figure 9: Example SQL Analysis
Of course even after you come up with a proposed
index list you must thoroughly test them in a test environment as
they may have other 'side-effects? on other SQL statements, it would
be a shame to improve the performance of one statement and shoot six
others in the head.
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.
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.
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"
SEE CODE DEPOT FOR FULL
SCRIPT
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.
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.