Re: Where does GC grab historical SQL plans from?

>> Thoughts on where GC grabs SQL history from?  DBA_HIST_SQL_PLAN on the
>> target DB is oddly devoid of this particular SQL.
>>
>
> GC is using Database AWR repository - DBA_HIST_ tables.
> How often are you doing AWR snapshots ? Can you see that query in real
> time mode ?

After a bit of trial-and-error...erm..um..."empirical investigation", it looks like GC is pulling historical SQLs from the DBA_HIST_ACTIVE_SESS_HISTORY view on the target.

The problem with this particular query with respect to performance tuning was that because it was historically so efficient that it fell under the AWR radar and was not previously captured. So I had no way to view the SQL's previous plan to compare to the current bad one.

This whole issue stemmed from an ERP program that occasionally and unexpectedly generates SQL with binds that contains a filter against the PK for a non-existent value. Because we have histograms, CBO sees this and correctly chooses another index for that query. And because we're on 10.1, if this should be the first hard parse of that SQL, the "incorrect" index sticks for subsequent runs. I've been invalidating those cursors by ALTERing the target table with a (hopefully!) innocuous reset of the PARALLEL parameter, but this is hardly ideal and a mess to explain to management.

What I ended up doing was using a Perl script I wrote that compares a SQL in memory to its plan estimate using the actual captured bind values in place of the bind variables. It relies on timing, however, since the last binds captured may not be the ones used in the "problem" execution.

Adaptive Cursor Sharing, here I come! As soon as the upgrade project gets approved... :)

Thanks all for the feedback!

Rich

--
http://www.freelists.org/webpage/oracle-l