Category: Performance
-
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.
-
Table Statistics vs. SELECT COUNT: What to Choose?
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.
-
Troubleshooting gc cr/current block lost events and Improving Oracle RAC Network Performance
The Enterprise Manager Cloud Control’s Overview of Incidents and Problems section identified ‘Metrics Global Cache Blocks Lost at XXX’ for a relatively new database, suggesting a potential network issue, likely related to the private network interfaces. Troubleshooting was guided by My Oracle Support documents, which outline common causes and initial diagnostic steps. Further actions included making adjustments across nearly all OSI Model Layers; such as enabling jumbo frames, tuning NIC buffers, adjusting IP fragmentation parameters, and optimizing socket buffers as well as refining the database layer. These measures collectively reduced ‘gc block lost’ events to nearly zero.
-
Resolving Enq: TX – Row Lock Contention with Real-life Scenario Analysis
When encountering the Enq: TX – row lock contention wait event, it’s crucial to identify the root cause before taking action. This contention arises when multiple sessions attempt to modify the same row in a table simultaneously. To resolve it, database administrators should pinpoint the blocking session and sql statement. A detailed analysis of specific SQL statements can provide valuable insights for the development team, facilitating faster resolution.
-
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.
Osman’s DBlog 