R/3 On Oracle
Reorganize Memory
SAP encourages a buffer hit ratio of at least 95%.
[_] Dedicate a large amount of memory to the DB_BLOCK_BUFFER initialization parameter.
Spread I/O Around
[_] Segregate highly volatile tablespaces (such as PSAPSTABD and PSAPBTABD
containing main transaction tables VBAP, VBUP, and VBEP)
into separate dedicated tablespaces so that they can grow very large over many data files
with file striping to balance the load across many disk devices.
[_] Create two copies of each target tablespace to allow for COPY TABLE AS SELECT reorganization
between them.
[_]
Dedicate (partition) a separate disk controller for writing the archived redo logs.
To that mount point isolate high volumes and write activity with exclusive control.
Size Redo Logs
[_] Size redo logs so that they switch no more than once every 30 minutes.
[_] Set the LOG_BUFFER initialization parameter large enough to avoid redo log space requests.
[_] Buffer tables with many read accesses but with fewer than 100 changes per day.
Display table call statistics from the initial screen, Administration > System administration > Monitor > Performance > Setup/Buffers > Buffers > Detail analysis menu > Call statistics.
On the next screen, make a selection and choose Enter.
Sort the display according to the data records called.
Position the cursor on an entry in the column Calls (under DB activity) and choose Sort.
The Changes column indicates which tables are being changed most frequently since the system was started.
Size Rollback Segments
[_] Rollback segments should be set large enough to avoid "snapshot too old" messages,
while containing enough extents.
For example, if a 64MB rollback segment is large enough to avoid all "snapshot too old" messages, then allocate the rollback segment with the following code:
INITIAL=16M
NEXT=16M
MINEXTENTS=2
Freespace Not Needed In blocks
In its table creation data defintion language (DDL), SAP uses fixed-length CHAR datatypes rather than
VARCHAR (variable length character) datatype.
This is perhaps not all database vendors do not support it.
Oracle provides VARCHAR so that it can store data using the actual size of the field rather than
taking up blank space defined to make sure that users don't run out of room on a field.
However, because SAP tables are NOT created with VARCHAR datatypes
SAP produces large archived redo logs.
So it is not uncommon for large SAP databases to archive many gigabytes of information every hour.
Also for efficiency, Oracle performs I/O to/from disk in
fixed-sized (e.g., 4KB) blocks containing several rows.
Oracle accomodates varying actual VARCHAR lengths by reserving a percent of blocks free for expansion
as users add more bytes to VARCHAR fields.
As of SAP 4.0, SAP tables are defined with PCTFREE=10, which
means that 10% of every block is reserved for row expansion.
With default 4K blocks, the reserved space will equal 400 bytes (not counting block header space).
So any row that has more than 400 bytes in length is likely to encounter much additional I/0 looking for free blocks.
Ironically, since SAP does not use VARCHAR datatypes, expansion will never occur and the space is wasted.
Furthermore, PCTFREE=10 is not large enough because many SAP table rows are usually quite large (up to 15,775 bytes).
Thus, Oracle wastes additional I/O looking for free blocks which are usually too small anyway.
It is usually faster for Oracle to extend into new blocks than to reuse existing blocks, which
incurs I/O for Oracle to link and unlink blocks to a freelist of where free blocks are available for use.
Therefore, the strategy for SAP is:
[_] Set PCTFREE=1 (very low) to turn off freelist links/unlinks altogether.
Best yet, create tables with Oracle 10g EXTENT MANAGEMENT LOCAL.
[_] Because of large rows and use of LONG RAW within SAP's tables (particularly in the CLU tablespace)
increase Oracle's default block size to the largest allowed by the Unix operating systems (8K)
would minimize row chaining
when the row length exceeds the database block size.
user-defined SAP tables begin with the letter "Z".
PCTUSED
Burelson [1999, pg. 130] notes that, as of SAP 4.0,
SAP delivers all of its tables,
regardless of the database block size or the average row length for the table,
with PCTUSED=40.
This setting means that a block must become less than 40% full before being relinked on the table freelist.
The lower the value for PCTUSED, the less I/O the system will have at insert time,
and the faster the system can run.
A block will be nearly empty before it becomes eligible to accept new rows.
As a result, many SAP tables relink blocks onto the freelists,
even though there is not enough room on the block to accept a new row.
(pg. 132) "I've seen cases where changing the table values (using the Oracle ALTER TABLE command)
has more than doubled the throughput of transactions against a table."
[_] set PCTUSED so that newly linked blocks have enough room to accept rows.
Burelson's pctused.sql script calculates the setting for
PCTFREE and PCTUSED as a function of the number of rows to store between I/Os.
Cluster Resequence Tables
[_] When SAP table rows are resequenced in the same physical order as the table's primary key index,
adjacent rows reside on a cluster of contiguous blocks that Oracle can retrieve together all at once.
This situation saves as much as 50% in I/O with some SAP queries.
To identify tables that will benefit from resequencing,
analyze the SAP primary key indexes (i.e., those that end in a zero) to obtain
the clustering factor column in the DBA_INDEXES data dictionary view.
Just because a table has many extents does not necessarily mean it's inefficient.
Numerous benchmarks have repeatedly demonstrated that extended tables often outperform tables that reside in a single extent.
Due to file size limitations in older Unix systems,
larger SAP tables must be allocated in two-gigabyte chunks.
This means that any SAP tables that are more than two gigabytes must have more than two extents, since Oracle does not permit an extent to span a datafile.
Resequencing can be performed one of several ways:
Index Optimization
SAP tables are created with a primary key index (TABLENAME_0).
This means that full table index range scans are not needed.
This also means that during normal SAP processing does not take advantage of
Oracle's parallel query features which make use of SMP (Symmetric Multi-Processors) with multiple "independent" CPUs sharing a common communication bus.
However, SAP applications can take advantage of Oracle Parallel Server (OPS) services,
which enables several Oracle instances to read the same database.
A Distributed Lock Manager (DLM) specific to each hard disk manages access from several CPUs
and transfer data blocks between the buffer caches of each instance.
[_] To reduce pinging of data from other clusters,
each table needs a freelist group for each instance.
[_] Create large indexes with tablespaces that are twice as large as the index tree
so that the index can be rebuilt in place within the same tablespace.
SAP R/3 Performance Monitoring & Tuning
valid:
Internally, SAP provides a "Bandwidth Test" web service page
for clients to determine the speed of the network connecting them to SAP servers:
project phase
industry-standard benchmarks
SAP R/3 Implementation with ASAP: The Official SAP Guide (Hardcover)
(Sybex Inc., 1998)
SAP Planning: Best Practices in Implementation (Paperback)
(Sams; Pap/Cdr edition (May 19, 2003)
Implementing SAP With an Asap Methodology Focus (Paperback)
(Writers Club Press, July 2002)
Configuring SAP R/3 FI/CO: The Essential Resource for Configuring the Financial and Controlling Modules (Hardcover)
(Sybex; 1st edition, April 15, 2000)
Sams Teach Yourself SAP in 24 Hours
(Sams; 2 edition, November 7, 2005)
SAP NetWeaver For Dummies (John Wiley & Sons © 2004, 414 Hardcopy pages)
SAP R/3 Handbook, Third Edition
(McGraw-Hill/Osborne, 2006, 624 pages)
SAP R/3 Implementation Guide
(McGraw-Hill © 1999 (509 pages)
Testing SAP Solutions
(SAP Press, Hardcover, 2007)
Oracle databases are used by a large majority of SAP production instances.
Oracle SAP Administration
(Sebastopol, Calif.:O'Reilly and Associates, 1999)
Oracle Database Programming Using Java and Web Services
(Amsterdam, Boston: Elsevier, 2006)
To display all instance parameters, invoke report RSPARAM.
mySAP Tool Bag for Performance Tuning and Stress Testing
(prentice hall, 2006)
Basis Administration for SAP
(Premier Press © 1999, 580 pages)
sappfpar CHECK pf=C:\SAP\NSP\SYS\profile\NSP_DVEMGS00_BUBBA1
SAP Solution Manager
(SAP Press, February 12, 2007)
