4 Replies Latest reply on Jul 8, 2011 1:20 PM by Christopher Dale

    Oracle Database 11g Enterprise Edition Performance issue...

    Christopher Dale

      Hello all,

      i wanted to share this tidbit...

      I was having trouble with performance of the BBSA 8.0 with Service Pack 5-9 and could not find the issue until a coworker that specializes in DB Performance looked at the Database.

      What he found was in Oracle 11g Baselining was turned on and the Base plans were not being updated to the most efficient queries for the current amount of data. for example... when expanding a compliance job it would take up to 5 minutes to expand the "rules view". when he looked at the basline that oracle was using it was not very efficient for the query so as a test we disabled the baselining on the Database and the performance of BladeLogic improved from 3-5 minutes to open the "rules view" to less than 20 seconds. Below is his summary of what we found.. I hope this helps others....


      • Overall performance of the Bladelogic application had degraded significantly since the initial installation.
      • This performance degradation coincided with an increase in the amount of data in the system as additional components were added.



      • The Oracle parameters optimizer_capture_sql_plan_baselines and optimizer_use_sql_plan_baselines were set to TRUE when the BLADEP instance was initially created.  Setting optimizer_capture_sql_plan_baselines to TRUE (the default is FALSE) causes the instance to capture a SQL plan baseline for each statement that is executed more than once.  This initial baseline is set to both ENABLED and ACCEPTED by default.
      • If, at a future point in time, the optimizer determines that a better execution plan exists (e.g. as the data in the system changes), the optimizer adds a new plan to the baseline table that is ENABLED but is not ACCEPTED.  The optimizer still uses the original plan to process the query.  In order for the new plan to be used by the optimizer, a DBA must explicitly “evolve” this plan in order for it to be ACCEPTED (hopefully after confirming that the performance is actually better).  Only then can it be used to process a query.
      • A number of baselines were captured very soon after the initial install for queries that are commonly executed by the application.  As the data in the system increased, a number of additional baselines were captured (but not ACCEPTED) for these queries.  None of these new baselines were ever evolved so that they could be used by the optimizer.
      • Several of the worst performing queries were evaluated and in each case:
        • Multiple baselines existed in the baseline table for that query.
        • The only baseline that was accepted and enabled was for the original plan.
        • The performance was measured with optimizer_use_sql_plan_baselines set to TRUE and then to FALSE (this database parameter can also be set at the session level).
        • In each case the performance was dramatically better when the baseline was not used and the optimizer determined the execution plan at run time.



      • Short Term
        • Create a logon trigger for BLADELOGIC user in BLADET/BLADEP.  In the trigger, set optimizer_capture_sql_plan_baselines and optimizer_use_sql_plan_baselines to FALSE.  This will leave the current baselines intact but will tell the optimizer to ignore them and generate the appropriate plan at runtime.


      • Long Term
        • Set optimizer_capture_sql_plan_baselines to FALSE and optimizer_use_sql_plan_baseline to TRUE at the instance level.
        • Purge all baselines from the database.
        • Handle performance issues on a case by case basis and manually add baselines where this is the appropriate solution.