Databases@Powered by Passion
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.
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.
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.
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.
Counting rows in database tables is a common concern for DBAs, as it’s highly discussed in forums like ASKTOM and Stack Overflow. Despite its performance drawbacks, developers/dbas often use row counts for data validation during migrations, capacity planning, anomaly detection, and performance tuning. The use of table statistics and computing the exact row count with ‘SELECT COUNT(*) FROM’ is debated. The article emphasizes the need to question the necessity of exact row counts in business requirements perspective.
Released on 12th November 2024, Oracle Database 23ai now offers free container images optimized for ARM-based Apple MacBook computers with M-series chips, enhancing development efficiency. Two image options, Full and Lite, are designed to meet diverse requirements. This post details the process for setting up the Oracle Database using Docker, checking logs, connecting with SQLcl, and importing sample schemas(HR,SH and CO), providing Mac developers with an efficient way to create test environments.
Osman’s DBlog 