Before you start randomly adjusting knobs, you must identify the bottleneck . Throwing hardware at a problem or tweaking parameters without data is a recipe for wasted time and effort. The goal is to find the part of the system that is waiting the most, and then determine why it’s waiting.

Think of these as the vital signs for your database. Regularly checking them will give you a high-level picture of its health.

This is arguably the most important concept in Oracle tuning. The database is constantly processing work, and when a session can’t proceed, it’s waiting for a resource. These are “wait events.” Key Wait Events to Investigate: - : Typically indicates single-block reads, common in Index lookups. High waits can mean slow I/O or insufficient indexing. : Typically indicates single-block reads, common in Index lookups. High waits can mean slow I/O or insufficient indexing. - : Typically indicates multi-block reads, common in Full Table Scans (FTS). High waits might suggest missing indexes or poor SQL. : Typically indicates multi-block reads, common in Full Table Scans (FTS). High waits might suggest missing indexes or poor SQL. - : Waits for a commit to complete. This is about write speed to the online redo logs. Slow storage here can kill transaction-heavy systems. : Waits for a commit to complete. This is about write speed to the online redo logs. Slow storage here can kill transaction-heavy systems. - : Straightforward—one session is waiting for another to release a row lock. This is application-level locking. : Straightforward—one session is waiting for another to release a row lock. This is application-level locking. - : Multiple sessions are trying to access the same block in the buffer cache simultaneously. : Multiple sessions are trying to access the same block in the buffer cache simultaneously. Action: High waits in events? Look at your storage subsystem and SQL. High ? Check your redo log location and disk speed.

While the Buffer Cache Hit Ratio (BCHR) has its critics, it’s a good starting point. - Formula: Formula: - Interpretation: A ratio above 90% is generally good. A low ratio indicates that the database is frequently going to disk to fetch data, which is much slower than reading from memory. This could mean your Buffer Cache is too small, or you have inefficient SQL causing full scans. Interpretation: A ratio above 90% is generally good. A low ratio indicates that the database is frequently going to disk to fetch data, which is much slower than reading from memory. This could mean your Buffer Cache is too small, or you have inefficient SQL causing full scans. Action: A low BCHR warrants investigation into both memory allocation and the SQL being executed.

Don’t forget the underlying server! The database is ultimately constrained by its host. - CPU Utilization: Consistently high CPU (e.g., >80%) can point to parsing issues (more on that later) or computationally intensive SQL. CPU Utilization: Consistently high CPU (e.g., >80%) can point to parsing issues (more on that later) or computationally intensive SQL. - I/O Throughput & Latency: This is critical. What is the average read/write time for your storage? Latency over 10-20ms can be a major bottleneck for the wait events mentioned above. I/O Throughput & Latency: This is critical. What is the average read/write time for your storage? Latency over 10-20ms can be a major bottleneck for the wait events mentioned above. - Memory & Paging: Is the server swapping? Swapping (paging) is disastrous for database performance. Memory & Paging: Is the server swapping? Swapping (paging) is disastrous for database performance.

The single biggest cause of performance problems is inefficient SQL. One bad query can bring a system to its knees. Key Columns in : - : Total time taken. : Total time taken. - : How much CPU it consumed. : How much CPU it consumed. - : How many times it ran. : How many times it ran. - / (Logical Reads) : High logical reads are a primary indicator of inefficient SQL. It shows how many blocks the query had to process. / (Logical Reads) : High logical reads are a primary indicator of inefficient SQL. It shows how many blocks the query had to process. - : The payoff for all that work. : The payoff for all that work. Action: Find the top SQL statements by per execution or total . Tuning these statements gives you the biggest bang for your buck.

