Architecture

SELECT Statement Processing

- by OracleWorld 3.1K
Post Views: 3,112

Every SQL query you send to a server has to go through three  (four – in case of SELECT) stages. First of all, Oracle will search for a matching SQL hash in Library Cache. If the hash is found it will just retrieve the associated explain plan and executes it. If not, a hard parse is invoked -> that means the following steps are to be executed.

  1. PARSE
    • syntax check
    • semantics check
    • privileges check
  2. BIND
    • replace bind/host variables with actual values
  3. EXEC
    • actual execution based on an explain plan
  4. FETCH (SELECT statements only)
    • returns a result set to a user

Let me explain each phase in the following example.

You executed this query:

SELECT *
FROM employee;

The Server Process will generate a hash for the issues SQL query. The hash is a hexadecimal value generated based on the text of the SQL query. Here comes the very important part!! Even the slightest change (upper to lower case and vice versa; adding/removing a space; …) will change the hash and therefore it might generate a hard parse (more on the hard parse vs soft parse below). You have to be very careful and try to stick to one code only – that is why procedures work best (on the top of that – they mostly use bind variables that generate identical queries in Oracle’s eyes).

You can easily verify it on your own by executing the queries below:

-- first run this query
SELECT * FROM dual;

-- then run this one again with all uppercase letters
SELECT * FROM DUAL;


-- check the sql hashes 
SELECT sql_id
, sql_text
, hash_value
FROM v$sql
WHERE 1=1
AND lower(sql_text) LIKE '%select%from dual%';


   sql_id     |       sql_text      | hash_value
--------------------------------------------------
0x735fvggtnu6 | SELECT * FROM DUAL  | 3741111110
3vyt9wdmca69b | SELECT * FROM dual  | 1724193067


-- As you can see Oracle evaluated it as two different queries.

Once the hash is generated, Oracle checks if this query was already executed in this instance. How? By checking whether the hash already exists in the SQL Area.

Assuming that this query is not available yet, there will be an SQL area created with the hash value for this query and the Oracle will initiate the first stage of query processing and that is PARSE.

  • During the syntax check Oracle will check the query whether it is syntactically correct (SELECT instead of SELECT; the correct order of commands -> SELECT * FROM table ORDER BY col1 WHERE col2 = ‘John’; etc).
  • The next step is semantics checks where Oracle verifies whether the column names and object names are correct. How? By crosschecking them with Data Dictionary Cache
  • In the last step of PARSE stage, Oracle checks whether the user/application has correct permissions to access queried objects.

Once this is over, the SQL Area is valid and another tool in Oracle called OPTIMIZER will generate an execution plan – that means the OPTIMIZER will decide how the query will be executed. After the best execution plan is selected, Oracle binds all variables and proceeds to the third step – EXECution.

What is going to happen here? Oracle will read data blocks related to queried objects and bring them to buffer cache (if they are not presented there yet. If they are there, Oracle will not read them again!). This process will generate I/O (which, as I mentioned in the article Computing Architecture, is very slow compared to reading from the memory). I will stop here for a while and stress the I/O generation. In the case of soft parse, all data are read from the memory (buffer cache) which is much faster than reading them from a disk. That is why you have to strive to recycle/reuse your queries as much as possible. Every change of a query will generate a new hash and therefore will most probably generate I/O.  All of this is still handled by the server process.

Now, that the data are already in the memory (buffer cache) the SELECT statement is processed and the final stage (FETCH) is triggered and the result set is returned back to the user.

If the same query is executed again, the hash is generated and since the SQL Area for that hash already exists, PARSE stage is skipped and only EXEC and FETCH are executed.