Oracle Expert

Sometimes you will receive complains from users that something that has been running ok for ages on a production server, suddenly is running very very slowly. In 10g, AWR gives you the ability to go and check and possibly compare the execution path of a query at different times in the past.

select DBID, SQL_ID, SQL_TEXT from dba_hist_sqltext where sql_text like ‘%your query%’;

DBID      | SQL_ID      | SQL_TEXT |

———-+————-+—————————+-

1234567890|12345xxxxxxxx|UPDATE STAGING SET BLAH    |

We know that yesterday it was running fine, but today it’s not, and users have confirmed that data volumes are identical between the two dates

select s.begin_interval_time, s.end_interval_time , q.snap_id, q.dbid, q.sql_id, q.plan_hash_value, q.optimizer_cost, q.optimizer_mode

from dba_hist_sqlstat q, dba_hist_snapshot s

where q.dbid = 1234567890 and q.sql_id = ‘12345xxxxxxxx’

and q.snap_id = s.snap_id

and s.begin_interval_time between sysdate-2 and sysdate

order by s.snap_id desc;

When you find this, take note of the PLAN_HASH_VALUE from the 2 different times that you want to compare

Actually the 3 parameters that you need are:

·         DBID

·         SQL_ID

·         PLAN_HASH_VALUE

And to check the execution plan, run the following query:

select id, operation, options, object_name, cost

from dba_hist_sql_plan

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

and plan_hash_value = <plan_hash_value1>

compare that with the second one

select id, operation, options, object_name, cost

from dba_hist_sql_plan

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

and plan_hash_value = <plan_hash_value2>