Thread: Internal process of an execute query

What are the internal process ORACLE (in internal server side) do when we execute any query lets take example "SELECT * FROM EMP" ?

Thanks in Advance.
Question asked by visitor Nihar Ranjan Behera

You can set the environment variable auto trace to ON to get the internal execution process. You must have plan table in your schema to do so. To create teh plan table run the script UTLXPLAN.SQL

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.