Category: Performance

The performance fluctuations in a query on production database were attributed to different NLS_SORT settings affecting SQL plan selection. The query showed varying performance with differing plan_hash_values despite a simple structure. Investigations revealed the impact of NLS settings on plan generation. Mimicking the behavior with setting NLS_SORT value in session confirmed the issue, leading to the creation of a functional index that resolved the problem, as evidenced by the subsequent execution plan.

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.

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.