Oracle SQL Tuning Fundamentals

Introduction & Objectives

Structured Query Language (SQL) is basically a language for storing, manipulating and retrieving data in databases. Although there are a lot of “flavors” of SQL (MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres). The American National Standards Institute (ANSI) has set a standard in 1986 that most RDBMS’s employ.
While most simple SQL Queries are straight-forward and quick to execute, there are instances wherein we experience a slowness in the query execution. In this deck, we will explore, identify and address the numerous factors that come into play in this performance degradation.

Target Audience

The target audience of this deck would be individuals with sufficient experience in writing SQL queries and have sufficient knowledge in Database Fundamentals.
You should be able to:

• Understand why SQL Tuning is Important
• Understand when to Tune a SQL Script
• Understand the needed skills and knowledge to Tune SQL
• Understand how to achieve your tuning goals

Why should we tune SQL?

Two Words: Optimum Performance!

• Query Performance can be affected by the following:
• Hardware
• Network
• Application
• Program (i.e. PL/SQL Code)
• SQL Code

When the underlying SQL is Tuned effectively, The PL/SQL program is tuned as well! I know what you’re thinking:

“Isn’t there an automatic way to Tune SQL?”

• Yes, but it is costly and proprietary.
• Yes, but sometimes its not good enough.
• Yes, but sometimes it’s even worse than the manual method.

“If that’s the case, then what’s the solution?”

• Manual SQL Tuning Addresses the concerns above. Why? Because:
  1. It’s Free
  2. Improves coding skill
  3. Provides more control over your code

When do we need to Tune SQL?

Ideally, the time we develop code is the time we need to consider efficient and fast queries.

• A good, well-thought out and defined structure will yield the best results when it comes to performance.
• Planning the table structure, indexes, data types and unique identifiers and the data is highly valuable when it comes to performance.

However, if it is unavoidable for the objects have already been existing for some time, tuning is still possible. Consider tuning when the following occurs:

1. There is a Change of Hardware
   • If there is a hardware update in terms of processor, memory, storage, it might affect the performance of the SQL Query.
   • Usually occurs during an upgrade or downgrade of the actual server.
2. There is a Change of Table Structure (i.e. New or Modified Columns, Indexes, etc.)
   
   • If there are changes in the table that is referenced by the query, this may impact the retrieval of data.
   • If indexes are changed, there will be a significant performance impact to the query.
3. Significant change in the amount of Data
   • Data count significantly affects the query’s performance, affecting cardinality, selectivity and cost.
4. Change of Query Script
   • If a Report’s query has been changed, there is a high chance that the performance of the report will also change

What you need to know before you tune a SQL?

Before doing the actual work of tuning a script, one needs to know first the environment. This is highly important as this will lead you to the use best approach for the current problem at hand and ensuring that it doesn’t happen in the future.

Below are the things you need to know before turning the script.

1. Knowledge and Skill in SQL and Database Fundamentals
   • A good grasp of SQL skill and knowledge will be the first and foremost thing you need to know to tune SQL.
   • What SQL operators you need to use and what clauses you need to add will greatly reduced the work you need to put into tuning a script.
2. Familiarity with the Table Structure
   • Knowing what the table is used for, its structure, and its data is essential for effective and efficient tuning.
   • Correct Identification of each column’s use allows us to formulate a better plan to tune the SQL.
3. Familiarity with Index Types and the Explain Plan
   • Knowing the different index types, their usages and their advantage and disadvantages is highly recommended for you to identify if the index you want to use is actually being used by the explain plan.

How to Tune SQL?

Below are my personal guidelines on how to tune SQL scripts:

1. Identify High-Load SQLs
   1. Unless specified, you would need to figure out the under performing queries.
   2. There are numerous methods to know the Top High Load SQL statements in a Database

      1. Using the Oracle Enterprise Manager (OEM)

      2. Using the Dynamic Performance Views such as v$SQL and v$SQL_PLAN

2. Measure current performance metric
   1. For us to compare if the changes that will be applied to the script has any real improvement, it is imperative that we take a snapshot of how the performance was before it was modified.
   2. We would then need to take either of the following before Modification of the SQL Script:
      1. AWR
      2. SQLHC
      3. Explain Plan
