Basics of Privileges in Oracle 12c
Oracle PL/SQL Code Samples
How to Control Access to Your MySQL Data

How to Tune SQL in Oracle 12c

The biggest bang you are going to get in terms of performance tuning for your Oracle 12c databases is by having good Structured Query Language (SQL). Badly written SQL is predominantly the cause for most performance problems in any database.

Writing good SQL from the get go when a new application is being developed can save you buckets of money and time later on. Unfortunately, as a Database Administrator, most of the situations you find yourself in are on pre-existing systems where the SQL is already in place.

How to generate an explain plan in Oracle 12c

Say you have narrowed a performance problem down to a specific SQL statement in the database. Through questioning the user and testing the problem yourself, you’ve been able to replicate the issue with a specific SQL statement.

One of the tools provided with the database that you first want to become familiar with is Explain Plan. Explain Plan does just what it says. It shows you the execution of the SQL statement and explains what each step of the plan is doing.

The Explain Plan is generated by using the SQL command EXPLAIN PLAN FOR. As a simple example, say you have the following SQL statement, which is taking a long time to execute:

SELECT first_name, last_name
FROM emp
WHERE last_name = 'Hopkins';

Here is how you generate an Explain Plan for the preceding statement:

SELECT first_name, last_name
FROM emp
WHERE last_name = 'Hopkins';

You see this output after generating the Explain Plan:


After the SQL statement is explained, by default the plan is stored in the data dictionary table called the PLAN_TABLE$. There is a public synonym, PLAN_TABLE, that allows all users to have access to this internal table.

How to display and read the SQL statement output in Oracle 12c

You can pull out the information in the PLAN_TABLE in a number of ways. There is a lot of information to be displayed. Displaying all of it is not always useful. One simple method is to run an Oracle-supplied script to read and format the information.

This method displays only the most recent Explain Plan. The script is stored in the $ORACLE_HOME/rdbms/admin directory. The script is named utlxpls. From SQL in the same session where you ran the EXPLAIN, type this:

SQL> @?\rdbms\admin\utlxpls

A shortcut to specifying ORACLE_HOME is the “?” as shown in the preceding statement.

The explained output of your SQL statement looks similar to this:

Plan hash value: 3956160932
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)| Time   |
|  0 | SELECT STATEMENT |   |   1 |  15 | 40335  (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| EMP |   1 |  15 | 40335  (2)| 00:00:02 |
Predicate Information (identified by operation id):
  1 - filter("LAST_NAME"='Hopkins')

Although it takes some experience to read the fine details in the EXPLAIN PLAN output, a few things jump out:

  • The use of a full table scan (TABLE ACCESS FULL EMP)

  • The Predicate Information (1 – filter("LAST_NAME"='Hopkins'). This is your where clause.

  • The number 1 matching the Predicate Information back to the operation. The number indexes this back to the step in the section above. In this example, it seems simple, but, if you have an Explain Plan with dozens of lines, this can be very helpful.

Another bit of information you may note is the value for COST. In the preceding case, the COST is 40335. Although the cost in and of itself doesn’t necessarily mean anything at face value, you can use it to compare the changes that you make to the execution plan. Typically, the lower the cost is, the better your plan is.

Statements with many different operations have costs associated with each step. By looking at the cost of each step, you can determine what stage of the execution is the most expensive (resource-wise, not money-wise). Then you can focus your tuning on that stage. Most of the time, as you make changes and lower the cost, you’re making moves in the right direction.

Here’s a more complex example of an execution plan:

explain plan for
SELECT first_name, last_name, department_name
FROM emp join departments using (department_id)
WHERE last_name = 'Hopkins';
Elapsed: 00:00:00.09
SQL> @?\rdbms\admin\utlxpls
Plan hash value: 3338584009
| Id | Operation          | Name    | Rows | Bytes | Cost (%CPU)| Time |
|  0 | SELECT STATEMENT       |       |   1 |  34 | 40336  (2)| 00:00:02 |
|  1 | NESTED LOOPS        |       |    |    |      |   |
|  2 |  NESTED LOOPS        |       |   1 |  34 | 40336  (2)| 00:00:02 |
|* 3 |  TABLE ACCESS FULL     | EMP     |   1 |  18 | 40335  (2)| 00:00:02 |
|* 4 |  INDEX UNIQUE SCAN     | DEPT_ID_PK |   1 |    |   0  (0)| 00:00:01 |
|  5 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |   1 |  16 |   1  (0)| 00:00:01 |
Predicate Information (identified by operation id):
  3 - filter("EMP"."LAST_NAME"='Hopkins')

In this example, you see five operations. By looking at the height operation (Step 5) and working back, you can see that the cost looks like this:

Step 5 = 1
Step 4 = 0
Step 3 = 40335
Step 2 = 40336
Step 1 = 40336
Total = 40336

Notice how the cost of all the steps adds up. Also notice how the cost of Step 3 is by far the most expensive. With that said, a database administrator (DBA) would want to focus his tuning efforts on Step 3.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Restore Your MySQL Data
Perform a Complete Recovery of an Oracle 12c Database without the Database Recovery Advisor
Ten Hot Big Data Trends
Environment Requirements to Start and Stop Oracle 12c Databases
Basic Threats to Your Oracle 12c Database