Can “_first_spare_parameter fix the “cursor: pin s” issue?

The objective of this blog is to discuss the test results with _first_spare_parameter settings.  Please visit https://swervedba.wordpress.com/2011/03/ to understand  “cursor: pin s” issue.

So let’s get started with basics.

Oracle  development standards do not allow introducing new initialization parameters  with interim patches to fix bugs. So  “spare” parameters  are kind of reserve initialization parameters to fix bugs with interim patches. There are a lot of spare parameters like _first_spare_parameter , _second_spare_parameter and so  on

Using this parameter without Oracle support is s very dangerous as Patch 88888888  with spare setting of 1  may resolve bug ABC whereas Patch 99999999 with spare setting of 1 may resolve bug XYZ.

Please note that the value and meaning of the spare parameter changes with Oracle version.  As an example, _first_spare_parameter  setting of 50 was recommended for  streams in Oracle 9i.

Coming to  high CPU issue because of “cursor: pin  s” , the solution is to apply patch 6904068  and play with different values of _first_spare_parameter. The values for _first_spare_parameter is specified in centiseconds and the lowest value that can be set is 1 centisecond or 10ms .  When the patch and _first_spare_parameter is set ,  Oracle will sleep on CPU  for specified centiseconds when it cannot get mutexes

After applying patch 6904068 , we tested various configuration of  _first_spare_parameter to fix bug on 10.2.0.3 database.    Key points are

  1. Best results were with _first_spare_parameter setting of 1
  2.  Setting “_first_spare_parameter” to 0 is as good as not applying the patch as it reverts to default behavior without patch
  3. Querying x$ksppi for _first_spare_parameter before applying the patch returns null value but after applying the patch, _first_spare_parameter  defaults to 0. Off course this will depend on one-off patches applied in your environment
  4. Parameter “_first_spare_parameter” can be modified online
  5. All tested settings of this parameter other than 0 resulted in negligible performance degradation.
  6. The wait for the parameter ” _first_spare_parameter” did not work as intended even though the “cursor: pin s” issue was fixed.

See test results below

Advertisements

Tagged: , , , , , , , , ,

One thought on “Can “_first_spare_parameter fix the “cursor: pin s” issue?

  1. […] MOS note Database Hang due to Mutex Contention in 11g [ID 1292249.1] recommends “_first_spare_parameter”=10. This results in huge 0.1 sec timeout between spins. However, for all workloads I saw, the value 1 performs better. I will discuss the effectiveness of timeout choice in separate post. Look also on this results. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: