Oracle performance tuning: A 5-step approach to optimized performance

Oracle Performance Tuning: A 5-Step Approach to Optimized Performance

As part of Quest’s Database Training Days webcast series, Oracle ACE Janis Griffin presented her top 5 Oracle performance tuning tips. Janis’ webcasts are always packed with valuable information, and this one didn’t disappoint as she imparted her expertise in SQL query tuning on the Oracle platform.

Before starting any SQL performance tuning in Oracle, it’s important to understand the business purpose for the query. If it’s unclear why – or even if – the query results are being used, then maybe that query doesn’t need to run at all and you can immediately see some performance gains by disabling it.

Janis recommends a 5-step methodology for Oracle performance tuning – and these steps build upon one another, so it’s important to start at the beginning.

Mục Lục

Oracle tuning tips

Tip 1 – Monitor Wait Times

Oracle provides wait events that allow you to understand the total time a query spends in the database. Start your tuning efforts with the queries taking the most time. You can use V$ Views to see real-time performance and session information. Although this information does not persist, Janis demonstrated a query that polls the V$ Views and creates a table of the data on sessions, wait events, blocking session IDs, etc. If you automate this query and poll every second, you can begin to see trends over time and can focus on the queries that take the longest time. If you are an Oracle Enterprise Edition user and own the Tuning and Diagnostic Packs, you can access the view called V$_Active_Session _History, which holds an hour of session data.

Janis recommends recording baseline metrics – after all, you need a basis of comparison for performance improvement. She tends to look at buffer gets to determine whether she’s making progress in tuning. Tuning the query that requires the most buffer gets will often have a positive trickle-down effect on other queries.

Tip 2 – Review the Execution Plan

Oracle provides many ways to get execution plans. An explain plan is an estimate of what the query will do and can be wrong for many reasons. A cached plan will show you an actual plan that the Optimizer used. Knowing how to read these plans gives you insight into what the Optimizer is doing. Janis explained how the Optimizer works, which was a helpful overview. Because the Optimizer continues to evolve with Oracle versions, you should become familiar with how it treats execution plans with each new release.

Tip 3 – Gather Object Information

As you drill into the poorly-performing query, take the time to look at the expensive objects. Examine table definitions and segment sizes. Look at statistics gathering – are the statistics current, or if they’re not, do they hurt the choices the Optimizer is making?

Take the time to review index definitions, existing keys and constraints. Be sure you understand the order of columns and column selectivity in the index. Also make sure the Optimizer can use the index.

Tip 4 – Find the Driving Table

You always want to know the size of the actual data sets for each step in the execution plan because your goal is to drive the query with the table that returns the least data. That reduces the number of logical reads. In short, you study Joins and Filtering predicates to filter the data earlier in the query rather than later.

Here, Janis demonstrated a SQL diagramming technique that helps visualize the amount of data in the tables and which filter will return the least amount.

Tip 5 – Identify Performance Inhibitors

This is a useful list of unintentional performance inhibitors that should be removed from queries or avoided altogether:

Cursor or row-by-row processing
Parallel processing
Nested views that use db_links
Abuse of wildcards
Using functions on indexed columns
Hard-coded hints
Complex expressions
Joining complex views

And here are some actions you should take instead:

Use bind variables instead of literal variables
Use an index if less than 5% of data needs to be accessed
Use equi-joins whenever possible
Always use a WHERE clause

A methodical approach to Oracle performance tuning

Janis demonstrated this five-step Oracle performance tuning process using two case studies based on actual queries and data sets. This part of the presentation really tied together how it makes sense to take a methodical approach to Oracle SQL query tuning.

Finally, Janis pointed out how using a tool like Foglight for Cross-Platform Databases can simplify query tuning by narrowing down performance issues. When it feels overwhelming, performance monitoring tools make the process much more manageable.