Category: Oracle

The post discusses the use of partial indexes as a solution for improving query performance. It explains that a partial index is built over a subset of a table defined by a conditional expression. By excluding common values, it reduces index size, speeds up relevant queries, and improves table update operations. The author shares their successful implementation of a filtered index solution in Oracle, achieving a 98% performance boost.

In a critical OLTP database, a long-running query caused a “Latch: cache buffer chains” wait event issue. To address this, the query was optimized by making use of a deterministic function and using scalar subquery caching. When the table partitioning strategy was changed, the issue was resolved, but a batch job suffered performance issues. Investigating further, it was found that the optimizer’s choice changed after table statistics were refreshed, leading to degraded query performance. Various solutions were explored to address this, including using an index hint, fixing the plan with a baseline, using a SQL profile and rewriting the query.

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.