Oracle hard parse Vs soft parse

Here is a long winded answer, it is extracted in part from a new book coming out soon "beginning Oracle programming" that I collaborated on: Parsing This is the first step in the processing of any statement in Oracle. Parsing is the act of breaking the submitted statement down into its component parts ? determining what type of statement it is (query, DML, DDL) and performing various checks on it. The parsing process performs two main functions: o Syntax Check: is the statement a valid one? Does it make sense given the SQL grammar documented in the SQL Reference Manual. Does it follow all of the rules for SQL. o Semantic Analysis: Going beyond the syntax ? is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Do you have access to the objects ? are the proper privileges in place? Are there ambiguities in the statement ? for example if there are two tables T1 and T2 and both have a column X, the query ?select X from T1, T2 where ?? is ambiguous, we don?t know which table to get X from. And so on. So, you can think of parsing as basically a two step process, that of a syntax check to check the validity of the statement and that of a semantic check ? to ensure the statement can execute properly. The difference between the two types of checks are hard for you to see ? Oracle does not come back and say ?it failed the syntax check?, rather it returns the statement with a error code and message. So for example, this statement fails with a syntax error: SQL> select from where 2; select from where 2 * ERROR at line 1: ORA-00936: missing expression While this statement failed with a semantic error ? if the table NOT_A_TABLE existed and we had permission to access it, this statement would have succeeded: SQL> select * from not_a_table; select * from not_a_table * ERROR at line 1: ORA-00942: table or view does not exist That is the only way to really tell the difference between a semantic and syntactic error ? if the statement COULD have executed given the proper objects and privileges, you had a semantic error, otherwise if the statement could not execute under any circumstances, you have a syntax error. Regardless ? Oracle will not execute the statement for you! The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session. If it has ? we may be in luck here, we can skip the next two steps in the process, that of optimization and row source generation. If we can skip these next two steps in the process, we have done what is known as a Soft Parse ? a shorter process to getting our query going. If we cannot, if we must do all of the steps, we are performing what is known as a Hard Parse ? we must parse, optimize, generate the plan for the query. This distinction is very important. When developing our applications we want a very high percentage of our queries to be Soft Parsed ? to be able to skip the optimize/generate phases ? as they are very CPU intensive as well as a point of contention (serialization). If we have to Hard Parse a large percentage of our queries, our system will function slowly and in some cases ? not at all. The way this sharing of SQL in Oracle is accomplished is via the shared pool, a piece of memory in the SGA maintained by Oracle. We covered this topic in chapter 5 but will revisit is again in the context of processing a query. After Oracle parses the query and it passes the syntax and semantic checks ? it will look in the shared pool component of the SGA to see if that same exact query has already been processed by another session. Since it has performed the semantic check it has already figured out: o Exactly what tables are involved o That we have access to the tables (the proper privileges are there) And so on. Now, it can look at all of the queries in the shared pool that have already been parsed/optimized and generated to see if the work has already been done. Credit goes to the below website(s) : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082 Oracle SQL is parsed before execution, and a hard parse includes these steps: Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step) Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords. Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data. Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate. Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g). Create executable - Oracle builds an executable file with native file calls to service the SQL query. Oracle gives us the shared_pool_size parm to cache SQL so that we don't have to parse, over-and-over again.  However, SQL can age-out if the shared_pool_size is too small or if it is cluttered with non-reusable SQL (i.e. SQL that has literals "where name = "fred") in the source. What the difference between a hard parse and a soft parse in Oracle?  Just the first step, step 1 as shown in red, above.  In other words, a soft parse does not require a shared pool reload (and the associated RAM memory allocation). A general high "parse call" (> 10/sec.) indicates that your system has many incoming unique SQL statements, or that your SQL is not reentrant (i.e. not using bind variables). A hard parse is when your SQL must be re-loaded into the shared pool.  A hard parse is worse than a soft parse because of the overhead involved in shared pool RAM allocation and memory management.  Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated.  Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables. See the cursor_sharing parameter for a easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant. http://www.dba-oracle.com/t_hard_vs_soft_parse_parsing.htm

Hard parse vs. soft parse
		Hard parse If a session executes an SQL statement that does not exist in the shared pool, then Oracle has to do a hard parse. Oracle must then: Allocate memory for the statement from the shared pool. Check the statement syntactically Check if the user trying to execute the statement has the necessary rights to execute it A hard parse is expensive in both terms of CPU used and number of shared pool latch and library cache latch it needs to acquire and release. It should be avoided whenever possible. If event 10053 is set, this event will be triggered in a hard parse (not a soft parse). Soft parse If a session executes an SQL statement that exists in theshared pool and there is a version of the statement that can be used, then this is refered to as a soft parse. Identical Statements? A statement is identical to another statement, if there isabsolutely no difference between the letters. For example select x from y and SELECT X FROM Y are not identical, although they clearly do the same thing. Even if two statements are identical, this doesn't mean they are shareable. In order for two identical statements to be shareable, the following must be true Object names must resolve the same actual objects The optimizer goal is the same Types and length of bind variables is similar The NLS environment is the same Versions of statements If two statements are identical but not shareable, they have different versions of the statement. High version counts for sql statements should be avoided. The number of versions for a statement can be found in v$sqlarea: select sql_text from v$sqlarea where version_count > 1;

