Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

How Does the Database Parse SQL and PL/SQL?
Oracle Tips by Burleson
 

Sit down in front of a computer and get to an SQL prompt. Get comfortable. Then execute the following commands at the SQL prompt:

set serveroutput on
SELECT 23 FROM DUAL;

Notice that as soon as you finished typing your command, SQL*Plus sent the command off to the database for a response. Now enter this block of PL/SQL code:

DECLARE
   iRowCount      integer;
 
BEGIN
   SELECT 23
   INTO   iRowCount
   FROM DUAL;
   SELECT 18
   INTO   iRowCount
   FROM DUAL;
 
   DBMS_Output.Put_Line ('The value of iRowCount is ' || to_char (iRowCount));
END;
/

You might notice that none of your SQL commands were executed until you finished your PL/SQL block and told SQL to execute the block with the / character.

You may sit in front of SQL every day and not have noticed this difference before, but you should notice it now. The Oracle7 database handles SQL and PL/SQL commands differently. Each SQL statement inside your PL/SQL block was sent to the database only when the PL/SQL block went to the database. Figure 2.12 illustrates this concept.

An SQL statement is immediately matched against existing statements in the SGA. If no matching SQL statement is found, the statement is reparsed and then executed; otherwise, a statement that is already cached in the SGA is executed instead. When the database has retrieved the necessary data or an error occurs, the database returns a response to the user.

A PL/SQL block is sent to the database as a whole concept. Because a PL/SQL block can contain both SQL statements and other PL/SQL blocks, the database will then handle each SQL statement and each PL/SQL block inside the main PL/SQL block. When the procedure has finished executing or an error condition occurs, a response is given to the user who executed the procedure.

This difference may seem to be insignificant, but the nature of PL/SQL makes it much better suited for client/server development than SQL. Each SQL statement travels over the network singly, while PL/SQL sends one block of code to the database, which then handles PL/SQL sub-blocks, embedded SQL statements, and calls to stored PL/SQL objects internally.

Consider 10 SQL statements sent over a network individually versus 1 PL/SQL block that contains 10 embedded SQL statements; 1 call to the network is necessary to send the PL/SQL block, while 10 calls are required for the individual SQL statements. Furthermore, the result sets for the embedded SQL statements are not sent back across the network, further reducing the number of packets required to complete the PL/SQL call.

Summary

Hopefully, this chapter has given you a basic grounding for the coming chapters, which contain some rather detailed examples of SQL and PL/SQL code. I have tried to avoid lapsing into a primer on syntax, but in some cases an adequate explanation demands the most elementary terms. By no means should you consider this chapter to be a complete tutorial or reference on SQL or PL/SQL; for that type of material the best sources are the Oracle7 Server SQL Language Reference Manual and the PL/SQL User’s Guide that you can borrow from your database administrator.

In the coming chapters, you’ll cover not only some detailed examples of SQL and PL/SQL code, but you’ll also be given insight into the design and testing of these procedures.
 

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter