Basis and others: November 2013

SAP Oracle System Refresh from PRD to QAS while PRD is running. Short Notes

Three Important notes

First - The below is assuming that you are performing system refresh while PRD system is still running and you do not want to stop the system or can't point to tape backup of online
database backup performed on PRD to QAS server.

Second - Important note would be to READ step 1 to 16 first !!!! before starting step 1. or maybe read step 15!!

Third - I would consider this as a short note as by explanation its very descriptive in nature that a 1.5yrs basis whom have experience in Oracle and SAP should able to grasp the idea.

1. Issue the below command in PRD system

sqlplus "/as sysdba"

alter database begin backup;

commit;

Additional info : You may issue " select * from v$backup to see if the content is active"

2. Perform the below on PRD system

alter database backup controlfile to trace;

3. Copy the PRD sapdata files and redo log files to QAS system location ( assuming sapdata1-4 in PRD is in G and QAS also G drive)

4. Once copying sapdata1-4 completed

sqlplus "/as sysdba"

alter database end backup;

commit;

5. Now copy the trace file performed in step 2 to QAS.

6. Edit the trace file and use the second part control script and change the REUSE to SET and remove all other entries and save it as control.sql

7. Login in QAS and now execute as below assuming the control.sql is being saved in C:\ drive

sqlplus "/as sysdba"
@control.sql

8. During Step 1, 3 and 4, we need to make note the -20 ( to be save ) archive logs generated before step 1 and + 20 archive logs generated after step 4.

9. Copy over the logs to QAS system oraarch directory

10. Now assuming we continue from step 7. Issue the command below, this will apply all the logs generated during the copy in step 3. You may enter cancel on the file log request.

alter database recover database using backup controlfile;

11. Now you may perform the below

alter database open resetlogs;

12. Now you may stop and start Oracle as normal assuming step 11 does not ask for any more archive logs from production.

13. In step 2 where the trace file created, right at 2nd part of control script, there's a create PSAPTEMP datafiles in reuse state. If you notice 2-3 just execute the script in QAS.

14. Stop and Start Oracle, if there's no issue consider technical part of system refresh from PRD to QAS completed.

15. Now you may proceed with Post activities which are those standards stuffs such as pre-requisite performed before step1, such as printer, bdls run, stop all prd jobs, backups, etc, etc.

16. Step 15 will take 16-26hrs based on system size and its resources.

Hope you have enjoyed.

1020260 - Delivery of Oracle statistics (Oracle 10g, 11g)

Symptom

The performance with Oracle 10g or higher is poor. This is due to unfavorable decisions by the Cost Based Optimizer.

Last change of the SAP Note: NOV/26/2013

Reason and Prerequisites

In certain situations, the Cost Based Optimizer (CBO) may make unfavorable decisions, although there are no CBO errors, for example:

Tables with heavily fluctuating volumes of data (Note 756335)

Selective conditions on columns that do not have many attributes (Note 176754 (8))

Correlated values in different columns

In Oracle 9i or lower, performance problems were solved by activating the Rule Based Optimizer (RBO) by using the RULE hint, or by not creating CBO statistics. However, this is no longer possible since RBO is no longer supported in Release 10g or higher. In addition, with Oracle 9i, there are already situations in which the RBO cannot be used (for example, in the case of FIRST_ROWS hint).

Due to several bugs you cannot use the Bind Value Peeking to optimize such situations either.

Alternatively, implement the script for the automatic postprocessing of critical statistic values using DBMS_STATS functions (Note 724545), which is attached to this note. In addition, it ensures that the changes remain in the system permanently and are not overwritten by BRCONNECT statistical runs.

The script is designed for systems with Oracle 10g or higher, but it can also be used for Oracle 9i or lower. In the case of Oracle 9i, you must ignore the PLS-00302 error issued by the system. Note that the delivered statistics may lead to unfavorable accesses under Oracle 9.2.0.6 or lower (for example, die to the Oracle bug 3566843, see Note 176754). Therefore we recommend that you use Oracle 9.2.0.7 or higher.

Currently, statistics are delivered for the following tables:
/SAPAPO/MATLOC
/SAPAPO/ORDADM_I
AFKO
AFPO
AFVC
ARFCRSTATE
ARFCSDATA
ARFCSSTATE
AUFK
AUSP
BBP_PDHGP
BDCP2
BKPF
CATSDB
DDXTF
DDXTT
DFKKOP
DRAW
EDIDC
EKKN
HRP1001
LTAK
MARA
MLST
MSEG
PAYR
QREFTID
RSBATCHDATA
SMOEJOBID
SMOFCDBHD
SMOFCMPDAT
SMOFCMPHD
SMOFCMPOBJ
SMOHJOBQ
SMOHMSGQ
SMOHMSGQRE
SMOHSITEQ
SMOHSITEQEX
SMOHSITEQRD
SWWWIHEAD
SXMSCLUP
SXMSCLUP2
SXMSCLUR
SXMSCLUR2
SXMSPEMAS
SXMSPEMAS2
SXMSPERROR
SXMSPERRO2
SXMSPHIST
SXMSPHIST2
SXMSPMAST
SXMSPMAST2
SXMSPVERS
SXMSPVERS2
TATAF
TBTCO
TESTDATRNRPART0
TRBAT
TRBAT2
TRFCQDATA
TRFCQIN
TRFCQOUT
TRFCQSTATE
UPSITX
VEPO
XI_AF_SVC_ID_MAP
XI_IDOC_IN_MSG
XI_IDOC_OUT_MSG

Solution

A prerequisite for using the delivered statistics is that you use BRCONNECT 7.10 (25) or higher so freezing individual statistics in accordance with SAP Note 1374807 is successful. If required, first apply a current patch before you implement statistics. If you import statistics.txt (even though you use an older BRCONNECT version), BRCONNECT deletes the table statistics due to the ACTIV=R entries you used, which can lead to critical performance problems.

When importing, note the information in the header of the selected script. Note in particular that, as of December 2009, the script is started using "/ AS SYSDBA" instead of the SAP user and that the name of the SAP user must also be transferred in the command line:
sqlplus /nolog @statistics.txt <sapuser>
To solve individual problems (for example, in the RFC area) you can also execute only those parts of the script that refer to the affected tables.

