kodaliprakash

 Profile Answers by kodaliprakash 

• Jul 14th, 2007

 

Till the time of execution the sql staement is live.After the execution it has gone.

But, I dont think that any DB has mentioned about this.

fancyoracle

 Profile Answers by fancyoracle 

• Aug 15th, 2007

 

Once parsed, the SQL statement is available in the memory till the session is valid.

sreekumar_nair_it

 Profile Answers by sreekumar_nair_it Questions by sreekumar_nair_it

• Mar 27th, 2008

 

Dear All,

LIFE OF SQL STATEMENT

Step 1: Oracle Create a Cursor
For every SQL, first a cursor is created (cursor creation can occur implicitly, or explicitly by declaring a cursor).

Step 2: Oracle Parse SQL Statement

A SQL statement is parsed only if an identical SQL statement does not exist in the library cache (shared pool-System Global Area). In this case, a new shared SQL area is allocated and the statement is parsed (hard parse), otherwise existing stored information in library cache is used (soft parse).

Hard Parsing is the process of

• Translating a SQL statement and verify the syntax.
• Checking data dictionary to check table and column definitions
• Acquiring parse locks on required objects so that their definitions do not change during the statement’s parsing
• Checking privileges to access referenced schema objects
• Determining the optimal execution plan for the statement
• Loading it into a shared SQL area
• For distributed statements, routing all or part of the statement to remote nodes that contain referenced data

Stage 3: Describe Results (FOR SELECT STATEMENT ONLY)

The describe phase is used to determine the characteristics (datatypes, lengths, and names) of a query’s result.

Stage 4: Defining Output (FOR SELECT STATEMENT ONLY)

Specify the location, size, and datatype of variables defined to receive each fetched value. Oracle performs datatype conversion if necessary.

Stage 5: Bind Any Variables

At this point, Oracle needs values for any variables listed in the statement;

for example, In SQL statement “SELECT * FROM EMP WHERE EMPNO = p_empno”, Oracle needs a value for p_empno.

This process is called binding variables. A program must specify the location (memory address) where the value can be found.

Stage 6: Execute the Statement

At last, Statement is executed.

Oracle checks if the data it needs for the query are already in the buffer cache. If not, it reads the data off the disk into the buffer cache.
The record(s) that are changed are locked i.e. in case of DELETE or UPDATE statement. Also, before and after images describing the changes are written to the redo log buffer and the rollback segments. The original block receives a pointer to the rollback segment. Then, the data is changed.

For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.

Stage 7: Parallelize the Statement

When using the parallel query option, Oracle can parallelize queries and certain DDL operations. Parallelization causes multiple query servers to perform the work of the query so that the query can complete faster.

Stage 8: Fetch Rows of a Query Result (FOR SELECT STATEMENT ONLY)

Rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched