Dispatch /

VMware vCenter Server Database CURSOR_SHARING Init Parameter on Oracle 11.2

01 Nov 2013

Earlier this week one of my customers contacted me regarding the recommended value for the init.ora CURSOR_SHARING parameter for this vCenter Server database. Since the database had been performing poorly and they raised a case with Oracle, but wanted our input before taking any action.

The CURSOR_SHARING parameter on the Oracle 11.2 database was set to ‘SIMILAR’ and Oracle noted the following:

The problem was that before 11gR2 CURSOR SHARING parameter had 3 parameters:

CURSOR_SHARING = { SIMILAR | EXACT | FORCE }

As of 11gR2 CURSOR SHARING parameter has just two parameters when ‘SIMILAR’ was deprecated:

CURSOR_SHARING = { EXACT | FORCE }

So value should be set for the parameter in the vCenter Server database init.ora?

In general, the option to use the CURSOR_SHARING value of ‘FORCE’ is to help applications with mostly non-binding parameter queries while the use of ‘EXACT’ reduces the overhead of finding similar SQL statements in the pool.

VMware does not have a published recommendation for this setting since the parameter values are provided by Oracle.

However, when speaking with our engineers they mentioned that although the vast majority of the SQL statements use bind variables in the vCenter Server database, the most frequently run statements do not use bind variables (e.g. VPX_VM , VPX_DVS*, VPX_HOST tables). Bind variables are use for queries with low cardinality (candidates like bitmap value, fixed lookup, etc) are not used.

As a result, consider setting CURSOR_SHARING to ‘FORCE’ due to deprecation of the ‘SIMLAR’ value post Oracle 11gR2 and because the most frequent SQL statements in use within vCenter Server do not use bind variables.

Then again, I prefer Microsoft SQL Server myself…


Twitter Facebook