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.

This is one of the post in Performance Tuning Fundamentals Series. Click on below links to read more posts from the series:

Performance Tuning Basics 1 : Selectivity and Cardinality
Performance Tuning Basics 2 : Parsing
Performance Tuning Basics 3 : Parent and Child Cursors
Performance Tuning Basics 4 : Bind Variables
Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
Performance Tuning Basics 9 : Optimizer Mode
Performance Tuning Basics 10 : Histograms
Performance Tuning Basics 11 : Steps to analyze a performance problem
Performance Tuning Basics 12 : Dynamic Performance Views
Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
Performance Tuning Basics 14 : Active Sessions History (ASH) Basics
Performance Tuning Basics 15 : AWR Report Analysis
Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)

• Author
• Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Latest posts by Brijesh Gogia (see all)

• Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
• Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
• Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021