The Association of System

Performance Professionals

The Computer Measurement Group, commonly called CMG, is a not for profit, worldwide organization of data processing
professionals committed to the measurement and management of computer systems. CMG members are primarily concerned
with performance evaluation of existing systems to maximize performance (eg. response time, throughput, etc.) and with capacity
management where planned enhancements to existing systems or the design of new systems are evaluated to find the necessary
resources required to provide adequate performance at a reasonable cost.

This paper was originally published in the Proceedings of the Computer Measurement Group’s 2009 International Conference.

For more information on CMG please visit http://www.cmg.org

Copyright 2009 by The Computer Measurement Group, Inc. All Rights Reserved
Published by The Computer Measurement Group, Inc., a non-profit Illinois membership corporation. Permission to reprint in whole
or in any part may be granted for educational and scientific purposes upon written application to the Editor, CMG Headquarters,
151 Fries Mill Road, Suite 104, Turnersville, NJ 08012. Permission is hereby granted to CMG members to reproduce this
publication in whole or in part solely for internal distribution with the member’s organization provided the copyright notice above is
set forth in full text on the title page of each item reproduced. The ideas and concepts set forth in this publication are solely those
of the respective authors, and not of CMG, and CMG does not endorse, guarantee or otherwise certify any such ideas or concepts
in any application or usage. Printed in the United States of America.
Capacity planning in an Oracle
database

1
Agenda
•
• Capacity Planning for database
servers
• Oracle10g tools for capacity planning
• Trend analysis
• Correlation analysis
• Capacity Forecasting

In this presentation, we will look at capacity planning for servers that use
Oracle10g database. Oracle10g provides a number of tools that can be used
as part of the capacity planning activity in order to determine not only current
usage but also gather data for trend analysis and forecasting.

Several key activities involved with Oracle10g capacity planning includes:

•How much system resources (CPU, SGA, Disk etc.) to allocate to the
database server?
•Sizing of various objects (tables, indexes)
•Oracle overhead for running PL/SQL, triggers etc.
•Determining the number, size and contents of tablespaces
•How much growth factor to account for? (influenced by Oracle settings such
as PCTUSED, PCTFREE)
•How much data to keep? How long to keep it for? (before it is archived)

2
We are expecting a
Blah, blah blah GigaBytes blah
significant increase in the blah, CPUs, blah, expensive
user base after the blah!
merger.

Business Rep Capacity 3

Planner

It is quite common to see a significant lack of communication between the

business and IT personnel. It is also common to see that business and IT
departments have different expectations from the system.

3
Capacity Planning

Capacity planning is part of the planning phase. This very important process
is completed with the intention of minimizing performance problems with the
system after it is implemented. In this sense, capacity planning involves
analysing the same kinds of issues that you would analyse when tuning the
system:
•How should you choose the system components to use?
•What type of architecture is suitable?
•What values should be set for the various parameters?
•What are the characteristics of the applications?
•What are the characteristics of the system?
•What business rules are to be implemented?
•How much can be forecasted or predicted about the system usage?
•What are the system requirements in terms of availability? Scalability?
Security? Performance?

4
IT Service Optimization
•
• Identify Business Requirements
• Prioritize Services and identify risks
• Establish Service Level Agreements
• Planning & Provisioning of Services
• Managing the performance of
Services

There can be several levels of capacity planning:

•Capacity Management of Resources
•Capacity Management of Services
•Capacity Management of Business processes

Establish Service Level Agreements:

•Workload Profile
•Throughput requirements
•Response time
•Number of users
•Data Storage requirements
•Business growth projections
•Availability requirements

5
IMP_TABSPC tablespace is full
again!!! This is a lot of work. I will have to
Reclaim free space from other search for free space, then resize,
tablespaces in the database at least a few dozen datafiles to get
server that are not 100% full 1 GB of free space on the server
and allocate to it. and then allocate to IMP_TABSPC
tablespace.

DBA Manager On-Call DBA 6

Business requirements and demands on application change all the time.

Further, capacity planning provides estimates that are based on
assumptions, sampling, statistics etc. As a result, capacity planning can’t
achieve perfection but good capacity planning should provide sufficient free
space for the database to grow for at least three months down the road. The
support team should have sufficient time to meet the database’s disk space
requirements.

It should avoid or at-least minimize the occurrence of fire-fighting situations.

6
Capacity Planning process
•

It requires the establishment of data collection

mechanisms and then following an iterative process.
1. Gather performance and capacity data
2. Analyze gathered data
3. Build a baseline view of current infrastructure
4. Identify and understand business plans and their impact
on infrastructure
5. Predict future resourcing needs
6. Report capacity plan & forecasts
7. Restart with step#1

