Cursor S Pin Waits & High CPU utilization (Oracle 10g)

We are running a Mission Critical 24x7 (10.2.0.5) on HP-UX platform a stand alone HP Itanium server. One morning when everything else was running smoothly, we were alerted of connection requests timing out on this database.
We were already facing a lot of Network/Firewall issues, so we thought it might be the network. As a formality, I tried to login into the server with the intention to check the incoming sessions traffic in the listener logs. But to my surprise, my connection request was taking toooo long by the server and then timed out. I got in touch with the System Admin and he was able to get a session into the server. We saw that the whole server was in a hanged or in an extremely slow state!
On checking for the top resources, we found that the Oracle processes were utilizing 100% CPU, hence causing the hang. I was then lucky to have a session of OEM opened on that database, and saw the top wait event was "Cursor S Pin" related waits. I later checked that there were more than 700 sessions waiting for mutex to be released. As it became almost impossible to execute any more commands, it was decided to bounce the server to get the services back online.
Till then we were not sure whether the problem was in the hardware, the OS or the database. The only clue was:
1. 100% CPU utilization in the server
2. An AWR report, which just "luckily" completed before the Server Reboot !

  Top 5 Timed Events Event Waits Time(s) Avg Wait(ms) % cursor: pin S 71,298,824 321,773 53.2 latch: library cache 527,845 67,211 11.1 latch: row cache objects 408,606 44,486 7.4 cursor: pin S wait on X 764,754 15,212 2.5 CPU time 15,054 2.5

I checked the metalink, and found the exact same symptoms for a bug: Bug 6904068 - High CPU usage when there are "cursor: pin S" waits [ID 6904068.8] Applied the patch at midnight, and haven't had the issue since then.

Basically what is happening is that a session tries to get the mutex (kind of a latch) in S mode, but is unable to, and immediately yields so another session can come in and request it, and this can start causing excessive CPU usage and cause extreme performance degradation or hangs. This is a very common bug when the "cursor: pin S" wait is seen.

Applying this patch allows you to set _first_spare_parameter to wait for a fixed time instead of yielding when trying to obtain the mutex in S mode and there is no X holder.

Oracle 11g: OER 7451 in Load Indicator

I installed Oracle 11g R1 on my laptop running Windows 7 Enterprise Edition (x64 bit).
As I was working on it, i felt like there was something wrong - it was too slow.
I shutdown the database, and shot the startup command, but got stuck on "Mounted".
Although I was able to get it started a minute later by stopping and starting the Services (services.msc).
I checked the alert log for any errors and found the following was filling up my alert log:

Thu Sep 08 21:00:41 2011
Errors in file e:\oracle11g\app\diag\rdbms\rizwan\rizwan\trace\rizwan_j008_8896.trc:
ORA-12012: error on auto execute of job 11689
ORA-01403: no data found
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:00:47 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:01:07 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:01:09 2011
ALTER TABLESPACE example READ WRITE
Completed: ALTER TABLESPACE example READ WRITE
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:01:17 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !

After checking a document on MOS, I found out what the mistake was. I was running a 32-bit Oracle Software on my 64-bit OS.

I fixed it by uninstalling the 32-bit software and installing the 64-bit software.

"ORA-01652: unable to extend temp segment by 128 in tablespace TEMP" encountered while creating new table using Create Table As Select (CTAS)

Recently, in a SQA database I noticed something... CTAS was using TEMPORARY Tablespace!

In SQA, we have 4 copies of the same database, we call them 'Regions' - namely I,P,Q,R. Each region is used for a different testing stage. In region R, the SQA was testing on a Housekeeping Solution on a large table (85 GB) to purge 6 months data. Something went wrong, and they requested us to get a copy from another Region (Q) where this release was not tested.

It was decided to use the option of CTAS, rather than export/import due to storage space constraints. This was to be done using DB Link to the Q Region's Database. When the command was executed, we ran into
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP. 

12:25:35 SQL> create table GECT
12:25:35   2  TABLESPACE TS_GECT
12:25:35   3  PCTUSED    0
12:25:35   4  PCTFREE    10
12:25:35   5  INITRANS   10
12:25:35   6  MAXTRANS   255
12:25:35   7  STORAGE    (
12:25:35   8              INITIAL          100M
12:25:35   9              NEXT             100M
12:25:35  10              MINEXTENTS       1
12:25:35  11              MAXEXTENTS       UNLIMITED
12:25:35  12              PCTINCREASE      0
12:25:35  13              BUFFER_POOL      DEFAULT
12:25:35  14             )
12:25:35  15  NOLOGGING
12:25:35  16  PARALLEL
12:25:35  17  AS
12:25:35  18  (SELECT * FROM FNSONLP.GECT@GECT_LINK);
create table GECT
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

It didn't make any sense! CTAS should not use TEMPORARY Tablespace unless some sort was to be performed.

I did a little digging on Blogs and MOS, but the best I could find was:

DURING CTAS (CREATE TABLE AS SELECT) ORA-1652 REPORTED ON THE DATA TABLESPACE [ID 577643.1]

But the document or blogs only speaks about ORA-1652 on the "data tablespace", not the "temporary tablespace."

Solution: 

I removed the clause "PARALLEL" from my CTAS statement. As soon as I did that, the target tablespace started filling up, and my CTAS was successful.

How to stop access using "/ as sysdba"

A friend of mine wanted to secure his client's database access by restricting the DBAs' connecting to the database as 'SYS' user by using "/ as sysdba".

In the file sqlnet.ora, located in $ORACLE_HOME/network/admin folder, add the following line:

SQLNET.AUTHENTICATION_SERVICES=(NONE)

Try logging in again by using "/ as sysdba":

No doubt this will prevent the access of "/ as sysdba" when connected as the 'oracle' user (oracle owner account) but the DBA can easily comment the configuration parameter in SQLNET.ORA - if he has the required permisssion on the file.
To avoid this, change the ownership of the sqlnet.ora file to 'root' or any other functional OS user, and provide a read permission to dba/oinstall group.

chown root:oinstall sqlnet.ora
chmod 640 sqlnet.ora