Month: December 2024
-
Oracle Database 19c APEX Upgrade: 19.2 to 24.1.7
This post outlines the process for upgrading Oracle Application Express (APEX) from version 19.2 to 24.1. APEX is a low-code platform for developing web applications with Oracle Database. The upgrade process is independent of the database version but requires Oracle Database version 19c or later. The steps include downloading the new version, creating dedicated tablespaces, executing installation scripts, modifying request-handling procedures, and dropping the old APEX user and tablespace. Finally, the latest cumulative patch, released on December 9, 2024, is applied to ensure a secure, reliable, and efficient APEX environment.
-
Oracle Incrementally Updated Backups : Detect and Delete Orphaned (Expired) Datafile Copies
Oracle recommends adopting a merged incremental disk backup strategy to ensure efficient database protection and faster recovery times. This includes keeping an up-to-date image copy as the base while applying incremental backups. Key practices involve setting a REDUNDANCY 1 retention policy, utilizing the Fast Recovery Area, and employing Block Change Tracking for efficiency. DBAs must manage leftover datafiles after tablespace deletion to ensure that the base image copy remains in sync with the database, as detailed throughout the blog post.
-
CPU-intensive query to simulate CPU load in PostgreSQL database
This blog post provides a PL/pgSQL script that utilizes pg_background extension to create and execute CPU-bound background worker processes. Also validates the CPU usage with docker stats.
-
CPU-intensive query to simulate CPU load in Oracle database
This blog post provides a PL/SQL script that utilizes Oracle’s DBMS_SCHEDULER to create and execute CPU-bound jobs. Also validates the CPU usage with AWR Report and docker stats command. Additionally, it addresses constraints of the Oracle Database 23ai Free version, limiting CPU usage to two cores, despite available resources.
-
Finding Row Counts for All Tables in Oracle Database
This post explores the debate between using table statistics and executing SELECT COUNT(*) queries for retrieving row counts for all tables in Oracle databases. It highlights the efficiency of relying on table statistics, which require minimal buffer gets (20–29) compared to the substantial overhead of directly counting rows, as observed in the test case with buffer gets reaching up to 492,252. Online statistics(12c) and Real-Time Statistics(19c) features are also highlighted. Overall, it recommends only using table statistics for row count retrieval when querying all tables in the database or schema.
-
Finding Row Count for All Tables in PostgreSQL Database
The post discusses the effectiveness of two concepts for counting rows in PostgreSQL: using table statistics versus executing the SELECT COUNT(*) command. While table statistics offer faster row estimates with minimal resource consumption, accuracy can be hindered if the statistics are outdated. In contrast, SELECT COUNT(*) provides exact counts but incurs substantial performance penalties, especially with large tables. Testing with the Adventureworks database shows that for retrieving counts of all tables relying statistics collector or mimicking the planner’s logic can be significantly more efficient than direct queries.
Osman’s DBlog 