Databases@Powered by Passion
This content discusses manipulation of table statistics and direct path read decisions in Oracle database. It explores the usage of the table_stats hint to force a direct path read and the impact of various thresholds on this decision. The author shares their findings, including challenges faced and solutions tried, such as using OPT_PARAM hint and optimizing rownum filter. The post concludes with a resolution to the issue.
The content discusses an issue with non-shared cursors in a production database due to differing NLS settings, leading to increased child and version counts under “LANGUAGE_MISMATCH.” It details the specific NLS settings, their impact, and solutions, such as monitoring login triggers, detecting NLS settings, and standardizing NLS_ENV settings for scheduler jobs.
The performance issue with an SQL query was resolved by using the /*+ PARALLEL(2) */ hint, reducing execution time from 180-200 seconds to milliseconds. The decision to perform a direct read from disk to memory instead of reading blocks from disk was made by the database engine and is controlled by the hidden “_serial_direct_read” parameter. To force direct path read for SQL statements, the running statement can be used with a parallel hint or by setting table statistics. This solution allows for applying a SQL patch and significantly improves query performance.
The content discusses the concept of remastering in database management, particularly regarding Oracle RAC. It explains how remastering reallocates resources among nodes during changes, which can temporarily disrupt access to data. The innovative “lazy remastering” technique minimizes these disruptions by focusing on critical resources, allowing most processes to continue uninterrupted. In upcoming releases from 19.20 onwards, the default value for “_gc_policy_minimum” will change due to an internal bug. It will be reset to 15000, eliminating the need for manual tuning. DRM attributes are intentionally undocumented and should not be altered without support consultation.
Patching your database is crucial for security and performance. Develop a strategy based on your organization’s needs. Regularly schedule routine patches, test thoroughly, and inform stakeholders. Prioritize critical security fixes. Read release notes and consider expert blogs. Apply patches selectively, and consider in-place and out-of-place patching methods. Fleet Patching and Provisioning can scale patch deployment effectively.
The post discusses the process of cleaning old Oracle Homes after patching database servers. It advises keeping old homes for 15 days as a precaution, then removing them when comfortable with the new ones. The methods described for removal involve the deinstall command and fleet maintenance using cleanup_software verb.
Osman’s DBlog 