I Recently came across a performance issue in a 12c database. Queries that used to run in 500 ms, now were taking several minutes to complete, in some cases 30 or more minutes. While troubleshooting the issue, many different ideas came to mind to try to resolve the issue - gather schema stats, change optimizer settings, partition related objects, etc.
After trying all these options - and some more, we were able to improve response time; now it was consistently around 5 seconds, but still way too far from the 500 ms that was before.
While analysing the (still) issue, I realized that when getting an explain plan for the queries, it would take around 5 seconds to get it. So I decided to gather stats on fixed objects and on data dictionary in order to improve explain plan response time.
After having done this, overall system performance improved considerably, explain plan response time came down to couple milliseconds, and problematic queries returned to normality, ~500ms.
Obviously this is not black magic, now I know this is just how Oracle works. In order for the optimizer to choose an optimal execution plan, it relies on the fixed objects to generate such - those are the x$ tables. If those objects have stale or missing statistics, then the optimizer uses a set of predefined default values for these tables that could lead to suboptimal execution plans.
In the end the solution was easy, but getting to it was not that easy. This area seems to be something that not many people talk about, I don’t know if that is because is too obvious or because is not generally known. Fixed object stats should be refresh any time a major change occurs on the database, for example, partitioning tables, purging data, dropping objects, changing database parameters, index rebuilds - anything that could affect the data stored in the x$ tables.
Next time when you run out of options when tuning SQLs or the database, remember that may be suffering from fixed objects stale stats.
By the way, this is the command I used:
begin
DBMS_STATS.GATHER_DICTIONARY_STATS;
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;end;/
To read further: https://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why
How to get to Harrah's Lake Tahoe Resort and Casino - JT
ReplyDeleteDirections to Harrah's Lake 안동 출장안마 Tahoe Resort and Casino (Lake Tahoe) 하남 출장샵 with 제주 출장마사지 public transportation. The 광주 출장샵 following transit lines have routes that pass 안동 출장안마 near
Harrah's Casino - Mapyro
ReplyDeleteFind the best Harrah's Casino in 태백 출장마사지 Harrah's 서산 출장안마 Lake Tahoe, 광양 출장안마 including popular hotels, 대전광역 출장마사지 casinos and entertainment, restaurants and more. Harrah's 경상북도 출장안마 Hotel Lake Tahoe.