http://www.adp-gmbh.ch/ora/misc/hard_parse_vs_soft_parse.html

Whenever a statement is executed, Oracle follows a methodology to evaluate the statement in terms of syntax, validity of objects being referred and of course, privileges to the user. Apart from this, Oracle also checks for identical statements that may have been fired, with the intention of reducing processing overheads. All this takes place in a fraction of a second, even less, without the user knowing what is happening to the statement that was fired. This process is known as Parsing.

Types of Parsing

All statements, DDL or DML, are parsed whenever they are executed. The only key fact is that whether it was a Soft (statement is already parsed and available in memory) or a Hard (all parsing steps to be carried out) parse. Soft parse will considerably improve the system performance where as frequent Hard parsing will affect the system. Reducing Hard parsing will improve the resource utilization and optimize the SQL code.

Parsing process

Oracle internally does the following to arrive at the output of an SQL statement.

1. Syntactical check. The query fired is checked for its syntax.

2. Semantic check. Checks on 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.

3. Allocation of private SQL area in the memory for the statement.

4. Generating a parsed representation of the statement and allocating Shared SQL area. This involves finding an optimal execution path for the statement.

In point four, Oracle first checks if the same statement is already parsed and existing in the memory. If found, the parsed representation will be picked up and the statement executed immediately (Soft parse). If not found, then the parsed representation is generated and stored in a shared SQL area (Part of shared pool memory in SGA), the statement is then executed (Hard parse). This step involves the optimization of the statement, the one that decides the performance.

Identical statements

Oracle does the following to find identical statements to decide on a soft or a hard parse.

a. 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.

b. Next, the text string of the new statement is compared with the hash value matching statements. This includes comparison of case, blanks and comments present in the statements.

c. If a match is found, the objects referred in the new statement are compared with the matching statement objects. Tables of the same name belonging to different a schema will not account for a match.

d. The bind variable types of the new statement should be of same type as the identified matching statement.

e. If all of the above is satisfied, Oracle re-uses the existing parse (soft). If a match is not found, Oracle goes through the process of parsing the statement and putting it in the shared pool (hard).

The shared pool memory can be increased when contention occurs, but more important is that such issues should be addressed at the coding level. Following are some initiatives that can be taken to reduce hard parsing.

1. Make use of bind variables rather than hard-coding values in your statements.

2. Write generic routines that can be called from different places. This will also eliminate code repetition.

3. Even with stringent checks, it may so happen that same statements are written in different formats. Search the SQL area periodically to check on similar queries that are being parsed separately. Change these 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 at system level

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

Check for statements with a lot of executions. It is bad to have the PARSE_CALLS value in the above statement close to the EXECUTIONS value. The above query will fire only for DML statements (to check on other types of statements use the appropriate command type number). Also ignore Recursive calls (dictionary access), as it is internal to Oracle.

Identifying unnecessary parse calls at session level

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;

Identify the sessions involved with a lot of re-parsing (VALUE column). Query these sessions from V$SESSION and then locate the program that is being executed, resulting in so much parsing.

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;

The above query will also show recursive SQL being fired internally by Oracle.

4. 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 theSESSION_CACHED_CURSORS to a higher value to allow more cursors to be cached at session level and to avoid re-parsing.

Identify how many cursors are being opened by sessions

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;

The VALUE column will identify how many cursors are open 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.

Evaluate cached cursors for sessions as compared to parsing

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 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.

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%';

STATISTIC# NAME                           CLASS
---------- ------------------------- ----------
       217 parse time cpu                    64
       218 parse time elapsed                64
       219 parse count (total)               64
       220 parse count (hard)                64
       221 parse count (failures)            64

5. Shared SQL area may be further utilized for not only identical but also for some-what similar queries by setting the initialization parameter CURSOR_SHARING to FORCE. The default value is EXACT. Do not use this parameter in Oracle 8i, as there is a bug involved with it that hangs similar query sessions because of some internal processing. If you are on 9i, try out this parameter for your application in test mode before making changes in production.

6. Prevent large SQL or PL/SQL areas from ageing out of the shared pool memory. Ageing out takes place based on Least recently used (LRU) mechanism. Set the parameterSHARED_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.

7. Pin frequent objects in memory using the DBMS_SHARED_POOL package. This package is created by default. It can also be created explicitly by running DBMSPOOL.SQL script; this internally calls PRVTPOOL.PLB script. Use it to pin most frequently used objects that should be in memory while the instance is up, these would include procedure (p), functions (p), packages (p) and triggers (r). Pin objects when the instance starts to avoid memory fragmentation (Even frequently used data can be pinned but this is a separate topic).

To view 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;

To pin a package in memory

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

To view a list of pinned objects

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

8. 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. The size of the shared pool can be increased by setting the parameter SHARED_POOL_SIZE in the initialization file.

Conclusion

Reduce Hard parsing as much as possible! This can be done by writing generic routines that can be called from different parts of the application, thus the importance of writing uniform and generic code.

http://www.databasejournal.com/features/oracle/article.php/3341851/Parsing-in-Oracle.htm

http://www.orafaq.com/wiki/Parsing