I have often wondered why when we consult a past execution plan that is protected by an SPM baseline, there is no Note at the bottom of that execution plan showing the use of that SPM? There is good news. Starting with 21c this has been corrected by Oracle. Here is the demonstration: This is […]| Mohamed Houri’s Oracle Notes
It often happens that changes are implemented in new Oracle releases without most (if not all) DBAs and developers being informed. Among those changes, introduced most probably in version 19.13, is a behavior change in the force matching the signature of a SQL query using a mix of bind variables and literals. Here is a […]| Mohamed Houri’s Oracle Notes
Here is a simple but very practical example to show how to identify the root cause of a sudden and intermittent lock that considerably affects the proper functioning of a database: Above, I have presented the output of the dashtop Tanel Poder script applied during two periods where the application was suffering a clear slowdown. […]| Mohamed Houri’s Oracle Notes
PREAMBLE In the previous article, we discussed the two-pass technique used by Oracle to evaluate the cost of the different OR-Expansion states. We have shown that this technique evaluates the cost of only two states, the initial NT state, and the FORE state. We have demonstrated as well that this strategy is used by Oracle whenever the number of […]| Mohamed Houri’s Oracle Notes
Abstract The OR Expansion represents the ability of the CBO to transform an initial query that includes predicates linked together by an OR operator into a UNION ALL operator applied on two or more query blocks known as the UNION ALL branches. This transformation allows the CBO to consider new index access paths and join […]| Mohamed Houri’s Oracle Notes
In this 7th episode of the series of articles about why my execution plan has not been shared, I will deep dive into the AUTO_CHECK_MISMATCH. Before I start, I wanted, first, to remind you that I actually already explained this reason in part 5 of the same series. I have, in fact, demonstrated in that […]| Mohamed Houri’s Oracle Notes
Notice the output of the following query: Do you know what the impact of the Notes HIST_FOR_INCREM_STATS HYPERLOGLOG is? Impact n°1 The first impact I have recently discovered concerns the bind sensitive property of cursors using predicates on columns flagged with the above Notes. Here’s a simple demonstration. I will first start with a “normal […]| Mohamed Houri’s Oracle Notes
I realized the other week while following the Diagnosing Oracle RAC Performance using AWR reports webinar that there are many persons out there asking why, despite their sql_id is present in dba_hist_active_sess_history (AWR) they are, however, unable to get its corresponding execution plan from AWR. This is the third situation, in just a couple of […]| Mohamed Houri’s Oracle Notes
When Disk file operations I/O wait event starts showing at the top of the AWR Top 10 Foreground Events by Total Wait Time it can consume a lot of CPU and freeze your database. Since I have gone through that myself a couple of weeks ago, I thought it might be interesting to write a […]| Mohamed Houri’s Oracle Notes
Here’s an interesting OR-Expansion limit which reared its head a little bit after a 12cR1-to-19c Oracle upgrade. It happens during a Create Table As Select (CTAS) which was using a CONCATENATION op…| Mohamed Houri’s Oracle Notes