It is usually sufficient to execute the script once. However, to allow for continuous adjustments to the script, you can import the latest version in regular intervals (for example, once per quarter). It may also be useful to execute the script again after SAP upgrades.

If you experience performance problems due to incorrect CBO decisions after implementing the script, you can proceed as follows:

On Oracle 10g, you can use DBMS_STATS.RESTORE_TABLE_STATS to restore the original statistics (see Note 588668).

On Oracle 9i, you can use DBMS_STATS.EXPORT_TABLE_STATS to export the statistics in accordance with Note 448380 and reimport them using DBMS_STATS.IMPORT_TABLE_STATS (if problems occur). In addition, you must remove the ACTIV=I settings from DBSTATC again.

Also open a customer message, so that SAP can investigate why the system behavior has deteriorated.

If you want to rebuild participating indices after implementing the script, note the following:

Oracle 10g or higher: An index REBUILD has no effect on the locked statistics, so you can perform this task without any problems. Note, however, that the follow-on problems described in Note 1095171 may result from index REBUILDs if you perform other activities such as online reorganization or statistics exports. If in doubt, implement this note again after you have performed a reorganization of the tables involved.

Oracle 9i: You should always perform a REBUILD without COMPUTE STATISTICS, because otherwise the delivered statistics are overwritten.

In certain cases, you have to take special care with tables for which a complete statistic record is delivered; that is, tables that are listed in the section "COMPLETE STATISTIC DELIVERY" of the script. If, after executing the script, you add new customer-specific indexes or columns to one of the affected tables, you must ensure that the table contains Cost-Based Optimizer (CBO) statistics. The same applies if such a table is to be partitioned in a customer-specific way.

There is no ideal solution in this case. Basically, the following three alternatives are available:

Creating new statistics for the affected table:
brconnect -u / -c -f stats -t <table> -f collect,allsel,keep,locked

You must use BRCONNECT 7.00 or higher
After this, you must implement the relevant script of this SAP Note again.

Not using the delivered CBO statistics and, instead, using the normal statistics - which may be locked

Manually setting the missing statistics in a useful way (Note 724545)

Header Data


Released On	26.11.2013 15:42:41
Release Status	Released for Customer
Component	BC-DB-ORA Oracle
Priority	Recommendations / Additional Info
Category	Performance

180605 - Oracle database parameter settings for BW

Symptom

You want to set Oracle database parameters for the Business Information Warehouse (BW).
Only when you work with an Oracle Version higher than 8.1.7
For Oracle 8.1.7.x, use BW Parameter Note 632427
For Oracle 9.2.0.x , use BW Parameter Note 632556
For Oracle 10g , use Note 830576
This note is relevant only if you work with an Oracle version higher than Oracle 8.1.7

The performance of the BW system is inadequate.
ORA-600 errors occur.
You require more information about Oracle standard parameter settings for BW.

To ensure error-free database operation and good system performance, you must set the database parameters optimally.

This note does not apply to R/3 systems. You can find the R/3 parameter settings in Note 124361.

As part of the DB system check (transaction DB16 or DB17), SAPDBA also checks Oracle database parameters. Adjust the test values in accordance with this note (use transaction DB17) to avoid error messages in DB16. However, you must make the actual parameter changes in init<SID>.ora.

Other Terms

Performance, BW, ORA-600, ORA 600 12700, parallel query, EarlyWatch, GoingLive, init.ora, profile parameter, sapdba, DB16 -check, DBCHECKORA, DB17, ORA-03113, ORA, 3113

Reason and Prerequisites

The recommendations in this note are based on discussions with all the relevant groups in SAP that deal with questions regarding Oracle performance and high availability as well as analysis and tuning of numerous SAP/Oracle customer systems.

Due to Oracle software errors, it is always possible that you may have to set parameters differently to the values listed here to avoid the relevant error. In this case, follow the instructions from Oracle support at SAP. However, you should return to the previous settings once you have corrected the error. Only if you have precise knowledge of the effect should you deviate from the standard recommendations listed below.

Note that a hardware sizing of your system is necessary to determine the values of the parameters db_block_buffers and shared_pool_size before production startup. All other parameters should be set as specified for the production startup.

Solution

This note is updated regularly. Therefore read this note regularly.
Latest changes:
MAY/08/2001 - Inserting 8.1.7 parameters
MAY/31/2001 - STAR_TRANSFORMATION_ENABLED and Note 380523
JUN/26/2001 - Upgrade to 8.1.7.1 or higher recommended as soon as available
DEC/06/2001 - Do not set ALWAYS_SEMI_JOIN other than to default
SEP/02/2001 - Inserting 9.2.0 parameters

Note: This note is NOT valid for R/3 systems

Note that the Oracle parameter optimizer_index_cost_adj must never be set. Delete this parameter from the (init<SID>.ora) Oracle profile, if it exists there.

If a parameter is marked with the recommendation '<delete from profile>', this parameter should be deleted from the Oracle profile (init<SID>.ora) so that the default value can be taken from Oracle.

I Oracle 8.0.4
Parameter Name                      Recommended Value
---------------------------         ------------------------------
always_anti_join                    hash
bitmap_merge_area_size              33554432
b_tree_bitmap_plans                  < delete from profile>
compatible                          8.0.4.x.y (the exact release)
control_file_record_keep_time       >= 30 (as of BRBACKUP Release 4.5A)
cursor_space_for_time              <delete from profile>
create_bitmap_area_size             33554432
db_block_buffers                    should never be smaller than
                                    9000 (approximately 70 MB)
db_block_checkpoint_batch           >= 8
db_block_lru_latches                # CPUs * 2
db_block_size                      8192
db_file_multiblock_read_count       32
distributed_transactions            0 (only if you do not use any
                                      distributed data retention or database
                                      links)
event:
    10181                          Do NOT set!
    10183                          Do NOT set!
    10191                              Refer to Note 128221
hash_area_size                      sort_area_size * 2
hash_join_enabled                  true
hash_multiblock_io_count            32
log_archive_start                  true
log_buffer                          1048576
log_checkpoint_interval             3000000000
log_checkpoint_timeout              0
log_checkpoints_to_alert            true
log_simultaneous_copies             # CPUs * 2
max_dump_file_size                  20000 (If larger trace files are
                                    required, the value can be adjusted
                                    using an alter system
                                    command)
