Month: August 2024
-
v$sql INVALIDATION column and Prevent SQL Cursor Invalidation
This content highlights the impact of SQL cursor invalidation on system performance and suggests solutions to reduce the number of hard parses. It emphasizes the importance of avoiding hard parses in OLTP systems and discusses reasons for SQL cursor invalidation, such as schema changes, statistics updates and parameter changes. The implementation of atomic refresh for materialized views is presented as a solution to reduce SQL cursor invalidations leading to improved performance in OLTP systems.
-
How to configure HAProxy with external-check to query PostgreSQL database directly
The blog post discusses configuring HAProxy with an external check to query PostgreSQL directly for database server health monitoring. Choosing to deploy PgBouncer on database hosts led to the decision to use an external check in HAProxy. The configuration and benefits of this setup are detailed, along with sample HAProxy configurations for external checks. Deploying PgBouncer directly on PostgreSQL servers minimized network latency and simplified architecture while securing local connections.
-
Oracle Database 23ai Fuzzy Matching and Similarity Searches
This content provides a tutorial on performing similarity searches in Oracle 11g+ databases using various algorithms like Jaro-Winkler and Levenshtein by using ULT_MATCH package. It also introduces the FUZZY_MATCH package in Oracle 23ai release, allowing for greater flexibility and alignment with developer needs. The post includes sample SQL queries for implementing these algorithms and a referral to the Oracle 23ai SQL Language Reference for more information.
-
Unified Audit Trail: SQL Text Not Recorded in Syslog
The Oracle Database 12.2 or later uses Unified Auditing to track activity. However, certain fields are not captured in the syslog of Unified Audit Trail, impacting the completeness of log data. To address this, a simple method of sharing audit records with SIEM tools like WAZUH involves using SQL/JSON functions to stream data in NDJSON format. This can be done using a shell script scheduled with cron.
-
Fuzzy Matching in PostgreSQL: A Guide to pg_trgm extension
PostgreSQL’s pg_trgm extension facilitates efficient similarity searches using trigram matching, breaking text into trigrams for pattern matching and enabling the use of distance operators. It supports fuzzy matching and indexing for speedier searches, allowing for functions like similarity() and operator usage.
Osman’s DBlog 