Establish a Baseline System Performance Database

Collect detailed performance data for the workloads on the systems
under study. Workloads allow you to view system performance data from
a business or functional perspective. It is important to identify periods of
time that are considered adequate and representative of the problem
period, typical period as well as one for normal period.
Define the Peak Workload Period
Once the performance data has been collected, it can be analyzed to
define the peak workload period. The criteria used to identify the peak
period varies from system to system, depending on the types of
workloads being run. Many workload types use CPU utilization as the
metric used to establish the peak period. However, for Oracle database
servers, disk I/O may be the most important system activity to measure.
Build a Baseline Model of the System
A baseline model of the system can be derived by extracting the
performance data and system hardware configuration for the time interval
to be used as input to the system model. When calibrated, the model
represents the baseline system as it existed when you collected the
performance data. The baseline model shows system resource
utilization, throughput, response time, and queue delay (stretch) factors
for each workload defined.
Perform "What If" Analysis
Several types of what-if analysis can be performed using the baseline
system model to see what the performance impact might be:
• Increasing the amount of work the system is doing
• Modifying the system hardware configuration such as adding CPUs,
increasing CPU speed, adding I/O controllers or changing their
performance characteristics, adding disk drives, changing RAID
characteristics, and so forth.
• Combining workloads from different systems on a single system 7
Planning capacity for
migrations
•

• Type of Source system

• Amount of data load
anticipated
• Process of data migration
• Application type
8

Capacity planning for Oracle databases requires understanding certain key characteristics of the source system:
(1) Source / Input system
• Is the source system a legacy RDBMS or flat files?
• What is the size of data in legacy RDBMS or flat files?
• What % of data (or columns) are to be loaded from the legacy RDBMS or flat files?
(2) Amount of data loads anticipated
• What is the size of the initial data load?
• What is the size of daily/incremental data load?
• What is the daily size of user input data?
(3) How is data migrated from the staging to the production database?
• Is de-normalised source data loaded into normalised destination database tables?
• Are there any additional intersection tables or cross reference tables created?
• Is normalised source data loaded into de-normalised destination database tables?
• Is there space required for holding flat files for loading data into database.
• Is there space required for holding the import / export dump files of the database.
• Consider space for maintaining multiple versions of software homes (ORACLE_HOME) on the server.
(4) What is the type of application?
• OLTP or DSS? (add additional space for temporary tablespace, redo log groups, undo tablespace and
archive log destinations as required)
(5) Additional database disk space required (overhead)
• System tablespace
• Sysaux tablespace
• Undo tablespace
• Temporary tablespace
• Redo log groups
(6) Archivelog destination size
• Average amount of redo generated by the application
• Frequency of jobs that are scheduled to perform archivelog backups and cleanup of the archivelog
destination
• Additional disk space should be considered for situations such as archivelog backup failures and the
destination getting full
(7) Consider storage settings for tablespaces and database objects such as PCTFREE
PCTFREE is the percentage of space reserved by Oracle in each block. This reserved space is never used
for inserting data but is reserved for the future expansion of existing rows. The default pctfree is 10%. When
the PCTFREE parameter is increased, the number of rows that can be inserted into an oracle block decrease
resulting in a boost up of the disk space requirements for the database.

8
Planning capacity for
migrations
•

• Index requirements
• Server architecture
• Backup requirements

(8) Index space requirements

• Type of application (OLTP/DSS)
• Number of columns indexed per table
• Types of indexes
• Are indexes compressed?
• Are Multi-column /Compound column indexes created?
(9) Server architecture
• Standalone server (single instance running on single stand-alone server)
• Oracle RAC (Real application clusters)
o Number of nodes in the RAC
o For each node, consider space for private undo tablespace, archivelog destination and redo log
threads
• Oracle replication
o Number of replicated sites
o Consider space for resynchronisation of replicated sites
• Standby database
o Primary and standby database require equal amount of disk space
o Type of standby database (physical or logical)
o Archivelogs to be transported to the standby database
(10) Backup requirements
Allocate sufficient disk space requirements for database backup based on:
• Frequency of backups scheduled
• Types of backups scheduled
• Location where backups are stored
• Backup retention policy
Some of the best practices followed in Oracle systems include:
• Retain the latest full backup on the database server
• Backup older files onto tapes
• Retain at least 15 days worth of backups on tape
• At least, once in every 45 days, restore your backups onto a different server and recover your database

(11) Add some insurance/padding to cater for unknowns/assumptions