open_cursors                        800
optimizer_features_enable           <delete from profile>
optimizer_mode                      choose
optimizer_percent_parallel          100
parallel_broadcast_enabled          true
parallel_execution_message_size     16384
parallel_max_servers                # CPUs * 2
processes                          > (2 * #BW work processes + 20)
replication_dependency_tracking     false (ONLY if you do not use
                                          replication)
replication) row_cache_cursors                  300
shared_pool_size                    should never be smaller than
                                    50000000 (approximately 50 MB)
shared_pool_reserved_size           10% of shared_pool_size
sessions                            >= processes
sort_area_retained_size             0
sort_area_size                      >= 10485760 (corresponds to 10 MB;
                              depending on the main memory)
                              Refer to Note 164925 and especially
                              for NT, Note 335230.
sort_direct_writes                  true
sort_write_buffers                  4
sort_write_buffer_size              262144
star_transformation_enabled         true (<Z1>absolutely</> do see
Note 380523) timed_statistics                    true
transaction_auditing                false

II Oracle 8.0.5
Install at least patch 8.0.5.2. Note 127395 contains information about available patch sets for Oracle.
Parameter Name                      Recommended Value
---------------------------         ------------------------------
always_anti_join                    hash
bitmap_merge_area_size              33554432
b_tree_bitmap_plans                  < delete from profile>
compatible                          8.0.5.x.y (the exact release)
control_file_record_keep_time       >= 30 (at least BRBACKUP Release 4.5A)
cursor_space_for_time              <delete from profile>
create_bitmap_area_size             33554432
db_block_buffers                    should never be smaller than
                                    9000 (approximately 70 MB)
db_block_checkpoint_batch           >= 8
db_block_lru_latches                number of CPUs * 2
db_block_size                      8192
db_file_multiblock_read_count       32
distributed_transactions            0 (only if you do not use any
                                      distributed data retention or database
                                      links)
events:
    10181                          Do NOT set!
    10183                          Do NOT set!
hash_area_size                      sort_area_size * 2
hash_join_enabled                  true
hash_multiblock_io_count            32
log_archive_start                  true
log_buffer                          1048576
log_checkpoint_interval             3000000000
log_checkpoint_timeout              0´
log_checkpoints_to_alert            true
log_simultaneous_copies             # CPUs * 2
max_dump_file_size                  20000
open_cursors                        800
optimizer_index_cost_adj            < delete from profile >
optimizer_features_enable           <delete from profile>
optimizer_mode                      choose
optimizer_percent_parallel          100
parallel_broadcast_enabled          true
parallel_execution_message_size     16384
parallel_max_servers                # CPUs * 2
processes                          > (# BW work processes + 20)
replication_dependency_tracking     false (ONLY if you do not use
                                          replication)
replication) row_cache_cursors                  300
shared_pool_size                    should never be smaller than
                                    50000000 (approximately 50 MB)
shared_pool_reserved_size           10% of shared_pool_size
sessions                            >= processes
sort_area_retained_size             0
sort_area_size                      >= 10485760 (corresponds to 10 MB;
                              depending on the main memory)
                              Refer to Note 164925 and especially
                              for NT, Note 335230.
sort_direct_writes                  true
sort_write_buffers                  4
sort_write_buffer_size              262144
star_transformation_enabled         true (<Z1>absolutely</> do see
Note 380523) timed_statistics                    true
transaction_auditing                false

III Oracle 8.0.6
Use the same parameter settings as for 8.0.5.
Exception:
Parameter Name                      Recommended Value
---------------------------         ------------------------------
compatible                          8.0.6.x.y (the exact release)

IV Oracle 8.1.5
For the changes of the parameter settings for 8.0.5, refer to the following table. Set all other parameters as for 8.0.5.

Parameter Name                      Recommended Value
---------------------------         ------------------------------
b_tree_bitmap_plans                obsolete - remove from profile
compatible                          8.1.5.x.y (the exact release)
complex_view_merge                  obsolete - remove from profile
db_block_checkpoint_batch           obsolete - remove from profile
db_file_multiblock_read_count       32
hash_multiblock_io_count            should have the same value as
                                    db_file_multiblock_read_count

log_checkpoint_interval             3000000
log_simultaneous_copies            obsolete - delete from profile
log_small_entry_max_size           obsolete - delete from profile
optimizer_index_caching             < delete from profile >
optimizer_index_cost_adj            < delete from profile >
optimizer_features_enable           <delete from profile>
                                       (you MUST read Note 217478)

