SQL Consuming Significant DB Time Event

Overview

There can be occations where an event occurs where a SQL statement consumes significant database time. In some events they may not show up in an AWR.

What follows are the steps to find and address SQL that is no longer using the ideal profile because the data has changed enough for the cost based optimizer (CBO) to inaccurately change plans.

Procedure

Identify Misbehaving SQL

Option A: AWR From an AWR search on "Recommendations".

Example Entry

 Recommendation 1: SQL Tuning
   Estimated benefit is n active sessions, 53.7% of total activity.
 Action
      Investigate the UPDATE statement with SQL_ID "dx7wbjdzxvq1w" for
      possible performance improvements. You can supplement the information
      given here with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID dx7wbjdzxvq1w.
         UPDATE CASHBOXBALHIST SET BALAMT = BALAMT + :B1 , DATELASTMAINT =
         SYSDATE WHERE CASHBOXNBR = :B4 AND CASHBOXSUBNBR = :B3 AND EFFDATE =
         :B2

Option B: gv$active_Session_history or dba_hist_active_Sess_history