SQL Consuming Significant DB Time Event
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.
Identify Misbehaving SQL
Option A: AWR From an AWR search on "Recommendations".
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