Tune SQL Execution Plans

Use Oracle’s SQL profiling capability to tune the SQL execution plans of inefficient SQL queries that are used by ActiveMatrix BPM.

Procedure

  1. Collect fresh performance statistics from objects with either stale or non-existent statistics, by running a command similar to the following: 
    BEGIN
   DBMS_STATS.gather_schema_stats (
      ownname            => 'BPMUSER',
      estimate_percent   => dbms_stats.auto_sample_size,
      method_opt         => 'for all indexed columns',
      options            => 'GATHER AUTO',
      cascade            => TRUE);
END;
/
  2. Run an Automatic Workload Repository (AWR) report against two database snapshots to identify any SQL queries that are taking a long time to execute.
  3. For each offending SQL query:
    1. Generate a SQL profiling report for the SQL statement (using the sqltrpt.sql tool).
    2. Implement any SQL profile(s) that the report recommends to produce an improved execution plan for this SQL statement.

Example

For further information, see the Oracle documentation.