SQL Tuning Tools   «Prev  Next»

Lesson 5 Using SQL inside Stored Procedures
Objective Describe the reasons for placing SQL inside stored procedures.

Using SQL inside Stored Procedures

Here's a breakdown of the primary reasons for placing SQL inside stored procedures in an Oracle 19c environment, targeted towards your role as an Oracle DBA:
  1. Performance and Efficiency:
    • Execution Plan Caching and Reuse: Stored procedures are compiled and stored in the database with an associated execution plan. When repeatedly executed, the optimizer doesn't have to re-generate the plan each time, leading to potential performance gains.
    • Network Traffic Reduction: If an application frequently executes a complex SQL block, calling a stored procedure encapsulating that logic reduces repetitive network round trips between the application and database server.
    • Modular Code: Stored procedures can act as self-contained execution units, potentially streamlining complex queries and updates.
  2. Maintainability and Code Organization:
    • Simplified Updates: If a piece of SQL logic is used in multiple applications or locations, changing the procedure updates it everywhere. This reduces the risk of inconsistencies due to ad hoc SQL scattered across applications.
    • Centralized Logic: Complex business rules or calculations can be encapsulated within stored procedures, keeping core logic within the database layer.
    • Abstraction: Stored procedures hide some of the underlying SQL complexity from applications, making them potentially easier to maintain.
  3. Security and Controlled Access:
    • Reduced Attack Surface: Limiting direct table access and instead granting execute permissions on stored procedures can improve security posture.
    • Privileges: You can fine-tune the permissions granted to users/applications by designing procedures that control the specific operations they can perform.
    • Auditing: It can be easier to audit actions taken at the stored procedure level rather than tracking scattered SQL statements.
  4. Development and Standardization:
    • Reusability: Well-defined stored procedures act as reusable code blocks across various applications or development teams, promoting consistency.
    • Enforcing Standards: Stored procedures can help maintain database and coding standards, ensuring best practices are followed.
    • Abstraction for Developers: Procedures can simplify SQL access for developers less familiar with the intricacies of Oracle.

Considerations and Additional Benefits
  • Debugging: PL/SQL procedures offer debugging capabilities within the database environment.
  • Version Control: Procedures, as database objects, can be managed within version control systems.
  • Transaction Control: Procedures can manage their own transactions, ensuring data integrity.

Important Notes from a DBA Perspective:
  • Stored procedures are not a silver bullet. Overuse can sometimes make management more complex.
  • Consider the development and maintenance overhead of stored procedures before employing them widely.
  • A well-structured mix of stored procedures and appropriate inline SQL often provides the best balance.


Determining where the SQL source is located has dramatic impact on three areas:
  1. security,
  2. performance analysis, and
  3. tuning.
Because of benefits realized in all these areas, SQL should be placed inside stored procedures.

Benefits of using SQL inside of Stored Procedures

  1. Security: The DBA has complete control over all SQL in the database. Rather than making Oracle roles to govern database access, the DBA only needs to issue the GRANT EXECUTE statement to the stored procedures that are required. At runtime, Oracle will give the proper table grants to the person who is authorized to execute the stored procedure.
  2. Performance Analysis: Without a stored procedure, the DBA will only be able to analyze SQL that happens to reside in the library cache at any point in time.
  3. Tuning: SQL source can be pinned into the SGA. If the stored procedures are placed into packages, the dbms_shared_pool.keep procedure can be used to pin the package text interlibrary cache memory. This prevents frequently used SQL statements from being flushed from the shared pool.
    All SQL is guaranteed to be portable. Since all procedural programs will not contain SQL (replaced with stored procedure calls), the application code becomes totally independent from the database and becomes very portable. All SQL is totally reusable. Because the same SQL statement is executed by all end users, we are guaranteed that the SQL will not need to be repaired, and that proper host variables are used.

The only way to enforce the use of stored procedures is to remove ALL table GRANTs in your database and replace them with GRANTs to the stored procedures. This will prevent all unwanted SQL since any unauthorized SQL will not have table access privileges. The only downside to this approach is the extra work for the DBA, placing the SQL into stored procedures and packages, and then granting execute privileges to the end users. Now, let us conclude this module with a review of the important concepts.

SEMrush Software Target 5SEMrush Software Banner 5