Excessive parsing is a common performance killer. There are two types: - Hard Parse: Oracle has to check syntax, semantics, security, generate an execution plan, and load it into the library cache. This is very CPU-intensive. Hard Parse: Oracle has to check syntax, semantics, security, generate an execution plan, and load it into the library cache. This is very CPU-intensive. - Soft Parse: Oracle finds a matching SQL statement and execution plan in the library cache and reuses it. This is efficient. Soft Parse: Oracle finds a matching SQL statement and execution plan in the library cache and reuses it. This is efficient. Key Metrics: - - Action: Aim for a high percentage of soft parses. A high hard parse rate often points to applications that don’t use bind variables, instead sending unique SQL strings for every execution (e.g., vs. ).

Now that you know what to look for, here are the tools that will help you find it.

What it is: Oracle’s built-in performance warehouse. It takes a snapshot of the database’s performance data (wait events, SQL stats, etc.) every hour by default. Why it’s great: It provides a historical record. You can compare a “bad” period to a “good” period to see what changed. The script (in ) generates a comprehensive HTML or text report.

What it is: AWR takes an hourly picture, but ASH is like a high-speed video camera. It samples active session data every second, giving you a incredibly detailed, near-real-time view of what sessions were doing. Why it’s great: Perfect for diagnosing short-lived performance spikes (minutes instead of hours). The script is your go-to for this.

What it is: An expert system built into the database that analyzes AWR snapshots for you. Why it’s great: Instead of just giving you data, ADDM provides findings and recommendations . It will say things like, “33% of database time was spent on SQL statement with ID due to full table scans,” and recommend creating an index. It’s your first stop after identifying a problematic time period with AWR.

What it is: Give it a poorly performing SQL statement, and it will run it through a series of tests and provide recommendations. Why it’s great: It can suggest SQL profiles, new indexes, or even rewrite the SQL for you. It automates a lot of the manual tuning process.

What it is: The graphical, web-based console for managing your Oracle ecosystem. Why it’s great: It provides a fantastic visual interface for all the tools above (AWR, ASH, ADDM). You can click through performance data, see top SQL in real-time, and view ADDM findings without running a single script. It’s the best place to start for DBAs of all experience levels.

- EXPLAIN PLAN: The fundamental tool for understanding how Oracle intends to execute a SQL statement. It shows the execution plan (e.g., TABLE ACCESS FULL, INDEX RANGE SCAN, HASH JOIN). EXPLAIN PLAN: The fundamental tool for understanding how Oracle intends to execute a SQL statement. It shows the execution plan (e.g., TABLE ACCESS FULL, INDEX RANGE SCAN, HASH JOIN). - SQL Monitoring ( ) : For long-running or resource-intensive SQL, this provides real-time monitoring of its actual execution, including rows processed and time spent in each operation. It’s far more dynamic than a static . SQL Monitoring ( ) : For long-running or resource-intensive SQL, this provides real-time monitoring of its actual execution, including rows processed and time spent in each operation. It’s far more dynamic than a static .

- Complaint: “The system is slow between 10 AM and 11 AM.” Complaint: “The system is slow between 10 AM and 11 AM.” - Diagnose: Run an AWR report comparing 10-11 AM to a good period (e.g., 8-9 AM). Diagnose: Run an AWR report comparing 10-11 AM to a good period (e.g., 8-9 AM). - Analyze: Check the “Top 5 Timed Foreground Events” section. See high ? Look at the “SQL ordered by Elapsed Time” section. Analyze: Check the “Top 5 Timed Foreground Events” section. See high ? Look at the “SQL ordered by Elapsed Time” section. - Drill Down: Identify the top SQL. Run it through the SQL Tuning Advisor or get its execution plan. Drill Down: Identify the top SQL. Run it through the SQL Tuning Advisor or get its execution plan. - Fix: Implement the recommendation (e.g., create a missing index, fix the SQL code to use a bind variable). Fix: Implement the recommendation (e.g., create a missing index, fix the SQL code to use a bind variable). - Verify: Monitor the system to confirm the issue is resolved. Verify: Monitor the system to confirm the issue is resolved.