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

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.