Skip to end of metadata
Go to start of metadata

Purpose

This section describes key Oracle-specific steps to troubleshoot ME application performance problems.

For ease of use in viewing an SAP Note, right click on the Note link and select Open in new window or Open in new tab.

Overview

Check database statistics configuration

The presence of histograms on an Oracle database can cause extreme performance degradation. Correctly configured statistics prevent the creation of histograms.

  • Check for Histograms
    1. Run SQL*Plus or other query tool and logon to the system account
      For SQL*Plus, type the following at the command line:
      sqlplus system/<password>@<instance>
      Replace <password> with the system account password. Replace <instance> with the name of the database instance.
    2. Run Query to Check for Histograms
      Type the following query at the SQL prompt (or execute within SQL Developer or other query tool):
      select histogram, count (*) from dba_tab_col_statistics where owner='WIP_USER_NAME' group by histogram;
      Replace WIP_USER_NAME with the schema name of the WIP (or ODS) user. If this query returns any values other than NONE, histograms are present on this instance.
  • Verify Statistics Settings
    1. Run SQL*Plus or other query tool and logon to the system account
      For SQL*Plus, type the following at the command line:
      sqlplus system/<password>@<instance>
      Replace <password> with the system account password. Replace <instance> with the name of the database instance.
    2. Check METHOD_OPT value
      Type the following query at the SQL prompt (or execute within SQL Developer or other query tool):
      select dbms_stats.get_param('method_opt') from dual;
      If this query returns any value other than FOR ALL COLUMNS SIZE 1, the value will need to be corrected using the following query:
      exec dbms_stats.set_param('method_opt','FOR ALL COLUMNS SIZE 1');
    3. Check ESTIMATE_PERCENT value
      Type the following query at the SQL prompt (or execute within SQL Developer or other query tool):
      select dbms_stats.get_param('estimate_percent') from dual;
      If this query returns any value other than NULL, the value will need to be corrected using the following query:
      exec dbms_stats.set_param('estimate_percent','NULL');
    4. Check CASCADE value
      Type the following query at the SQL prompt (or execute within SQL Developer or other query tool):
      select dbms_stats.get_param('cascade') from dual;
      If this query returns any value other than TRUE, the value will need to be corrected using the following query:
      exec dbms_stats.set_param('cascade','TRUE');
    5. If any statistics settings are changed, statistics will need to be gathered again
      Work with client DBA to gather statistics once settings are corrected
    6. After statistics are gathered, check for histograms again

Check parameter configuration

The parameters most likely to cause issues are the memory sizing and optimizer-related parameters.

  • Check Optimizer Parameters
    1. Run SQL*Plus or other query tool and logon to the system account
      For SQL*Plus, type the following at the command line:
      sqlplus system/<password>@<instance>
      Replace <password> with the system account password. Replace <instance> with the name of the database instance.
    2. Check Optimizer Parameter Values
      Type the following query at the SQL prompt (or execute within SQL Developer or other query tool)
      show parameter optimizer
      Check the value of optimizer_index_caching. If this value is not 90, it will need to be corrected using the following query:
      alter system set optimizer_index_caching=90 scope=both;
      Check the value of optimizer_index_cost_adj. If this value is not 10, it will need to be corrected using the following query
      alter system set optimizer_index_cost_adj=10 scope=both;
      The change is immediate and does not require the database to be restarted
  • Check Memory Settings
    1. Check physical RAM available on server.
    2. Run SQL*Plus or other query tool and logon to the system account
      For SQL*Plus, type the following at the command line:
      sqlplus system/<password>@<instance>
      Replace <password> with the system account password. Replace <instance> with the name of the database instance.
    3. Check database memory allocation
      Type the following query at the SQL prompt (or execute within SQL Developer or other query tool):
      show parameter target
      Check the value of sga_target and pga_aggregate target. Add these values together to get the database memory allocation.
      In Oracle 11g, there is a memory_target parameter. This value is the total memory allocation if not zero.
      The minimum memory allocation from the _target parameter(s) is 2 Gb for SAP ME WIP and ODS database instances.
    4. Verify system RAM is sufficient
      The total memory allocation for all databases on the server should be less than 80% of the available physical RAM. If the database allocation is higher than 80%, reduce the database allocation or increase system RAM.
  • Verify database settings
    1. Verfiy database settings according to SAP Note 1405260  - 'SAP ME Databases: Oracle Recommendations'

Run diagnostic reports

If the above checks have not addressed the performance issue, the next step is to run the Oracle ADDM, ASH and AWR reports and analyze the results.

Related Notes

SAP Note 1405260  - 'SAP ME Databases: Oracle Recommendations'
SAP Note 1569075  - 'Oracle statistic gathering following an upgrade or data load'
SAP Note 1457893  - 'How to generate Oracle ADDM, ASH and AWR reports'

  • No labels