Yep, when stepping back a bit - the real goal is to avoid using this bad
plan in the future. So, purging/flushing the current plan from the shared
pool is just one small step, but the main goal can be achieved with a SQL
Profile with force_match=>true like Andy said or SQL Baselines, once you
have binds used in your SQLs.
You can get binds used by getting your developers to do it; or by using
cursor_sharing=force as a (hopefully) temporary workaround. Baselines will
be created on the SQL statements/hashes after the literal->bind
replacement, so as long as the *number of literals* (like a varying number
of items in IN-lists) doesn't change, you'll have the same SQL_ID for all
invocations of this query.
And you can then disable some plans in the baseline:
- https://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL635
*When plans are loaded with the enabled parameter set to YES (default), the
database automatically marks the resulting SQL plan baselines as enabled,
even if they are unaccepted. You can manually change an enabled plan to a
disabled plan, which means the optimizer can no longer use the plan even if
it is accepted.*
Note that while cursor_sharing=force can fix the current SQL statement's
problems, then enabling it system- or session-wide may cause trouble for
others. Better to get developers to use binds - and perhaps this problem is
a good way to push them to get it done...
--
Tanel Poder
https://blog.tanelpoder.com/seminar/
On Tue, Sep 17, 2019 at 4:29 PM Andy Klock <andy@xxxxxxxxxxxxxxx> wrote:
On Tue, Sep 17, 2019 at 3:53 PM Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
wrote:
I would like to know how to drop the plan_hash_value , in order to avoidThere isn't a way to drop a PHV from Oracle, however, you can try to
any sql_id use it again.
Does it make sense ? or when I drop the plan for the sql_id, a new sql_id
will generate a new plan_hash_value that can be equal that one dropped ?
create a SQL Profile with force_match=> TRUE. Provided you have TUNING
pack, of course.
Andy K