9
Useful Oracle tools
•
Need to collect historical performance data over time.
• V$SYSSTAT
• CPU used by this session
• User commits
• Session logical reads
• User calls
• SQL Net Get
• SQL Net Send
• Physical reads
• Physical writes
• DB Block changes
• STATSPACK
• Introduced in Oracle8i (Oracle 8.1.6)
• Data is collected and stored in Oracle tables which can be used
for trend analysis
• All the tables are owned by PERFSTAT and begin with STATS$
• Take snapshots every N seconds and compare the resource
utilization parameters
• Automatic Workload Repository (AWR)
• Supersedes Statspack in Oracle10g
10

A variety of tools are required to conduct proper capacity planning:

•Data Collection
•Historical data store
•Data analysis
•Capacity Modeling
•Reporting

10
Useful Oracle tools
•

Look for database signatures by

performing trend analysis.
• STATSPACK
• Oracle supports
STATSREP.SQL but no trend
analysis.
• Automatic Workload Repository
(AWR)
• Oracle supports AWRRPT.SQL
but no trend analysis. 11

DBAs commonly use trend analysis to understand the behavior of the

database at various times. This type of analysis also helps in identifying
patterns of behavior in terms of database usage. STATSPACK provides a
large number of statistics that can be used to generate different types of
trend reports. Here are some useful metrics:
* Data buffer hit ratio—This metric gives an idea of the effectiveness of the
database buffer cache and the setting of the db_cache_size parameter.
* Physical disk reads and physical disk writes—These metrics give a
good idea about the I/O activity in the system.
* I/O waits—This metric can be used to determine I/O contention problems
and can indicate whether the physical design of the database is good.
* Sorts—This metric is an indication of the effectiveness of sort activity.
* Buffer busy waits—This metric is an indication of freelist contention and
can also indicate concurrent UPDATE or INSERT activity on the same
object.

11
Capacity - Storage
•
• Size of Datafile
• Size of Datafile contents
• DBMS_SPACE package
• Statistics gathering
• Column data length
• Oracle Enterprise Manager Capacity
Planner package
12

Database storage capacity planning involves 2 main subject areas: Current space usage and potential space
usage growth. There are variety of techniques available and it is important to note that some are accurate but can
have a performance impact and be time-consuming while other techniques can be fast but inaccurate.
•Size of datafile
This technique involves getting the datafile size of all data-containing datafiles from the operating system.
Inaccurate.
•Size of Datafile content
Involves joining DBA_DATA_FILES and DBA_FREE_SPACE views. Extent-based calculation but inaccuracies
can result due to PCTINCREASE settings. Oracle10g has locally-managed tablespaces which do not use
PCTINCREASE and hence avoid this problem.
•DBMS_SPACE package
More reliable than the datafile size methods but can result in about 50% inaccuracies if PCTUSED and PCTFREE
settings are left to the installation defaults. If a database has a lot of delete activity, either in many or a few large
tables, this method could give very misleading results.
•Statistics gathering
Using STATSPACK etc. tools, usage statistics can be gathered but this can be resource-intensive if not used
properly. Statistics can be gathered for indexes and tables and even for a whole schema or an entire database.
•Column data length
All column lengths in all rows in all tables in a database can be counted using length functions for string fields plus
fixed known lengths for all other basic data types. Good for a purely relational database but for those that have
object types, it is not reliable. The only variable length simple datatypes in Oracle are the VARCHAR2 datatypes
which would require using the LENGTH function to the column to find the length of the data in the column. All
other datatypes will have fixed lengths. The column length specifications of the table can be obtained by selecting
the appropriate columns from the USER_TAB_COLUMNS Oracle performance view. The resulting calculation
simply needs to multiply the number of rows in each table with the sum of the lengths of its datatype defined
column lengths. Index sizes can be assessed simply by multiplying table rows again by the sum of the lengths of
the columns for all indexes created on each table. Indexes can be erroneous due to null values not being stored in
indexes in some cases. However, most modern databases use object front-end applications which tend to avoid
use of composite keys for anything but many-to-many join resolution entities; these entities usually contain
integers in their key values which are never null.
•Oracle Enterprise Manager Capacity Planner package
It provides lots of very nice graphical representation, automated warnings and bells and whistles.

12
Capacity – Storage using
DBMS_SPACE
•
• Estimating the space use of a table
The CREATE_TABLE_COST procedure of the DBMS_SPACE
package lets you estimate the space use cost of creating a
table.
• Estimating the space use of an index
The CREATE_INDEX_COST procedure of the DBMS_SPACE
package lets you estimate the space use cost of creating an
index on an existing table.

