Category: Performance

The content highlights key Oracle SQL and PL/SQL behaviors that, while seemingly simple, often result in unexpected issues in production environments. It emphasizes the importance of revisiting foundational topics to avoid bugs and performance problems. Through a series of blog posts, real-world examples are shared to assist others and enhance understanding of crucial functionalities, aiming to support the Oracle community effectively.

Hash partitioning enhances database management and performance by evenly distributing data across partitions, crucial for handling high concurrency. However, choosing an appropriate partition key is vital to avoid skewed data distribution. Proper use of partition keys allows for efficient query performance and reduces contention in both single-instance and RAC environments.

This content highlights the importance of stability in database performance, especially regarding Oracle’s SQL execution plans, which can fluctuate based on various factors. It focuses on three primary methods for influencing execution plans: SQL Plan Baselines, SQL Patches and SQL Profiles and providing a detailed explanation of how each method functions. It includes a test scenario where all three options are used, demonstrating their behavior. Additionally, it presents a conflicting scenario to help determine the precedence of these methods.

The content discusses essential tools and scripts for monitoring Oracle Database Health, emphasizing the importance of proactive health checks to prevent performance issues. It introduces various tools such as AHF Insights, ORACHK/EXACHK, OSWatcher, Procwatcher, and TFA, all contributing to efficient database management. The post also reviews health check scripts designed for specific components like Oracle Spatial and APEX, alongside utilities for diagnosing SQL performance issues using tools like AWR, SQLHC, and SQLT. Also, using DBMS_DICTIONARY_CHECK for data dictionary health checks is demonstrated. Ultimately, it encourages customized checks based on unique database needs.

Bind variables improve SQL performance and security by reusing execution plans, reducing hard parsing overhead and mitigating SQL injection risks. However, key information can be hidden from the optimizer, limiting optimal execution plan generation unless adaptive cursor sharing is used. Sometimes, using bind variables can result in an excessive number of child cursors due to reasons such as BIND_MISMATCH, BIND_LENGTH_UPGRADEABLE, and BIND_EQUIV_FAILURE. Tools like version_rpt help diagnostic efforts for child cursors, while effective solutions often involve application code modifications and NO_BIND_AWARE hints.

This post discusses the ‘ORA-01405: fetched column value is NULL’ error encountered in a query that executes a function where NULL values are passed as input. The initial solution was to add a NOT NULL filter, but it did not resolve the issue. However, adding the suggested DBMS_LOB.GET_LENGTH() function to filter out NULL BLOB columns ultimately solved the problem.