Range partitioned by month.
It looks like several years' worth of partitions were created a while ago
(maybe even on table creation) and we have partitions for 2018 and 2019 left.
I like the idea of using dbms_scheduler to add the partition and copy the stats
from an existing partition. Of course I'd have to drop all the current empty
partitions first but it seems like it should solve the problem. Thank you
Jonathon!
Time to start scripting and testing. If anyone happens to have a script handy
it would be appreciated. If not, no big deal.
Though first I'll verify that I don't have no_invalidate=true set. I doubt it
but doesn't hurt to check.
Jay Miller
Sr. Oracle DBA
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Thursday, February 01, 2018 11:54 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Bad plan selected at the beginning of the month for partitioned
table
Is this range partitioned, or interval partitioned ?
If it's range, how does a partition get added to the table ?
I would suggest using dbms_scheduler to add one partition just before it is due
to be populated and rolling stats forward (with slight adjustements - check
dbms_stats.copy_table_stats) rather than adding lots of empty partitions. The
scope for some queries doing silly stuff if very high if you've got stats
describing a large volume of data that doesn't actually exist. (Obvious
example - "select ... where partition_column > sysdate - 7")
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jay.Miller@xxxxxxxxxxxxxxxx <Jay.Miller@xxxxxxxxxxxxxxxx>
Sent: 01 February 2018 15:29
To: oracle-l@xxxxxxxxxxxxx
Subject: Bad plan selected at the beginning of the month for partitioned table
Hello everyone,
I'm currently battling an annoying problem and was hoping someone had a better
solution than the one I came up with.
One of our databases has 2 frequent queries that run against a partitioned
table. At the beginning of the month when the partition is empty they start
doing a full partition scan instead of an index range scan (no surprise).
However even after rows start being loaded to the partition and stats are
gathered that night the plan does not change back. I have verified that the
stats have been updated, numrows is populated, and the stats do not show as
stale. But even after 3 days the bad plan continues to be used and app
performance gets so poor that I manually purge the sql_id from the shared pool.
It then reverts to the index range scan and everything is fine.
When I first encountered the problem I thought it was an easy fix, I'd just
create a baseline. Unfortunately I then discovered that the text of the sql
changes each month to give a month specific alias. E.g., something like
select column1 "January 2018 Totals", column2...
So the sql_id is different each month and any baselines or profiles will not be
used. Also because of this I can't easily schedule a job to purge the plan. I'd
don't want to schedule a job to flush the entire shared pool at the beginning
of the month due to performance issues when everything is reparsed and the
possibility of a bad plan being chosen somewhere else.
Barring any of you suggesting a more elegant suggestion I intend to precreate a
few years' worth of partitions, copy stats from a current partition, and then
lock them. I'll put an Outlook reminder to do the same in a few years and hope
I'm either still with the company or remember to pass this piece of lore down
when I leave.
Any better ideas he asked hopefully?
Oracle 11.2.0.3 on RH 6.
TIA,
Jay Miller
Sr. Oracle DBA
--
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwIF-g&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=kxmB2b4j22t18zBaosKuAKXob9QVX3ewMXd0qE7NDfI&s=G2I6OvCozeGelrCZ1fJvVfON78ArFOFbGvxSitb1QpY&e=
--
//www.freelists.org/webpage/oracle-l
