Re: How to drop a plan_hash_value from shared pool

There's several ways to check if some hogging SQL is not using bind variables (and you can't change cursor_sharing parameter). In this case a SQL Plan Baseline with force_match=>true is always a good approach.

As Tanel has entered into the conversation, here´s my one, using the lovely dashtop.sql script.

If you found two hogging (and different) SQL_ID with the same FORCE_MATCHING_SIGNATURE you got it.

*[11g RAC]>  _at_dashtop &s,&fms "&fms <> 0" &d     Total   Seconds     AAS
%This   SQL_ID          FORCE_MATCHING_SIGNATURE FIRST_SEEN

-------------------------- -------------------- --------------------
 83840     1.0   41%   2q0pvprfzqpds       15478817536217367974 2019-09-23
09:32:50  2019-09-24 09:06:51     16280      .2    8%   11j6hhubkv2sh
10391738638077405425 2019-09-23 09:34:11  2019-09-24 08:08:18     10970
 .1    5%   d10y1qy1k3jj9       10976467828042558926 2019-09-23 10:00:39
 2019-09-24 09:03:30      6320      .1    3%   0fs1w53wzddzc
 4671524384080753541 2019-09-23 09:33:51  2019-09-24 08:11:59      6110
 .1    3%   3kt1v439my5xd        4671524384080753541 2019-09-23 09:35:11
 2019-09-24 08:13:29      4370      .1    2%   0ycdvacgrcv1z
118640440300348972 2019-09-23 12:33:51  2019-09-24 07:53:15      3630
 .0    2%   3rur9qtjh8k3f       14779287056306492627 2019-09-23 10:16:12
 2019-09-24 08:24:01      3620      .0    2%   af73n0n34m1vk
17350151329603862951 2019-09-23 09:42:32  2019-09-24 08:29:23      3620
 .0    2%   05a9b49j4zfm2       10562462374696552156 2019-09-23 10:03:17

*--*

*just my $0.02*
*Att*
*Luis Santos*

Em qua, 18 de set de 2019 às 02:55, Andy Sayer <andysayer_at_gmail.com> escreveu:

> Correcting the statistics so that this bad plan is costed as a bad plan
> will usually prevent it from being used. Have you looked at what’s causing
> it to be so desirable?
>
> If you use the no_invalidate=>false argument to your gather_stats command
> then you’ll also cause your current child cursors to need reparsing (if
> they’re ever used).
>
> To me, this seems like the easiest and most sensible option.
>
> Of course, you could force cursor sharing and stick on a SQL Plan
> Baseline. Or you could do a similar thing with a SQL Patch that hints at
> not doing the thing in the bad plan you want to avoid.
>
> Hope that helps,
> Andrew
>

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