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
Originally posted by dbafreak I agree with "mbe". I want to know how literal strings have poorer performance as compared to bind variabled. Uday, please explain us the intricasy.
Ideally you would want the oracle to use prepared plans for similar queries so that we can save on the parse time which involves precious resources ( shared pool and latches ) ..
But when we use string literals in a query .. each such query is assumed to be a different query and for each query parsing is done (costly) and then execution .
So you should always try to use prepared statements whenever string literals are involved that way a same plan is used .
Now there are some cases when you can not have prepared statement for example when the parameter is not just one but could be many .. ie someif in ( 'xyz','abc',...) so on ..
When its the above case there are several options available in oracle 8.1.6 onwards where dynamic binding is possible .. (cursor_sharing option) in other words .. oracle binds the string literals for you ... now these options will have to be carefully used as each has its set of adv and disadv ..