Chapter 4. Optimizing SQL Code

In this chapter, we will cover the following topics:

• Using bind variables

• Avoiding full table scans

• Exploring index lookup

• Exploring index skip-scan and index range-scan

• Introducing arrays and bulk operations

• Optimizing joins

• Using subqueries

• Tracing SQL activity with SQL Trace and TKPROF

Introduction

In this chapter, we will see how to diagnose and solve typical performance problems caused by poorly written SQL code. We will inspect both queries and Data Manipulation Language (DML), starting with the correct use of bind variables in the first recipe.

This chapter will illustrate various aspects related to SQL code, providing solutions to the most common issues. We will see how to avoid full table scans, when possible,using indexes. For this, it is necessary to know the differences between index full scan, index skip-scan, and index range-scan operations.

We will also discuss arrays and bulk operations, revealing some tricks to increase performance in DML operations. Joins and subqueries will be discussed in the later part of the chapter.

The last recipe illustrates the use of SQL Trace and TKPROF, tools that help diagnose and correct problems. After reading this chapter, if you experience a problem in the SQL code of your database, you know how to start solving it using these...

Using bind variables

We have discussed bind variables in the A working example recipe in Chapter 1, Starting with Performance Tuning.

In this recipe, it is time to dig deeper into this topic, illustrating the benefits of using bind variables and testing the result of our efforts with simple examples. We will see examples on query statements, but the same methodologies and results apply to DML statements.

Getting ready

Follow these steps to prepare the database:

1. Create a package named Chapter4 to test various aspects related to bind variables.

2. Connect to SQL*Plus using the SH schema:

   CONNECT sh@TESTDB/sh
   

3. Create the required package:

   CREATE OR REPLACE PACKAGE sh.CHAPTER4 AS
     PROCEDURE WORKLOAD_NOBIND;
     PROCEDURE WORKLOAD_BIND;
     PROCEDURE WORKLOAD_BIND_STATIC;
     PROCEDURE TEST_INJECTION(NAME IN
       sh.customers.cust_last_name%TYPE);
     PROCEDURE TEST_INJECTION2(NAME IN
       sh.customers.cust_last_name%TYPE);
   END;
   /
   
   CREATE OR REPLACE PACKAGE BODY sh.CHAPTER4 AS
     PROCEDURE TEST_NOBIND(CUSTID IN...

Avoiding full table scans

In this recipe, we will see what a full table scan is, how to avoid it, and when to choose a full table scan over other methods.

How to do it...

Let's start by creating two tables from the data in the SALES table of the SH schema:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create the MY_SALES_ALL table:

   CREATE TABLE sh.MY_SALES_ALL AS
     SELECT ROWNUM AS ID, X.* FROM sh.SALES X;
   

3. Create the MY_SALES_2 table:

   CREATE TABLE sh.MY_SALES_2 AS
     SELECT * FROM sh.MY_SALES_ALL NOLOGGING;
   

4. Compute statistics on the tables we just created:

   EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_SALES_ALL',
     estimate_percent => 100,
     method_opt => 'for all columns size 1');
   EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_SALES_2',
     estimate_percent => 100,
     method_opt => 'for all columns size 1');
   

5. Verify the database blocks used by the two tables:

   SELECT BLOCKS FROM DBA_TABLES
     WHERE TABLE_NAME IN ('MY_SALES_ALL', 'MY_SALES_2');
   

6. Delete some rows from MY_SALES_2, resulting...

Exploring index lookup

In the previous recipe, we have seen some issues related to FTS operations and when it's better to avoid them.

One of the methods to avoid FTS is indexing. In this recipe, several issues related to index lookup and index scan will be presented along with an illustration of the counterpart for indexes of FTS operation—Index Full Scan.

Getting ready

The examples given are based on a copy of the CUSTOMERS table in the SH schema; we will use SQL*Plus to execute our tests.

How to do it...

The following steps will demonstrate index lookup:

1. Connect to SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create the MY_CUSTOMERS table as a copy of CUSTOMERS:

   CREATE TABLE sh.MY_CUSTOMERS AS
     SELECT * FROM sh.CUSTOMERS NOLOGGING;
   

3. Update the CUST_VALID field to obtain a skewed distribution of values:

   UPDATE sh.MY_CUSTOMERS SET
     CUST_VALID = 'I'
     WHERE CUST_VALID = 'A' AND MOD(CUST_ID,100) <> 0;
   SELECT CUST_VALID, COUNT(*)
     FROM sh.MY_CUSTOMERS
     GROUP BY CUST_VALID;
   

4. Create an index on the MY_CUSTOMERS...

Exploring index skip-scan and index range-scan

In this recipe, we will see how to use composite indexes and also the difference between index skip-scan and index range-scan operations.

Getting ready

For this recipe, we will use a copy of the CUSTOMERS table in the SH schema and SQL*Plus to execute our tests.

How to do it...

