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]> @dashtop &s,&fms "&fms <> 0" &d Total Seconds AAS
%This SQL_ID FORCE_MATCHING_SIGNATURE FIRST_SEEN
LAST_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
2019-09-23 20:07:20*
*--*
*just my $0.02*
*Att*
*Luis Santos*
Em qua, 18 de set de 2019 às 02:55, Andy Sayer <andysayer@xxxxxxxxx>
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
