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...

Manipulating table statistics for the statement period 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. 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's blog post ( Correct syntax for the table_stats hint  ) offers up some useful information about it. These are the variables that play a 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 Thres...

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...

Some decisions are actually not taken by the optimizer. 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. We started to experience a performance problem with an SQL query which we cannot change the software code and tried to find a solution in solely the database layer.  SQL query was a sophisticated one, but the problem can be summarized with the short one below. Here is the plan output. Products table was a large table exceeding 50 Gb size and query's execution time was 180 - 200 seconds.  Query was searching in PRODUCTS table for the HTML_CONTENT with not null records, but for all rows,  HTML_CONTENT column values were null, and as this column is not indexed, the query was performing a full ...

No more frequent remastering of objects. 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. As default value of "_gc_policy_minimum"(formerly known as _gc_affinity_minimum) is quite low for busy environments, Those who are setting this parameter to 15000 according to the Best Practices and Recommendations for RAC databases with SGA size over 100GB (Doc ID 1619155.1) , I have good news for you.  From 19.20 (19c DBRU JUL '23) on, You may reset it to its default value.  Due to internal bug 34729755, 15000 is the new default in 23c, 19c DBRU JUL '23, and 19c ADB.  DBAs having compulsive tuning disorder like me won't have to tune this parameter any more in those releases or later. According to the  DRM - Dynamic Resource management (Doc ID 390483.1) , DRM attribu...

Bonus Step - What a Wonderful Patch (Patching Tips) 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. Failure to patch your database software may lead to losses of data that can cost money and reputation. That is the point of security. Also there are many other benefits, including adding features, and fixing bugs that make your applications run slow or not work as intended.   Every database administrator should have a patching strategy that suits up with his/her organization. I will share my tips that can also be useful for others and may not be suitable for some. Because every organization has their own constraints and unique environment. Generally Applicable  * First know your database environment and identify all the features you are using actively. Kno...

Step 5 - Another one Patches the Dust. (Cleaning Old Oracle Homes) 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. After you patch your database servers grid and database homes whether using out-of-place method or fleet patching method, you should remove the old Oracle Homes.  As a precautionary measure, I keep old Oracle Homes at least 15 days and once i am comfortable with the new ones, then i remove old ones. Removing old Oracle Grid Infrastructure Home with deinstall command:  The deinstall command detects cluster nodes, displays a short summary, asks for confirmation, operates on all nodes and warns you to run root.sh on all nodes, and usually leaves some leftovers. I also remove the old directory. After deinstallation, old grid home is flagged as deleted ...

Step 4 - Patchin' Alive. (Oracle RAC Database Patching using Fleet Maintenance) 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 will patch all my database servers database software from 19.16 to 19.20. As there are more than 20 servers to patch, we will use fleet patching. First, I have patched a 2-node cluster database homes by using out of place (OOP) patching methodology through runInstaller in silent mode in Step 2 - Patch My Breath Away(DB OOP Patching) . Now I will use patched an Oracle RAC database home to create a gold image for fleet patching of all Oracle RAC database homes. Some useful references:       *  Primay Note for Database Patching Using Enterprise Manager 13c Cloud Control Fleet Maintenance (Doc Id 2435251.1)   ...

Step 3 - Patching in the Deep. (Grid Patching using Fleet Maintenance) 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 will patch all my database servers grid infrastructure from 19.16 to 19.20. As there are more than 20 servers to patch, we will use fleet patching. First i have patched a 2 node cluster grid infrastructure by using out of place ( OOP ) patching  methodology through gridSetup.sh in silent mode in Step 1 - Patch Me If You Can (Grid OOP Patching)      Now i will use patched grid homes to create a gold image for fleet patching of all grid homes. Some useful references:      *  Primay Note for Database Patching Using Enterprise Manager 13c Cloud Control Fleet Maintenance (Doc Id 2435251.1)   ...

Step 2 - Patch My Breath Away. (DB OOP Patching) 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 will patch all my database servers database software from 19.16 to 19.20. As there are more than 20 servers to patch, we will use fleet patching. First, I will patch a 2-node cluster database homes by using out-of-place (OOP) patching methodology through runInstaller in silent mode. Later, I will use patched database homes to create a gold image for fleet patching of all database homes. Setup List:  * Database 19.3 Base Release (LINUX.X64_193000_db_home.zip)  * Database 19.20 RU (p35320081_190000_Linux-x86-64.zip)  * Grid 19.20 August MRP involves DB August MRP. (p35656840_1920000DBRU_Linux-x...