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...