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:
- Validate the Syntax
- Validate the objects being referenced in the statement
- Privileges assigned to user executing the Job
- Verifying if statement is already available in the shared pool. Oracle engine will calculate the hash value for the SQL statement and look in
- If statement is not already present then allocate shared memory and create a cursor in shared pool
- Generate the Execution Plan
TYPES OF PARSES
- 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’.
- 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:
- Generation of an execution plan is a very CPU-intensive operation.
- 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)
Latest posts by Brijesh Gogia (see all)
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020
Simple and short.. Marvelous…!
Thank you for the great examples on Performance Tuning.
Hope you will share more such Posts.
Thank You
Very good Explanation!
Excellent
Simple, yet concise. Thank you!
phenomenal.,