Databases@Powered by Passion

  • Offline Upgrade of PostgreSQL and Patroni

    How to Upgrade PostgreSQL, PostGIS and Patroni in Offline Environment

    —

    by

    Osman DİNÇ
    ,

    This blog post outlines the process of upgrading a PostgreSQL environment with no internet, covering major and minor upgrades, as well as updates to various extensions. It details the specific steps for upgrading each component, including Patroni, pgbackrest, PostGIS, pg_cron, and Oracle_FDW. The post emphasizes the importance of careful planning and thorough testing for major upgrades, along with considerations for downtime and data replication. Additionally, it provides guidance on memory resource management and configuration adjustments during the upgrade process.

  • Oracle Database Patching using Ansible

    Oracle Database Patching by Using Ansible

    —

    by

    Osman DİNÇ
    , ,

    This post discusses using Ansible Playbooks to patch Oracle database software, from installing Ansible to completing the patching process. It provides detailed playbooks for creating new database software directories, applying release updates and one-off patches, running datapatch, and removing old database homes. The author also revised the playbooks for better readability and compatibility.

  • Using Ansible to patch Oracle Grid Infrastructure

    Oracle Grid Patching by using Ansible

    —

    by

    Osman DİNÇ
    , ,

    This blog post discusses the usage of Redhat Ansible to patch grid infrastructure, emphasizing its simplicity and efficiency. It explains the process of installing Ansible, preparing the environment, and executing playbooks for grid software patching with detailed steps. The author also provides valuable insights into the revision of playbooks for best practices.

  • No bind peeking when using TOAD

    TOAD and sqlplus have Different Execution Plans: Bind variable peeking

    —

    by

    Osman DİNÇ
    , ,

    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.

  • Oracle Filtered Index Implementation

    Creating a Conditional Oracle Index Equivalent to a Partial Index in PostgreSQL

    —

    by

    Osman DİNÇ
    , ,

    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.

  • Histogram directs to wrong execution plan

    Column statistics – histogram negatively affected query performance

    —

    by

    Osman DİNÇ
    , ,

    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.