The post discusses a performance issue where an identical query yields different execution plans in TOAD and sqlplus due to various factors such as statistics, physical differences, settings, and OCI commands. The issue is identified as related to Adaptive Cursor Sharing (ACS), and the difference in behavior is observed when running the query with different bind values. The response also mentions a change in TOAD’s OCI Array Buffers size option, resolving the issue in the upcoming beta release.

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.