Soft Parse

Hi All,

When I was going through some stuff online, I have found that soft parse's reduces the scalability of an system and a SQL statement should be more likely parsed once and executed many times. I am kind of confused over here, soft parse will always consume less resources and less time-memory latches which is a good thing and more over many dba's expect the session connecting to database issue sql statements that are already present in shared pool, so can you please explain why many soft parses would reduce the scalability of the system. Thanks in advance for your help.

You are correct when you say soft parses are better than hard parses nevertheless a soft parse would do syntax and security checks which is a time consuming process - not as bad as a hard parse, of course.

If its a hard parse, then it needs to go through different checks, right from the syntax of the statement to the availability of the object in question and to the privileges of the user running the statement, making it cpu intensive. If a statement is found the shared pool, then it takes all these information from there skipping the above steps, making it use lesser cpu. Despite all the these good things, if a statement remains in shared pool, it increases the number of LATCH there. As the latches serialized its bound to wait, which can decrease scalability of your application as the number of transaction increases.

HTH

though it is simple but I would have never been able to relate it with scalability of application. Great gtcol!

Soft parse is better than hard parse in all type of resource consumption.

Great response, thank you fellows for your replies. I would also like to share that when session_cached_cursors set, soft parsing can be reduced, resulting in reducing the cpu overhead for parsing the sql already present in shared pool.