The following steps will demonstrate index skip-scan and index range-scan:

1. Connect to SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create MY_CUSTOMERS table as a copy of CUSTOMERS:

   CREATE TABLE sh.MY_CUSTOMERS AS
     SELECT * FROM sh.CUSTOMERS NOLOGGING;
   

3. Create an index on the MY_CUSTOMERS table based on multiple fields:

   CREATE INDEX sh.CUSTOMERS_IXMULTI ON sh.MY_CUSTOMERS
     (CUST_GENDER, CUST_YEAR_OF_BIRTH, CUST_FIRST_NAME);
   

4. Compute statistics on the table:

   EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_CUSTOMERS',
     estimate_percent => 100,
     method_opt => 'for all columns size 1');
   

5. Execute a query on the table, using the first two fields of the CUSTOMERS_IXMULTI index in the predicate...

Introducing arrays and bulk operations

In this recipe, we will see different ways to insert data in our tables and we will make some considerations about the INSERT statement's performance.

We will see how arrays can be used to speed up insert and select statements, and why it may be better to use a single statement to achieve certain goals than using a procedural approach.

How to do it...

The following steps will demonstrate the use of arrays to insert data into the tables:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create an empty table called MY_SALES with the same structure as the SALES table:

   CREATE TABLE sh.MY_SALES AS
     SELECT cust_id, prod_id FROM sh.sales WHERE 1=0;
   

3. Enable timing:

   SET TIMING ON
   

4. Create a PL/SQL block to insert the sales of the second half of year 2001 from the SALES table to the new table using a cursor to scroll the SALES table:

   DECLARE
     CURSOR curs_c1 IS
       SELECT cust_id, prod_id FROM sh.sales
       WHERE time_id between TO_DATE('20010701', 'YYYYMMDD')
         AND...

Optimizing joins

One of the most time-consuming operations in a database is the JOIN. We use this when we need to join two or more tables due to the normalized structure of the database. There are many types of joins (equi-join, self-join, outer join, anti-join, and so on).

In this recipe, we will see some join algorithms the database can use to answer our queries, performance related to every type of join, and some tricks to avoid joins (when possible).

How to do it...

The following steps will demonstrate some common types of joins:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create a table called MY_CUSTOMERS as a copy of the CUSTOMERS table:

   CREATE TABLE sh.MY_CUSTOMERS AS SELECT * FROM sh.CUSTOMERS;
   ALTER TABLE sh.MY_CUSTOMERS
     ADD CONSTRAINT PK_MY_CUSTOMERS PRIMARY KEY (CUST_ID);
   

3. Create a table called MY_COUNTRIES as a copy of the COUNTRIES table:

   CREATE TABLE sh.MY_COUNTRIES AS SELECT * FROM sh.COUNTRIES;
   ALTER TABLE sh.MY_COUNTRIES
     ADD CONSTRAINT PK_MY_COUNTRIES PRIMARY KEY (COUNTRY_ID...

Using subqueries

We often use subqueries in our SQL statements to nest more queries in one statement, using the results from an "inner" query to calculate other values.

In this recipe, we will see the use of subqueries for getting only a subset of records, demonstrating the constructs (NOT) EXISTS and (NOT) IN, highlighting the semantic difference between them (and when to choose one type of statement or the other).

How to do it...

The following steps will demonstrate the use of subqueries:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   SET AUTOT TRACE EXP STAT
   

2. Select a table using the IN operator:

   SET AUTOT TRACE EXP STAT
   SELECT AMOUNT_SOLD FROM sh.SALES S
   WHERE S.CUST_ID IN (
     SELECT C.CUST_ID FROM sh.CUSTOMERS C
     WHERE C.CUST_CREDIT_LIMIT IN (10000, 11000, 15000)
   );
   

3. Rewrite the same query using the EXISTS construct:

   SELECT AMOUNT_SOLD FROM sh.SALES S
   WHERE EXISTS (
     SELECT NULL FROM sh.CUSTOMERS C
     WHERE S.CUST_ID = C.CUST_ID
     AND C.CUST_CREDIT_LIMIT IN (10000, 11000, 15000)
   );
   

4. Select...

Tracing SQL activity with SQL Trace and TKPROF

In this recipe, we will see how to use SQL Trace and TKPROF to trace SQL statements in a session.

There could be situations when we have to diagnose and tune a database, on which an application is running for which we don't have the source code, so we don't know which SQL statements are executed. In these situations, or when we want to investigate deeper than the AUTOTRACE feature we have used until now, the use of these tools is invaluable.

Getting ready

To trace SQL in our session, we have to make some modifications to the database parameters (if not set according to our needs).

The first parameter to set is TIMED_STATISTICS=TRUE, it can be set at the system or session level, to allow the database to trace the timing of the operations. It adds a very little overhead to the operations, so it can be left in place forever.

ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

We have to set the destination for our trace files also. When using dedicated servers...