Problem solve Get help with specific problems with your technologies, process and projects.

What is CPU parse overhead?

What is CPU parse overhead? What should be done to improve server performance if it is high?

What is CPU parse overhead and if it is high, what does it indicate? What should be done to improve server performance if it is high?
Whenever you submit any SQL statement to the database, that SQL statement must be parsed and an execution plan determined before Oracle can begin to fetch results. Once Oracle has done all of this work, Oracle saves all of this information in the SQL area of the shared pool. That way, if another user submits the exact same SQL statement, Oracle does not have to go through all of the same work. It can use the results it has saved in the shared pool.

There are two common reasons for the parse overhead to be high. One is lack of bind variables. The other is a shared pool that is sized too small. As a quick tutorial for bind variables, consider these two SQL statements:

SELECT * FROM emp WHERE empid=1001;
SELECT * FROM emp WHERE empid=1002;

Notice that they are identical except for the value we are looking for. To Oracle, these are two completely different SQL statements which must be individually parsed. However, let's replace the literal value with a bind variable:

SELECT * FROM emp WHERE empid=:b1;

The first user binds the value '1001' to the variable ':b1'. The second user binds the value '1002' to this variable. Now the SQL statement looks identical to Oracle and they can share the same parsing in the shared pool. The details on how you implement bind variables vary depending on your application development platform.

If the shared pool is too small, then when a user comes back a few minutes later to reissue the same SQL statement, all the work Oracle previously did may be aged out of the shared pool (to make room for other SQL statements). To determine if the shared pool is too small, issue the following query:

SELECT * FROM v$sgastat WHERE pool='shared pool' AND name='free memory';

If the amount of free memory being reported is consistently less than 1 or 2 MB, then your shared pool is too small. Consider increasing the SHARED_POOL_SIZE parameter.

Dig Deeper on Oracle database design and architecture