Insane DBA...

Some decisions are actually not taken by the optimizer. We started to experience a performance problem with an sql query which we can not change the software code and tried to find a solution in solely database layer.  Sql query was a sophisticated one, but 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 was null and as this column is not indexed, query was performing a full table scan. And also, html_content column data type was CLOB. For that reason I could not create a functional index and i could not rewrite the query as the source code is not available. I tried to execute the query with  /*+ PARALLEL(2) */ hint, execution completed within milliseconds.  In Parallel Execution with Oracle Database White Paper document  it

No more frequent remastering of objects. 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 attributes are intentionally undocumented since they may change depending on the version. These attributes should not be changed without discussing with Support. You can access much more information about the Dynamic Resource management from the " RAC object rema

Bonus Step - What a Wonderful Patch (Patching Tips) 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. Know your connected clients/applications capabilities and how they make use of databases.  * Define a regularly scheduled routine to patch your databases. Start patching with your dataguard environments, test the new version with developers and inspect alert.logs and crs daemons trace f

Step 5 - Another one Patches the Dust. (Cleaning Old Oracle Homes) 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 in the inventory.xml under oraInventory. Old Grid Home is tagged with Removed=“T” attribute. Users can also cleanup a Oracle Grid Home using fleet maintenance by means of CLEANUP_SOFTWARE. More information is available on  EM 13c : Usage of CLEANUP_SOFTWARE Verb In Fleet Mai

Step 4 - Patchin' Alive. (Oracle RAC Database Patching using Fleet Maintenance)   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)        *  Prerequisite Checks For Setting Up Fleet Environment (Doc ID 2434264.1)     *  Enterprise Manager Fleet Maintenance FAQ (Doc ID 2272569.1)     *  How to Patch Or Upgrade RAC Database Using Fleet Maintenance (Doc ID 2448341.1) First create a location on Software Library in EMCC. We will us

Step 3 - Patching in the Deep. (Grid Patching using Fleet Maintenance)        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)        *  Prerequisite Checks For Setting Up Fleet Environment (Doc ID 2434264.1)     *  Enterprise Manager Fleet Maintenance FAQ (Doc ID 2272569.1)     *  How to Patch Or Upgrade Grid Infrastructure Using Fleet Maintenance (Doc ID 2436830.1)      First create a location on Software Library in EMCC. We will use it as a desti

Step 2 - Patch My Breath Away. (DB OOP Patching) 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-x86-64.zip)  * OJVM 19.20 Patch (p35354406_190000_Linux-x86-64.zip)  * DPBP 19.20 Datapump Bundle Patch (p35512813_1920000DBRU_Generic.zip)  * Spatial 19.20 Bundle Patch (p35602358_1920000DBRU_Linux-x86-64.zip)  * DB 555.1 Recommended One-off (p35372179_1920000DBRU_Linux-x86-64.zip)  * DB 555.1 Recommended One-off (p30787757_1920000DBRU_Linux-x86-

Step 1 - Patch Me If You Can. (Grid OOP Patching) 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 will patch a 2 node cluster grid infrastructure by using out of place ( OOP ) patching  methodology through gridSetup.sh in silent mode.  Doc ID 2853839.1   and Patching Oracle Grid Infrastructure 19c using out-of-place SwitchGridHome video by Daniel Overby Hansen   can be used a reference.  Later i will use patched grid homes to create a gold image for fleet patching of all grid homes. Setup List:  * Grid 19.3 Base Release (LINUX.X64_193000_grid_home.zip)  * Grid 19.20 RU (p35319490_190000_Linux-x86-64.zip)  * Grid 19.20 August MRP (p35656840_1920000DBRU_Linux-x86-64.zip)  * Grid 555.1 Recommended One-off (p35372179_1920000DBRU_Linux-x86-64.zip)  * Opatch Latest Version (p6880880_122010_Linux-x86-64.zip) Preparation Phase: Content of Response File: Deploying new software to cluste