Oracle Tuning

  1. Set CURSOR_SHARING to FORCE
  2. Use ALL_ROWS (default value for OPTIMIZER_MODE
  3. Tune PROCESSES
    1. As a rule of thumb, PROCESSES should be set to the double of the sum of maximum concurrent sessions of all content server instances.
    2. For example, if there are two instances each with concurrent_sessions set to 250, PROCESSES should be set to 2000 (2x2x250).
  4. Keep statistics up-to-date
    1. Oracle statistics are used by the query optimizer to choose the best execution plan for each SQL statement. A worse execution plan can be chosen with out-of-date statistics. To make sure the statistics are up-to-date, the following command can be executed in SQLPLUS as SYSDBA:
    2. exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'<repository>’, options=>’GATHER’, estimate_percent=><percentage, 30 is recommended>, method_opt=>’FOR ALL COLUMNS SIZE AUTO’, degree=><cpu cores>);

Leave a Reply

Your email address will not be published. Required fields are marked *