Chapter 8. Tuning the PL/SQL Code

Database tuning refers to an exercise that aims to improve the performance of a database. The art of optimizing the database performance largely depends on how well one understands the database architecture, application design and server environment. The areas that can be potentially tuned are the application design, network, SQL queries, and PL/SQL code. This exercise starts with the detection and identification of a problem, followed by analysis and tuning recommendations. In this chapter, we will see tuning practices related to the Oracle PL/SQL code.

Programs written in Oracle PL/SQL can be tuned to optimize data-centric and CPU-intensive operations. In a PL/SQL program unit, statements like loops, dynamic SQL statements, routine calls, and the compilation mode can be tuned for better performance. This chapter will discuss the different code compilation techniques and their benefits. The chapter outline is as follows:

• The PL/SQL Compiler

  • Subprogram inline...