Re: How to drop a plan_hash_value from shared pool
Date: Tue, 17 Sep 2019 22:34:37 -0400
Message-ID: <CAMHX9J+Rwkx4gyf1B-ry-coQT6PqcRC9e0evDOfUu9WWbJTBsQ_at_mail.gmail.com>
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:
*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_at_oracledepot.com> wrote:Received on Wed Sep 18 2019 - 04:34:37 CEST
>
>
> On Tue, Sep 17, 2019 at 3:53 PM Eriovaldo Andrietta <ecandrietta_at_gmail.com>
> wrote:
>
>> I would like to know how to drop the plan_hash_value , in order to avoid
>> 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 ?
>>
>>
> There isn't a way to drop a PHV from Oracle, however, you can try to
> create a SQL Profile with force_match=> TRUE. Provided you have TUNING
> pack, of course.
>
> Andy K
>
-- http://www.freelists.org/webpage/oracle-l