13

Three procedures in the DBMS_SPACE package provided by Oracle can be used to predict the size of new
objects and monitor the size of existing database objects:
(1) The CREATE_TABLE_COST procedure of the DBMS_SPACE package lets you estimate the space use cost
of creating a table. The procedure has two variants:
(a) The first variant uses average row size to estimate size and requires as input the following values:
•TABLESPACE_NAME: The tablespace in which the object will be created.
•ROW_COUNT: The anticipated number of rows in the table.
•PCT_FREE: The percentage of free space you want to reserve in each block for future expansion of existing
rows due to updates.
•AVG_ROW_SIZE: The anticipated average row size in bytes.
(b) The second variant uses column information to estimate table size and requires as input the following values:
•TABLESPACE_NAME: The tablespace in which the object will be created.
•ROW_COUNT: The anticipated number of rows in the table.
•PCT_FREE: The percentage of free space you want to reserve in each block for future expansion of existing
rows due to updates.
•For each anticipated column, values for COLINFOS, which is an object type comprising the attributes COL_TYPE
(the datatype of the column) and COL_SIZE (the number of characters or bytes in the column).
Two values are returned:
•USED_BYTES: The actual bytes used by the data (including overhead).
•ALLOC_BYTES: The amount of space anticipated to be allocated for the object.
(2) The CREATE_INDEX_COST procedure of the DBMS_SPACE package lets you estimate the space use cost
of creating an index on an existing table. The procedure requires as input the following values:
•DDL: The CREATE INDEX statement that would create the index. The table specified in this DDL statement must
be an existing table.
•[Optional] PLAN_TABLE: The name of the plan table to use.
Two values are returned:
•USED_BYTES: The number of bytes representing the actual index data.
•ALLOC_BYTES: The amount of space allocated for the index in the tablespace.
It should be noted that the results returned by this procedure depend on statistics gathered on the segment.

