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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.