Learn PL/SQL from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

Search in

How does a SQL Query Execute inside Oracle? What are the steps followed in the background?

Gavi Y.
05/01/2020 0 0


The above snapshot says it all. Hence it is said, 'Picture says it all'.

Basically, there are 4-5 steps. There are two possible ways of query being executed.
      1. Soft Parse-  Improves performance. This is also called as 'Library Cache Hit'.
      2. Hard Parse- Degrades performance. This is also called as 'Library Cache Miss'.

Let us understand the steps one by one briefly.
There is a parser inside Oracle, which parses the SQL Query for further processing.
The first 3 steps of SQL Query Execution are taken care of by the parser.

Step 1: Syntax check

       This checks only for syntax errors. A pure syntax check and nothing else.
Examples: 1. Are the keywords correct?
                      Like, 'seelect', 'form', 'groupby', 'orderby' are few common spelling mistakes.
                 2. Does SQL Query end with a semicolon (;)?
                 3. Are column names in the 'Select' Clause are separated by commas (,)?
                 4. Does 'Select' clause include only the colmuns which are present in the 'Group by' clause?
                 5. Does 'Group by' clause appear after 'Where clause'?
                 6. Is 'Order by' clause the last clause in the query?
                 etc. etc....

            In each of these cases, if the answer is 'No', oracles throws an error stating the same.

Step 2: Sematic chck

      Once the query passes the Syntax check, next step is Semantic check.
Here, the references of all objects present in the query are checked along with user privileges. The check happens against metadata maintained in the Oracle.
Examples: 1. Is table name valid or such a table exists?
                 2. Are columns names correct?
                 3. Does user have select/update/delete/insert/create privilege?
                 etc. etc. etc.

So during Syntax check, it doesn't check for the validity of table names, column names, privileges etc.

Let's say, I am running this... "select * from epm;" 

This passes Syntax check, though I am writing a wrong table name. Instead of "emp", I have written "epm". But, this query is fine syntax-wise and hence passes the Syntax check. But it fails in the next step of Semantic check where the object names are verified.

But we will not be able to notice whether a query failed at Syntax check or at Semantic check when we run a query from SQL*Plus or any other GUI. Because, everything is handled in one go from user point of view and the errors are sent if the Query fails at any step. Otherwise, we get the final output.

 Step 3: Shared pool check

This is an important check. I am planning to write a separte lesson on this. Let us understand briefly here.

Basically, once the query passed both Syntax check and Semantic check, it goes for third check called 'Shared pool check'

Just to put it briefly, 'Shared pool' is a memory segment present inside every Oracle instance, which retains the recently executed SQL and PLSQL code inside its 'Library Cache' based on LRU algorithm.

So, if parser finds that the query is present inside 'Library Cache' or 'Shared pool', then it is called 'Soft Parse'. It goes to step 5, executes the query and sends the output to whoever requested it.

As simple as that. But, if parser finds that such a query is not present inside 'Shared pool' or 'Library Cache', then it is called 'Hard Parse'. So, Oracle must carry out step 4 first to prepare the query and then go to step 5 finally to execute the query and then send the output to the requestor.

So, the first three steps are always carried out for every SQL Query and they are less expensive.

Step 4:

      The step 4 is very expensive or costly. Meaning, it consumes lot of resources to complete this, like CPU, I/O reads, physical reads, extra algorithms to generate mutliple queries and select the best one etc.

Hence, 'Hard Parse' degrades performance. We should keep this in mind always before writing any query. To put it simply for now, avoid hard coding and use bind variables to take the advantage of 'Soft Parse'.

Step 4 happens only in case of 'Hard parse', which is decided in step 3 as we discussed above.

We can split this into two sub-steps.
      a. Optimization: The Optimizer generates mutliple plans using various combinations of joins                      (Nested loop/Sort Merge/Hash), access paths (full table scan/index range scan/index skip scan                etc) and join orders.
          Finally selects the best plan based on cost. The lower the cost, the better the performance.

      b. Row source generation: Query execution plan is generated for the best query
          selected in the above step. This is what we see in Explain plan.
          We can further make modifications to the query, force the Optimizer to select an even lower cost            query by changing access paths, joins, join orders or using Hints. And we can verify if the                  execution plan got changed by again looking at its Explain plan.
         This is called 'Performance tuning' or 'Query Tuning'. Let's not go deep into it now.

None of this happens in case of 'Soft Parse' and hence improves performance.

Step 5: Query Execution

      Step 5 is again common for each query irresepctive of whether it is 'Soft Parse' or 'Hard Parse'. 

As we already discussed, it executes the SQL Query and sends the output
to the requested program or user.


So this is about it. To wrap up, there are two ways of execution or parsing namely- 'Hard parse' and 'Soft Parse'. And there are 5 steps totally. Steps 1 to 3 are common for every query or for each type of execution/parsing. Step 4 happens only for 'Hard Parse' way of execution and not for 'Soft Parse' way. Step 5 is the final and common step, which finally executes the SQL Query. Use Explain plan to check the execution plan selected by Optimizer and tune the query. Use Bind variables to enable 'Soft Parse'.

0 Dislike
Follow 1

Please Enter a comment


Other Lessons for You

Primary Key VS Unique Key In Database.
PRIMARY KEY UNIQUE KEY Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. Primary keys must contain unique values. Unique-key...

Looping In Oracle SQL Using Level
We will see about the syntax LEVEL in Oracle sql. If we want to loop through and print a set of values in SQL, LEVEL could come handy. Example query: SELECT 'hi', LEVEL FROM DUALCONNECT BY LEVEL <=...

DBMS (Database Management System) Vs RDBMS ( Relational Database Management System)
DBMS RDBMS Stored data in a file Stored data in a table As it is stored in a file, there is no relationship concept Data in one table may be relationship with data in another table...

SQL (Structured Query Language)
SQL (Structured Query Language) is a standardized programming language used for managing relational databases and performing various operations on the data in them. Initially created in the 1970s, SQL...

A chart is a set of coordinates When you make a chart you start with an empty, two-dimensional space, a vertical dimension (y) and a horizontal dimension (x) . You also have a data source. Your job is...

Looking for PL/SQL Classes?

Learn from Best Tutors on UrbanPro.

Are you a Tutor or Training Institute?

Join UrbanPro Today to find students near you

Looking for PL/SQL Classes?

The best tutors for PL/SQL Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn PL/SQL with the Best Tutors

The best Tutors for PL/SQL Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more