Monthly Archives: May 2011

Oracle Patch sets , PSU and CPU

Most Popular, Oracle May 30, 2011 Comments: 21

If you are new to Oracle or lost in 1000s of  pages  of Oracle documentation, then these terms patch set , PSU or CPU is sure to create some kind of confusion.

Based on my experiences with 11gR2, looks like PSU is the way to go.   For example  we  applied PSU3 to resolve mutex issues since there was no one-off fix  w/o  the PSU route.  I also thought that PSUs were more flexible than I initially assumed because  when we encountered another bug(10190759), the bug fix was available only for  11.2.0.2  (w/o PSU 3).  Oracle still recommended applying this patch to 11.2.0.2.3(with PSU3)  and it worked.

Here is brief  summary of what is the difference between patch set , PSU and CPU. I will start with a image, might not be 100% accurate but will help a long way in understanding the differences.

Patch sets and PSU

PSU’s(Patch set updates) are patch sets but with some major differences with respect to regular patch sets.

PSUs are generally low risk and do not contain fixes to bugs that require configuration changes or impact database components like optimizer. To achieve this low risk behavior, Each PSU is limited from 25 to 100 new bug fixes. PSU’s are also well tested by Oracle compared to one off patches.

PSUs are referenced by their 5th place in the Oracle version numbers which makes it easier to track ( (e.g. 10.2.0.3.1) and will not change the version of oracle binaries (like sqlplus, exp/imp etc.) As of now (10.2, 11,2) , the best way to  determine the PSU version installed  is to use opatch utility using the command below

opatch lsinv -bugs_fixed | grep -i  PSU

From the database , you can execute the following query(Assuming that catbundle.sql  was executed when PSU was installed). The last 2 columns will provide information about the PSU.

select substr(action_time,1,30) action_time, substr(id,1,10) id, substr(action,1,10) action,substr(version,1,8) version,
substr(BUNDLE_SERIES,1,6) bundle, substr(comments,1,20) comments from registry$history;

In summary PSU’s  contain fix for bugs that cause

  • Instance crash
  • Wrong results
  • Data Corruption

PSU’s  do not contain fix for bugs  that may result in

  • Dictionary changes
  • Major Algorithm changes
  • Architectural changes
  • Optimizer plan changes

Regular patch sets on other hand include major bug fixes and most of the time requires re-certification. The importance of PSU is diminished once a regular patch set is released for a given version as they tend to be more security related fixes as CPU is part of PSU

PSU and CPU

PSUs  contain CPU’s and are released every quarter (like CPU’s) ; In other words  Critical Patch Update (CPU) is a subset of the Patch Set Update (PSU). CPU’s are built on the base Patch Set version (e.g. 10.2.0.3) whereas PSU are built on the base of the previous PSU (e.g. 10.2.0.3.1)

A PSU can always be applied over any CPU where as applying a CPU over a PSU will roll back the PSU (at least for now). Therefore it is easy to go from CPUs to PSUs and hard to go back to CPUs  from PSUs.

Simple explanation of “cursor: pin S wait on X”

Most Popular, Oracle May 21, 2011 Comments: 7

I think it is very  difficult if not impossible to  understand “cursor: pin S wait on X” without the knowledge of “cursor: pin S“.  So this blog will start with “cursor: pin S” along with basics of mutex.

Also see my blog about “cursor:pin s”  with more detailed info https://swervedba.wordpress.com/2011/03/30/cursor-pin-s/

Oracle introduced Mutexes in Oracle Version 10.2 and they stand for mutual exclusion (functionality is similar to mutexes in c).  Mutexes are used to protect data or other resources from concurrent access.

Working of Oracle Mutexes

Oracle uses counters  in implementing mutexes.  The counters are called as reference counters

1. Every time a particular mutex is obtained, Oracle will increment its value.

2. Every time a particular mutex is released, Oracle will decrement its value.

Cursor Pin S : A wait even that occurs when a session wants a specific mutex in shared mode on a specific cursor. As mentioned in the previous section,  Oracle has to update the ref counters  to get the mutex

However it is very important to understand that access to these counters are not concurrent. If there are concurrent sessions trying to obtain a mutex, then only one session can actually increment or decrement the reference counter at a time. Therefore concurrent sessions requesting the mutex must wait. This wait is called as “cursor: pin s” wait.

In short

“cursor: pin s” wait event occurs when Oracle wants a specific mutex in shared  mode on a specific cursor but could not obtain it as it was not able to update the reference counters (Not because another session was holding  the mutex in exclusive mode) .

“cursor: pin S wait on X” wait event occurs when Oracle is not able to get the mutex  in share(S)  mode  because some other session is  holding the  same mutex in  exclusive(X) mode.

Sharing Tablespaces between Databases With Transportable Tablespaces

Oracle May 21, 2011 Leave a comment

Sharing tablespaces between databases for data refresh process  has been there for long time using transportable tablespace.  Oracle officially supports this process  from 2010. See metalink document 90926.1  for details.

Following  are the steps involved using EMC BCV Snap/Clone and Transport tablespace

Note: EMC BCV Snap/Clone is optional and required only if the live and archive database reside on different host.  BCV snap is primarily used to clone the live database and  present the data to different host.

1. Use EMC to make  copy of live database from different host.

2. Use  transport tablespace to transport required tablespace to  archive database.  The process is same as regular transport tablespace, only difference being that you are avoiding the time consuming copy process. Instead the read-only files are shared between 2 database.

3.  Use custom scripts to refresh the data

4.  Drop the tablespace from archive database  after data refresh  (Without the INCLUDING DATAFILES OPTION);

5.  Repeat  steps 1-4 for daily refresh

Requirements

1. Tablespaces  that are transported must be READ-ONLY

2.  Databases that must be shared the tablespace must reside on same host

3.  Enabling and disabling of constraints may be required.

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

Oracle May 21, 2011 1 Comment

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

CURSOR_SHARING=SIMILAR is deprecated

Oracle May 14, 2011 1 Comment

Oracle introduced CURSOR_SHARING option for legacy applications that were not written to use bind variables. This was more of temporary fix. Oracle assumed that customers/vendors would rewrite their applications to use bind variables. The assumption seems to be proving wrong; Today CURSOR_SHARING has become more of a positive feature and solution for all shared pool issues.  This is not true. As Per Oracle Metalink document 957431.1, Oracle recommends setting CURSOR_SHARING to EXACT. The consequences of not setting this parameter to EXACT are following:

  1. Errors with cache algorithm;
  2. Migration issues when upgrading from 9i to 10g or higher;
  3. Bugs related to cursor sharing.

Until Oracle 11gR1, Oracle limited the number child cursors for a given parent to 1024. Once this number is reached, Oracle invalidated the parent and all related child cursors. The 1024 limit was introduced to avoid performance issues associated with soft parsing. In other words hard parsing performs better than soft parsing when there are too many dependent child cursors.

With 11gR2, Oracle removed the 1024 limit knowing that there are issues with soft parsing. The reasoning behind this is the improvements made to adaptive Cursor Sharing feature.  However adaptive Cursor Sharing does not work well with CURSOR_SHARING=SIMILAR as it is very restrictive and overrides improvements made to adaptive cursor sharing . Therefore it will be deprecated in Oracle version 12.

Compare Schemas or Tables ?

Oracle May 14, 2011 Leave a comment

In Oracle 11g ,  a new package called DBMS_COMPARISON  lets  you compare data between  2 objects.  So now , you don’t have to write and test your home grown scripts.  DBMS_COMPARISON is provided mainly to detect or monitor data inconsistencies between database objects in  streams or GoldenGate. environment  This package lets you compare and converge data   for tables , materialized views , table synonyms and views (single table view only).

Once the comparison of database object is complete, you can get data inconsistencies report from data dictionary views like  DBA_COMPARISON, DBA_COMPARISON_COLUMNS,DBA_COMPARISON_SCAN, DBA_COMPARISON_SCAN_SUMMARY, DBA_COMPARISON_SCAN_VALUES and DBA_COMPARISON_ROW_DIF

Refer to Oracle Documentation for more information : http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_comparison.htm

SQL*Plus and Performance….What ?

Oracle May 14, 2011 Leave a comment

Most us tend to believe that SQL*Plus is  tool from where  you issue SQL commands.   There is more to it. In one of the recent  projects where our spool file was 75GB, tuning LINESIZE and ARRAYSIZE helped us shave 2 hours.

Significance of LINESIZE
  1. If the LINESIZE is too big, then more memory is allocated resulting in more memory copying and impacting  performance significantly
  2. If the LINESIZE is too small, then columns that do not fit in one single line are put on separate line(s) and impacting  performance significantly
 Significance of  ARRAYSIZE
  1. Reduce number of SQL*net round trips.
  2. Reduce the number of Logical IO’s or consistent gets (Laws of diminishing returns come into picture)
  3. Huge performance benefits if the network latency is high.
  4. TCP packets size also influences the performance benefits.
  5. Value of 100-300 provides good performance generally

Importance of AIXTHREAD_SCOPE in Oracle

Oracle May 14, 2011 Leave a comment

Lets start with basics

A process can spawn multiple threads with each  thread executing performing different task concurrently. The advantages with threads is that they require less system resources and start  easily when compared to processes.

Generally a process can run  in 2 modes

  1.   Kernel mode or  system mode
  2.  User mode:

Kernel mode : There is a 1-1 relationship between kernel and thread. Execution of process in kernel mode  provides the thread with  complete and unrestricted access to the underlying hardware.  Thereby improving the performance of the  process.

User mode: There is no dedicated kernel process and many to many relation ship exist between kernel and  thread.  The user mode is less expensive and support less concurrency compare to kernel mode.  Scheduling is done by user scheduler

AIXTHREAD_SCOPE

On  AIX the variable AIXTHREAD_SCOPE specifies if Oracle processes will run in kernel mode or user mode. That is whether a thread has  process wide contention(P)  or system wide contention(S). The default is P or process wide contention.

Process wide Scope
When Oracle starts a thread with  process wide scope, the following occurs:

1. No dedicated kernel thread, Many to Many relation ship exist between kernel and thread.
2. Placed in  run queue for CPU intensive system
3. Subjected  to  time slicing by the user scheduler.

System Wide Scope
When Oracle starts a thread with system wide scope,  the thread is bound to the kernel and scheduled by the kernel.  There is a 1-1 relationship between kernel and thread. In other words, the kernel thread is not shared with any other user thread.  As you are aware , execution of process in kernel mode  provides the thread with  complete and unrestricted access to the underlying hardware.  Therefore improving the performance of the  process.

Oracle recommends system wide scope.

References : IBM Documenation , Oracle Metalink