parallel_automatic_tuning           < delete from profile >
parallel_max_servers                # CPUs * 8
parallel_threads_per_cpu            1
processes                          > (4 * #BW work processes + 20)
query_rewrite_enabled              <delete from profile>
                                    See Note 375130.
row_cache_cursors                  obsolete - remove from profile
sort_direct_writes                  obsolete - delete from profile
sort_multiblock_read_count          should have the same value as
                                    db_file_multiblock_read_count

sort_read_fac                      obsolete - delete from profile
sort_write_buffers                  obsolete - delete from profile
sort_write_buffer_size             obsolete - delete from profile
star_transformation_enabled         true (you MUST
                                          read Note 217478)
temporary_table_locks              obsolete - delete from profile
timed_statistics                    false        (due to Oracle bug)

V Oracle 8.1.6
For differences to the parameter settings for 8.1.5, refer to the following table. Set all other parameters as for 8.1.5.

Parameter Name                      Recommended Value
---------------------------         ------------------------------
compatible                          8.1.6.x.y (the exact release)
optimizer_max_permutations          79999 (read Note 336402)
star_transformation_enabled         true (you MUST
                                          read Note 380523)
timed_statistics                    true (you MUST
                                          read Note 332105)

For 8.1.7.x and higher ORACLE Releases
separate notes are maintained for each release.
Refer to the note relevant for your release.

VI Oracle 8.1.7
Note: SAP recommends that you import patch Set 8.1.7.1 or higher as soon as it is available for the corresponding platform (see Note 362060).

Refer to Note 632427.

See Note 387056 for information about the necessary bug fixes.

VI Oracle 9.2.0

Refer to Note 632556.

If the following ORA-600 errors occur, consult the changed parameter settings in accordance with the attached notes:
ORA-00600 [2865]                    Note 332696
Important note for Release 8.1 (64-bit releases)

If performance problems occur due to long parsing times after the upgrade, refer to Note 376905.
Parameters no longer supported as of Oracle Release 8.1:

For an overview of all parameters that are no longer valid as of Release 8.1, see Note 320540

1923050 - DBACockpit: New calculation of buffer quality

Symptom

This SAP Note is valid for the DBA Cockpit on Oracle.

This SAP Note corrects the calculation of the data buffer quality. The calculation is based on the Oracle performance view V$SYSSTAT. The value for "physical reads direct (lob)" is contained in the value for "physical reads direct". The following formula is therefore implemented:

Buffer quality = 100 * (1 -
("physical reads" - "physical reads direct") /
("session logical reads" - "physical reads direct"))

This SAP Note adjust the following two monitors accordingly:

Performance Overview

Performance Database

Other Terms

CL_ORA_ACTION_MM2 CL_ORA_ACTION_PDB_2

Reason and Prerequisites

Change in the calculation for buffer quality

Solution

Implement the corrections.

192658 - Setting parameters for BW systems

Symptom

You want to set the Business Warehouse System (BW) basis parameters. The BW basis parameters must be set optimally for the BW system to work without errors and the system to perform efficiently. The recommendations for BW systems are not always the same as those for R/3 systems.

Other Terms

Memory Management, EarlyWatch, GoingLive, Release 1.2B, Release 2.0A,
Release 2.0B, Release 2.1C, Release 3.0A, Release 3.0B, Release 3.1
Release 3.5, Releases 7.x

Reason and Prerequisites

The recommendations for BW systems are not always the same as those for R/3 systems. This note describes the differences between the parameter settings of a BW system and an R/3 system. Therefore, this note supplements Note 103747 for BW systems (Performance 4.0/4.5/4.6: Parameter recommendations). As with the recommendations in Note 103747, the recommendations here are intended as initial settings before you start using the system productively. After going live, you must adjust the individual parameters as required and adapt them to the actual requirements.

Number and type of work processes
This note does not contain any recommendations about the number and type of work processes. You can receive work processes tailored for your system from the recommended remote services. However, if you have not received any recommendations from the GoingLive services, you have to set up a minimum of six dialog work processes and three batch work processes.

This note is valid only for BW 1.2B, BW 2.0A, BW 2.0B,
BW 2.1C, BW 3.0A, BW 3.0B, BW 3.1, BW 3.5 and BI 7.x

For NetWeaver 2004s or BI 7.0, see also Note 1044441.

Solution

**********************************************************************
*  Support by Support Services                                          *
**********************************************************************
To ensure that the system performs efficiently after you start using it productively, we recommend you use the support services, for example, the GoingLive check for BW systems or an EarlyWatch contract for BW systems. With a GoingLive check for BW systems, you should schedule two sessions for 4-6 weeks before you start using the system productively and one session 4-6 weeks after you have started using the system productively.

TO AVOID LONG WAITING TIMES FOR A SERVICE SESSION APPOINTMENT, BOOK IN SUFFICIENT TIME BEFORE YOU START USING THE SYSTEM PRODUCTIVELY. TO DO THIS, CONTACT YOUR LOCAL SUPPORT.

This note supplements Note 103747  (Performance 4.0/4.5/4.6: Parameter recommendations) for BW systems. It describes the different parameter settings of a BW system and an R/3 system.

Recommendations given in Note 103747 for R/3 systems that are different for BW systems:

1a. Export/import buffer
BW 1.2B:
This buffer is frequently used in BW systems so the export/import buffer must be set to at least 10 MB. If you work with large hierarchies, you have to increase the size of this buffer considerably. You should be able to store at least 5,000 objects in the buffer.
rsdb/obj/buffersize                          10000 or higher (10 MB)
rsdb/obj/max_objects                          5000 or higher (entries)
rsdb/obj/large_object_size                10000 (10 MB)

BW 2.X: The buffer should be set to the size recommended for R/3 4.6 systems (see Note 103747).

BW 3.X: The buffer should be set to a larger size than the buffer for BW 2.X.

rsdb/obj/buffersize                          40000 or higher (40 MB)

1b. Export/import buffer SHM
BW 3.X ONLY (OLAP cache):
Parameter name             Value     Description
rsdb/esm/buffersize_kb     40000 or higher   Size of exp/imp SHM buffer (kB)
rsdb/esm/max_objects       10000     Maximum number of objects in buffer

2. Program buffer (abap/buffersize)
Depending on the size of the main memory, the program buffer should be between 200 and 400 MB. Unlike in R/3 Systems, a higher number of program buffer swaps is less important in BW Systems and is often unavoidable since the information stored in the program buffer is significantly less likely to be reused.  While the response times of R/3 transactions is only around several hundred milliseconds, the response times of BW queries takes seconds. However, by tuning the program buffer, you can only improve the performance by milliseconds.
Therefore, if the available main memory is limited, you should increase the size of the extended memory. However, the program buffer should not be set lower than 200 MB. If the available main memory is sufficient, the program buffer in BW 2.X/3.X systems should be set to at least 300 MB.

3. Extended Memory
BW users require significantly more extended memory than R/3 users. The size of the extended memory is related to the available main memory but should not be lower than 512 MB.

4. Single Record buffer
BW 2.X & 3.X: The buffer should be set to the size recommended for R/3 4.6 systems (see Note 103747).
Limits for BW 2.X:
You must also see Note 414220 with regard to the setting of the
parameters rtbb/max_tables, rtbb/buffer_length.
You must not exceed certain limits.
Limits for BW 3.X:
See Note 480710.

5. Recommendations for the 64-bit R/3 kernel
In addition to the recommendations from Note 146289, set the following parameter specifically for BW:
            em/blocksize_KB      4096

6. Maximum runtime for a work process
Set a higher runtime than is usual for R/3 systems:
rdisp/max_wprun_time                          3600

7. Connection to the front end
To avoid connection terminations, set the gw/cpic_timeout parameter to a sufficiently large value:
gw/cpic_timeout                                60

8. Further CPIC/RFC settings
   gw/max_conn                    2000
   gw/max_overflow_size       25000000
   rdisp/max_comm_entries         2000
   Because of the high RFC load, see the
   recommendataions from Note 74141.

9. Oracle Database Interface
Set the parameter dbs/ora/array_buf_size to a sufficiently large size to keep the number of array inserts, for example, when you upload data or during the rollup, as low as possible. This improves the performance during insert operations. Depending on the size of the available main memory, set the parameter dbs/ora/array_buf_size to a value of 1000000 (1MB).
dbs/ora/array_buf_size             1000000

10. Internet Communication Manager (ICM) Parameter
icm/host_name_full                <fully qualified name>
                                  You use this parameter to specify
                                  the fully qualified host name of the
                                  of the host on which the ICM is
                                  running. If this parameter is
                                  not set, the system queries the
                                  system for the host name.
                                  With several host names, this
                                  parameter can override this value.
SAPLOCALHOSTFULL                  <fully qualified name>
General timeouts:
icm/keep_alive_timeout             3600
                                  This parameter refers to the
                                  "keep-alive-feature" of HTTP1.1
rdisp/plugin_auto_logout           3600
                                  The user context is closed only after
                                  this time interval
Kernel Release 6.20
icm/server_port_: PROT=<xxxx>,PORT=<xxxx>,TIMEOUT=3600
                                  Defines keep_alive_timeout per port
                                  binding
Kernel Release 6.40ff
icm/server_port_: PROT=<xxxx>,PORT=<xxxx>,TIMEOUT=60, PROCTIMEOUT=3600
icm/keep_alive_timeout = 60

Further information:
Note 622629: Procedure when timeouts occur in BW Web Reporting
Note 622130: Timeout problems in BW Web Applications
Note 538405: Composite SAP Note on the SAP Web Dispatcher
11. Notes for configuring the databases
BW and R/3 Systems can differ significantly in terms of the configuration of the database.
The following notes contain recommendations for setting the database in a BW system:
                        0632556 Oracle 9.2.0.* database parameterization for BW
                    0632427 Oracle 8.1.7* database parameterization for BW
                    0180605  Oracle database parameter settings for BW
Informix            0114884 Performance Guide:  BW 1.2B on Informix
                    0181945 Performance guide: BW 2.0X & BW 3.0X
MS SQL Server        0142334 Installation BW 1.2B on MS SQL server
                    0209596 Setting up Microsoft SQL Server 2000
DB2 UDB Unix/Windows 0374502 DB6: BW performance: Note overview
                    0546262 DB6: Performance on SAP BW 3.0B
DB2 UDB OS/390       0390016 DB2/390: BW: Database settings and
                            performance
DB2 UDB AS/400       541508  iSeries: Checking the system parameters for BW

12. Essentials for parameter settings
- Save your old profile before you make any changes. If the instance does not start with the new parameters, you can use the old profile.
- Check all parameter settings before you use them (program sappfpar, in the test system, and so on).
- Take into account limitations due to the architecture of the operating system (for example, shared memory limitations). Errors and terminations may occur if you do not take these limitations into account.
  Bear in mind that concrete recommendations for parameter settings using TTC can only be given during a session service. ***********************************************************************
IMPORTANT:
  If the settings in the R/3 memory areas (R/3 buffer, R/3 extended memory and so on)
  are too small, unnecessary performance problems can occur.
    If the settings are too large, errors may occur when you start the
  R/3 instance, or runtime errors may occur.
  The optimal settings depend on the hardware (CPU and RAM), the R/3 release
  and the operating system release.
  The TCC services GoingLive and EarlyWatch provide recommendations tailored to your system.

354080 - Note collection for Oracle performance problems

Symptom

The Oracle database performance is poor.

Other Terms

Performance, runtime, TIME_OUT, timeout

Reason and Prerequisites

There are several possible causes of poor database performance. Numerous notes are available for troubleshooting Oracle performance and they are described below:

This note

This note lists performance problems that you can solve by changing the database configuration or by importing a patch.

Note 618868: FAQ Oracle Performance

This note describes general checks that you can perform in the event of poor performance and refers to other key notes.

Note 521264: Hang situations

This note deals with situations in which the database hangs and with performance problems that occur during the connect and when you start and stop Oracle.

Note 184905: Collective note Performance BW 2.0
Note 567745: Composite note BW 3.x performance: DB-specific settings

These notes deal with BW-specific problems with the database.

Below you will find an overview of performance problems that you can solve by changing the Oracle configuration or by importing a patch.

Solution

1. Release-independent performance problems:

OLTP: Note 164925: Storage parameter of tablespace PSAPTEMP

BW: Note 359835: Design of the temporary tablespace in the BW System

Symptoms: Long runtime during index creation, many space transaction enqueues, ORA-01575
Explanation: When you use a dictionary-administered temporary tablespace with extent sizes that are too low, or with a TEMPORARY type, the SMON process cannot clean the extents quickly enough. You must therefore define the extents with a sufficient size, and set the type to PERMANENT. Alternatively, use a locally administered temporary tablespace.

Note 128221: Increased memory consumption with Oracle >8.0.X

Symptoms: Oracle has high memory consumption, increased swapping, poor database performance, ORA-04030
Explanation: When using the CBO, the shadow processes sometimes need a lot of local memory that is no longer released. To avoid this, set Event 10191.

Note 198752: TCP delay problem under Oracle 8.1.x

Symptoms: TCP-based database accesses take unnecessarily long.
Explanation: By default, data packets are only sent by TCP once they have reached a certain size, or after a certain period of time has passed. To ensure immediate transfer, you must set the parameter TCP_NODELAY appropriately.

Oracle >= 8.1: Note 871455: DBA_SEGMENTS performance

BW, Note 519448: Accessing USER_INDEXES, USER_IND_COLUMNS

BW, Note 519407: Accessing USER_IND_PARTITIONS

Note 558197: Accessing USER_IND_COLUMNS during upgrade

Symptoms: Long-running accesses to Oracle DDIC objects and external tools like SAPDBA or BR*Tools hang for a long time at certain points, and DB02 refreshes take unusually long to complete. Deactivating BW aggregates takes too long.
Explanation: These problems are caused by Oracle bugs or inefficient accesses to Oracle DDIC objects. Apply the relevant bug fixes and scripts from the notes to ensure better access.

Note 563359: Poor performance on LOBs

Symptoms: Access to LOB information takes longer than access to comparable RAW information.
Explanation: This poor performance is caused by the fact that LOB data is not cached by default. The script from the note also activates caching for LOB data.

Note 505246: ORA-04031 and ORA-00600 [12333]

Note 556764: Upgrade hangs in phase ACT_*

Symptoms: Database error, poor performance or database hangs because of RBO accesses.
Explanation: Set the Oracle parameter shared_pool_size to at least 400MB. If you do not, fragmentation effects may cause the problems described above.

BW: Note 558746: Better Oracle Data Dictionary BW Performance

Symptoms: If there are no statistics: Poor BW performance; if statistics were created: Poor performance is possible during upgrades or monitoring statements.
Explanation: In the BW environment, statistics should be created in the Oracle DDIC to improve BW performance. If performance is poor when accessing the Oracle DDIC in another area with these statistics present, the DDIC statistics can be deleted as a test.

Note 596420: System standstill during deadlock (ORA-60)

Symptoms: Database hangs during a deadlock, "latch free waits" on "enqueue hash chains".
Explanation: Oracle locks central database resources when the deadlock trace file is written. This causes a serialization of all other processes using those resources. You can solve the problem by using a patch, an event, or by reducing the trace file size.

2. Release-dependent performance problems:

Oracle 8.1.7 (< patch set 3): Note 449136: Cache buffer chains latches

Oracle 8.1.7 (< patch set 4): Note 488583: Cache buffer chains latches

Symptoms: The system sometimes performs poorly and hangs, large-scale occurrence of the "latch free" wait event on the "cache buffer chains" latch.
Explanation: These are Oracle bugs that you can fix by implementing patches.

BW, Oracle 8.1.7.4: Note 598552: BW Queries

Symptoms: Execution plan shows sort-merge-joins instead of hash-joins.
Explanation: A patch with errors was delivered. To fix the problem, implement the corrected version of the patch.

Oracle 8.1.7, 9.2.0 (< patch set 2): Note 520568: CBO: Long runtimes for large IN lists

Symptoms: SQL statements with large IN lists perform poorly.
Explanation: This is an Oracle bug that you can fix by implementing a patch.

Oracle 9i: Note 626615: Error because of _B_TREE_BITMAP_PLANS

Symptoms: Poor performance of certain SQL statements, unusual execution plans, ORA-00600 [20022]
Explanation: Set the underscore parameter _B_TREE_BITMAP_PLANS to FALSE to avoid these problems

Oracle 9.2.0.2/9.2.0.3: Note 649876: Library Cache Pins

Symptoms: Wait event "library cache pin" causes wait situations
Explanation: This is an Oracle bug that you can remove by implementing the fix from Note 649876.

Oracle 9.2: Note 626172: Performance problems with outer joins

Symptoms: Performance problems with SQL statements containing outer joins (for example, "(+)" in the SQL text), "null event" as accompanying wait event, high CPU usage by the session
Explanation: Set the underscore parameter _PUSH_JOIN_PREDICATE to FALSE to avoid this problem.

Oracle 9.2.0: Notes 632336, 684545: DBMS_STATS

Symptoms: Statistic creation performs poorly with DBMS_STATS
Explanation: These are Oracle bugs that you can fix by implementing patches or bug fixes.

Oracle 8.1.7: Note 601668: Merge fix for Oracle 8.1.7.4

Oracle <= 9.2.0.3: Note 610445: Merge fix for 9.2.0.3

Oracle 9.2.0.4: Note 695080: Merge fix for 9.2.0.4

Oracle 9.2.0.5: Note 755629: Merge fix for 9.2.0.5

Oracle 9.2.0.6: Note 834100: Merge fix for 9.2.0.6

Oracle 9.2.0.7: Note 896903: Merge fix for 9.2.0.7

Oracle 9.2.0.8: Note 992261: Merge fix for 9.2.0.8

Oracle 10.2.0.2: Note 981875: Merge fix for 10.2.0.2

Note 1002062: Additional merge fix for 10.2.0.2

Symptoms: Wrong resulting quantities, Oracle error messages, poor performance
Explanation: The merge fixes correct a large number of Oracle bugs, which sometimes also manifest themselves as performance problems.

Oracle 9i: Note 875477: BEGIN BACKUP runtimes

Symptoms: Poor performance during TRUNCATE TABLE with large buffer pool

Oracle 9i: Note 758989: Poor performance with TRUNCATEs

Symptoms: Poor performance of TRUNCATE operations

Oracle >= 9i: Note 755342: Time-consuming accesses with Bind Value Peeking

Symptoms: Time-consuming SQL statements and incomprehensible CBO decisions

Oracle 10g: Time-consuming DDIC and X$ accesses

Symptoms: Monitoring transactions (such as access to Shared Cursor Cache) take a long time, INSERT operations perform poorly
Explanation: To ensure optimum access to the Oracle DDIC, you must implement the DDIC and fixed object statistics from Note 838725.

3. Performance problems in Oracle releases that are no longer supported:

Oracle 8.0: Note 170989: Poor view performance

Symptoms: Long runtimes when accessing Views, ORA-01652, Full Table Scans.
Explanation: The CBO makes incorrect decisions because of Oracle bugs or invalid parameterization. Set the parameters correctly and implement the relevant patches to correct the problem.

Oracle 8.1.6, BW: Note 336402: Long parsing times with 8.1.6

Symptoms: Performance problems
Explanation: This is an Oracle bug. You can avoid it by setting optimizer_max_permutations to a value less than 80000.

659946 - FAQ: Temporary tablespaces

Symptom

1. Why is a temporary tablespace required?

2. What is the default name of the temporary tablespace in the SAP environment?

3. Which types of temporary tablespaces are available?

4. How can I find out how the temporary tablespace was created?

5. How is the temporary tablespace assigned to a database user?

6. How I can find out which temporary tablespace is assigned to a database user?

7. Which general problems may occur in connection with temporary tablespaces?

8. What are the restrictions and problems associated with the individual types of temporary tablespaces?

9. Which type of temporary tablespace does SAP recommend?

10. How I can find out how the temporary tablespace is currently filled?

11. If problems occur, can I recreate the temporary tablespace without a restore?

12. What is a default temporary tablespace?

13. How can I change over from DMTS/P to LMTS/T?

14. Where can I find more information about temporary tablespaces?

Other Terms

Frequently asked questions

Reason and Prerequisites

Solution

1. Why is a temporary tablespace required? A temporary tablespace is mainly required for the intermediate storage of temporary data during certain database actions, for example:

Sorting/aggregation with CREATE INDEX, SELECT ... DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS (when the memory area defined using SORT_AREA_SIZE is insufficient)

Creating and accessing bitmap indexes (if the memory areas defined using BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE are insufficient)

Executing hash joins (if the memory area defined using HASH_AREA_SIZE is insufficient)

Explicit or implicit use of temporary tables (as part of the STAR transformation, for example)

Temporary LOBs (for example in J2EE environments)

With BW 2. x, permanent objects are also created in the temporary tablespace in some situations (see Note 216440).

2. What is the default name of the temporary tablespace in the SAP environment? The default name of the temporary tablespace in the SAP environment is PSAPTEMP.

3. Which types of temporary tablespaces are available?

Dictionary-managed tablespace with PERMANENT contents (DMTS/P):

CREATE TABLESPACE <temp_tsp> DATAFILE ... EXTENT MANAGEMENT
DICTIONARY [PERMANENT];

Dictionary-managed tablespace with TEMPORARY contents (DMTS/T):

CREATE TABLESPACE <temp_tsp> DATAFILE ... EXTENT MANAGEMENT
DICTIONARY TEMPORARY;

Locally-managed tablespace with PERMANENT contents (LMTS/P):

UNIFORM:

CREATE TABLESPACE <temp_tsp> DATAFILE ... EXTENT MANAGEMENT
LOCAL UNIFORM SIZE <size> PERMANENT;

AUTOALLOCATE:

CREATE TABLESPACE <temp_tsp> DATAFILE ... EXTENT MANAGEMENT
LOCAL AUTOALLOCATE PERMANENT;

Locally-managed tablespace with TEMPORARY contents (LMTS/T):

CREATE TEMPORARY TABLESPACE <temp_tsp> TEMPFILE ...
EXTENT MANAGEMENT LOCAL UNIFORM SIZE <size>;

As described further below, only DMTS/P and LMTS/T may be considered as temporary tablespaces in the SAP environment. Never use LMTS/P as a temporary tablespace. For detailed information about LMTS, see Note 214995. Only temporary objects can be created in TEMPORARY tablespaces. You can create both temporary and permanent objects in PERMANENT tablespaces.

4. How can I find out how the temporary tablespace was created? You can find the relevant information using the following query on DBA_TABLESPACES:

SELECT CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES WHERE
TABLESPACE_NAME = '<temp_tsp>';
In this case, CONTENTS contains either PERMANENT or TEMPORARY tablespaces. EXTENT_MANAGEMENT is either DICTIONARY or LOCAL. ALLOCATION_TYPE is USER (for DMTS), UNIFORM (for UNIFORM-LMTS) or SYSTEM (for AUTOALLOCATE-LMTS).

5. How is the temporary tablespace assigned to a database user? To assign a temporary tablespace to a new user that you want to create, proceed as follows:

CREATE USER <user> TEMPORARY TABLESPACE <temp_tsp> ...;
To assign another temporary tablespace to an existing user, proceed as follows:

ALTER USER <user> TEMPORARY TABLESPACE <temp_tsp>;

6. How I can find out which temporary tablespace is assigned to a database user? The following SQL statement gives you a list of all database users and their temporary tablespaces:

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

7. Which general problems may occur in connection with temporary tablespaces?

ORA-00959: If a temporary tablespace is assigned to a user that does not exist, transactions that have to carry out an operation in the temporary tablespace terminate with ORA-00959.

ORA-01220: This error occurs if, in the case of a closed database, the access to a V$ view or a RMAN activity requires a sorting that cannot be fully carried out in the memory (because of the PGA parameter settings). In this case, increase the size of the PGA parameters such as SORT_AREA_SIZE or PGA_AGGREGATE_TARGET, or avoid extensive sorting. See also Note 919588.

ORA-01652: This error is caused by a tablespace overflow. As a rule of thumb, PSAPTEMP should be at least twice as large as the largest index. If an overflow occurs despite this size, this is frequently due to external reasons such as incorrect Oracle parameter settings, large-scale parallel processing or unfavorable access paths. See also Note 3155.

ORA-01658: If this error occurs in BW, use SAP_DROP_TMPTABLES to delete any temporary tables that exist (Note 379499).

ORA-03232: If this error occurs in connection with the temporary tablespace, check the configuration of the temporary tablespace as described in Note 485755.

8. What are the restrictions and problems associated with the individual types of temporary tablespaces?

DMTS/P

If the selected default extent sizes (INITIAL, NEXT) are too small, performance problems may occur due to space transaction enqueues. Errors such as ORA-01575 (see Note 78595) may also occur. Notes 164925 (R/3) and 359835 (BW) describe the optimal setup.

Even if the setup is correct, this tablespace type may perform poorly in comparison with LMTS.

If you select a MAXEXTENTS value that is too low, error ORA-01630 may occur on the temporary tablespace. Therefore, you should generally set the MAXEXTENTS default for PSAPTEMP to UNLIMITED.

If SYSTEM is a LMTS (Note 706625) or if a default temporary tablespace is used (Note 683075), you cannot create a temporary tablespace of the type DMTS/P.

If you try to use a DMTS/P tablespace as a default temporary tablespace, error ORA-12902 occurs.

If you open a database in read-only mode (for example, a standby database within structure validation using VALIDATE STRUCTURE or an export - see Note 817253), error ORA-01682 ("read-only DB cannot allocate temporary space in tablespace") may occur when you access a DMTS/P-PSAPTEMP (for example, within sortings). To avoid the problem, change to LMTS/T.

DMTS/T

This tablespace type may result in very long runtimes when you shut down a database (Note 183842) as well as other performance problems (Notes 183791 and 216881). Therefore, we do not recommend that you use this tablespace type.

Temporary segments are only released again when you shut down the database. For this reason, the tablespace always appears to be full (see Note 107257).

If SYSTEM is a DMTS or if a default temporary tablespace is used (Note 683075), you cannot create a temporary tablespace of the type DMTS/T.

LMTS/P

Generally, tablespaces of this type cannot contain any temporary segments and they return error ORA-3212 (Note 609532) when you try to create a temporary segment. This tablespace type is therefore unsuitable for use as a temporary tablespace. The special BW 2.x configuration described in Note 387946 is the only exception here.

As of Oracle 9i, you can no longer assign an LMTS/P tablespace to a database user as a temporary tablespace. An assignment fails with error ORA-12911.

LMTS/T

You can only use this tablespace type as of Oracle 8.1.x.

With BW 2.x, partially permanent objects are stored in PSAPTEMP (Note 216440). If PSAPTEMP is of the type LMTS/T, this would fail with error ORA-02195. To avoid the problem, PSAPTEMP must remain PERMANENT, but you can create another (TEMPORARY) tablespace. Note 387946 describes this procedure.

The data files from LMTS/T tablespaces ("TEMPFILES") are created as sparse files on certain operating system platforms, and therefore initially only a fraction of the file size is physically allocated. This may cause the information of df, du, ls and similar OS commands to appear to be inconsistent as well as cause inadvertent multiple allocation of memory space.

If error ORA-01114 occurs in R/3 with SQL statements that use the temporary tablespace (for example, because of an ORDER BY statement or a sort merge join), and if the placeholders %s are not replaced correctly in the error message, or if a file is referenced with a number > DB_FILES, a TEMPFILE created as a sparse file can no longer be extended because no more space is available in the file system.
For more information, see Note 548221 and make sure that the file system has sufficient space to extend the TEMPFILES.

If ORA-01257 occurs when you create TEMPFILES in Oracle 9.2 or lower, refer to Note 487318 and enter an explicit SIZE specification.

Depending on the release, TEMPFILES are not saved by BRBACKUP as part of the backup (see Notes 416782 and 635552). This may result in ORA-25153 during the restore/recovery. Note 600513 describes some possible solutions.

TEMPFILES are not found in the DBA_DATA_FILES view. Instead, you must execute a SELECT on DBA_TEMP_FILES to obtain information about these files.

9. Which type of temporary tablespace does SAP recommend?               As of Oracle 8I, SAP recommends that you use LMTS/T. However, when you use R/3 Releases 4.5B or lower, you must refer to Note  662900 when you activate LMTS/T. Important advantages of this PSAPTEMP type are: you can avoid ABAP access in Space transactions; you can avoid redo information, since LMTS/T tablespace is automatically created with NOLOGGING.
              In theory, you can also use DMTS/P, as long as the SYSTEM tablespace is a DMTS and you do not use a default temporary tablespace (Note 683075).
              However, due to some drawbacks such as the greater effort involved for Space Management, LMTS/T is the preferred option.

10. How I can find out how the temporary tablespace is currently filled?               You can use the script SQL_Workareas. txt from Note 1438410 to determine the current or historical work areas that use space in the temporary tablespace.
              You can use the script Space_Tablespaces. txt from Note 1438410 to determine the current size and current fill level of the temporary tablespace.
              In the V$SORT_SEGMENT and V$SORT_USAGE views, you can find information about the sort segments that currently exist and the corresponding users.
              Note that the information from V$SORT_SEGMENT and V$TEMP_SPACE_HEADER often indicates that the temporary tablespace is extremely full. This information is usually incorrect, since many areas can be released and reused if necessary. For more information, see Note 827019.
              You can use V$TEMPORARY_LOBS to determine whether temporary LOBs currently exist. If they exist, you can then determine how many exist. The workaround described in Note 500340, to guarantee atomicity, may be the cause of a larger number of temporary LOBs in the J2EE environment.
              You can use V$TEMPSEG_USAGE to determine the current temporary segments. These are not restricted to the temporary tablespace. Instead, they can also exist in other tablespaces (for example, as part of an index construction).

11. If problems occur, can I recreate the temporary tablespace without a restore?               If no permanent objects existed in the temporary tablespace, you can use drop/create to recover the tablespace without a restore (see also Note 4161).
              In the case of an LMTS/T tablespace, you can also proceed as follows:

ALTER DATABASE TEMPFILE  '<path_and_filename>' DROP;
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '<path_and_filename>'
  SIZE <size>;
              This is particularly necessary if you use a default temporary tablespace because it is not possible to drop the tablespace in this case.

12. What is a default temporary tablespace? If you do not specify a temporary tablespace when you create a user, the SYSTEM tablespace is automatically used as a temporary tablespace. To prevent this, you can define a default temporary tablespace, which is then entered as a temporary tablespace for the user. For more information, see Note 683075.
With newer releases and patches, BRCONNECT checks whether the default temporary tablespace is still set to SYSTEM. If it is, the system issues warning BR0971W. To solve the problem, define PSAPTEMP as the default temporary tablespace as described in Note 683075:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP;

13. How can I change over from DMTS/P to LMTS/T? To change over from DMTS/P to LMTS/T, carry out the following steps:

Make sure that there are no R/3 tables and indexes in PSAPTEMP:

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'PSAPTEMP';

If you want to make the change while the system is running, you can temporarily assign another existing tablespace to the SAP user or you can assign an additional tablespace created especially for this purpose:

ALTER USER <sapuser> TEMPORARY TABLESPACE <temp_tsp>;

Otherwise, there is a risk that transactions may terminate with error ORA-00959. However, it is better to make the change during downtime.

You can now drop the previous temporary tablespace:

DROP TABLESPACE PSAPTEMP INCLUDING CONTENTS;

Note: When you drop a tablespace, current accesses to PSAPTEMP terminate with ORA-00959.

In the next step, you can recreate the LMTS/T-PSAPTEMP as described above. If you want to reuse the previous data file as a temp file, replace "SIZE <size>" in the CREATE command with "REUSE".

If you have converted the temporary tablespace for the SAP user in the meantime, you can now reset it to PSAPTEMP. If you created a help tablespace, you can now drop it again.

14. Where can I find more information about temporary tablespaces?               Note 416772 contains more information about tablespaces in the SAP environment in general and about temporary tablespace in particular.
              The Oracle 9i Online Documentation also contains information about tablespaces in general and temporary tablespaces in particular in the section entitled:

Administrator's Guide
-> 11 Managing Tablespaces

Header Data


Released On	14.03.2012 12:29:27
Release Status	Released for Customer
Component	BC-DB-ORA Oracle
Priority	Recommendations / Additional Info
Category	FAQ