--------------------------------------------------------------------------------

--

-- File name:   sqlhist.sql

-- Purpose:     search plan changes in history for given sqlid in ash data

--

-- Usage:      sqlhist <sql_id>

--

--------------------------------------------------------------------------------

column begin_interval_time format a30

select distinct dash.snap_id,BEGIN_INTERVAL_TIME,sql_id,sql_child_number child_no,sql_plan_hash_value  plan_hash_value

from dba_hist_active_sess_history dash,dba_hist_snapshot dhs where dhs.snap_id=dash.snap_id

and sql_id='&1' order by 1 asc;