Re: Where does GC grab historical SQL plans from?
- From: "Rich Jesse" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 13 May 2011 10:22:08 -0500 (CDT)
Hey Marcin,
>> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