13
Capacity – Storage using DBMS_STATS
This query sums total physical size of all tables and indexes in a schema
set termout off echo off feed off trimspool on head off pages 0 timing off;
spool ./scripts/statistics.log
./scripts/statistics.log;;
select 'exec dbms_stats.gather_table_stats(''ACCOUNTS'','''||table_name||''');'
dbms_stats.gather_table_stats(''ACCOUNTS'','''||table_name||''');' from
user_tables;
user_tables;
select 'exec dbms_stats.gather_index_stats(''ACCOUNTS'','''||index_name||''');'
dbms_stats.gather_index_stats(''ACCOUNTS'','''||index_name||''');' from
user_indexes;
user_indexes;
spool off;
set termout on timing off;
@@./scripts/statistics.log
@@./scripts/statistics.log;;
SELECT
SUM((t.BLOCKS+SUM(i.LEAF_BLOCKS/DECODE(i.BLEVEL,0,1,i.BLEVEL))))*
SUM((t.BLOCKS+SUM(i.LEAF_BLOCKS/DECODE(i.BLEVEL,0,1,i.BLEVEL))))*
8192 AS BYTES
,ROUND(SUM((t.BLOCKS+SUM(i.LEAF_BLOCKS/DECODE(i.BLEVEL,0,1,i.BL
EVEL)))
*8192/1024/1024)) AS MB
,ROUND(SUM((t.BLOCKS+SUM(i.LEAF_BLOCKS/DECODE(i.BLEVEL,0,1,i.BL
EVEL)))
*8192/1024/1024/1024),1) AS GB
FROM USER_TABLES t, USER_INDEXES i
WHERE t.TABLE_NAME = i.TABLE_NAME
GROUP BY t.TABLE_NAME,t.BLOCKS;
t.TABLE_NAME,t.BLOCKS;
exit; 14

Using statistics to calculate the number of blocks or byte size for a table is
simple. After statistics have been generated,
(a) Query the USER_TABLES Oracle performance view as shown below:
SQL> SELECT TABLE_NAME,
NUM_ROWS,BLOCKS,BLOCKS*db_block_size AS BYTES FROM
USER_TABLES;
(b) Find the db_block_size (database block size), when logged in as SYS or
SYSTEM, by executing the query shown below.
SQL> SELECT VALUE FROM V$PARAMETER WHERE
NAME=’db_block_size’;
(c ) Add up the size of all tables in a specified schema
SQL> SELECT SUM(BLOCKS*db_block_size) AS BYTES,
SUM(BLOCKS*db_block_size)/1024/1024 AS MB FROM USER_TABLES;
As with the USER_TABLES view, obtain the total size of all indexes in a
schema by executing a query like this one.
SQL> SELECT SUM(LEAF_BLOCKS*db_block_size/BLEVEL) AS BYTES,
SUM(LEAF_BLOCKS*db_block_size/BLEVEL)/1024/1024 AS MB FROM
USER_INDEXES;

14
Capacity – CPU and
MEMORY
•
V$OSSTAT
Displays system utilization statistics from the operating system.
system. One
row is returned for each system statistic.
V$SYSMETRIC_HISTORY
Displays all system metric values available in the database.
database. Both long
duration (60-
(60-second with 1 hour history) and short duration (15-
(15-second
with one-
one-interval only) metrics are displayed by this view.
DBA_HIST_OSSTAT
Displays historical operating system statistics. This view contains
contains
snapshots of V$OSSTAT
DBA_HIST_SYSMETRIC_HISTORY
Externalizes all available history of the system metric values
values for the
entire set of data kept in the database
DBA_HIGH_WATER_MARK_STATISTICS
Displays information about database high-
high-watermark statistics.

15

Operating system monitoring tools can be used to determine what processes are running on
the system. When bottlenecks are encountered, you can check the memory, I/O, and
process management areas using tools such as sar -u on many UNIX-based systems
allowing you to examine the level of CPU utilization on your entire system. CPU utilization in
UNIX is described in statistics that show user time, system time, idle time, and time waiting
for I/O. On Windows, use the administrative performance tool to monitor CPU utilization.
This utility provides statistics on processor time, user time, privileged time, interrupt time,
and DPC time.
In Oracle 10g CPU and memory statistics are gathered by default. The V$OSSTAT or
V$SYSMETRIC_HISTORY views can be used to monitor system utilization statistics from
the operating system. Some useful statistics contained in V$OSSTAT and
V$SYSMETRIC_HISTORY include:
-Number of CPUs
- CPU utilization
- Load
- Paging
- Physical memory
- Average IO Wait time
Historical information provided by these views can also be accessed from
DBA_HIST_SYSMETRIC_HISTORY and DBA_HIST_OSSTAT.

DBA_HIGH_WATER_MARK_STATISTICS can be used to obtain statistics such as:

•Size of largest segment
•Maximum number of partitions belonging to a user table / index
•Maximum number of concurrent sessions
•Maximum database size
•Maximum query length

15
Capacity – CPU and
•
MEMORY
STATSPACK limitations
The Oracle statspack tool doesn’
doesn’t show the aggregate demand upon the
database server. In other words, it is not easy to determine the server stress.
Using VMSTAT
# capture vmstat output
cat /tmp/reports/msg$$|sed
/tmp/reports/msg$$|sed 1,3d | awk '{ printf("%s %s %s %s %s\n", $1, $8,
$9, $14, $15) }' | while read RUNQUE PAGE_IN PAGE_OUT USER_CPU
SYSTEM_CPU
do
$ORACLE_HOME/bin/sqlplus
$ORACLE_HOME/bin/sqlplus -s system/manager@orcl
system/manager@orcl<<EOF
<<EOF
insert into sys.vmstats_dat
values (
sysdate,
sysdate,
$SAMPLE_TIME,
'$SERVER_NAME',
$RUNQUE,
$PAGE_IN,
$PAGE_OUT,
$USER_CPU,
$SYSTEM_CPU);
EXIT
EOF
done
done 16

Statistics about resource usage can be captured at the server level as well as the Oracle database level. The
Oracle Statspack utility can be used to capture snapshots showing the differences in system interaction over pre-
specified periods of time. Statspack captures information relating to the server such as:
•Physical I/O.
•CPUs used by specific transactions.
•RAM memory used by specific transactions
However, the Oracle statspack tool doesn’t show the aggregate demand upon the database server. In other
words, it is not easy to determine the server stress.
The UNIX utility VMSTAT which is available on most UNIX implementations can be helpful to determine the stress
on the database server and store the results within the Oracle database as follows:
# sample every ten minutes (600 seconds)
SAMPLE_TIME=600
while true
do
vmstat ${SAMPLE_TIME} 2 > /tmp/reports/msg$$
# capture vmstat output
cat /tmp/reports/msg$$|sed 1,3d | awk '{ printf("%s %s %s %s %s\n", $1, $8, $9, $14, $15) }' | while read
RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU
do
$ORACLE_HOME/bin/sqlplus -s system/manager@orcl<<EOF
insert into sys.vmstats_dat
values (
sysdate,
$SAMPLE_TIME,
'$SERVER_NAME',
$RUNQUE,
$PAGE_IN,
$PAGE_OUT,
$USER_CPU,
$SYSTEM_CPU);
EXIT
EOF
done
done
rm /tmp/reports/msg$$ 16
Capacity – Using LOGMINER
•
SQL> SELECT operation, to_char(timestamp,'HH')
to_char(timestamp,'HH')
hour, count(*) total
SQL> FROM v$logmnr_contents
SQL> WHERE seg_name = 'AUDIT_LOG' AND
SQL> seg_owner = ‘MTHAKKAR' AND
SQL> seg_type_name = 'TABLE'
SQL> GROUP BY operation, to_char(timestamp,'HH');
to_char(timestamp,'HH');
• OPERATION HOUR TOTAL
• --------- ---- ----------
• INSERT 10 21
• INSERT 11 19
• INSERT 12 20
• INSERT 13 21
17

LogMiner provided by Oracle can be used to do some performance tuning or

capacity planning. Say that you've got a table that is busy capturing audit
information for your application. The insert rate in the table can be
determined by loading up LogMiner with an hour's worth (or some other time
period) of archive log file and running a query against those archive logs:
SQL> SELECT operation, to_char(timestamp,'HH') hour, count(*) total
SQL> FROM v$logmnr_contents
SQL> WHERE seg_name = 'AUDIT_LOG' AND seg_owner = ‘MTHAKKAR'
AND seg_type_name = 'TABLE'
SQL> GROUP BY operation, to_char(timestamp,'HH');
OPERATION HOUR TOTAL
--------- ---- ----------
INSERT 10 21
INSERT 11 19
INSERT 12 20
INSERT 13 21

17
Parameters affecting AWR and ADDM
•
• STATISTICS_LEVEL
• TYPICAL (default)
• ALL

• CONTROL_MANAGEMENT_PACK_ACCESS
• DIAGNOSTIC+TUNING (default)
• DIAGNOSTIC

18

The STATISTICS_LEVEL and

CONTROL_MANAGEMENT_PACK_ACCESS parameters must be carefully
set when using AWR and ADDM tools of Oracle10g for capacity planning.

18
Object growth trends using
DBMS_SPACE
•
• OBJECT_GROWTH_TREND procedure of the
DBMS_SPACE package
• It returns a table, each row of which provides space use
information on the object for one interval.
• INPUTS:
- OBJECT_OWNER,
- OBJECT_NAME,
- PARTITION_NAME,
- OBJECT_TYPE,
- START_TIME,
- END_TIME,
- INTERVAL and
- SKIP_INTERPOLATED
• OUTPUT table has the following columns:
• TIMEPOINT,
• SPACE_USAGE,
• SPACE_ALLOC and
• QUALITY 19

The OBJECT_GROWTH_TREND procedure of the DBMS_SPACE package produces a table of one or more
rows, where each row describes the space use of the object at a specific time. It retrieves the space use totals
from the Automatic Workload Repository or computes current space use and combines it with historic space use
changes retrieved from Automatic Workload Repository.

This procedure takes the following inputs:

•OBJECT_OWNER: The owner of the object.
•OBJECT_NAME: The name of the object.
•PARTITION_NAME: The name of the table or index partition
•OBJECT_TYPE: The type of the object.
•START_TIME: A TIMESTAMP value indicating the beginning of the growth trend analysis.
•END_TIME: A TIMESTAMP value indicating the end of the growth trend analysis. The default is "NOW".
•INTERVAL: The length in minutes of the reporting interval.
•SKIP_INTERPOLATED: This setting is useful when the result table will be displayed as a table rather than a
chart, because you can see more clearly how the actual recording interval relates to the requested reporting
interval.

The output table has the following columns:

•TIMEPOINT: A TIMESTAMP value indicating the time of the reporting interval.
•SPACE_USAGE: The number of bytes actually being used by the object data.
•SPACE_ALLOC: The number of bytes allocated to the object in the tablespace at that time.
•QUALITY: A value indicating how well the requested reporting interval matches the actual recording of statistics.
The values of the QUALITY column are:
oGOOD: The value is based on recorded statistics with a recorded timestamp within 10% of the
specified INTERVAL.
oINTERPOLATED: The value was based on recorded statistics before and after the value of TIME.
Current in-memory statistics can be collected across all instances in a cluster and treated as the
"recorded" value for the present time.
oPROJECTION: The value of TIME is in the future

19
Trend analysis - example
•
Listing: Determining the Average I/O by Hour of the
Day
Set pages 9999;
Column phyreads format 999,999,999
Column phywrites format 999,999,999
Select To_char(snap_time,
To_char(snap_time, 'HH24'),Avg(physical_reads)
phyreads,Avg(physical_writes)
phyreads,Avg(physical_writes) phywrites
From Perfstat.stats$buffer_pool_statistics bp,
bp,
Perfstat.stats$snapshot sn,
sn,
Where Bp.snap_id = sn.snap_id
Group by To_char(snap_time,
To_char(snap_time, 'HH24');

20

After STATSPACK captures the various performance statistics, you can

generate various trend reports using the data gathered. These trend reports
would provide signatures for various database metrics. For example, you
might observe that on Monday mornings between 8 a.m. and 9 a.m., the I/O
activity is extraordinarily high. This knowledge can help in taking the proper
actions. In order to forecast the database growth trend properly, you must
archive and analyze the data that is collected by any tool such as
STATSPACK. The script below shows how to plot the average I/O occurring
by hour of the day.

Listing: Determining the Average I/O by Hour of the Day

Set pages 9999;
Column phyreads format 999,999,999
Column phywrites format 999,999,999
SelectTo_char(snap_time, 'HH24'),Avg(physical_reads)
phyreads,Avg(physical_writes) phywrites
From Perfstat.stats$buffer_pool_statistics bp, Perfstat.stats$snapshot sn,
Where Bp.snap_id = sn.snap_id
Group by To_char(snap_time, 'HH24');

20
Capacity Forecasting
CPU Forecast
3500 700

New Server Capacity 3010 (RPE2)

3000 600

2500 500

Peak Concurrent Users

2000 400
RPE2

1500 300

1000 200
Current Server Capacity 1020 (RPE2)

500 100

0 0
May-07

May-08

May-09
Nov-06
Dec-06
Jan-07
Feb-07
Mar-07
Apr-07

Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07
Jan-08
Feb-08
Mar-08
Apr-08

Jun-08
Jul-08
Aug-08
Sep-08
Oct-08
Nov-08
Dec-08
Jan-09
Feb-09
Mar-09
Apr-09

Jun-09
Jul-09
Aug-09
Sep-09
Oct-09
Nov-09
Dec-09
Jan-10
Feb-10
Month
Actuals
Actual Trades Forecast is Within Capacity Forecast 75 Pctl > Capacity
Average Utilisation 75 pctl Utilisation 95 Pctl Utilisation Forecast Trades Forecast Max > Capacity Forecast Avg > Capacity

21

Trend analysis:
•Extrapolation of resource utilization over time
•Simple and does not require sophisticated tools
•Not suitable for multi-tiered systems that experience
non-linear behavior
Simulation Modeling:
•Simulate the running of processes and events that
occur during transaction and batch executions
•Time consuming to build and run
•Can be resource intensive
Analytical Modeling:
•Mathematical techniques are applied to develop
baseline view of the selected time period
•Formulas are used to apply planned business growth
to the baseline developed and predictions can be
made regarding future resource requirements
•Can be fast and accurate

21
Capacity and Performance of
Oracle systems
TRAP: 0% idle CPU indicates a very busy
CPU and therefore is a bottleneck
Why do we fall into this trap?
CPU represents processing power,
therefore with more power you get better
performance
Fact: CPU might be busy doing something
useful or something useless.
ROT: Investigate the reasons for high CPU
utilization and cure the disease not just
the symptoms

22

CPU operates in tiny slices of time referred commonly as cycles. During

each CPU cycle, it is either busy or not. CPU utilization is the number of
CPU busy cycles in a certain interval divided by the total number of cycles in
that interval. CPU utilization is a way of averaging the busy and non-busy
cycles during an interval to give us some indication of the CPU capacity
being used.

In a batch-dominant environment, having CPU utilization at 100% can be

very good because it indicates that CPU is able to process the jobs as fast
as possible without queuing jobs at other system components affecting
overall performance and response.

For systems, dominated by interactive users, it is common to have lower

CPU utilization to allow for some headroom to cater for large queuing
variance thereby minimizing the impact on the response time of the users.

Response time = Service time + Queue time

Each CPU cycle that is unused is wasted forever.

22
Capacity and Performance of
Oracle systems
Considerations:
• Is there a performance problem at
all?
• % of CPU capacity being wasted
waiting for I/O
• % of CPU capacity being wasted
due to OS overhead
• Monitor the run queue depth

23

In understanding CPU utilization, it is important to note whether or not there

is a performance problem being experienced.

If performance problems related to application response times are being

experienced, then we should check what the CPU is actually doing.
Determine the percentage of CPU capacity that is being wasted while
waiting for I/O (e.g. check %wio+%idle in sar –u command for Unix and/or
%Interrupt Time counter of the processor object in Windows). High levels of
context switching and paging activity may result in high OS overhead and
this should be analysed. E.g. a CPU wait of 55% for I/O would indicate a
system that has an I/O bottleneck and not a CPU bottleneck. Likewise,
investigation for high OS overhead should be conducted before simply
looking at CPU upgrades (e.g. check %sys in sar –u command for Unix
and/or %Privileged Time counter of the processor object in Windows).

Once the underlying bottleneck has been determined, effort should focus on
solving that problem, E.g. in a database system experiencing I/O contention,
CPU-intensive applications such as sub-queries should be rewritten to
minimize the logical I/O that might be occurring.

The depth of the run queue should be monitored to determine how frequently
contention occurs.

23
Capacity and Performance of
Oracle systems

Figure: Wait Event Analysis in an Oracle database

24

An understanding of where time is being spent and what resources are being
waited on is vital to understand the true utilization of key system resources.

The above figure represents the outcome of a wait event analysis in an

Oracle database.

From this figure, we can see that ‘Direct path I/O’ is the main event being
waited on and an analysis of the activity in the system during that time will
reveal the application components that are requesting I/Os of that particular
type.

End-to-end response time can be derived by combining the response time

for all the key architectural components and realizing that response time for
any component is the sum of the service time and queue time for that
component.

24
CPU Correlation Analysis

25

There are several key activities involved with capacity planning of

database servers:
•Perform data collection
•Ensure that the data view matches the system reality
•Build profiles for each component or group of components
•Identify repetitive cycles
•Identify representative time frames
•Obtain business volumes and growth projections

25
Capacity Planning for Oracle
RAC

26

Many mission critical applications tend to be very demanding and

are often load balanced both at front end (application servers)
and backend (database servers). Oracle RAC is a solution
commonly used in Oracle implementations. Capacity planning for
an Oracle RAC environment requires proper understanding and
careful planning.

Oracle Real Application Clusters (RAC) allows multiple instances

running on separate nodes to share a single database on shared
storage. Clusterware is used to bound the nodes using a private
network. Oracle clusterware is a mandatory part of the RAC
configuration and prevents a failed node from corrupting the
shared storage or database.

26
OBJECT_GROWTH_TREND
procedure for RAC
•
•
• Each instance can choose independently which
objects will be selected.
• The output is an aggregation of values recorded
across all instances.
• The aggregate value returned is marked GOOD if at
least 80% of that value was derived from GOOD
instance values.
• Value can be computed from a combination of GOOD
and INTERPOLATED values.
27

Several components need special attention:

•Storage: SAN, NAS, DAS
•HBA (Host Bus Adapters)
•Network NICs: High Speed Interconnects, Redundant switches,
VLANs, NIC Teaming
•Software stack: OS (Windows, Linux, Solaris, AIX)
•Hardware: CPUs, Memory, Sockets (Dual socket, Quad Socket)

27
After the merger in about
The current capacity of the
6 months, we are
database server is
expecting a 70%
insufficient for this additional
increase in the user
load and an upgrade will be
base.
required at a cost of
$150K.

Business Rep Capacity 28

Planner

It is common to start looking at the CPU when it is shooting over a 75%

range for a length of time, and then relying on your instincts to scale out your
infrastructure before an unplanned downtime is encountered. However, a
capacity planner for Oracle systems should have a good understanding of
the Oracle architecture and various configurations in use. This is in addition
to the need to properly understanding the application workload and data
requirements.

A clear understanding of requirements goes a long way in coming up with

good estimates for capacity.

28
Summary
•
• Use appropriate tools to perform capacity
planning
• Develop a symbiotic relation between
business and IT
• Identify key workload drivers
• Correlation between workload and resource
utilization should be identified.

29

Performance analysis and capacity planning is a continuous effort.

Subsequent to an application or environment change, the performance
analysis has to be revisited and the capacity planning model re-calibrated.
For a system that is operating on the upper limit of its capacity, performance
optimization is a continuous search for the next resource constraint.

Good capacity planning provides a cushion for sudden bursts in load and
can tolerate some application inefficiencies.

Capacity planning for Oracle systems is more than sticking a wet finger in
the air. It requires some special considerations and tools. The database
server should not be treated just like another server. In addition to a robust
and reliable infrastructure, you should gather several key metrics using
Oracle provided tools that will help you determine when and where you can
use more capacity: on a node level (advisable) or on a per machine level
(CPU, Memory etc).

29