2

One of the job schedulers is running in the production environment on a daily basis which use to take only 20 mins based past execution history, but today it's been more than 2 hours still not completed.

a) How to check whether the SQL plan has changed today or not?

b) What could be the reasons for the plan change? One I know due to code change. What else could cause plan change?

general46
  • 800
  • 6
  • 16

2 Answers2

0

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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
0

Quick Check :

Please check whether the Statistics, is upto Date, both System and Table statistics. Pleae check if any changes to table or index made ?

Naga
  • 1
  • 1