Using “Hard Parse” vs “Soft Parse” in Oracle databases

Detection

When a query is run on an Oracle database, the first step of the process is known as parsing. In this step the statement is split into its components to determine what kind of statement it is and to check both its syntax and its semantics. Once this check has passed without errors, Oracle looks for whether the statement has already been processed by another session, by searching for it in the instance's SGA.

If the statement has already been processed and is in the SGA, this is a “soft parse”: the instance uses the execution and access plan generated earlier and goes on to find the rows the statement affects.

Otherwise, if the statement has not been processed before, this is a “hard parse”. The database instance must generate an optimised execution and access plan for the statement and then read the affected rows. A hard parse can be a performance problem, because it needs considerably more CPU than a soft parse. Although at least one hard parse is needed for each statement, too many of them will considerably increase the statement's CPU consumption and make possible performance problems harder to analyse, since the execution statistics of statements that can be considered the same will not be grouped together.

Excessive hard parsing is usually caused by using constant values in the statements' filters. With constants, the Oracle optimizer treats every statement to be run as a new statement and has to run the full parsing process. This bad practice can be solved by using bind variables: the optimizer then understands that the statement contains variable values and can consider two statements to be the same, so it can reuse plans already generated and group the execution statistics.

Example and results

Below is an example of a statement that searches for the values 1 to 10 in column “C1” of table “T1”:

• Using constant values:

SELECT * FROM T1 WHERE C1=1;
SELECT * FROM T1 WHERE C1=2;
….
SELECT * FROM T1 WHERE C1=10;

Here the statements are the same, yet the optimizer has to perform 10 hard parses, because it cannot tell that the value is variable: it generates an execution plan for each execution, and the execution statistics show the values separately, which makes it harder to track the statement's performance.

• Using bind variables:

SELECT * FROM T1 WHERE C1=:B1;

Here, by using bind variables, the optimizer knows that the value searched for in C1 is variable, so the plan generated can be used whatever value of C1 is searched for. In our example, a first hard parse is performed and the rest of the executions perform a soft parse. In addition, the execution statistics of this statement are grouped together, which makes it simpler to track its impact on the instance.

In the example above, the elapsed time per execution is 1,000 ms, with a CPU consumption of 600 ms, for the executions that perform a hard parse. By using bind variables and being able to perform soft parses, we estimate a 40% reduction in CPU use and a 24% reduction in elapsed time per execution. Bear in mind that a first hard parse is needed even when bind variables are used, so the total elapsed time for the 10 executions of the example falls from 10,000 ms to 7,840 ms, and CPU consumption falls from 6,000 ms to 3,840 ms.

In conclusion, if we are going to run a query repeatedly against the same table or tables, looking for different values, it is always advisable to use bind variables. That way, the Oracle optimizer can tell that the statements are the same, and we save the CPU consumed in creating an execution and access plan by reusing the plans already created.