You can check if the SQL execution plan has changed by using the Active Workload Repository (AWR). First, you need to find the SQL_ID for the relevant query. The view GV$SQL
contains the most recent SQL. If you can't find the query in this view, try DBA_HIST_SQLTEXT
instead.
select sql_id, sql_text
from gv$sql
where lower(sql_fulltext) like '%some unique string%';
With the SQL_ID, you can start investigating historical information. The table DBA_HIST_SQLSTAT
contains lots of summary information about the SQL. The most important column is PLAN_HASH_VALUE; if that value changes, then the execution plan has changed.
select snap_id, sql_id, plan_hash_value, executions_delta, elapsed_time_delta/100000 seconds_delta
,dba_hist_sqlstat.*
from dba_hist_sqlstat
--join to dba_hist_snapshot if you want to find precise times instead of SNAP_IDs.
where sql_id = '&SQL_ID'
order by dba_hist_sqlstat.snap_id;
If the plan has changed, you can view both plans with this:
select * from table(dbms_xplan.display_awr(sql_id => '&SQL_ID'));
Unfortunately, the most difficult part of query tuning with Oracle is that there are a dozen different ways to view the execution plans, and each of them provides slightly different data.
This query only returns numbers for the last execution, but it returns actual numbers and times, which helps you focus on the specific operation and wait events that caused the problem.
select dbms_sqltune.report_sql_monitor(sql_id => '&SQL_ID', type => 'text') from dual;
This query returns some additional execution plan information, specifically the Note
section. Most graphical IDEs leave out that section, but it's vital for complex troubleshooting. If something weird is going on, the Note
section will often explain why.
select * from table(dbms_xplan.display_cursor(sql_id => '&SQL_ID'));
There are many reasons why execution plans can change. If you add additional information to the question I may be able to make an educated guess.