Sometimes, we experience hard parsing and more CPU utilization issues in database. In an ideal world, literally there should be no hard parsing in a database. But in reality, it's not possible to completely overcome this. To parse a SQL, CPU cycles are needed. So, more hard parsing means more CPU utilization. Oracle parse a query before fetching the actual data from database.
Parsing are of two types -
Hard parsing: When bind variables are not used, oracle thinks every query is new to it.
Soft Parsing: When bind variables are used, still oracle parse (which is called soft parse) the query if it is in memory, it requires very less CPU.
The hard parsing should not be more than 1-2% for a well-tuned application.
Parsing are of two types -
Hard parsing: When bind variables are not used, oracle thinks every query is new to it.
Soft Parsing: When bind variables are used, still oracle parse (which is called soft parse) the query if it is in memory, it requires very less CPU.
The hard parsing should not be more than 1-2% for a well-tuned application.
In this situation, most of the time we need to deal with the settings of CURSOR_SHARING parameter - the values availabe to use for this parameter are EXACT, FORCE and SIMILAR.
I was reading some articles other than Oracle's own docs. Here I am presenting some basic concept of CURSOR_SHARING parameter settings in Oracle database.
There are three modes -
EXACT --> The default setting is EXACT. In this case, cursor for SQL will only be shared if bind variables are used in the query. If literals are used in the query, each time the the query will be parsed. It forces us to use bind variable in the application otherwise performance impact would be obvious.
FORCE --> If the setting is FORCE, literals in the query will be overwritten and cursor for the query will be shared. But in this case, the execution plan will be fixed and not be changed.
SIMILAR --> This feature provides the combined capabilities of EXACT and FORCE. In this case, literals will be replaced first and also there will be scope to change the plan for any new bind values - that means the plan will not be static.
The setting FORCE or SIMILAR depend on the necessity of the application. Tom nicely described in his article -
No comments:
Post a Comment