Insane DBA...

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 predicate. Partial indexes are a specialized feature, but there are several situations in which they are useful. One major reason for using a partial index is to avoid indexing common values. Since a query searching for a common value (one that accounts for more than a few percent of all

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 experience performance problems and i digged down the issue. Root cause was the change of partitioning strategy. One query suffered deeply. This is how i investigated poor query performance and cured it.  ANALYSIS After changing the partitioning strategy o

Manipulating table statistics for the statement period On my blog post " Who makes the decisions? I thought it was Optimizer ", I used parallel hint to force a direct path read. This time i will try to make use of table_stats hint to force a direct path read. There is not much documentation about the usage of this hint. Roger Macnicol blog post ( Correct syntax for the table_stats hint  ) offers up some useful information about it. These are the variables play role in direct path read decision.  STT stands for small table threshold = _db_block_buffers * 0,02 =  3875820 * 0,02 = 77516 MTT stands for medium table threshold = _small_table_threshold * 5 = 77516 * 5 = 387580 VLOT stands for Very Large Object Threshold = _db_block_buffers * 5  = 3875820 * 5 = 19379100 I will show how the thresholds effect the direct path decision. We will use nsmtio (non smart IO) event  to trace the direct path or non-direct path/buffered decision.   Before 11.2.0.2 release, the direct path read d

The reason behind is NLS_SETTINGS 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 with "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 got changed in different combinations in every execution, it creates new child and version count increases with LANGUAGE_MISMATCH. Exactly the same queries and PL/SQL packages are called from different applications . Different applications connect to database with different database users, but execute some common sql statements. Also These applications are deployed to different servers with different operating systems and client environments. Some example child cursor reasons are as below. <Child