If a new SQL statement is issued which does not exist in the shared pool then this has to be parsed fully. Eg: Oracle has to allocate memory for the statement from the shared pool, check the statement syntactically and semantically etc... This is referred to as a hard parse and is very expensive in both terms of CPU used and in the number of latch gets performed.
Hard parsing happens when the oracle server parses a query and cannot find an exact match for the query in the library cache. This occurs due to inefficient sharing of SQL Statements and can be improved by using bind variables instead of literals in queries. Some times Hard parsing causes excessive CPU usage
To start analyzing your database first understand how your SGA is laid out. You can get the values from server manager by using the \"show sga\" command or by running a query \"select * from v$sgastat;\"
A simple sql statement can be used to find the statements that are similar but are being parsed due to the use of Literals in the statement.
\"select substr(sql_text,1,50), count(*) from v$sql
group by substr(sql_text,1,50) having count(*) > 25\"
The above query would return the statements that have the same first 50 characters and have more than 25 occurences in the shared pool. Adjust the lenght of the statement based on your application.
You can now go back to your application team and if its a java application, encourage them to use prepared statements so that your Shared Pool is not always full. They may also consider using bind variables rather than explicitly specified constants in your statements whenever possible.
Hope that helps you find problems that you never knew existed.
Found this interesting feature of 8.1.6 on metalink. Enjoy
[Edited by uday on 04-11-2001 at 12:06 PM]