SAP ADM506 (ora adm 2) notes

Edgar Hiersekom (low level SAP consultant: DB,OS,cluster,...)

physical memory usage split-up:

• 100% mem. if Oracle is sepparated from SAP-CI
• 30% SAP-CI / 70% Oracle
• 40%+ SAP+Appl. / 60%- Oracle

SAP rule of thumb: 80% SGA / 20% PGA SAP rule of thumb for BW: 60% SGA / 40% PGA

see SAPnote: 617416, 124361(ora9, there is alo one for ora10)

• DB buffer cache: (DB_BLOCK_BUFFERS obsoleted in ora10), DB_CACHE_SIZE
• shared pool: SHARED_POOL_SIZE >= 400MiB => 600MiB+
• LOG_BUFFER = 1MiB (no tuning required/possible)

For dynamic SGA tuning SGA_MAX_SIZE needs to be set large enough, this is not common practice for SAP production systems. For tuning of production do it accordingly with EWA reports.

Note 619876 describes Automatic PGA Memory Management. Set WORKAEREA_SIZE_POLICY to AUTO (together with PGA_AGGREGATE_TARGET) when PGA memory is lacking during sorts.

Some Oracle 10 features requires ASSM switched-on. SAP systems installed with Oracle version 8/9/10 use the following features:

  
       DMTS LMTS ASSM
       ==== ==== ====
ORA8   v
ORA9        v    x (ASSM not by default, if you use it ORA9.2.0.7)
ORA10       v    v

Besides st04n the Oracle wait interface will help us to further identify the general problem.

In Oracle you have V$ views and GV$ views, these are Global and only used in Oracle RAC.

The reason for physical database reads can be that the db-cache is too small; it can also be that the cache is [more then] sufficient but there are some expensive statements that do full-table-scans!

If you start analyzing and see that (eg.: in sm66) there are sap reports using sap tables then start searching the sap notes on those tables/reports. If you see Z* reports/tables then start searching for the develloper :P

Oracle 10 has no rule based optimizer any more and the optimizer *always* uses the statistics, therefore it is important that te statistics are always updated. For Oracle 9 you use 6.40 for Oracle 10 use 7.00 no matter what SAP version. For creating ORA10 statistics take a look at note 838725. Also check note 628590 that says to include -f monit if you do brconnect -f stats -t all (schedule this *before* the check).

You could also use db21 to overwrite defaults on statistics.

note 23345 provides parameters for dbconnect to check analyze table cascade index.

defragment indexes: rebuild for unbalanced / fragmented indexes, drop+create for indexes with defect blocks.

Support for Oracle functions in the SAP environment note 105047 (not allowed = dont do it; not supported = you can do it but dont ask for help in OSS messages).

In Oracle 10 it is now also possible to do online tablespace shrinks.

Terms:

  
dml (Data Manipulation Language) 
cbo (Cost Based Optimizer)