Sql-Tuning

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. ...

This article is a practical tuning script library. Each script targets a specific, common real-world performance scenario — not abstract examples, but the situations that actually appear at 2am during an incident. Scripts are annotated with where they run, what the output means, and what action to take. Reference Environment Single Instance: Host: ora19c01.prod.example.com DB Name: ORCL19C Oracle: 19.22.0.0 (Oracle Linux 8) Memory: 256 GB RAM, 32 vCPU Storage: Local NVMe (non-Exadata) CDB Environment: Host: cdb01.prod.example.com (Exadata VM) CDB Name: PRODCDB PDBs: PDB_OLTP, PDB_DWH, PDB_REPORTING Oracle: 19.22.0.0 Memory: 512 GB RAM (Exadata X8M-2 node) Scenario 1: “The Database Was Slow from 09:00 to 09:30 This Morning” Situation: Application team reports intermittent slowness on cdb01. You need to identify what happened between 09:00 and 09:30. ...

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. ...

SQL tuning is one of the most critical skills for an Oracle DBA. A poorly written query can bring an entire system to its knees, while a well-tuned one can run in milliseconds. This guide covers the most important techniques every Oracle DBA should know. 1. Understanding the Execution Plan The execution plan is the roadmap Oracle uses to execute your SQL. Always start here when tuning. Using EXPLAIN PLAN EXPLAIN PLAN FOR SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > 5000 ORDER BY e.last_name; -- Display the plan SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); Using DBMS_XPLAN with actual runtime statistics -- Step 1: Run the query with the hint SELECT /*+ gather_plan_statistics */ e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > 5000; -- Step 2: Pull the actual execution plan SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( sql_id => NULL, cursor_child_no => 0, format => 'ALLSTATS LAST' ) ); 2. Gathering Statistics Stale or missing statistics are the number one cause of bad execution plans in Oracle. ...