Skip to content

Performance Tuning Basics 2: Parsing

From performance tuning perspectives, it is very important to understand the concept of parsing. Parsing is primary phase in the SQL execution. It is followed by other stages: Execute and Fetch.

PARSING BASICS:

Whenever a SQL statement is executed, Oracle Engine performs below actions on it:

  1. Validate the Syntax
  2. Validate the objects being referenced in the statement
  3. Privileges assigned to user executing the Job
  4. Verifying if statement is already available in the shared pool. Oracle engine will calculate the hash value for the SQL statement and look in
  5. If statement is not already present then allocate shared memory and create a cursor in shared pool
  6. Generate the Execution Plan

TYPES OF PARSES

  1. HARD PARSE: It means that statement is not available in shared memory or this is a brand new statement that user is trying to execute. If your shared pool is small then also hard parse may be required as the old statement got aged out the shared pool. All the steps mentioned above for parsing need to be done for this situation. Hard parse requires extra system resources. This is also known as ‘Library Cache Miss’.
  2. SOFT PARSE: It means that statement was executed earlier and was already parsed and is available in memory. So Oracle need to do steps 1-3 only as mentioned above since rest of the tasks were already done earlier. It is like work hard once and reap benefits multiple times. This is also known as ‘Library cache Hit’ as you got the statement parsed and available to use in the Library cache.

 

WHY HARD PARSES SHOULD BE AVOIDED

There are two key reasons why hard parses should be kept to bare minimum required:

  1. Generation of an execution plan is a very CPU-intensive operation.
  2. Memory in the shared pool is limited and also memory operations are serialized. Memory operations happens using shared pool latches and if so many hard parses are happening then other processes in the database will have to wait in queue to get the shared pool latch. So hard parse impacts both umber of shared pool latch and library cache latch.

 

[Post Views: 96]

Read Offline: PDF Download

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

One Comment

  1. Vishal Vishal

    Simple and short.. Marvelous…!

Leave a Reply

Required fields are marked *