Performance

AWR reports are simultaneously the most powerful and most misread performance tool in Oracle’s arsenal. Most DBAs jump straight to Top SQL, copy the SQL_ID, and start tuning — skipping the sections that tell them why that SQL became a problem in the first place. This case study walks through a realistic AWR from a production Exadata system, section by section, using a deliberately constructed problematic scenario. Every number is fabricated for illustrative purposes; every diagnostic conclusion is grounded in real Oracle internals. ...

Performance diagnostics without a methodology is archaeology — you dig until you find something interesting, not necessarily something important. Oracle’s AWR and ASH provide an extraordinarily detailed record of database activity, but interpreting them requires a structured approach. This article presents a top-down methodology that moves from macro to micro: DB Time → Wait Profile → Top SQL → Root Cause. The Foundation: DB Time DB Time is the total time all sessions spent working in the database (on CPU or waiting for database resources) during a snapshot period. It is the single most important number in the AWR report. ...

When multiple VM Clusters share the same Exadata storage cells on ExaCC, storage I/O becomes a shared resource that can interfere between workloads. Oracle’s I/O Resource Manager (IORM) is the mechanism for controlling how storage I/O is distributed across VM Clusters, databases, and consumer groups. Without IORM configuration, a single runaway workload on one VM Cluster can starve others. IORM Architecture on ExaCC IORM operates at two levels: Inter-VM Cluster IORM (configured via OCI): Controls storage I/O allocation between VM Clusters sharing the same physical Exadata infrastructure. Intra-database IORM (configured via DBCA or DBRM): Controls I/O between databases within a single VM Cluster and between consumer groups within a database. Because ExaCC does not give customers cellcli access, inter-VM Cluster IORM is configured through the OCI Console or API — Oracle’s management layer pushes the configuration to the storage cells. ...

Oracle Database In-Memory (DBIM) adds a columnar representation of data alongside the existing row-based buffer cache. The key architectural insight is that both formats coexist — OLTP operations continue using the row-store (buffer cache) for optimal single-row performance, while analytical scans use the column store for optimal aggregation performance. The optimizer decides which format to use per operation. The Dual-Format Architecture Traditional databases force a choice: row format for OLTP, column format for analytics. Oracle’s approach avoids this by maintaining both simultaneously: ...

Partition pruning is the optimizer’s ability to eliminate partitions from a query’s access path based on predicate analysis. When it works, a query against a 10 TB table might access only 50 GB of relevant partitions. When it fails, you get a full table scan across every partition — often with no visible indication in the execution plan that pruning is not occurring. How Partition Pruning Works The optimizer evaluates predicates in the WHERE clause against the partition key definition during the parse phase. If a predicate can be resolved to a specific partition range, those partitions are excluded from the access path. ...

Oracle SQL Monitor is the most powerful single-query diagnostic tool in the Oracle performance toolkit. Unlike DBMS_XPLAN.DISPLAY_CURSOR, which gives you a static execution plan, SQL Monitor captures real-time runtime statistics at every step of the execution plan — rows processed, memory used, temporary I/O, CPU time, and elapsed time per operation. For parallel queries, it shows the workload distribution across parallel server processes. When SQL Monitor Activates Automatically SQL Monitor begins monitoring a SQL statement automatically when either: ...

If you are running workloads on Oracle Exadata, you are likely sitting on a goldmine of performance capability. The most significant differentiator between Exadata and traditional storage architectures is a feature called Smart Scan (also known as Cell Offload Processing). Instead of treating storage as dumb disks that just serve blocks to the database, Exadata pushes SQL processing down to the storage tier. This article explains how Smart Scan works, how to verify it, and how to control its behaviour for testing. ...