Insane DBA...

GOOD NEWS is, It will kick in soon. I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. Last week, when I was investigating a performance issue, I observed that an identical query resulted in different execution plans in TOAD and sqlplus. According to " Doc ID 1671642.1 How To Diagnose Why an Identical Query Has Different Plans (and Performance) in Different Environments ", there may be different factors in plan selection. Although not all, some of them are as follows: Statistics   * Base Object Statistics   * System Statistics   * Dynamic Statistics (OPTIMIZER_DYNAMIC_SAMPLING)   * Statistics Feedback (_OPTIMIZER_USE_FEEDBACK) Physical Differences   * Physical and Logical Layout of the Database     * Different vol...

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. INTRO   In one of our critical OLTP database, we faced " Latch: cache buffer chains " wait event issue(a long running query) on a commonly used page. Long running query was called inside a PL/SQL package function. To alleviate the situation, first we changed the function to a deterministic function and rewrite the query's function call into a scalar subquery allows Oracle to use scalar subquery caching to optimize the number of function calls. As these cautions could not cure the issue and segment access statistics were not even, we changed table partitioning strategy from list partitioned to hash-partitioned. For the problematic query issue was solved, but a batch job which completes in 5 minutes before, started to ...

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. Previously on my blog post ( Sometimes a histogram existence may mislead to a suboptimal plan ), I analyzed a poorly performing query in detail and detected that sometimes a histogram existence may mislead to a suboptimal plan. I have listed some possible solutions.  But what i applied as a solution was to emulate a partial/filtered index in Oracle.   I know partial indexes concept from Postgresql database, it is also available on MS SQL and some others.  What is Partial Index? ... A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicat...

The reason behind is NLS_SETTINGS I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. I generally use Stew Ashton's below query to summarize reasons why cursors are not shared.  In one of our production database, the amount of child cursor count related to "LANGUAGE_MISMATCH" attracted my attention and I tried to make clear the reason behind. According to the  Database Hang With 'cursor: mutex X' Contention Due To High Version Count Under LANGUAGE_MISMATCH (Doc ID 2542447.1) , whenever there are different NLS settings that get changed in different combinations in every execution, it creates new child cursors and the version count increases with LANGUAGE_MISMATCH. Exactly the same queries and PL/SQL packages are called from different applications . Differ...