3. Identify the root cause
4. Analyze and Identify the Best approach
   1. Re-write the script?
   2. Correct the Data?
   3. Add Indexes?
   4. Change the Table Structure?
   5. Split the table?
   6. Convert to Materialized Views?
   7. Use Hints?
   8. Change the Database Parameters?
5. Test, Test and Test
6. Measure the new performance metric
   1. Similar to Step #2, we would then need to take a snapshot of the performance after the script modification. The same tools apply to getting the metrics.

Cardinality, Selectivity and Cost

Oracle uses the Cost Estimator, to estimate the resources that will be used to execute a given query using the following measurements:

1. Selectivity
   1. The percentage of rows in the row set that the query selects, with 0 meaning no rows and 1 meaning all rows. Selectivity is tied to a query predicate, such as WHERE last_name LIKE 'A%', or a combination of predicates. Simplest term: Uniqueness
   2. A record becomes more selective as the selectivity value approaches 0 and less selective (or more unselective) as the value approaches 1.
2. Cardinality
   1. The cardinality is the estimated number of rows returned by each operation in an execution plan. Cardinality can be derived from the table statistics collected by DBMS_STATS, or derived after accounting for effects from predicates (filter, join, and so on), DISTINCT or GROUP BY operations, and so on.
3. Cost
   1. This measure represents units of work or resource used. The query optimizer uses disk I/O, CPU usage, and memory usage as units of work.

These three are very important for the estimator in figuring out how to map the Explain Plan, what access paths to be used and what Indexes should be used to execute the query.

Indexes

What are Indexes?

By definition: An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows.
Oracle Database supports the following indexes:

1. Binary Tree (B-Tree Index)
   • These indexes are the standard index type and uses only 1 column
   • They are excellent for primary key and highly-selective indexes.
   • B-tree indexes have the following subtypes
     1. Composite Indexes
     2. Index-organized tables (IoT)
     3. Reverse key indexes
     4. Descending indexes
     5. B-tree cluster indexes
2. Bitmap Index - In a bitmap index, an index entry uses a bitmap to point to multiple columns. In contrast, a B-tree index entry points to a single row. A bitmap join index is a bitmap index for the join of two or more tables.
3. Partitioned Indexes
4. Function-based Indexes
5. Application Domain Index - An Index that is application-specific, can be used inside or outside the Oracle Database.

Binary Tree (B-Tree Index)

Bitmap Index

Partitioned Indexes

Function-based Indexes

Application Domain Index

The Explain Plan

Access Paths

Types of Access Paths

Access Paths (aka Execution Paths), is basically the "road" that the parser plans to traverse to execute the query script, step-by-step.
An Access Path is shown inside an Explain plan. Treat Explain Plan as the Map, and the Access Path as the Road.
Depending on your various factors (i.e. Tables, Complexity, Database Parameters), the Explain plan will show you the recommended approach the parser will use to execute your query.

There are multiple types of Access Paths, as listed below:

• Full Table Scans
• Table Access by RowId
• Sample Table Scans
• By Index
  • Index Unique Scans
  • Index Range Scans
  • Index Full Scans
  • Index Fast Full Scans
  • Index Skip Scans
  • Index Join Scans
• By Bitmap
• Bitmap Index Single Value
• Bitmap Index Range Scans
• Bitmap Merge
• Bitmap Index Range Scans
• Cluster Scans
• Hash Scans

References

• Manuel, J., Oracle SQL Tuning Training (Oracle University – TechEdge Solutions)
• http://stackoverflow.com/questions/10621077/what-is-cardinality-in-databases
• http://docs.oracle.com/cd/A87860_01/doc/inter.817/a77063/aoptim2.htm
• https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm
• https://docs.oracle.com/cloud/latest/db112/CNCPT/indexiot.htm#CNCPT1182
• https://use-the-index-luke.com/
• https://www.w3schools.com/sql/
• https://www.iso.org/standard/63555.html
• https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm#:~:text=Oracle%20Database%20supports%20several%20types,key%20value%20as%20a%20bitmap