Oracle hard-parse vs. soft parse

Oracle hard-parse vs. soft parse Oracle Tips by Burleson Consulting

Oracle SQL is parsed before execution, and a hard parse includes these steps:

1. Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step)
2. Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.
3. Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.
4. Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate.
5. Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g).
6. Create executable - Oracle builds an executable file with native file calls to service the SQL query.

Oracle Parsing:

Parsing is the first step in the processing of any statement in an Oracle database. The statements are broken down into its component parts and the type of statement whether DML, or DDL is determined and various checks are performed on it. A statement must be evaluated and validated before execution. Oracle evaluates the statements for syntax, validation of objects and the privileges assigned to user.

Types of Oracle Parsing:

There are two types of parsing in oracle

Soft Parsing	Hard Parsing
In soft parsing the statement is already parsed and available in memory.	In hard parsing all parsing steps are to be carried out.
Soft parse considerably improve the system performance.	Hard parsing affects the system. Reducing hard parsing improves the resource utilization and optimizes the SQL code.

Oracle Parsing Process:

Oracle parsing process follows below steps in order to execute the SQL statement and arrive at the output.

Syntactical check:

In syntactical check the query is checked for its syntax.

Semantic check:

In semantic check the query is checked for the validity of the objects being referred in the statement and the privileges available to the user firing the statement. This is a data dictionary check.

Allocation:

This step includes the allocation of private SQL area in the memory for the statement.

Generating Parsed Representation and allocation Shared SQL area:

In this step a parsed representation of the statement is generated and shared SQL area is allocated. This involves finding an optimal execution path for the statement. Oracle first checks if the same statement is already parsed and exists in the memory. If yes then soft parse will be done in which the parsed representation will be picked up and the statement will be executed immediately. However if the statement is not found then hard parsing will be done where the parsed representation is generated and stored in a shared SQL area and then the statement is executed.

Deciding the type of parsing:

Oracle does the following in order to decide on a soft parse or hard parse.

Comparing new hash value with existing hash value:

When a new statement is fired, a hash value is generated for the text string. Oracle checks if this new hash value matches with any existing hash value in the shared pool.

Comparing text string with hash value:

In this step the text string of the new statement is compared with the hash value matching statements.

Comparing referred objects with matching statements objects:

If a match is found, the objects referred in the new statement are compared with the matching statement objects. The bind variable types of the new statement should be of same type as the identified matching statement.

Deciding the parse type:

If all of the above is satisfied, Oracle either uses the soft parse by re-using the existing parse. However if a match is not found, Oracle follows hard parse and goes through the process of parsing the statement and putting it in the shared pool.

Reducing hard parsing:

Below is a guideline that can be taken to reduce hard parsing.

• Use bind variables instead of hard-coding values.
• Avoid code repetition.
• Write generic routines that can be called from different places.
• Avoid separate parsing of similar statements. Change such statements to be look-alike or put them in a common routine so that a single parse can take care of all calls to the statement.

Identifying Unnecessary Parse Calls:

Check for statements with a lot of executions. Avoid PARSE_CALLS value close to the EXECUTIONS value.

select parse_calls, executions, 
substr(sql_text, 1, 300) 
from v$sqlarea 
where command_type in (2, 3, 6, 7);

In the below code the sessions involve lot of re-parsing. Query these sessions from V$SESSION and then locate the program that is being executed, resulting in so much parsing.

select b.sid, a.name, b.value 
from v$sesstat b, v$statname a 
where a.name in ('parse count (hard)', 'execute count') 
and b.statistic# = a.statistic# 
order by sid;

select a.parse_calls, a.executions, substr(a.sql_text, 1, 300) 
from v$sqlarea a, v$session b 
where b.schema# = a.parsing_schema_id 
and b.sid = <:sid> 
order by 1 desc;

Provide enough private SQL area to accommodate all of the SQL statements for a session. Depending on the requirement, the parameter OPEN_CURSORS may need to be reset to a higher value. Set the SESSION_CACHED_CURSORS to a higher value to allow more cursors to be cached at session level and to avoid re-parsing.

Identifying the opened cursors:

The below code will help in identifying the open cursors for a session and how near the count is to the OPEN_CURSORS parameter value. If the margin is very small, consider increasing the OPEN_CURSORS parameter.

select a.username, a.sid, b.value 
from v$session a, v$sesstat b, v$statname c 
where b.sid = a.sid 
and c.statistic# = b.statistic# 
and c.name = 'opened cursors current' 
order by 3 desc;

Evaluating cached cursors for sessions:

The CACHE_CNT ('session cursor cache hits') of a session should be compared to the PARSE_CNT ('parse count (total)'), if the difference is high, consider increasing the SESSION_CACHED_CURSORS parameter.

select a.sid, a.value parse_cnt, 
(select x.value 
from v$sesstat x, v$statname y 
where x.sid = a.sid 
and y.statistic# = x.statistic# 
and y.name = 'session cursor cache hits') cache_cnt 
from v$sesstat a, v$statname b 
where b.statistic# = a.statistic# 
and b.name = 'parse count (total)' 
and value > 0;

The following parse related information is available in V$SYSSTAT and V$SESSTAT views, connect with V$STATNAME using STATISTIC# column.

SQL> select * from v$statname where name like '%parse%';

Utilizing Shared SQL area:

The shared SQL area can be further utilized for identical as well as some-what similar queries by setting the initialization parameter CURSOR_SHARING to FORCE. The default value is EXACT. Try out this parameter for your application in test mode before making changes in production.

Preventing Ageing out:

Set the parameter SHARED_POOL_RESERVED_SIZE to a larger value to prevent large packages from being aged out because of new entries. A large overhead is involved in reloading a large package that was aged out.

Pinning:

Pin frequent objects in memory using the DBMS_SHARED_POOL package. Use it to pin most frequently used objects that should be in memory while the instance is up. Pin objects when the instance starts to avoid memory fragmentation. Below code provides a list of frequently used and re-loaded objects

select loads, executions, substr(owner, 1, 15) "Owner", 
substr(namespace, 1, 20) "Type", substr(name, 1, 100) "Text" 
from v$db_object_cache 
order by executions desc;

In order to pin a package in memory and to view the list of pinned objects, use below syntax

SQL>exec dbms_shared_pool.keep('standard', 'p');

select substr(owner, 1, 15) "Owner", 
substr(namespace, 1, 20) "Type", 
substr(name, 1, 100) "Text" 
from v$db_object_cache 
where kept = 'YES';

Increasing the shared pool size:

The size of the shared pool can be increased by setting the parameter SHARED_POOL_SIZE in the initialization file. Increasing the shared pool size is an immediate solution, but the above steps need to be carried out to optimize the database in the long run.