Hi Stefan,
You are right.
I still not have understood why the predicate part is not saved into
historical data dictionary tables (the last release I've tested was
12.1.0.1.0). We can get the bind variables from historical execution plans
and not the predicate part.
In my previous e-mail, I wanted to say that there is a big chance that the
performance issue in this case is not due to a change in execution plan but
rather to sharing the same plan for a set of input bind variable that are
not doing the same amount of work. Indeed Kumar said *“**I thinking
flushing the sql above helped in making the program complete”*
And tends to confirm that a new hard parse (thought that we have not been
shown or told if the flushing has produced a new plan) solved the issue
Best regards
Mohamed
2015-04-04 13:48 GMT+02:00 Mauro Pagano <mauro.pagano@xxxxxxxxx>:
I thought we were talking Oracle PHV2 and not SQLT_PHV2, my bad :-)
You are right, no filter predicates in AWR so SQLT_PHV2 cannot consider
them when the plan is just in AWR so SQLT_PHV2 will just have the same
value as SQLT_PHV(1)
On Sat, Apr 4, 2015 at 4:41 AM, Stefan Koehler <contact@xxxxxxxx> wrote:
Hi Mauro,
yes, of course :-)
But does DBA_HIST_SQL_PLAN.OTHER_XML also include the predicate section?
Afaik it does not. Carlos also differs in his (old) tool between SQLT_PHV1
(id, parent_id, operation, options, index_columns and object_name) and
SQLT_PHV2 (SQLT_PHV1 + access and filter predicates). SQLT_PHV2 can not be
derived from AWR data (DBA_HIST_SQL_PLAN.OTHER_XML) afaik, but it would
be great of course.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Mauro Pagano <mauro.pagano@xxxxxxxxx> hat am 4. April 2015 um 00:29geschrieben:
PHV2 is available in OTHER_XML in DBA_HIST_SQL_PLAN :-)
